Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mivey4
Yak Posting Veteran
66 Posts |
 Posted - 2007-12-19 : 13:07:22
|
Hi all!I have been trying to come up with a script that will allow you to dynamically switch between databases using a cursor and then dynamically assigning each database to a defined variable as type sysname.The issue is I can perform certain things using the database name using the EXEC statement to perform the tasks but I can't figure out how to switch between databases using the syntax:USE someDataBaseGO// Some code manipulations hereDoes anyone have any ideas how I might go about achieving this? If I for example: use EXEC('Use ' + @myDatabaseName)it won't break the code and will even execute the statement successfully but it won't actually switch to the database assigned in the variable before executing the remaining portion of my code.I thought to place everything within the EXEC parameter including the variable declarations and actual cursor loop and etc. but other than the obvious (that it isn't a very elegant solution to the problem) I haven't been able to get it to work either.I know there has to be some solution but I just can't figure the darn thing out. Can anybody give me some assistance  Any help at all would be greatly appreciated.  |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-19 : 13:13:06
|
We had an exact same discussion earlier this week.Try to search for similar keyword and see if you can catch that topic.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
mivey4
Yak Posting Veteran
66 Posts |
Posted - 2007-12-19 : 13:56:51
|
| Hey I tried searching for dynamic database variables and a few variations of this but I couldn't find that discussion. Any hints on what keywords may have been used so I can track it? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-19 : 15:17:10
|
The direction of the discussion was that when you use "USE DbName" in a dynamic execution, the problem is the scope.The scope is ONLY during the execution. Immediately after execution, the scope is lost and the current database is the same as before executing the dynamic query.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
|
|
|
|
|
Dynamically switching DB's using T-SQL