Using USE does not set DB context

Question

Post reply

Using USE does not set DB context

belal360

SSC Veteran

Points: 268
More actions
June 10, 2024 at 9:25 pm

#4427557

Hi Experts,
I have a Sql file with a bunch of DDL statements (Create Tables/Views). I start with a DB then change DB context to another DB followed by 2nd set of DDLs.
In Sql file, I have:
DECLARE
@DB1 NVARCHAR(MAX) = 'DevDB',
@DB2 NVARCHAR(MAX) = 'TestDB',
@ENV NVARCHAR(MAX)
SELECT @ENV = 'USE ' + @DB1
EXEC (@ENV)
Issue is next line is CREATE TABLE does not get created in desired DB; as if DB context wasn't set.
Is there a way to have USE DevDB issued and followed statements are run in correct DB and same for the 2nd set of DDLs present in same Sql file??

Thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

The Dynamic SQL is executed in a different context.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

Does your dynamic SQL get executed as one string?

e.g.:

SELECT @ENV = 'USE ' + @DB1
SET @ENV = @ENV + CHAR(13) + CHAR(10) + 'SELECT * FROM myTable;'
EXEC (@ENV)

belal360 SSC Veteran Points: 268 More actions · Answer 3

Thanks for the feedback.

Jon,

This is a code deploy script file, I'm trying to create. Hence, need to set DB context first then run a few DDLs (see below)

Example snippet:

SELECT @ENV = 'USE ' + @DB1

SET @ENV = @ENV + CHAR(13) + CHAR(10)

EXEC (@ENV)

CREATE TABLE T1;

CREATE TABLE T2;

CREATE VIEW V1; (cannot wrap all DDLs in a dynamic Sql as list is large)

Objects are not created as context does not persist. I cannot hardcode USE DBName due to ENV changes from Dev to test to Prod, Etc.

An alternate or workaround is appreciated.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

IF @DB1 = 'TestDB' USE TestDB
ELSE IF @DB1 = 'ProdDB'  USE ProdDB

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

belal360 wrote:

Thanks for the feedback.
Jon,
This is a code deploy script file, I'm trying to create. Hence, need to set DB context first then run a few DDLs (see below)
Example snippet:
SELECT @ENV = 'USE ' + @DB1 SET @ENV = @ENV + CHAR(13) + CHAR(10) EXEC (@ENV)
CREATE TABLE T1; CREATE TABLE T2; CREATE VIEW V1; (cannot wrap all DDLs in a dynamic Sql as list is large)
Objects are not created as context does not persist. I cannot hardcode USE DBName due to ENV changes from Dev to test to Prod, Etc.
An alternate or workaround is appreciated.

HOW exactly are you executing the scripts?

a rather standard way to do this is using sqlcmd - and on this particular case you can pass variables to sqlcmd which you can then use to replace text within the scripts.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 6

Jonathan AC Roberts wrote:

IF @DB1 = 'TestDB' USE TestDB
ELSE IF @DB1 = 'ProdDB'  USE ProdDB

don't know what tool you are using to execute the above - but on SSMS it fails - and likely will fail as well with sqlcmd.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 7

frederico_fonseca wrote:

Jonathan AC Roberts wrote:
IF @DB1 = 'TestDB' USE TestDB
ELSE IF @DB1 = 'ProdDB'  USE ProdDB
don't know what tool you are using to execute the above - but on SSMS it fails - and likely will fail as well with sqlcmd.

You have to declare the variable and USE a database that actually exists.

DECLARE @DB1 varchar(20)
SET @DB1 = 'Test' -- Or set to 'Prod'
IF @DB1 = 'Test' USE TestDB
ELSE IF @DB1 = 'Prod'  USE ProdDB

What error do you get?

frederico_fonseca SSCoach Points: 16154 More actions · Answer 8

Jonathan AC Roberts wrote:

frederico_fonseca wrote:
Jonathan AC Roberts wrote:
IF @DB1 = 'TestDB' USE TestDB
ELSE IF @DB1 = 'ProdDB'  USE ProdDB
don't know what tool you are using to execute the above - but on SSMS it fails - and likely will fail as well with sqlcmd.
You have to declare the variable and USE a database that actually exists.
DECLARE @DB1 varchar(20)
SET @DB1 = 'Test' -- Or set to 'Prod'
IF @DB1 = 'Test' USE TestDB
ELSE IF @DB1 = 'Prod'  USE ProdDB
What error do you get?

well... you yourself clarified the point I was trying to make - that construct will never work for a deployment against different servers - a TEST db would not exist in the prod server - and reverse is true.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 9

frederico_fonseca wrote:

Jonathan AC Roberts wrote:
frederico_fonseca wrote:
Jonathan AC Roberts wrote:
IF @DB1 = 'TestDB' USE TestDB
ELSE IF @DB1 = 'ProdDB'  USE ProdDB
don't know what tool you are using to execute the above - but on SSMS it fails - and likely will fail as well with sqlcmd.
You have to declare the variable and USE a database that actually exists.
DECLARE @DB1 varchar(20)
SET @DB1 = 'Test' -- Or set to 'Prod'
IF @DB1 = 'Test' USE TestDB
ELSE IF @DB1 = 'Prod'  USE ProdDB
What error do you get?
well... you yourself clarified the point I was trying to make - that construct will never work for a deployment against different servers - a TEST db would not exist in the prod server - and reverse is true.

well... me myself, I just employ the time-honoured technique of chaining multiple USE statements with a healthy dose of 'GO' commands. It goes something like this:

GO
USE DevDb
GO
USE TestDb
GO
USE UatDb
GO
USE ProdDb
GO

Who needs database existence checks when you can just use brute force and persistence, right? It's the 'if-at-first-you-don't-succeed, USE, USE again' methodology!