SQL - Smoke and mirrors stuff

[Ian Semmel]

It is hard to test SQL because running a test twice does not give the same results.

Anyway, what I am trying to do is create a database (in initialization
circumstances) and do the following by Executing .sql scripts

DROP database
CREATE database
CREATE each table

So far so good

Then when I try to use the database, as in the normal case, I get an error like
'Database in use by another process' and test fails.

Try the test again

This time get the error 'Login failed'

I think what I need is to somehow really close the database because I think that
there are connections hanging around (I am not sure because I don't know).

Are there some settings/commands to handle this situation ?

[Mark Rae]

"Ian Semmel" <is***********@NOKUNKrocketcomp.com.auwrote in message
news:uG**************@TK2MSFTNGP02.phx.gbl...

It is hard to test SQL because running a test twice does not give the same
results.

???

Anyway, what I am trying to do is create a database (in initialization
circumstances) and do the following by Executing .sql scripts

DROP database
CREATE database
CREATE each table

This is a C# forum - please show the C# code which is not working as you
expect.

[Nicholas Paldino [.NET/C# MVP]]

Ian,

When you are using the database, are you actually disposing of all the
connections? Are you also sure that others are closing all the connections?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Ian Semmel" <is***********@NOKUNKrocketcomp.com.auwrote in message
news:uG**************@TK2MSFTNGP02.phx.gbl...

It is hard to test SQL because running a test twice does not give the same
results.

Anyway, what I am trying to do is create a database (in initialization
circumstances) and do the following by Executing .sql scripts

DROP database
CREATE database
CREATE each table

So far so good

Then when I try to use the database, as in the normal case, I get an error
like 'Database in use by another process' and test fails.

Try the test again

This time get the error 'Login failed'

I think what I need is to somehow really close the database because I
think that there are connections hanging around (I am not sure because I
don't know).

Are there some settings/commands to handle this situation ?

[chanmm]

That sound to me you are attaching the mdf file in VS 2005 using connection
string. I would say attach your mdf to SQL 2005 server and use the database
in the server.

chanmm

"Ian Semmel" <is***********@NOKUNKrocketcomp.com.auwrote in message
news:uG**************@TK2MSFTNGP02.phx.gbl...

It is hard to test SQL because running a test twice does not give the same
results.

Anyway, what I am trying to do is create a database (in initialization
circumstances) and do the following by Executing .sql scripts

DROP database
CREATE database
CREATE each table

So far so good

Then when I try to use the database, as in the normal case, I get an error
like 'Database in use by another process' and test fails.

Try the test again

This time get the error 'Login failed'

I think what I need is to somehow really close the database because I
think that there are connections hanging around (I am not sure because I
don't know).

Are there some settings/commands to handle this situation ?

[Ian Semmel]

This sounds like exactly what I am doing, but I am not sure what your solution
actually means.

chanmm wrote:

That sound to me you are attaching the mdf file in VS 2005 using connection
string. I would say attach your mdf to SQL 2005 server and use the database
in the server.

chanmm

"Ian Semmel" <is***********@NOKUNKrocketcomp.com.auwrote in message
news:uG**************@TK2MSFTNGP02.phx.gbl...

>>It is hard to test SQL because running a test twice does not give the same
results.

Anyway, what I am trying to do is create a database (in initialization
circumstances) and do the following by Executing .sql scripts

DROP database
CREATE database
CREATE each table

So far so good

Then when I try to use the database, as in the normal case, I get an error
like 'Database in use by another process' and test fails.

Try the test again

This time get the error 'Login failed'

I think what I need is to somehow really close the database because I
think that there are connections hanging around (I am not sure because I
don't know).

Are there some settings/commands to handle this situation ?

[warlord]

I'm doing something similar and in the beginning I did as you are now -
trying sql scripts to achieve this.

Eventually I moved onto SQLDMO with some hard work, it did get easier and
more controllable. Even through things like adding keys and foreign keys.

Some pointers:

Add a reference to SQLDMO
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();

SQLDMO.SQLServer sqlServer = new SQLDMO.SQLServerClass();

SQLDMO.Database sqlDB = new SQLDMO.Database();

SQLDMO.DBFile sqlMDF = new SQLDMO.DBFile();

SQLDMO.LogFile sqlLDF = new SQLDMO.LogFile();

SQLDMO.Table tbl = new SQLDMO.Table();

tbl.Name = "tblmaster";

SQLDMO.Column colId = new SQLDMO.Column(); // Add the ID Column

colId.Name = "id";

colId.Datatype = "int";

colId.AllowNulls = false;

colId.IdentityIncrement = 1;

blah blah blah...
"Ian Semmel" <is***********@NOKUNKrocketcomp.com.auwrote in message
news:uG**************@TK2MSFTNGP02.phx.gbl...

It is hard to test SQL because running a test twice does not give the same
results.

Anyway, what I am trying to do is create a database (in initialization
circumstances) and do the following by Executing .sql scripts

DROP database
CREATE database
CREATE each table

So far so good

Then when I try to use the database, as in the normal case, I get an error
like 'Database in use by another process' and test fails.

Try the test again

This time get the error 'Login failed'

I think what I need is to somehow really close the database because I
think that there are connections hanging around (I am not sure because I
don't know).

Are there some settings/commands to handle this situation ?