Suddenly get "sql server does not exist or access denied" after connected for ages.

Suddenly get "sql server does not exist or access denied" after connected for ages.

• James CACN

  Please go ahead and provide a simple repro. Should be fully compliable, no external dependencies (please use NorthWind db), command line app and single file if possible. Thanks.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

  
  
  

• Rob Relyea

  How exactly you're "releasing" these objects Do you call Dispose() on all of them

  
  
  

• CindyPsych

  me too got the same question ....
  
  

• trilk

  Here is some code to reproduce the error. It consists of:
  
  1) A simple stored procedure with one input parameter and one output that executes against Northwind.
  
  2) PollNorthwind Sub which calls the stored procedure at each tick interval of a timer.
  
  3) The timer's tick event code that simply calls PollNorthwind.
  
  Start a new SmartDevice Project, drop a timer onto the form and name it timPollDatabase. Set the interval to 6000 and enable it. Also add a label and name it lblPollCount to show the progress of the polling.
  
  The project will need references to System.Data.Common & System.Data.SqlClient as well as Imports to System.Data & System.Data.SqlClient.
  
  Lastly you will need to put in your own connection string.
  
  The Stored Procedure
  

  --NorthTest stored procedure USE NORTHWIND IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'NorthTest' AND TYPE = 'P') DROP PROCEDURE dbo.NorthTest GO USE NORTHWIND GO CREATE PROCEDURE dbo.NorthTest ( @productID int, @productName nvarchar(40) output ) AS SET NOCOUNT ON SET @productName = (SELECT ProductName FROM Products WHERE ProductID = @productID) GO

  
  
  
  The Sub Procedures
  

  Private Sub PollNorthwind()         Dim m_Con As SqlConnection         Dim m_Cmd As New SqlCommand         Dim success As Boolean          m_Con = New SqlConnection("Your Connection String")                    With m_Cmd             .CommandType = CommandType.StoredProcedure             .CommandText = "NorthTest"             .Connection = m_Con         End With         Try             With m_Cmd                 If .Parameters.Count = 0 Then                     .Parameters.Add("@productID", SqlDbType.Int)                     .Parameters(0).Direction = ParameterDirection.Input                     .Parameters(0).Value = "1"                     .Parameters.Add("@productName", SqlDbType.NVarChar, 40)                     .Parameters(1).Direction = ParameterDirection.Output                 End If                 .Connection.Open()                 .ExecuteNonQuery()                 .Connection.Close()             End With             success = True         Catch exSql As SqlClient.SqlException             If (MessageBox.Show(String.Format("Unable to connect to database.{0}This could be due to being out of range of a site network.{0}{1}", ControlChars.CrLf, lblPollCount.Text), "Error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1) = DialogResult.Retry) Then                 PollDatabase()             Else                 success = False             End If         Catch ex As Exception             MessageBox.Show(ex.Message)             success = False         Finally              m_Con.Close()             m_Con = Nothing             m_Cmd = Nothing         End Try         If success = True Then              timPollDatabase.Enabled = True             lblPollCount.Text = CInt(lblPollCount.Text) + 1         Else         End If     End Sub     Private Sub timPollDatabase_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles timPollDatabase.Tick          timPollDatabase.Enabled = False          PollNorthwind()     End Sub

  
  
  When you run the project you should see lblPollCount count to 70 and then generate the error.
  
  Thanks in advance to anybody that takes the time to try this even if you don't manage to find a solution. .
  

• sneaky11111111

  My code calls conn.Close as soon as the command has executed. Remember that it works well for many polls as per my original post:
  
  

  imec wrote:

  I run the application in debug mode and it successfully polls the database several (anything up to 70) times. It uses the same connection object each time who's connection is simply opened and closed for each poll.

  
  
  I only experimented with local variables going out of scope in the hope that this would call their finalize method and cleanup the resources as all of my attempts have failed. I am beginning to think that there is a deeper issue here as I cannot see a reason why conn.Open > cmd.Execute  > conn.Close couldn't be called an infinite number of times using the same conn and cmd objects. Anybody

  
  

• MMatt

  Ilya
  
  Unless I am missing something (very possible ), unlike the full .Net Framework, there is no public Dispose method for sqlConnection in the Compact Framework. There is only a protected Dispose method available.
  
  My "releasing" testing has therefore revolved around creating local variables that go out of scope once the command object has executed in the hope that the runtime would cleanup. This is not the case and in any event, as stated before, I really want to keep the object alive indefinitely for polling duties anyway. Any further ideas anybody  < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

  
  

• Lucian Ghitoc

  I did not run it yet, but this code would fail eventually because command objects are created for each call and never disposing of:
  
          Finally 
              m_Con.Close()
              m_Con = Nothing
              m_Cmd = Nothing
          End Try 
  
  Should be:
  
          Finally 
              m_Con.Close() 
              m_Cmd.Dispose()
          End Try
  
  
  

• Dave Marsh

  Your "releasing" won't release anything, you have to call Dispose(). If there's no Dispose() on SqlConnection(), please call Close() instead.
  
  You can keep them as long as you limit number of these objects. Say, one connection, one command, one data adapter. OK, may be two of each. :)
  
  

  
  
  

• Ulrich Straus

  me too got the same question ....
  
  

• wjcyg

  Are you running into this issue consistently (Connects and runs fine for a while then disconnects).
  
  I will recommend that you check your server to ensure that it is up and running.
  Also check the event log on the server.
  
  Also check if you can get the same application to work fine on the desktop.
  

• brunozck

  Hi Ilya and thanks for your post.
  
  I do not wish to dispose the connection or command objects as it seems a good idea to keep them alive for each poll rather that destroy and re-create them every time. After all, the poll simply needs to call conn.Open > cmd.Execute > conn.Close each time as the stored procedure parameters and all other properties do not change. I really only want to dispose the data access objects when I leave the form or exit the application. Having said that, in the interests of testing I have tried to create and release on each poll and still get the same issue.

  
  

• Aarvee

  Are you disposing of SQL objects (e.g. SlqConnection, SqlCommand etc.)
  
  

• Faber

  My code calls conn.Close as soon as the command has executed. Remember that it works well for many polls as per my original post:
  
  

  imec wrote:

  I run the application in debug mode and it successfully polls the database several (anything up to 70) times. It uses the same connection object each time who's connection is simply opened and closed for each poll.

  
  
  I only experimented with local variables going out of scope in the hope that this would call their finalize method and cleanup the resources as all of my attempts have failed. I am beginning to think that there is a deeper issue here as I cannot see a reason why conn.Open > cmd.Execute  > conn.Close couldn't be called an infinite number of times using the same conn and cmd objects. Anybody

  
  

• Junnys

  I am also experiencing this issue.

  < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /> 

  VS 2003

  Windows Mobile 2003 running Compact Framework 1, service pack 3 (also tried Compact Framework 2)

   

  My application is a simple form with a timer control that polls a SQL Server 2000 database every 15 seconds. I use a class level SqlClient.SqlConnection object to execute a stored procedure via a configured SqlCommand object.

   

  I run the application in debug mode and it successfully polls the database several (anything up to 70) times. It uses the same connection object each time who's connection is simply opened and closed for each poll.

   

  After the error has occurred, the project will not run on the device again until the device is soft reset. It is almost the behaviour of a memory leak of some kind although I am not creating objects that are accumulating. Additionally, the error is clearly caught in the try, catch block as sqlException "SQL Server does not exist or access denied". Very strange indeed after so many successful executions of the stored procedure.

   

  I have tried everything I can think of including:

  Deploying the app to the device and running it directly over the LAN, bypassing ActiveSync.

   

  Experimenting with different timeout values on the connection and command objects.

   

  Any help would be very much appreciated.

   

  
  
• Suddenly get "sql server does not exist or access denied" after connected for ages.