Multiple db query call from within different context into #temp table

[santaferubber]

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_curso r CURSOR FOR
SELECT DBNAME
FROM DBINFO
WHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')
ORDER BY DBNAME
OPEN companies_curso r
FETCH NEXT FROM companies_curso r INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @exec_context = @DBNAME + '.dbo.sp_execut esql '
set @sql = N'select top 10 * from products'
exec @exec_context @sql
FETCH NEXT FROM companies_curso r INTO @DBNAME
END
CLOSE companies_curso r
DEALLOCATE companies_curso r
-------------------------------------------------------------------------------------
CREATE TABLE #Test (field list here)
declare @sql nvarchar(4000)
DECLARE @DBNAME nvarchar(50)

DECLARE companies_curso r CURSOR FOR
SELECT NAME
FROM sysdatabases
WHERE OBJECT_ID(Name+ '.dbo.products' ) IS NOT NULL
ORDER BY NAME
OPEN companies_curso r
FETCH NEXT FROM companies_curso r 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_curso r INTO @DBNAME
END
CLOSE companies_curso r
DEALLOCATE companies_curso r
SELECT * from #Test
DROP TABLE #Test

[othellomy]

On Mar 21, 12:57 pm, santaferub...@g mail.com 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_curso r CURSOR FOR
SELECT DBNAME
FROM DBINFO
WHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')
ORDER BY DBNAME
OPEN companies_curso r
FETCH NEXT FROM companies_curso r INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @exec_context = @DBNAME + '.dbo.sp_execut esql '
set @sql = N'select top 10 * from products'
exec @exec_context @sql
FETCH NEXT FROM companies_curso r INTO @DBNAME
END
CLOSE companies_curso r
DEALLOCATE companies_curso r
---------------------------------------------------------------------------*----------
CREATE TABLE #Test (field list here)
declare @sql nvarchar(4000)
DECLARE @DBNAME nvarchar(50)

DECLARE companies_curso r CURSOR FOR
SELECT NAME
FROM sysdatabases
WHERE OBJECT_ID(Name+ '.dbo.products' ) IS NOT NULL
ORDER BY NAME
OPEN companies_curso r
FETCH NEXT FROM companies_curso r 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_curso r INTO @DBNAME
END
CLOSE companies_curso r
DEALLOCATE companies_curso r
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_curso r INTO @DBNAME
END