November 22, 2011 at 10:42 am
Hello everyone,
I'm running an openquery with stored procedure sp_msforeachdb and get an error which is difficult to understand:
Msg 208, Level 16, State 1, Procedure sp_MSforeach_worker, Line 102
Invalid object name '#qtemp'.
Here is the query:
declare @sql nvarchar (1000)
declare @port nvarchar (5)
,@linkserver nvarchar (10)
set @port = 1433
set @linkserver = 'srvr12'
set @sql = N'INSERT dbo.db_ROLES
SELECT *
From OPENQUERY ([' + @linkserver + ',' + @port + '],
''SP_MSFOREACHDB
''''SELECT
@@SERVERNAME AS servername
,DB_NAME() AS dbName
,USER_NAME(memberuid) AS Srvr_lvl_login
,USER_NAME(groupuid) AS dbRole
FROM sysmembers
'''''')'
EXEC (@SQL)
Where did "Procedure sp_MSforeach_worker" come from? Any idea where to begin solving this? Any and all help will be greatly appreciated as always!
john
November 22, 2011 at 11:35 am
Looks like sp_MSforeach_worker and #qtem come form the code in the prcedure sp_msforeachdb...
November 28, 2011 at 4:06 am
applebyte (11/22/2011)
Hello everyone,I'm running an openquery with stored procedure sp_msforeachdb and get an error which is difficult to understand:
Msg 208, Level 16, State 1, Procedure sp_MSforeach_worker, Line 102
Invalid object name '#qtemp'.
Here is the query:
declare @sql nvarchar (1000)
declare @port nvarchar (5)
,@linkserver nvarchar (10)
set @port = 1433
set @linkserver = 'srvr12'
set @sql = N'INSERT dbo.db_ROLES
SELECT *
From OPENQUERY ([' + @linkserver + ',' + @port + '],
''SP_MSFOREACHDB
''''SELECT
@@SERVERNAME AS servername
,DB_NAME() AS dbName
,USER_NAME(memberuid) AS Srvr_lvl_login
,USER_NAME(groupuid) AS dbRole
FROM sysmembers
'''''')'
EXEC (@SQL)
Where did "Procedure sp_MSforeach_worker" come from? Any idea where to begin solving this? Any and all help will be greatly appreciated as always!
john
SP_MSForEach_Worker is the internal stored procedure & #qtemp is the temporary table which may have been created during the execution of the SP to hold the temporary results.
However, the first thing is, OPENQUERY does not support multiple result sets whereas SP_MSForEachDB returns one result set per database :hehe:. So you need to adjust your query so that it returns only single result set after the execution.
November 28, 2011 at 10:57 am
Makes sense about OPENQUERY not supporting multiple results. Thanks.
November 29, 2011 at 1:58 pm
Here is the functioning re-worked openquery.
If anyone is wondering why openquery is used instead of 4-part naming, it's because half of our servers use NTLM authentication instead of Kerberos.
Thanks for everyone's help.
DECLARE @sql nvarchar (1000)
,@port nvarchar (5)
,@linkserver nvarchar (50)
,@dbname varchar(80)
,@sqlQuery VARCHAR(8000)
,@finalQuery VARCHAR(8000)
DECLARE Cursr_Srvr_port_db CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR SELECT DISTINCT
A.servername
,A.port_nbr
,B.dbname
FROM dbo.hostservers A
JOIN dbo.SERVERS_DATABASES B ON B.servername = A.servername
WHERE isActive = 1
AND db_notfound = 0
OPEN Cursr_Srvr_port_db
-- get very first record
FETCH NEXT FROM Cursr_Srvr_port_db
INTO @linkserver
, @port
, @dbName
PRINT( 'first record: ' + @linkserver + ',' + @port + ',' + @dbname)
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sqlquery =
'SELECT @@SERVERNAME as servername
,''''' + @dbname + ''''' as dbName
,C.name as Srvr_lvl_login
,B.name as dbrole
FROM [' + @dbname + '].sys.database_role_members A
JOIN [' + @dbname + '].sys.database_principals B on (A.Role_Principal_id = B.principal_id)
JOIN [' + @dbname + '].sys.database_principals C on (A.Member_Principal_id = C.principal_id) '
SELECT @finalQuery = N'INSERT dbo.db_Roles
SELECT *
FROM OPENQUERY([' + @linkserver + ',' + @port + '],' + '''' + @sqlquery + '''' + ')'
EXEC(@finalQuery)
FETCH NEXT FROM Cursr_Srvr_port_db
INTO @linkserver
, @port
, @dbName
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply