April 28, 2015 at 12:06 pm
USE master
Declare @db as nvarchar(258) = quotename (N'tempdb')
EXEC (N'USE' + @db + N'; EXEC(''SELECT DB_NAME();'');');
Thanks.
April 28, 2015 at 12:38 pm
Hmmm....that code works as expected for me, i.e., the result of SELECT DB_NAME() outputs the database set as @db.
Are you perhaps expecting it to change the context of the session running the entire batch?
From https://msdn.microsoft.com/en-us/library/ms188001.aspx (admittedly, about sp_executesql, but as it says, it's the same for EXECUTE).
sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch cannot reference variables declared in the batch that calls sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch that calls sp_executesql. Changes in database context last only to the end of the sp_executesql statement.
Cheers!
April 28, 2015 at 1:08 pm
I have to agree. It works as expected for me as well.
April 28, 2015 at 3:00 pm
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [database_name].[dbo].[sp_fulltext_database] @action = 'enable'
end
I want to execute the above database to a specific database.
Please suggest how to write the syntax dynamically.
Thanks.
April 28, 2015 at 3:34 pm
Something like the following would construct the EXEC command dynamically:
DECLARE @db VARCHAR(250)
SET @db='Some Database'
DECLARE @sql VARCHAR(1000)
SET @sql='EXECUTE '+QUOTENAME(@db)+'.[dbo].[sp_fulltext_database] @action=''enable'''
EXEC (@sql)
I'm curious, though. What exactly are you trying to do?
Cheers!
April 28, 2015 at 4:14 pm
Thanks JAcob, Lynn and Others....
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply