Multiple db query call from within different context into ..

The first query returns me the results from multiple databases, the
second does the same thing except it puts the result into a #temp
table? Could someone please show me an example of this using the first
query? The first query uses the @exec_context and I am having a
challenge trying to figure out how to make the call from within a
different context and still insert into a #temp table.

DECLARE @exec_context varchar(30)
declare @sql nvarchar(4000)
DECLARE @DBNAME nvarchar(50)
DECLARE companies_cursor CURSOR FOR
SELECT DBNAME
FROM DBINFO
WHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')
ORDER BY DBNAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @exec_context = @DBNAME + '.dbo.sp_executesql '
set @sql = N'select top 10 * from products'
exec @exec_context @sql
FETCH NEXT FROM companies_cursor INTO @DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
-------------------------------------------------------------------------------------
CREATE TABLE #Test (field list here)
declare @sql nvarchar(4000)
DECLARE @DBNAME nvarchar(50)

DECLARE companies_cursor CURSOR FOR
SELECT NAME
FROM sysdatabases
WHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULL
ORDER BY NAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = N'select top 10 * from '+@DBNAME+'.dbo.products'
INSERT INTO #Test
exec (@sql)
FETCH NEXT FROM companies_cursor INTO @DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
SELECT * from #Test
DROP TABLE #Test

On Mar 21, 12:57 pm, wrote:
> The first query returns me the results from multiple databases, the
> second does the same thing except it puts the result into a #temp
> table? Could someone please show me an example of this using the first
> query? The first query uses the @exec_context and I am having a
> challenge trying to figure out how to make the call from within a
> different context and still insert into a #temp table.
>
> DECLARE @exec_context varchar(30)
> declare @sql nvarchar(4000)
> DECLARE @DBNAME nvarchar(50)
> DECLARE companies_cursor CURSOR FOR
> SELECT DBNAME
> FROM DBINFO
> WHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')
> ORDER BY DBNAME
> OPEN companies_cursor
> FETCH NEXT FROM companies_cursor INTO @DBNAME
> WHILE @@FETCH_STATUS = 0
> BEGIN
> set @exec_context = @DBNAME + '.dbo.sp_executesql '
> set @sql = N'select top 10 * from products'
> exec @exec_context @sql
> FETCH NEXT FROM companies_cursor INTO @DBNAME
> END
> CLOSE companies_cursor
> DEALLOCATE companies_cursor
> ---------------------------------------------------------------------------­----------
> CREATE TABLE #Test (field list here)
> declare @sql nvarchar(4000)
> DECLARE @DBNAME nvarchar(50)
>
> DECLARE companies_cursor CURSOR FOR
> SELECT NAME
> FROM sysdatabases
> WHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULL
> ORDER BY NAME
> OPEN companies_cursor
> FETCH NEXT FROM companies_cursor INTO @DBNAME
> WHILE @@FETCH_STATUS = 0
> BEGIN
> set @sql = N'select top 10 * from '+@DBNAME+'.dbo.products'
> INSERT INTO #Test
> exec (@sql)
> FETCH NEXT FROM companies_cursor INTO @DBNAME
> END
> CLOSE companies_cursor
> DEALLOCATE companies_cursor
> SELECT * from #Test
> DROP TABLE #Test

Not sure if this solves the problem:

WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = ' INSERT INTO #Test select top 10 * from '+@DBNAME
+'.dbo.products'
exec (@sql)
FETCH NEXT FROM companies_cursor INTO @DBNAME
END