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

 

[wbodger]

Hmm... very interesting, I will look into that.Thx!