CODEWITHSUNDEEP
sqlsql-serversql-server-2012asp-classic
Olesya
Olesya

Reputation: 19

SQL Server Error executing SQL stored procedure in ASP classic [Microsoft][SQL Server Native Client 11.0][SQL Server] Error

Please see the SQL server error messages I am getting when executing a SQL Server stored procedure from ASP classic.

I am using two session variables as parameters for the stored procedure.

I had no issues with this, until just recently I started getting the following error, the errors are different between production and test servers.

Why is the Session variable appended to the error description, what does it mean?

Please see the code below and the errors.

Dim strStudentID
Dim transactionId
strStudentID = Session("ix_National_ID")
transactionId = Session("transactionId")

Session("accesslevel") = ""
session("SessionStatus") = "5"

Set cmd = server.createobject("ADODB.command")
With cmd
.ActiveConnection = con
.CommandType = adCmdStoredProc
.CommandText = "Temple_sp_Application_Insert"
.Parameters.Append cmd.CreateParameter("@TUID ", adVarchar, adParamInput, 200, strStudentID)
.Parameters.Append cmd.CreateParameter("@transactionId ", adVarchar,     adParamInput, 200, transactionId)
.Execute

if Err.Number <> 0 then
  Response.Write "An Error Has Occurred on this page!<BR>"
  Response.Write "The Error Number is: " & Err.number & "<BR>"
  Response.Write "The Description given is: " & Err.Description & "<BR>"
end if

End With
Set cmd = Nothing
Set Con = Nothing

con.Close

I am getting the following error, note there is the Session variable (strStudentID) at the end of the error description.

This error I get on the production server

The Error Number is: -2147217911
The Description given is: [Microsoft][SQL Server Native Client 11.0][SQL Server]915111111

This error is on the non-production server.

The Error Number is: -2147217911
The Description given is: [Microsoft][ODBC SQL Server Driver][SQL Server]915111111

Upvotes: 1

Views: 529

Answers (1)

user692942
user692942

Reputation: 16671

Well judging by the error

The error code refers to -2147217911 which is;

[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on object 'Table_Name', database 'Database_Name', owner 'dbo'.

On Microsoft Support Site there's is some more detail;

From Microsoft Support - KB963994
The user's database login has not been granted the db_datareader role for the database.

So you need to GRANT permission to the login you are using to call the Stored Procedure or one of the tables inside it, either way we'd need more information. For whatever reason your descriptions returned are pretty meaningless.

Based on feedback

I have no idea why the Session values are appended to the half completed error output, perhaps something else is getting added to the output in the Stored Procedure? Could be rogue PRINT statement for example.

Ideally we would need to see the Stored Procedure definition to rule that out.

Upvotes: 2

Related Questions