Problem calling SQL Server stored procedure from classic ASP

Posted on 2007-10-02
Last Modified: 2011-09-20
Hi, I'm trying to call a SQL Server 2005 stored procedure from a classic ASP page using ADO.  I have verified that the connection string is correct, the user login has permissions to execute the procedure in question, and that the procedure itself functions properly.  I can reproduce the results in the ASP page by passing in hard-coded SQL.  However, when I call .Execute, I get this error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Syntax error or access violation

The relevant code snippet is below:

Dim Cmd : Set Cmd = Server.CreateObject("ADODB.Command")
Dim root
With Cmd
  .ActiveConnection = Cn
  .CommandType = adCmdStoredProc
  .CommandText = "getNAIC-ID"
  .Parameters.Append .CreateParameter("@naic_code", adVarChar, adParamInput, 15, Session("SalesImaging_CompanyNum"))
  .Parameters.Append .CreateParameter("@naic_id", adInteger, adParamOutput, ,0)
  .Execute ,,adExecuteNoRecords
  root = .Parameters("@naic_id")
End With

Sorry if I'm missing the obvious - thanks!
Question by:jgbader
    LVL 10

    Expert Comment

    What line do you get the error on?

    Expert Comment

    .CommandText = "getNAIC-ID"

    I think the error is due to the " - " in your stored porcedure name . Please change the SP name, as for eg. "getNAIC_ID"  and check it.
    LVL 142

    Accepted Solution

    or like this:

      .CommandText = "[getNAIC-ID]"

    Author Comment


    That worked perfectly.  As a matter of curiosity, is there a particular reason why those brackets should matter?  I've never needed them before.  Of course, all I've used before is .NET web apps, not classic ASP.

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    savitha actually gave you the reason. the "-" would be interpreted, without the [] around the name, as math operation...

    Author Comment

    Indeed.  A math operation?  I hadn't thought of that.  Thanks again!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
    This video discusses moving either the default database or any database to a new volume.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now