Re: Problem with disable msde connection pool
From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 09/24/04
- Next message: William \(Bill\) Vaughn: "Re: Best Practice data to int[]"
- Previous message: sbhmf: "DataBoundLiteralControl difficulties"
- In reply to: Mandy: "Re: Problem with disable msde connection pool"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 24 Sep 2004 11:09:40 -0700
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.
__________________________________
"Mandy" <peisang2002@yahoo.com> wrote in message
news:5289bbae.0409230714.678510d7@posting.google.com...
> 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
>
>
>
> "William \(Bill\) Vaughn" <NoSpamAtAllBillva@nwlink.com> wrote in message
> news:<eT35ZdRoEHA.3324@TK2MSFTNGP12.phx.gbl>...
>> 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" <peisang2002@yahoo.com> wrote in message
>> news:5289bbae.0409221333.1d31c9aa@posting.google.com...
>> > 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
- Next message: William \(Bill\) Vaughn: "Re: Best Practice data to int[]"
- Previous message: sbhmf: "DataBoundLiteralControl difficulties"
- In reply to: Mandy: "Re: Problem with disable msde connection pool"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
