Reply to Dynamic SQL does not work!

Your name:

Reply:


Posted by SQLJunkie on 02/07/07 15:02

I have a small SQL script that rotates through all databases on the
server and executes a Stored Procedure in each of them. Here are the
steps:
1: The first step is to get name of databases in to a cursor.
2: Build dynamic SQL to change database to database in variable - this
step fails
3: Execute SP
4: Move to next database.
Is it not possible to change current database using Dynamic SQL? Can
someone help me here please?

Here is the code I am using:
----- EXECUTE SQL Statement on all Databases

DECLARE
@DatabaseName varchar(100)
, @SQL varchar(500)

DECLARE DBNameCursor CURSOR FOR
SELECT
[Name] AS DatabaseName
FROM
master.dbo.sysdatabases
ORDER BY
DatabaseName

OPEN DBNameCursor

FETCH NEXT FROM DBNameCursor
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
----- Change to Database
SELECT
@SQL = 'USE ' + @DatabaseName

EXEC(@SQL)

----- SQL Statement to be run
EXEC dbo.SPName

FETCH NEXT FROM DBNameCursor
INTO @DatabaseName
END

CLOSE DBNameCursor
DEALLOCATE DBNameCursor


----------------------
Thanks in advance!
Vishal Sinha

[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

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