Wheeldo

Hi all,

I'm developing a PDA app using the compact framework. It connects to a SQL server 2000 database calling stored procedures to read data and update it etc..

It all works absolutely fine for 10 minutes or so - reading and writing lots of data. Then all at once I get the error "sql server does not exist or access denied".

In my error handler I try to reconnect - but it seems once this error has happened it's not possible to reconnect.

I don't know whether it makes a difference but the PDA is connecting to a database on my machine via a USB connection and the ActiveSync (the very latest version) program.

Any ideas would be much appreciated.



Re: .NET Compact Framework Suddenly get "sql server does not exist or access denied" after connected for ages.

Mark Ihimoyan

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.




Re: .NET Compact Framework Suddenly get "sql server does not exist or access denied" after connected for ages.

imec

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.

 





Re: .NET Compact Framework Suddenly get "sql server does not exist or access denied" after connected for ages.

Ilya Tumanov

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




Re: .NET Compact Framework Suddenly get "sql server does not exist or access denied" after connected for ages.

50322899

me too got the same question ....




Re: .NET Compact Framework Suddenly get "sql server does not exist or access denied" after connected for ages.

imec

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.





Re: .NET Compact Framework Suddenly get "sql server does not exist or access denied" after connected for ages.

Ilya Tumanov

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






Re: .NET Compact Framework Suddenly get "sql server does not exist or access denied" after connected for ages.

saffron

me too got the same question ....




Re: .NET Compact Framework Suddenly get "sql server does not exist or access denied" after connected for ages.

imec

Ilya

Unless I am missing something (very possible Smile), 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" />





Re: .NET Compact Framework Suddenly get "sql server does not exist or access denied" after connected for ages.

Ilya Tumanov

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. :)






Re: .NET Compact Framework Suddenly get "sql server does not exist or access denied" after connected for ages.

imec

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





Re: .NET Compact Framework Suddenly get "sql server does not exist or access denied" after connected for ages.

imec

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





Re: .NET Compact Framework Suddenly get "sql server does not exist or access denied" after connected for ages.

Ilya Tumanov

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" />






Re: .NET Compact Framework Suddenly get "sql server does not exist or access denied" after connected for ages.

imec

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. Smile .




Re: .NET Compact Framework Suddenly get "sql server does not exist or access denied" after connected for ages.

Ilya Tumanov

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