Problem when creating database with SQL batch via ExecuteNonQuery()

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
 
V

Val Mazur

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.
 
S

Scott

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...
 
V

Val Mazur

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...
 
S

Scott

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

LINQ + .Contains(s) + sp_executesql = big performance problem 1
Simple query problem 2
SqlDependency minimum permissions 0
MappingSource could not be found (DBLinq) 0
Help creating a stored procedure to call from .Net 1
Inserting Date info into Sql Server 1
Stored Procedure Error Question 3
CURSOR PROBLEM , Database timeout, Multiple Users 3

Top