Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Posts
    84

    Red face Unanswered: incorrect syntax after server up for a few days

    Dear All,

    My VB.Net application connects to MSSQL. It is always running fine for a few days, but encounters "incorrect syntax" as following unless the server is restarted.

    ----
    Exception occurred System.Runtime.InteropServices.COMException (0x80040E14): Line 1: incorrect syntax near 'CDO'.
    at ADODB.ConnectionClass.Execute (String CommandText, Object& RecordsAffected, Int32 Options)
    ----

    There are a few applications in the server. If certain service is stopped, my program continues to run. So I am sure that the MSSQL connections have been taken up, which causes the error. How to prove it? And is there any way to reserve some DB connections to a particular application only?

    Thanks for any hint!

    I attach my codes below. Anything wrong with the way that I handled the ADODB?


    ------------------------------------------------------------
    Public Sub SendAllEmails()
    Try
    Dim cn As ADODB.Connection = openConn()
    Dim rs As ADODB._Recordset
    Dim rs2 As ADODB._Recordset
    sqlstmt = "select * FROM EMAILTABLE"
    rs = cn.Execute(sqlstmt)

    While Not rs.EOF

    Dim MAIL_ADD_USED As String = rs.Fields("MAIL_ADD_USED").Value.ToString

    sqlstmt = "select * from NAMETABLE where EMAIL = '" & MAIL_ADD & "'"
    rs2 = cn.Execute(sqlstmt)


    If Not rs2.EOF And ErrMsg = "" Then

    ErrMsg = SendMail(MAIL_ADD, REPORT_TITLE)

    If Not ErrMsg Is Nothing And ErrMsg.Equals("Success") Then
    MAIL_DATE_SENT = Now.ToString
    MAIL_STATUS = "S"

    'wait to make sure the email is sent
    System.Threading.Thread.Sleep(1000 * 30)
    Else
    MAIL_STATUS = "F"
    End If

    End If

    rs2.Close()

    sqlstmt = "update EMAILTABLE set " & _
    " MAIL_STATUS = " & MAIL_STATUS & "," & _
    " MAIL_ERRMSG = null" & _
    " where EMAIL = " & MAIL_ADD

    cn.Execute(sqlstmt)

    rs.MoveNext()

    End While

    rs.Close()
    cn.Close()

    Catch e As Exception
    EventLog1.WriteEntry("Exception: " & e.ToString)
    End Try

    End Sub
    ----------------------------------------------------------------------

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by qxz
    Dear All,

    My VB.Net application connects to MSSQL. It is always running fine for a few days, but encounters "incorrect syntax" as following unless the server is restarted.

    --
    Are you using VB.Net? Then how come Recordset come into existence...
    See the BOL to use VB.net (ADO.Net)
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Apr 2002
    Posts
    84
    Well I took over the codes from the programmer. vb.net is quite new to me. thanks for the hint but is there any sample about how to make use of ado.net?

    Actually I am more keen to know whether the MSSQL connection can be released and allocated by DBA. There are othere applications that I simply have no control.

    Thanks.

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by qxz
    Well I took over the codes from the programmer. vb.net is quite new to me. thanks for the hint but is there any sample about how to make use of ado.net?

    Actually I am more keen to know whether the MSSQL connection can be released and allocated by DBA. There are othere applications that I simply have no control.

    Thanks.


    Check these....
    sending mail

    Ado.net stored proc use

    All about Ado.net

    And you can always use Profiler to check the status of your server.And to kill process check this
    Kill Process
    Last edited by rudra; 09-28-06 at 07:04.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •