problem in creating database and table in one shot

I need to create a database and then create tables in it..

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DB1')
DROP DATABASE [DB1]

-- create a new database
CREATE DATABASE [DB1] ON PRIMARY
( NAME = N'DB1', FILENAME = N'D:\DB1.mdf' ,
SIZE = 51200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 30720KB )
LOG ON
( NAME = N'DB1_log', FILENAME = N'D:\DB1_log.ldf' , SIZE = 2048KB , MAXSIZE = UNLIMITED , FILEGROWTH = 30720KB )
COLLATE Latin1_General_CI_AS

CREATE TABLE [DB1].[dbo].[SALES](
[PERIOD] [int] NOT NULL,
[LOC] [nchar](3) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]

If i run the above statement one after one, they work; however, if I run all of them together (or in sp), the following error raised:

Msg 2702, Level 16, State 2, Line 43
Database 'DB1' does not exist.

May I know how can i create a database and then immediately the tables in sp!

Thanks

use master;

go

drop database mydatabase;

go

create database mydatabase;

go

use mydatabase;

go

create table mytable(i int );

|||Thx, but it doesn't work in stored procedure.|||

HI;

EXEC('CREATE TABLE [DB1].[dbo].[SALES](
[PERIOD] [int] NOT NULL,
[LOC] [nchar](3) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]')

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||thx

Problem Dropping Database C#

Hi,

i have a problem dropping a database?

I have a WinForm and i can create DB, create the Tables and drop the DB via buttons.

When i start myProgram it's no problem to drop the Database.
Then i can create the database without problems.
The tables also without problems.

Now if i try to drop my whole DB, i get the error message:

SqlException Handle :System.Data.SqlClient.SqlException: Cannot drop database "XtmsDb" because it is currently in use.

The code for dropping db:

public void deleteProjectDB()
{
conn.ConnectionString = "Data Source=TURM21;Initial Catalog=master;Integrated Security=SSPI;";

sqlStr = "Drop database XTmsDB";

try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");

SqlCommand cmd = new SqlCommand(sqlStr, conn);
cmd.ExecuteNonQuery();
System.Console.WriteLine("Database dropped!!!");
}
catch (SqlException dropEx)
{
System.Console.WriteLine("SqlException Handle :{0}", dropEx.ToString());
}
finally
{
conn.Close();
System.Console.WriteLine("Connection closed!!!");
}
}

The code for creating db:

public void createProjectDB()
{
conn.ConnectionString = "Data Source=TURM21;Initial Catalog=master;Integrated Security=SSPI;";
sqlStr = projectDBStrings.getDBCreateString();

try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");
SqlCommand cmd = new SqlCommand(sqlStr, conn);
cmd.ExecuteNonQuery();
System.Console.WriteLine("Database created!!!");

}
catch (Exception)
{
System.Console.WriteLine("Could not establish Connection!");
}
finally
{
conn.Close();
System.Console.WriteLine("Connection closed!!!");
}
}

The code for creating Tables:

public void createProjectTables()
{
conn.ConnectionString = "Data Source=TURM21;Initial Catalog=XtmsDb;Integrated Security=SSPI;";
ArrayList createList = projectDBStrings.fillProjectSchema();
SqlTransaction tx;
SqlCommand cmd = new SqlCommand("", conn);
IEnumerator createListEnum = createList.GetEnumerator();

try
{
System.Console.WriteLine("Opening Connection...");
conn.Open();
System.Console.WriteLine("Connection opened!!!");
try
{
while (createListEnum.MoveNext())
{
sqlStr = (String)createListEnum.Current.ToString();
tx = conn.BeginTransaction();

cmd.CommandText = sqlStr;
cmd.Transaction = tx;
cmd.ExecuteNonQuery();

tx.Commit();

}
System.Console.WriteLine("Schema created!!!");

}
catch (SqlException deleteEx)
{
System.Console.WriteLine("SqlException Handle :{0}", deleteEx.ToString());
}
}
catch (SqlException connectionEx)
{
System.Console.WriteLine("SqlConnection Handle : {0}", connectionEx.ToString());
}
finally
{
conn.Close();

System.Console.WriteLine("Connection closed!!!");
}
}

Who can help me?You can't drop a database while you (or anyone) are connected to it. You will need to switch your connection to a different database before issuing your command.|||But i closed the connection. Isn't that enough. How can i change connection to other DB? SOrry, but i'm a beginner.

Greetz|||Ok i know hoe to change. But why isnt it enough to close connection?|||I'm not an application programmer, but if you close the connection how can you issue ANY command to the server?|||I like to think I'm an application programmer.

Just looking at the code... isn't Crean connected to Master when he tries to drop the db?|||yes, i'm connecting to master, so i don't know where the problem is.

I changed to another db

conn.ChangeDatabase("XTMSTEST2");

Now it works. But its not a nice solution.

Anyone knows what the problem is?|||If there are any open connections to the database you cannot drop the database and the error is self-explanatory. In general it is better to use master database whenever dropping any user datatabase.|||Yes but i switched to master in the connection string.

But I solved my Problem. U have to turn pooling off in connection String.

On master and on xtms

Connection string looks like this:

Data Source=TURM21;Initial Catalog=master;Integrated Security=SSPI;pooling false

So thats the solution. But what are the drawbacks if pooling is on false?

Really a great forum. U always get an answer. Great!!!!!!!!!!!

Greets|||Looks like you just GAVE the answer.

Thanks for posting the solution.|||U can improve the perfomance of ur application by enabling pooling.
Applications often have different users performing the same type of database access.
For example, many users might be querying the same database to get the same data. In those cases, the performance of the application can be enhanced by having the application pool, connections to the data source.

The overhead of having each user open and close a separate connection can otherwise have an adverse effect on application performance.|||U can improve the perfomance of ur application by enabling pooling.
Applications often have different users performing the same type of database access.
For example, many users might be querying the same database to get the same data. In those cases, the performance of the application can be enhanced by having the application pool, connections to the data source.

The overhead of having each user open and close a separate connection can otherwise have an adverse effect on application performance.In most cases, this is quite correct. In this case, since the pooled connection stays in a database that needs to be unused in order to be dropped, turning off the pooling for this particular operation seems to be required, not optional.

-PatP|||thx all.

Thread closed. ;)

problem creation, script with create view

I created a script like this :
use tk_main
GO
if exists (select table_name from information_schema.views where table_name
= 'V_08701')
drop view V_08701
GO
CREATE VIEW V_08701 (D_ATE,NO_ENVOI,ARRIVEE,TOT_COLIS) AS SELECT
D_ATE,NO_ENVOI,ARRIVEE,SUM(NB_COLIS) FROM POSBAR_L GROUP BY
D_ATE,NO_ENVOI,ARRIVEE
GO
grant all on V_08701 to public
GO
if exists (select table_name from information_schema.views where table_name
= 'V95001') drop view V_95001
GO
CREATE VIEW V_95001 (NO_FAC,NO_ENVOI,ARRIVEE,NOMBRE) AS SELECT
NO_FAC,NO_ENVOI_TK,ARRIVEE,COUNT(NO_ENVO
I_TK) FROM FACTURE_IMP_D GROUP BY
NO_FAC,NO_ENVOI_TK,ARRIVEE
GO
if exists (select table_name from information_schema.views where table_name
= 'V_08601') drop view V_08601
GO
CREATE VIEW V_08601
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO
IDS,TYP_SCANNAGE) AS SELECT ARRIVEE.DTE_ARR_DEP, ARRIVEE.TRANSPORTEUR,
ARRIVEE.ARRIVEE,POSBAR_E.NO_ENVOI,
ARRIVEE.OPERATION_C_D,ARRIVEE.ETAT_ARRIVEE, POSBAR_E.ETAT,
POSBAR_E.POIDS,POSBAR_E.TYP_SCANNAGE FROM ARRIVEE, POSBAR_E WHERE
ARRIVEE.ARRIVEE = POSBAR_E.ARRIVEE
GO
but the analyser doesn't like this script.
Can someboady help me out.
Thanks in advance
RalfWhat error messages do you get?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ralf Meuser" <rmeuser@.free.fr> wrote in message
news:40618606$0$7376$626a14ce@.news.free.fr...
> I created a script like this :
> use tk_main
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V_08701')
> drop view V_08701
> GO
> CREATE VIEW V_08701 (D_ATE,NO_ENVOI,ARRIVEE,TOT_COLIS) AS SELECT
> D_ATE,NO_ENVOI,ARRIVEE,SUM(NB_COLIS) FROM POSBAR_L GROUP BY
> D_ATE,NO_ENVOI,ARRIVEE
> GO
> grant all on V_08701 to public
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V95001') drop view V_95001
> GO
> CREATE VIEW V_95001 (NO_FAC,NO_ENVOI,ARRIVEE,NOMBRE) AS SELECT
> NO_FAC,NO_ENVOI_TK,ARRIVEE,COUNT(NO_ENVO
I_TK) FROM FACTURE_IMP_D GROUP BY
> NO_FAC,NO_ENVOI_TK,ARRIVEE
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V_08601') drop view V_08601
> GO
> CREATE VIEW V_08601
>
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO[col
or=darkred]
> IDS,TYP_SCANNAGE) AS SELECT ARRIVEE.DTE_ARR_DEP, ARRIVEE.TRANSPORTEUR,
> ARRIVEE.ARRIVEE,POSBAR_E.NO_ENVOI,
> ARRIVEE.OPERATION_C_D,ARRIVEE.ETAT_ARRIVEE, POSBAR_E.ETAT,
> POSBAR_E.POIDS,POSBAR_E.TYP_SCANNAGE FROM ARRIVEE, POSBAR_E WHERE
> ARRIVEE.ARRIVEE = POSBAR_E.ARRIVEE
> GO
> --
> but the analyser doesn't like this script.
> Can someboady help me out.
> Thanks in advance
> Ralf
>
>[/color]|||Sorry I forgot to sedn the error :
Serveur : Msg 170, Niveau 15, tat 1, Procdure V_08701, Ligne 2
Ligne 2 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 1
Ligne 1 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 2
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 3
Ligne 3 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 5
Ligne 5 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 6
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 7
Ligne 7 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 9
Ligne 9 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 10
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 11
Ligne 11 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 13
Ligne 13 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 14
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Ralf
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
dans le message de news:em9NhSaEEHA.3980@.TK2MSFTNGP09.phx.gbl...
> What error messages do you get?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Ralf Meuser" <rmeuser@.free.fr> wrote in message
> news:40618606$0$7376$626a14ce@.news.free.fr...
> table_name
> table_name
BY
> table_name
>
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO[col
or=darkred]
>|||Since this is an English speaking newsgroup, it would be helpful if you woul
d translate the French messages
instead of letting us do that.
I don't see a problem with this, unless you actually have a line-break in th
e middle of a column name in your
code as well (I assume it is inserted by your newsreader).
Perhaps someone has changed the batch separator (from GO to something else)
in Query Analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ralf Meuser" <rmeuser@.free.fr> wrote in message news:406197b3$0$309$626a14ce@.news.free.fr.
.
> Sorry I forgot to sedn the error :
> Serveur : Msg 170, Niveau 15, tat 1, Procdure V_08701, Ligne 2
> Ligne 2 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 1
> Ligne 1 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 2
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 3
> Ligne 3 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 5
> Ligne 5 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 6
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 7
> Ligne 7 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 9
> Ligne 9 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 10
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 11
> Ligne 11 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 13
> Ligne 13 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 14
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
>
> Ralf
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
> dans le message de news:em9NhSaEEHA.3980@.TK2MSFTNGP09.phx.gbl...
> BY
> (DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,
PO
>