Problem when creating database with SQL batch via ExecuteNonQuery()

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

Hi All,

I'm trying to create my database using a batch of SQL commands via
ExecuteNonQuery(). The SQL batch should create the database and then
all the tables to go into it. The start of the script is as follows:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
N'TEST')
DROP DATABASE [TEST]

print 'Creating database TEST.'
CREATE DATABASE [TEST] ON PRIMARY
(
Name=TEST_Data,
filename = 'C:\dbtest\scripts\\TEST.mdf',
size=3,
maxsize=5,
filegrowth=10%
)
LOG ON
(
name=TEST_log,
filename='C:\dbtest\scripts\TEST.ldf',
size=3,
maxsize=20,
filegrowth=1
)


exec sp_dboption N'TEST', N'auto create statistics', N'true'

exec sp_dboption N'TEST', N'auto update statistics', N'true'

use [TEST]

set nocount on

print 'Creating branch table.'
CREATE TABLE [dbo].[branch] (
Code: 
 [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[name] [varchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[addr1] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[addr2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[addr3] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[addr4] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[addr5] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[postcode] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[telnum] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[usemodemno] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[modemnum] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[commreq] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[active] [char] (10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]

..... and so it goes on.

(I'm picking up the 'print' statements via InfoMessage and a
SqlInfoMessageEventHandler)

The problem I get is that it's not creating the database or rather it
seems not to be waiting for the database to be created.  I've tried
adding a WAITFOR DELAY but the execution just seems to ignore the
statement and continues.  I really don't want to split out the CREATE
DATABASE into a separate script.  I've tried placing the CREATE
DATABASE into an EXEC ('..') but it still has the problem when it
comes to the USE [TEST] ... I keep getting 'Cannot locate entry in
sysdatabases for 'TEST' ...'

I'm using a trusted connection to MSDE.

Any help would be appreciated.

Thanks in advance.

Scott
 
Hi Scott,

I think you need to separate each *logical* part of SQL statements with the
semicolon. You cannot execute batch in one shot without it.
 
Hi,

Thanks for replying. I;ve tried separating the statements with ';'
but I'm still getting the same problem. :(

Any other ideas?

Thanks


Val Mazur said:
Hi Scott,

I think you need to separate each *logical* part of SQL statements with the
semicolon. You cannot execute batch in one shot without it.

--
Val Mazur
Microsoft MVP


Scott said:
Hi All,

I'm trying to create my database using a batch of SQL commands via
ExecuteNonQuery(). The SQL batch should create the database and then
all the tables to go into it. The start of the script is as follows:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
N'TEST')
DROP DATABASE [TEST]

print 'Creating database TEST.'
CREATE DATABASE [TEST] ON PRIMARY
(
Name=TEST_Data,
filename = 'C:\dbtest\scripts\\TEST.mdf',
size=3,
maxsize=5,
filegrowth=10%
)
LOG ON
(
name=TEST_log,
filename='C:\dbtest\scripts\TEST.ldf',
size=3,
maxsize=20,
filegrowth=1
)


exec sp_dboption N'TEST', N'auto create statistics', N'true'

exec sp_dboption N'TEST', N'auto update statistics', N'true'

use [TEST]

set nocount on

print 'Creating branch table.'
CREATE TABLE [dbo].[branch] (
Code: 
 [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[name] [varchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[addr1] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[addr2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[addr3] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[addr4] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[addr5] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[postcode] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[telnum] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[usemodemno] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[modemnum] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[commreq] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[active] [char] (10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]

.... and so it goes on.

(I'm picking up the 'print' statements via InfoMessage and a
SqlInfoMessageEventHandler)

The problem I get is that it's not creating the database or rather it
seems not to be waiting for the database to be created.  I've tried
adding a WAITFOR DELAY but the execution just seems to ignore the
statement and continues.  I really don't want to split out the CREATE
DATABASE into a separate script.  I've tried placing the CREATE
DATABASE into an EXEC ('..') but it still has the problem when it
comes to the USE [TEST] ... I keep getting 'Cannot locate entry in
sysdatabases for 'TEST' ...'

I'm using a trusted connection to MSDE.

Any help would be appreciated.

Thanks in advance.

Scott[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
Hi Scott,

Are you using SQL Server .NET Managed provider? If yes, then it should work
fine. Other way is to execute statements one-by-one

--
Val Mazur
Microsoft MVP


Scott said:
Hi,

Thanks for replying. I;ve tried separating the statements with ';'
but I'm still getting the same problem. :(

Any other ideas?

Thanks


Val Mazur said:
Hi Scott,

I think you need to separate each *logical* part of SQL statements with
the
semicolon. You cannot execute batch in one shot without it.

--
Val Mazur
Microsoft MVP


Scott said:
Hi All,

I'm trying to create my database using a batch of SQL commands via
ExecuteNonQuery(). The SQL batch should create the database and then
all the tables to go into it. The start of the script is as follows:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
N'TEST')
DROP DATABASE [TEST]

print 'Creating database TEST.'
CREATE DATABASE [TEST] ON PRIMARY
(
Name=TEST_Data,
filename = 'C:\dbtest\scripts\\TEST.mdf',
size=3,
maxsize=5,
filegrowth=10%
)
LOG ON
(
name=TEST_log,
filename='C:\dbtest\scripts\TEST.ldf',
size=3,
maxsize=20,
filegrowth=1
)


exec sp_dboption N'TEST', N'auto create statistics', N'true'

exec sp_dboption N'TEST', N'auto update statistics', N'true'

use [TEST]

set nocount on

print 'Creating branch table.'
CREATE TABLE [dbo].[branch] (
Code: 
 [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[name] [varchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[addr1] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[addr2] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[addr3] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[addr4] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[addr5] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[postcode] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[telnum] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[usemodemno] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[modemnum] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
[commreq] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[active] [char] (10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]

.... and so it goes on.

(I'm picking up the 'print' statements via InfoMessage and a
SqlInfoMessageEventHandler)

The problem I get is that it's not creating the database or rather it
seems not to be waiting for the database to be created.  I've tried
adding a WAITFOR DELAY but the execution just seems to ignore the
statement and continues.  I really don't want to split out the CREATE
DATABASE into a separate script.  I've tried placing the CREATE
DATABASE into an EXEC ('..') but it still has the problem when it
comes to the USE [TEST] ... I keep getting 'Cannot locate entry in
sysdatabases for 'TEST' ...'

I'm using a trusted connection to MSDE.

Any help would be appreciated.

Thanks in advance.

Scott[/QUOTE][/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
Hi,

I'm using System.Data.SqlClient. I've tried numerous ways of doing
this and all come up with the same result. I'm going to split the
script into logical parts and place these into separate script files.

Thanks for you help

Scott
 
Back
Top