Pass Through Query To Multiple SQL Server Databases.

[Ancalima]

I am having difficulty finding a solution to my problem. I am using MS Access 2003 and I'm trying to create a pass-through query to a SQL Server. I wish to run the same SQL on multiple databases on the server using a single query. I know I could use a giant union query for this but i prefer not to use that method because I do not want to have to update this query every time a new database is added to the server. After searching for information I saw that some people had listed two possible solutions to this issue. Either use a Cursor and loop through the databases in the Master.sys.databases table, or use the built-in yet not officially supported stored procedure sp_MSforeachdb. My attempts at using both of these methods give me the same error. Whatever procedure I use only seems to return the first database in the list and then stops the loop. My SQL for the Cursor method is below:

SET NoCount ON

DECLARE @DBName VarChar(200)
DECLARE @SQL nVarChar(500)

DECLARE DBCursor CURSOR FOR
SELECT Name FROM master.sys.databases WHERE Name Like '%analysis'

OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @DBName

WHILE @@FETCH_STATUS=0
BEGIN

SELECT @SQL='SELECT ' + '''' + @DBName + '''' + ' AS DBName, Name AS Company, ID AS CompanyID FROM ' + @DBName + '.dbo.Company WHERE ID>0'
EXEC sp_ExecuteSQL @SQL

FETCH NEXT FROM DBCursor INTO @DBName

END

CLOSE DBCursor
DEALLOCATE DBCursor

The SQL I am trying for the built-in stored procedure is below:

EXEC sp_MSforeachdb 'IF ''?'' Like ''%analysis'' SELECT ''?'' AS DBName, Name AS Company, ID AS CompanyID FROM ?..Company WHERE ID>0'

Both of these SQL statements return the same results but they both stop after only reading from 1 database. The SQL Server database I am connecting to is version 2005. If anyone could help on this I would greatly appreciate it. Thanks!