stored procedure

globalterminal used Ask the Experts™
i am having trouble gettting a stored procedure to work.  Using the following code i get this error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL7967 - PREPARE of statement QZ92D3E2CF9BFB1000 completed.

this is the code..its failing on the Execute statement.

Dim rsRETax,NewConnxRETax,NewCommandxRETax

set NewConnxRETax                         = server.CreateObject("ADODB.Connection")
NewConnxRETax.Open "test","gblodbc","odbcgbl"
set NewCommandxRETax                   = server.CreateObject ("ADODB.Command")
set NewCommandxRETax.ActiveConnection = NewConnxRETax

NewCommandxRETax.CommandText = "getOpenDvirByChassis"
NewCommandxRETax.CommandType = 4

NewCommandxRETax.Parameters.Append NewCommandxRETax.CreateParameter("term-code",200,1,30)
 NewCommandxRETax.Parameters.Append NewCommandxRETax.CreateParameter("chassis-number",200,1,30)

NewCommandxRETax("term-code") = "GBLDEV"
NewCommandxRETax("chassis-number") = "APLZ330211"

set rsRETax = NewCommandxRETax.Execute
set rsRETax = rsRETax.NextRecordset()

While not rsRETax.eof
     Response.Write "*" &  rsRETax(0) & "*"  & rsRETax(1) & "<br>"
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bob ButcheriSeries Engineer

For the heck of it - what would happen if you remove this statement -

NewCommandxRETax.CommandType = 4
Bob ButcheriSeries Engineer

You are connecting to an AS400?


yes as400


commenting out that line:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL0104 - Token GETOPENDVIRBYCHASSIS was not valid. Valid tokens: ( END GET SET CALL DROP FREE HOLD LOCK OPEN.

Bob ButcheriSeries Engineer
This statement -
NewCommandxRETax.CommandText = "getOpenDvirByChassis"

should look something like this -
NewCommandxRETax.CommandText = "{call lib/pgm(?,?)}"

I believe
VP Technology / Senior Consultant
SQL7967 indicates successful completion of the PREPARE.  

ado  probably just isn't capturing the correct message - SQL7967 is not generally an exception message.  

DB2 can issue multiple messages.  There is probably a previous or  subsequent message that was issued that indicates the real error.  The  job log from the database server job that handled the request may have  additional messages that provide more detail.

Most likely, the  prepared statement is invalid.  Find the variable that contains the  prepared statement and check it for validity.  Are all the table names  and column names valid?  Do you have the correct number and type of parameters?  

Take the prepared statement and run it  interactively using the green-screen STRSQL command and see if it  succeeds.

Here are detailed steps on how to troubleshoot ODBC  errors:

-  Gary Patterson

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial