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.Applicat ion sqlApp = new SQLDMO.Applicat ionClass();
SQLDMO.SQLServe r sqlServer = new SQLDMO.SQLServe rClass();
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.AllowNull s = false;
colId.IdentityI ncrement = 1;
blah blah blah...
"Ian Semmel" <is***********@ NOKUNKrocketcom p.com.auwrote in message
news:uG******** ******@TK2MSFTN GP02.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 ?