[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Problem calling SQL Server stored procedure from classic ASP

Posted on 2007-10-02
Medium Priority
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

ID: 20002975
What line do you get the error on?

Expert Comment

ID: 20003968
.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 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20004619
or like this:

  .CommandText = "[getNAIC-ID]"
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.


Author Comment

ID: 20006925

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 143

Expert Comment

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

Author Comment

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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

872 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