How to get SQL errors occured during Connection.execute() or RecordSet.open() ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

germul - 16 Aug 2007 14:58 GMT

Hello,

my server side VBScript code works fine and is as follows

dim con, param, sql
set con = Server.CreateObject("ADODB.Connection")
param = "Provider=sqloledb;Data Source=MYPC;User
Id=test;Password=test;"
sql = "EXEC Database.dbo.my_stored_procedure"
con.open (param)
con.execute (sql)

Now my problem:
I know that a SQL error occures because the user "test" does not have
any rights.
BUT how can my VBScript get any knowledge about this error?
The above code runs without any problems, no error occurs, and that's
very bad.
The code "thinks" everything (that means the execution of the SQL
statement) is fine but it is definitely not. But my code does not get
any error.

Btw.: I wrote the same in JScript and put everything into a try-catch
block, but no exception occurs; the code is running without problems
and doesn't get any knowledge of the occured SQL error.

My question is:
how can my code get knowledge about an occured SQL error during
execution of my SQL statement which is a call of a stored procedure?

Thanks in advance for any advice!

Regards,
Gernot Mulisch

Reply to this Message

Richard Mueller [MVP] - 16 Aug 2007 16:43 GMT

> Hello,
>
[quoted text clipped - 25 lines]
> how can my code get knowledge about an occured SQL error during
> execution of my SQL statement which is a call of a stored procedure?

I find an error is raised on the Open statement (con.Open) if the
user/password combination is incorrect. An error is raised on the Execute
statment if the user lacks permission to run the sql statement (or the sql
statement raises any error). Do you have "On Error Resume Next" in the
script? Or, is the stored procedure doing something to suppress errors? We
may need to see details on the stored procedure. What do you mean when you
say the sql statement is not fine?

Signature

Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

Reply to this Message

germul - 17 Aug 2007 09:38 GMT

Hello,

thanks for your reply.

No, I don't have anything that could suppress errors, neither in the
VB code nor in the SQL itself. I want the errors.
The error which shall occur is that the user "test" (named in the
connection string) does not have any rights on a certain database.

Regarding to my VBScript code in the first message of this thread, I
call the SQL statement three times:

a) inside the server side VBScript code,
b) inside SQL Query Analyzer and
c) inside a server side JScript code

In a) no error occurs
in b) I immediately get an SQL error: "Server user 'test' is not a
valid user in database 'SampleDB'
in c) no error occurs

In case of a) and c) I should get at least an HTTP 500 because the
server side code should run into an error like in b) .....
....but it does not. So my code never gets any knowledge that a SQL
error occured, no matter if it's VBScript or JScript.

Yesterday I tried three different ways in my VBScript code to call my
SQL statement "EXEC Database.dbo.my_stored_procedure".
They were:

1) using ADODB.Connection
2) using ADODB.RecordSet
3) using ADODB.Command

In all three ADODB objects I can call SQL statements, and in every
object no error occurs when I am calling "EXEC
Database.dbo.my_stored_procedure". And that's crazy. Why do the above
ADODB objects do not get any SQL error when I am calling a stored
procedure?

The SQL Query Analyzer on the other hand does, so I am sure there is
really an error :-)

That's bad for my application, I'm more than confused about this.

Regards,
Germut Mulisch

---

On Aug 16, 5:43 pm, "Richard Mueller [MVP]" <rlmueller-
nos...@ameritech.nospam.net> wrote:

> I find an error is raised on the Open statement (con.Open) if the
> user/password combination is incorrect. An error is raised on the Execute
[quoted text clipped - 3 lines]
> may need to see details on the stored procedure. What do you mean when you
> say the sql statement is not fine?

Reply to this Message

Richard Mueller [MVP] - 17 Aug 2007 15:01 GMT

I've never seen ADO fail to return and report errors, in VBScript or VB.
However I don't code in asp. Maybe you need to trap the error with code
similar to:
===========
dim con, param, sql
set con = Server.CreateObject("ADODB.Connection")
param = "Provider=sqloledb;Data Source=MYPC;User Id=test;Password=test;"
sql = "EXEC Database.dbo.my_stored_procedure"
' Suppress normal error handling so we can trap errors.
On Error Resume Next
con.open (param)
' Test for error.
If (Err.Number <> 0) Then
' Error, perhaps bad credentials or source not available.
Call MsgBox("Error Number: " & Err.Number _
& "Description: " & Err.Description)
Else
' No error, execute the sql statement
con.execute (sql)
' Test for error.
If (Err.Number <> 0) Then
' Error, perhaps lack permissions.
Call MsgBox("Error Number: " & Err.Number _
& "Description: " & Err.Description)
Else
' No error.
End If
End If
' Restore normal error handling.
On Error GoTo 0
=========
You probably will use something other than Call MsgBox statements to report
the errors. Without "On Error Resume Next" the VBScript will halt on errors.
Ordinarily an error message results, but maybe behaviour is different in
asp. With "On Error Resume Next" most errors are ignored, but the Err object
reports information on errors. I hope this helps. Otherwise, maybe someone
else has suggestions.

Signature

Richard Mueller
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net
--

> Hello,
>
[quoted text clipped - 58 lines]
>> you
>> say the sql statement is not fine?

Reply to this Message

VB Forum / Database Access / August 2007

How to get SQL errors occured during Connection.execute() or RecordSet.open() ?