Problem with disable msde connection pool

[Mandy]

Hello,

To resotre MSDE database, the connection pool must be disabled. I set
pooling=false in the connection string, i.e.,

SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
database=mydb; uid=sa; password=abc; pooling=false;");

It seems that the connection pool was not disabled. Here are the error
messages I got:

[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could
not be obtained because the database is in use.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE is
terminating abnormally.

Any ideas? Are there any other ways to disable the connection pool?

Any help would be appreciated!

Mandy

 

[William \(Bill\) Vaughn]

I talk about this in my ADO workshop. One approach (that seems to work) is
to use SQL
DMO to stop the server and restart in single-user mode. The problem with the
pools is that they don't close the connections for 4-8 minutes (or so) after
disconnect unless the application is ended. DMO forces the server to
disconnect and close. After that you can execute the restore from SQL DMO.

My next workshop is in Chicago in mid October--if there are still seats
left.

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Mandy]

Hi Bill,

Thanks for your reply! I tried to shut down the sql server, then start
the sql
server again. Unfortunately, it did not work. The following is the
code:

//create an instance of a server class
SQLDMO.SQLServer srv = new SQLDMO.SQLServer();
//connect to the server
srv.Connect("local)\\NetSDK", "sa", "abc");
try
{
srv.Shutdown(5000); // stop db server, wait for 2 seconds
}
catch (Exception e)
{
; // do nothing
}

Thread.Sleep(10000); // sleep 10 seconds
// restart server
srv.Start(true, "(local)\\NetSDK", "sa", "abc");

//create a restore class instance
SQLDMO.Restore restore = new SQLDMO.Restore();
restore.Action = 0; // full db restore
//set the database to the chosen database
restore.Database = "mydbfile";
restore.Files = Path.GetFullPath(@"..\..\backup\DBLPS.bak");

SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
uid=sa; password=abc; pooling=false");

// Restore the database
restore.ReplaceDatabase = true;
restore.SQLRestore(srv);

An exception is thrown at srv.Start with message:

"An unhandled exception of type
'System.Runtime.InteropServices.COMException' occurred in myapp.exe

Additional information: [SQL-DMO]This server object is already
connected."

I'd like to know what your approach is to use SQL DMO to stop and
restart the SQL server?

Thanks a lot!

Mandy

I talk about this in my ADO workshop. One approach (that seems to work) is
to use SQL
DMO to stop the server and restart in single-user mode. The problem with the
pools is that they don't close the connections for 4-8 minutes (or so) after
disconnect unless the application is ended. DMO forces the server to
disconnect and close. After that you can execute the restore from SQL DMO.

My next workshop is in Chicago in mid October--if there are still seats
left.

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hello,

To resotre MSDE database, the connection pool must be disabled. I set
pooling=false in the connection string, i.e.,

SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
database=mydb; uid=sa; password=abc; pooling=false;");

It seems that the connection pool was not disabled. Here are the error
messages I got:

[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could
not be obtained because the database is in use.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE is
terminating abnormally.

Any ideas? Are there any other ways to disable the connection pool?

Any help would be appreciated!

Mandy

 

[William \(Bill\) Vaughn]

This is the SQL-DMO example I've been using for some time to illustrate
restoring a database... It's in VB6, but perhaps you'll get this to work.

Public Function RestoreDatabase() As Boolean
' See article text re the need for the following TSQL command in some
versions.
'ALTER DATABASE TestDB SET single_user WITH ROLLBACK after 10
On Error GoTo RDEH

' Ensure that there are no connections in the pool or elsewhere.
ShutDownServer ' Stop server

' Restart the server... but in single-user mode
Set oSvr = New SQLDMO.SQLServer
oSvr.LoginTimeout = 30 ' Wait 30 seconds to complete start
and open
oSvr.Start True, "(local)", "sa", strSAPw ' Start and connect to local
server.
i = 0
Do
Sleep 100
i = i + 1 ' Only wait 500 x 100 ms (50 seconds)
Loop Until oSvr.Status = SQLDMOSvc_Running Or i > 500
If i > 500 Then Err.Raise -8

oSvr.Databases(strDataBase, "DBO").DBOption.SingleUser = True

' Execute query through SQLDMO to run the restore TSQL command
strS = "RESTORE DATABASE TestDB FROM [" & strDataBase & " Backup Device]"
Debug.Print strS
oSvr.ExecuteImmediate strS

' Change the Database back to multi-user mode.
oSvr.Databases(strDataBase, "DBO").DBOption.SingleUser = False
' Close the SQLDMO Server object... we don't need it anymore.
oSvr.Close
Set oSvr = Nothing
RestoreDatabase = True


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hi Bill,

Thanks for your reply! I tried to shut down the sql server, then start
the sql
server again. Unfortunately, it did not work. The following is the
code:

//create an instance of a server class
SQLDMO.SQLServer srv = new SQLDMO.SQLServer();
//connect to the server
srv.Connect("local)\\NetSDK", "sa", "abc");
try
{
srv.Shutdown(5000); // stop db server, wait for 2 seconds
}
catch (Exception e)
{
; // do nothing
}

Thread.Sleep(10000); // sleep 10 seconds
// restart server
srv.Start(true, "(local)\\NetSDK", "sa", "abc");

//create a restore class instance
SQLDMO.Restore restore = new SQLDMO.Restore();
restore.Action = 0; // full db restore
//set the database to the chosen database
restore.Database = "mydbfile";
restore.Files = Path.GetFullPath(@"..\..\backup\DBLPS.bak");

SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
uid=sa; password=abc; pooling=false");

// Restore the database
restore.ReplaceDatabase = true;
restore.SQLRestore(srv);

An exception is thrown at srv.Start with message:

"An unhandled exception of type
'System.Runtime.InteropServices.COMException' occurred in myapp.exe

Additional information: [SQL-DMO]This server object is already
connected."

I'd like to know what your approach is to use SQL DMO to stop and
restart the SQL server?

Thanks a lot!

Mandy

I talk about this in my ADO workshop. One approach (that seems to work)
is
to use SQL
DMO to stop the server and restart in single-user mode. The problem with
the
pools is that they don't close the connections for 4-8 minutes (or so)
after
disconnect unless the application is ended. DMO forces the server to
disconnect and close. After that you can execute the restore from SQL
DMO.

My next workshop is in Chicago in mid October--if there are still seats
left.

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________

Hello,

To resotre MSDE database, the connection pool must be disabled. I set
pooling=false in the connection string, i.e.,

SqlConnection conn = new SqlConnection("server=(local)\\NetSDK;
database=mydb; uid=sa; password=abc; pooling=false;");

It seems that the connection pool was not disabled. Here are the error
messages I got:

[Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could
not be obtained because the database is in use.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE is
terminating abnormally.

Any ideas? Are there any other ways to disable the connection pool?

Any help would be appreciated!

Mandy