none
Error for logshipping restore job RRS feed

  • Question

  • Error: execute non query requires an open and available connection. the connection's current state is closed.

    can you please give us solution for this error please so that we are free from this problem as we are getting this error so many times

    Thanku 
    swissmaster
    Wednesday, September 7, 2016 7:37 AM

Answers

All replies

  • here is the complete error 


    Error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.(System.Data) 
    Skipping log backup file 'G:\xxxxx\xxxxxx\xxxxxx_20160907061500.trn' for secondary database 'xxxxxxxx' because the file could not be verified.
    Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping)
    Wednesday, September 7, 2016 7:40 AM
  •  we are looking for quick reply.


    Thanku
    swissmaster
    Wednesday, September 7, 2016 7:42 AM
  • Please post your code. We cannot troubleshoot this issue without seeing how you are opening the connection and executing the query. It sounds like the SqlCommand object is not being properly set up.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, September 7, 2016 12:06 PM
  • You need to use code something like this before your ExecuteNonQuery():

    sqlCommand.Connection.Open();
    sqlCommand.ExecuteNonQuery();
    // other SqlCommands, if needed
    sqlCommand.Connection.Close();
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, September 7, 2016 1:44 PM
  • Hi swissmaster,

    According to your description, it seems that you execute SQL statement without opening connection. As Paul said, I would suggest that you'd better post related code, it will be beneficial to resolve the issue.

    In addition, I search on webs and find some similar thread for your reference.

    http://stackoverflow.com/questions/3548056/executenonquery-requires-an-open-and-available-connection-the-connections-curr

    http://stackoverflow.com/questions/28555564/executenonquery-requires-an-open-and-available-connection-the-connections-curr

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, September 8, 2016 1:15 AM
    Moderator
  • As I said before we have just configured Log Shipping for our databases.  LS is working fine for all the Databases except one database. I mentioned the error in above message.

     Is there any alternate solution for these issue without touching code. 

    Thanku
    swissmaster

    Thursday, September 8, 2016 7:31 AM
  • As I said before we have just configured Log Shipping for our databases.  LS is working fine for all the Databases except one database. I mentioned the error in above message.

     Is there any alternate solution for these issue without touching code. 

    Thanku
    swissmaster

    Not if you have a bug in your code, which of course we can't tell unless you post it.

    One tip, if you are re-using or maintaining persistent connections then I would modify your code to only open a connection when needed and then close it after the database call has completed or is no longer required.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, September 8, 2016 2:01 PM
  • This is the first time, in this thread, that you've said it works fine for other databases. My guess is that you've made a mistake in the configuration ... but of course, without you posting anything at all, code-wise or configuration-wise, we really can't be of too much help.

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, September 8, 2016 2:51 PM
  • ok paul .


    Thanku
    swissmaster 

    Friday, September 9, 2016 7:29 AM