Reply to Re: Multiple db query call from within different context into #temp table

Your name:

Reply:


Posted by othellomy on 03/21/07 08:38

On Mar 21, 12:57 pm, santaferub...@gmail.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_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

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация