T-SQL USE with a variable
-
DECLARE @ventyxdbname varchar(50) SET @ventyxdbname = 'OUTLOG_PACE' /* */ USE @ventyxdbname; This gives: Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '@ventyxdbname'. Is it not possible to use a variable in a script for the USE command?
-
DECLARE @ventyxdbname varchar(50) SET @ventyxdbname = 'OUTLOG_PACE' /* */ USE @ventyxdbname; This gives: Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '@ventyxdbname'. Is it not possible to use a variable in a script for the USE command?
-
use nvarchar datatype instead of varchar
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
Hi, Changed it to nvarchar, but still the same message. Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '@ventyxdbname'. It definitely does not seem to like a variable with USE. Could it be that tis is like the CREATE TABLE command where you cannot use a variable directly?
-
Hi, Changed it to nvarchar, but still the same message. Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '@ventyxdbname'. It definitely does not seem to like a variable with USE. Could it be that tis is like the CREATE TABLE command where you cannot use a variable directly?
-
declare @DBName varchar(20)
declare @Str varchar(100)set @DBName = databasename
set @Str = 'use ' + @DBName
exec (@Str)
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
Many thanks for trying! I am afraid that does not work either.
declare @DBName varchar(20)
declare @Str varchar(100)set @DBName = 'OUTLOG_PACE'
set @Str = 'use ' + @DBName
exec (@Str)select * from dbo.OUT_tbl_Request
-------------- Error message: Msg 208, Level 16, State 1, Line 8 Invalid object name 'dbo.OUT_tbl_Request'. The table name is correct, but this is the result if I start the query in master. Not to worry, there are only 4 places in the script where the database name needs to be changed. I was just trying to be lazy as it is quite a long script and I did not want to miss one. I will use Ctrl+H !!
-
Many thanks for trying! I am afraid that does not work either.
declare @DBName varchar(20)
declare @Str varchar(100)set @DBName = 'OUTLOG_PACE'
set @Str = 'use ' + @DBName
exec (@Str)select * from dbo.OUT_tbl_Request
-------------- Error message: Msg 208, Level 16, State 1, Line 8 Invalid object name 'dbo.OUT_tbl_Request'. The table name is correct, but this is the result if I start the query in master. Not to worry, there are only 4 places in the script where the database name needs to be changed. I was just trying to be lazy as it is quite a long script and I did not want to miss one. I will use Ctrl+H !!
What happens if you put a go statement after the exec? For some reason I remember something similar causing me problems and adding a go statement fixed it. Could be completely wrong as I don't have SQL Server in front of me.
-
What happens if you put a go statement after the exec? For some reason I remember something similar causing me problems and adding a go statement fixed it. Could be completely wrong as I don't have SQL Server in front of me.
I think I have found the answer - it don't work!!! :( From Microsoft Using EXECUTE with a Character String In earlier versions of SQL Server, character strings are limited to 8,000 bytes. This requires concatenating large strings for dynamic execution. In SQL Server 2005, the varchar(max) and nvarchar(max) data types can be specified that allow for character strings to be up to 2 gigabytes of data. Changes in database context last only until the end of the EXECUTE statement. For example, after the EXEC in this following statement is run, the database context is master. USE master; EXEC ('USE AdventureWorks; SELECT EmployeeID, Title FROM HumanResources.Employee;'); Thanks very much for trying anyway! :)