Pass Through Queries

[LadyDi]

I have created several "pass through" queries in the last several years. However, I have just had someone ask me if it is possible to run an SQL Procedure as a Pass Through Query. The procedure they want to run is set up to run a query, save the results to a server, and then run another query based on the saved data. I can get both queries to run as pass through queries, but I can't figure out how to save the data on the server. Is there a way to accomplish this? Can SQL procedures be run via Access?

Any assistance you can provide would be greatly appreciated.

 

[pbaldy]

If you mean SQL Server stored procedures, sure. With a pass through query, this type of thing:

EXEC ProcedureName AnyParameters

for instance

EXEC procResForExcel '9/3/2013'

 

[LadyDi]

I created a Pass Through Query and typed EXEC and my procedure name (my procedure does not have any parameters). Unfortunately, when I try to run it I am getting an "Expected End of Statement" error. I tried adding a semicolon to the end of it, but still get the same message. What am I doing wrong?

 

[pbaldy]

Not sure; this is the complete text of a pass through query that calls a stored procedure (this one returns records) in an app I happen to have open:

EXEC procBusHours

 

[LadyDi]

Could there possibly be a reference or something I am missing?

My query looks like this: EXEC SIS_CALL_P

When I try to run it, I get a message stating "ODBC--call failed. [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "EXEC" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "Expand", SQLSTATE=42601 (#104)"

I checked my ODBC connection and it will run, if I just try to run a query by typing the SQL in Access. Could there be something in my connection string that won't allow me to run a procedure? My connection string looks like this: ODBC;DSN=DIEBLDDQ;DBALIAS=DIEBLDDQ;

I don't know if it matters or not, but I am using Access 2010 and my operating system is Windows 7 (32 bit).

 

[pbaldy]

It looks like you're passing through to something other than SQL Server, so you'd have to find out the correct syntax for that system. If SQL works, that would indicate the connection string is fine.