469,267 Members | 971 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

SQL - Smoke and mirrors stuff

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 ?
Jul 2 '06 #1
5 1233
"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.
Jul 2 '06 #2
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 ?

Jul 2 '06 #3
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 ?

Jul 2 '06 #4
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 ?


Jul 2 '06 #5
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 ?

Jul 3 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by DesignGuy | last post: by
6 posts views Thread by lkrubner | last post: by
reply views Thread by Pixi | last post: by
6 posts views Thread by Steve C | last post: by
1 post views Thread by Andrew Rawnsley | last post: by
9 posts views Thread by Dan Vande More | last post: by
4 posts views Thread by www.gerardvignes.com | last post: by
6 posts views Thread by Jon | last post: by
4 posts views Thread by YASIN786 | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.