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 |
|
AndyThissen
Starting Member
7 Posts |
 Posted - 2005-09-14 : 17:22:50
|
| Hopefully this is an easy question so I can be laughed out of the forum (just get me the code 1st). I am trying to set the database dynamicly in a query. The database name is the same as the server name. neither of the last 3 commands tried individually seem to work.declare @sql nvarchar(30)set @sql = 'USE ' + rtrim(@@servername)sp_sqlexec @sqlexec sp_sqlexec @sqlexec @sqlThe output for @sql appears correct. (eg. USE GRIMUS) but I get an incorrect sytax error. Any help is appreciated. |
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-09-14 : 17:29:17
|
| Hi,Sorry - can't do it."Use" (changing database context) is one of the less-flexible parts of the language. The statement must be hard-coded, and it cant be used in a condition (if .. Use ... else ...).The way around this is to execute the entire batch dynamically, with "Use" at the top of the set of commands (no "Go" statement after the "Use"). The database context of the calling scope will not change, but the context of the executed scope will be in the database that is "use"d.Good luck. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-14 : 18:06:09
|
| Another workaround for dynamic SQL...Use sp_executesql with the three part naming convention of the database you want to use.EXEC DBNameGoesHere.dbo.sp_executesql N'SELECT * FROM SomeTableThatExistsInDBName'Or:SET @SQL = 'SELECT * FROM DBName.dbo.SomeTable'EXEC (@SQL)sp_executesql is much more flexible though when you are performing admin type tasks.Tara |
|
|
|
|
|
|
Dynamicly setting database