WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

ODBC
plus

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Calling a MS SQL Server 2000 Stored Procedure


Question:

I've got a stored procedure that does some complex query work and returns a rowset. I do all the allocations, and connections just fine, and then try:
retcode = qExecDirect(hstmt,'ExpressPassDays')
If (retcode != @QSUCCESS) && (retcode != @QSUCCESSINFO)
Message("qExecDirect failed", retcode)
Exit
EndIf
It hits the error and terminates. I've tried making the SQL statement argument an Execute() statement with a variety of quote formats; no joy.

The stored procedure works fine from SA with the same credentials and from Excel with the same ODBC datasource, just not from Winbatch.

Answer:

I recommend calling qError(hstmt, 2) and displaying the output. It should explain why it doesn't like that stored procedure (SP).

Maybe try:

retcode = qExecDirect(hstmt,'{call ExpressPassDays}')
Or
retcode = qExecDirect(hstmt,'{execute ExpressPassDays}')

User Reply:

That got me there. For anyone who needs to do this:

Even though the ODBC datasource points to the database where the stored procedure lives I had to call the fully qualified name. Before I added the fully qualified name the error message indicated that the ODBC driver was unable to find the stored procedure. That is, given that the stored procedure exists in a DB named 'JHMR_Custom':

retcode = qExecDirect(hstmt,'ExpressPassDays')
failed, but
retcode = qExecDirect(hstmt,'JHMR_Custom.dbo.ExpressPassDays')
succeeded.

Thanks!


Article ID:   W16835
File Created: 2017:07:28:13:58:31
Last Updated: 2007:07:03:14:26:28