none
ExecuteNonQuery weird timeout problem with SQL 2008 R2 RRS feed

  • Question

  • I have a verty bizarre problem I hope someone can help me with.

    From a VB.Net client I'm using ADO.Net to send a restore command to an SQLEXPRESS database:

    USE Master
    ALTER DATABASE OISTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    RESTORE DATABASE [OISTest] FROM  DISK = N'C:\OIS10\OISTest.bak' WITH FILE = 1,   Move N'OIS_dat' TO N'O:\MSSQL\Data\OISTest.mdf',  Move N'OIS_log' TO N'O:\MSSQL\Data\OISTest_log.ldf', REPLACE
    ALTER DATABASE OISTest SET MULTI_USER

    Usually this works fine but on occasion when the PC is bust with other things I experince a timeout and the DB gets left in an unusable state flagged as "restoring". So I figured I would add a timeout value to my connectstring as shown below. The restore proceeds OK but on the next attempt to acccess the restored database I get the exception message shown below:

    ?UserConnectString
    "Data Source=PC3090-086\SQLEXPRESS;Initial Catalog=OISTest;Integrated Security=True;timeout=30"
    ?ex.Message from ExecuteNonQuery:
    "A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"

    Again, if I don't specify the timeout the default of 15 works the majority of the time and subsequent access is fine. If I add the timeout parameter I get the transport-level error.
    Any idea what is going on here?

    Any other way to allow the server a bit more time to complete the restore before the timeout expires?

    I tried an Application.DoEvents between the restore SQL and the subsequent SQL and that didn't help. Also just pausing in debug doesn't help.

    Tuesday, April 3, 2012 8:35 PM

Answers

  • It's a SQL Server Error.

    You can refer to this article, http://aartemiou.blogspot.com/2009/02/transport-level-error-has-occurred-when.html 

    A similar issue here, http://forums.asp.net/p/1433838/3223633.aspx

    I think this Forum is more related, http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/threads


    Fighting like Allen Iverson. Neve give up!

    Thursday, April 5, 2012 5:42 AM