Script trouble: trying generate db, tables for various dbs with one script

[wbodger]

Here is a snippet of the code

DECLARE @ProjID AS INTEGER
DECLARE @ProtID AS INTEGER
Declare @DBName AS VARCHAR(15)
Declare @DBServer AS VARCHAR(15)
DECLARE @webServer AS VARCHAR(25)
DECLARE @SQLQuery AS VARCHAR(MAX)

SET @ProjID = 3
SET @ProtID = 555
SET @DBName = 'dbEDRN555'

SET @dbServer = 'rico' --'private' 'skipper'
SET @webServer = 'pongo' --'perdy' '[URL unfurl="true"]www.compass'[/URL]

USE master;

SELECT @SQLQuery = 'CREATE DATABASE ' + @DBName;
EXEC(@SQLQuery)


SELECT @SQLQuery = 'USE ' + @DBName;
EXEC(@SQLQuery)


/***** This section is where the base tables get created *****/
/****** Object:  Table [dbo].[tblAudit]    Script Date: 03/01/2012 14:41:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblAudit](
	[AUDITID] [int] IDENTITY(1,1) NOT NULL,
	[TBL_ID] [int] NOT NULL,
	[DE_ID] [int] NULL,
	[CASEID] [int] NOT NULL,
	[OVALUE] [varchar](500) NULL,
	[NVALUE] [varchar](500) NULL,
	[OSTAFFID] [int] NULL,
	[NSTAFFID] [int] NOT NULL,
	[REASON] [varchar](1000) NOT NULL,
	[ODATE] [datetime] NULL,
	[NDATE] [datetime] NOT NULL,
 CONSTRAINT [PK_tblAudit] PRIMARY KEY CLUSTERED 
(
	[AUDITID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tblFinalG]    Script Date: 03/01/2012 14:41:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblFinalG](
	[CASEID] [int] IDENTITY(1,1) NOT NULL,
	[STUDY_PARTICIPANT_ID] [varchar](20) NULL,
	[STUDY_CONSENT_DATE] [datetime] NULL,
	[FINAL_GROUP] [char](5) NULL,
	[FINAL_GROUP_TEXT] [varchar](300) NULL,
	[STUDY_COMMENTS_TEXT] [varchar](3000) NULL,
	[EligibilityVersion] [varchar](10) NOT NULL,
	[INISTAFFID] [int] NULL,
	[INIDATE] [datetime] NULL,
	[DELETEFLAG] [int] NOT NULL,
	[SpecimenStatus] [varchar](10) NULL,
	[ColonoscopyStatus] [varchar](10) NULL,
	[SpecimenStatusComponents] [varchar](250) NULL,
 CONSTRAINT [PK_tblFinalG] PRIMARY KEY CLUSTERED 
(
	[CASEID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

What seems to be happening is that it is not 'USING' the db just created when creating the tables. What am I missing? From here I just go to do more and more of the same (create more tables, views, sprocs, insert data into tables, create users assign rights etc.)

wb

 

[simian336]

did you try putting a "GO" after your create and use statements?

Simi

 

[wbodger]

Yes, I have tried it with 'GO' statements after the create and use statements. I have removed the 'use master' statement, but it still uses master. I guess more completely, the db (dbEDRN555) is getting created and the tables (tblAudit and tblFinalG) are getting created, but those two tables are being created in master, not in dbEDRN555. Right now I have everything in one big file where I create about 10 tables, maybe 20 views, 7 stored procedures and then create users and assign user rights. The big problem is the Use statement not working. Thanks for you help!

wb

 

[wbodger]

Oh... It looks like it is a scope issue, perhaps? When I use dynamic SQL to define a use statement, then my next statement that is not part of that dynamic SQL block is back in the original scope?

 

[GHolden]

Hi, I think it's something to do with the scope of the EXEC statement. I 'think' that the SQL executed in the EXEC is out of scope the rest of the TSQL.

There are two ways to write error-free programs; only the third one works.

 

[GHolden]

Sorry... posted at the same time...

There are two ways to write error-free programs; only the third one works.

 

[wbodger]

So, does that change at all if I use sp_executesql rather than exec()? Even if I place it inside of a stored procedure, I still want to pass the name of the database to the code and have that block of code run under that db context.

wb

 

[GHolden]

Hi,

the only way I can get this to work is to include the SQL in with the USE and EXEC all in one go...

E.g.

DECLARE @sql varchar(5000)
DECLARE @db varchar(50)

SET @db = 'DBName'

SET @sql = 'USE ' + @db + '; SELECT * FROM dbo.Table'

EXEC (@sql)

There are two ways to write error-free programs; only the third one works.

 

[GHolden]

RE: sp_executesql

From MS

sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context.

There are two ways to write error-free programs; only the third one works.

 

[wbodger]

Hmm... Thanks for the info. I'm reading an interesting on Dynamic SQL by Erland Sommarskog right now, mayhaps that will turn up a solution...

wb

 

[GHolden]

Good luck, if you find a good answer please post a link / solution.

Cheers,

There are two ways to write error-free programs; only the third one works.

 

[wbodger]

Not a solution (yet), but rather another question. Why does this work:

USE dbDEMapping

DECLARE @ProjID AS INTEGER
DECLARE @ProtID AS INTEGER
Declare @DBName AS NVARCHAR(15)
Declare @DBServer AS NVARCHAR(15)
DECLARE @webServer AS NVARCHAR(25)

SET @ProjID = 3
SET @ProtID = 555
SET @DBName = 'dbEDRN555'

SET @dbServer = 'rico' --'private' 'skipper'
SET @webServer = 'pongo'  --'perdy' '[URL unfurl="true"]www.main'[/URL]


INSERT INTO tblDataConnect
        (Server, UserRole, Proj_ID, Proto_ID, DBName, DataConnect)
VALUES
        (@webServer + '.mydomain.org', 0, @ProjID, @ProtID, @DBName, 'Provider=SQLOLEDB.1;  Persist Security Info=False; User ID = userid1;  Password=pwd1; Initial Catalog = ' + @DBName + '; Data Source = ' + @DBServer +'')

INSERT INTO tblDataConnect
        (Server, UserRole, Proj_ID, Proto_ID, DBName, DataConnect)
VALUES
        (@webServer + '.mydomain.org', 1, @ProjID, @ProtID, @DBName, 'Provider=SQLOLEDB.1;  Persist Security Info=False; User ID = userid2;  Password=pwd2; Initial Catalog = ' + @DBName + '; Data Source = ' + @DBServer +'')

This uses variables within the SQL itself, but it is not Dynamic SQL? Can somebody explain why this is different?

wb

 

[gmmastros]

It works, and it's not Dynamix SQL because the only dynamic part about it is the data. I mean.... no matter what values you use, it will still insert in to the tblDataConnect table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

The reason you are having trouble is because the USE statement isn't doing exactly what you think it is. More specifically, it's the EXEC that is causing this behavior.

When you run this block of code, you must be connected to some database (perhaps master). Whenever you run the EXEC command, it is essentially creating a new connection to the database and executing the code. When it's done running, the connection to the database is gone.

SELECT @SQLQuery = 'USE ' + @DBName;
EXEC(@SQLQuery)

In the code above, the EXEC is executed against the currently logged in database (Master?). The command is USE SOME_DATABASE. When the command is done executing, the remaining code will still execute within the context of the original database (master?).

What you could do is....

Set @SQL = '
CREATE TABLE [~DATABASENAME~].[dbo].[tblFinalG](
	[CASEID] [int] IDENTITY(1,1) NOT NULL,
	[STUDY_PARTICIPANT_ID] [varchar](20) NULL,
	[STUDY_CONSENT_DATE] [datetime] NULL,
	[FINAL_GROUP] [char](5) NULL,
	[FINAL_GROUP_TEXT] [varchar](300) NULL,
	[STUDY_COMMENTS_TEXT] [varchar](3000) NULL,
	[EligibilityVersion] [varchar](10) NOT NULL,
	[INISTAFFID] [int] NULL,
	[INIDATE] [datetime] NULL,
	[DELETEFLAG] [int] NOT NULL,
	[SpecimenStatus] [varchar](10) NULL,
	[ColonoscopyStatus] [varchar](10) NULL,
	[SpecimenStatusComponents] [varchar](250) NULL,
 CONSTRAINT [PK_tblFinalG] PRIMARY KEY CLUSTERED 
(
	[CASEID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]'

Set @SQL = Replace(@SQL, '~DATABASENAME~', @DBName)
Exec (@SQL)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Ohh... I hadn't thought of that, George. Thanks. What I am doing is creating a script to do the steps in an instance creation that can be automated. Most of those take place in the database that is created in the first step. I can implement this in stored procedures as well, but that still doesn't really allow me to dynamically set the 'USE' statement, right? So, the only way I could do a dynamic 'USE' statement would be as GHolden said and include everything that came after the 'USE' statement in that block of dynamic SQL?

DECLARE @ProjID AS INTEGER
DECLARE @ProtID AS INTEGER
Declare @DBName AS VARCHAR(15)
Declare @DBServer AS VARCHAR(15)
DECLARE @webServer AS VARCHAR(25)
DECLARE @SQLQuery AS VARCHAR(MAX)

SET @ProjID = 3
SET @ProtID = 555
SET @DBName = 'dbEDRN555'

SET @dbServer = 'rico' --'private' 'skipper'
SET @webServer = 'pongo' --'perdy' '[URL unfurl="true"]www.compass'[/URL]

USE master;

SELECT @SQLQuery = 'CREATE DATABASE ' + @DBName;
EXEC(@SQLQuery)
GO

SELECT @SQLQuery = 'USE ' + @DBName; +
'GO'+
'SET ANSI_NULLS ON'+
'GO'+
'SET QUOTED_IDENTIFIER ON'+
'GO'+
'SET ANSI_PADDING ON'+
'GO'+
'CREATE TABLE [dbo].[tblAudit]('+
'	[AUDITID] [int] IDENTITY(1,1) NOT NULL,'+
'	[TBL_ID] [int] NOT NULL,'+
'	[DE_ID] [int] NULL,'+
'	[CASEID] [int] NOT NULL,'+
'	[OVALUE] [varchar](500) NULL,'+
'	[NVALUE] [varchar](500) NULL,'+
'	[OSTAFFID] [int] NULL,'+
'	[NSTAFFID] [int] NOT NULL,'+
'	[REASON] [varchar](1000) NOT NULL,'+
'	[ODATE] [datetime] NULL,'+
'	[NDATE] [datetime] NOT NULL,'+
'CONSTRAINT [PK_tblAudit] PRIMARY KEY CLUSTERED '+
'('+
'	[AUDITID] ASC'+
')WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]'+
') ON [PRIMARY]'+
'GO'+
'SET ANSI_PADDING OFF'+
'GO'
EXEC(@SQLQuery)
GO

Gives me this error

Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@DBName".
Msg 137, Level 15, State 2, Line 30
Must declare the scalar variable "@SQLQuery".

 

[philhege]

Hi wbodger,

See the semicolon after your @DBName reference? It's not in quotes, so it is taken as an end of statement.

Try

SELECT @SQLQuery = 'USE ' + @DBName +

-----------
With business clients like mine, you'd be better off herding cats.

 

[wbodger]

OK, I see the problem with the variables, fixed that issue. I can get this to work by including everything inside of the Dynamic SQL block, but there is too much code to really do that. Even if I break this up into blocks in stored procedures, there still is the issue of scope, right? Trying to modify the 'USE' statement AND have it apply to a lengthy section of code after it seems to be the sticking point. Am I truly stuck on this?

wb

 

[philhege]

Use George's suggestion of providing a 3-part name for the object in the dynamic SQL, e.g. MyDatabase.dbo.MyTable to refer to the objects.

-----------
With business clients like mine, you'd be better off herding cats.

 

[wbodger]

OK. that was the only solution that I could see after George mentioned it, I was just curious if there was something else I had missed.

wb

 

[GHolden]

Hi,

This seems to work... and would give you the flexibility you need (I think!)

Using SYNONYM to reference the table, then all following code would use the SYNONYM name. You would need one synonym for each object but might work out for you

DECLARE @table VARCHAR(500)
DECLARE @sql VARCHAR(500)

SET @table = 'cofely_data.bags.airlines'
SET @sql = 'CREATE SYNONYM myTable FOR ' + @table

EXECUTE(@sql)

SELECT * FROM myTable

DROP SYNONYM myTable

just before I pressed submit post I realised that this won't work for the CREATE's but would work for INSERT's UPDATE's etc...

There are two ways to write error-free programs; only the third one works.