How to execute stored procs from A97 client

How to execute stored procs from A97 client

Post by Arnold Wilso » Sun, 27 Feb 2000 04:00:00



What's going wrong here:

On a client PC running Access97, in a vba-module, I get a run time error
3146 (ODBC-call failed) when trying to execute a stored procedure on a
SQL-server 7.
The connection string works OK to retrieve data from tables/queries.

The stored procedure itself works fine when executed on the server.

Sub DelCust()
    Dim qdf As QueryDef

    Set qdf = CurrentDb.CreateQueryDef("")
    qdf.Connect = "ODBC;DSN=NTDB1;UID=sa;PWD=;DATABASE=DB1"
    qdf.ReturnsRecords = False
    qdf.SQL = "{call DeleteCustomer(1200)}"
    qdf.Execute dbSQLPassThrough    ' ERROR:  ODBC-call failed
    qdf.Close

End Sub

Any suggestions?

Arnold Wilson.

 
 
 

How to execute stored procs from A97 client

Post by mary chipma » Sun, 27 Feb 2000 04:00:00


the culprit looks like it's the

Quote:>    qdf.SQL = "{call DeleteCustomer(1200)}"

line if DeleteCustomer is the stored procedure name and 1200 is its
parameter. an access query isn't the same thing as an RPC call. try
something like

qdf.sql = "EXEC DeleteCustomer 1200"



Quote:>What's going wrong here:

>On a client PC running Access97, in a vba-module, I get a run time error
>3146 (ODBC-call failed) when trying to execute a stored procedure on a
>SQL-server 7.
>The connection string works OK to retrieve data from tables/queries.

>The stored procedure itself works fine when executed on the server.

>Sub DelCust()
>    Dim qdf As QueryDef

>    Set qdf = CurrentDb.CreateQueryDef("")
>    qdf.Connect = "ODBC;DSN=NTDB1;UID=sa;PWD=;DATABASE=DB1"
>    qdf.ReturnsRecords = False
>    qdf.SQL = "{call DeleteCustomer(1200)}"
>    qdf.Execute dbSQLPassThrough    ' ERROR:  ODBC-call failed
>    qdf.Close

>End Sub

>Any suggestions?

>Arnold Wilson.


 
 
 

1. Stored procs in an Execute SQL Task

I have stored procedures that do either an update or insert.  I would like
to allow a certain number of errors, so the stored procedures return the
error count.

How can I execute a stored procedure and move on to the next step if the
error count is less than, say 10?

I have been trying to set a global variable from an Execute SQL Task, but
have not figured out how to put the return value into into the global.  If I
can set the global, I can use an ActiveX Script to determine whether or not
to proceed to the next step.  This seems cumbersome, is there a better way?

Thanks for any help.

David

2. XML/PHP

3. Grant Execute, stored procs, and variables.....

4. Looking for Software Programs

5. Can't get stored procs to execute from VB5

6. Calculated Members

7. Execute rights on Stored Procs

8. general database issue - help?

9. Executing stored procs

10. DB-Library Error, SqlSrv process dye when executing extended-stored-procs

11. Database role to execute user stored procs

12. ADO & SQLOLEDB, Executing stored procs

13. Executing Stored Procs