asked on

Problem calling SQL Server stored procedure from classic ASP

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!

slamhound

What line do you get the error on?

savitha

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jgbader

ASKER

angellll,

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.

Thanks!

Guy Hengel [angelIII / a3]

savitha actually gave you the reason. the "-" would be interpreted, without the [] around the name, as math operation...

jgbader

ASKER

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