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 #TestOn Mar 21, 12:57 pm, santaferub...@.gmail.com wrote:
Quote:
Originally Posted by
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
