Pass-through query problem 1

[KCcasey]

Hi all,

I'm having some problems with a Pass-through query calling a SQL stored procedure. The code I'm using is...

DECLARE @RC int
DECLARE @startDate datetime
DECLARE @endDate datetime
SELECT @startDate = '31/10/2004'
SELECT @endDate = '1/12/2004'
EXEC @RC = [Finance].[sp_SELECT_test] @startDate, @endDate

...which is lifted straight from working code in SQL Query Analyzer. But when I run the pass-through query I get no results. I've tried a simple SELECT from the DB the query uses to make sure its not a security issue and that was fine. I'm using the sa login (for my DSN).

Anybody have any ideas... ?

Thanks in Advance,

Casey.

 

[Toga]

Try something along the lines of this where you just send the variables to your stored procedure.....without the declarations. Leave those in your stored procedure.

EXEC @RC = [Finance].sp_SELECT_test] '31/10/2004', '1/12/2004'

 

[KCcasey]

Thanks for the suggestion Toga,

I gave it a whirl and rather than returning no result-set its failing on a conversion of the date strings... odd, because I perform a conversion in the stored procedure to change the datatype to datetime...

EXEC  [Payglobal].[MOF-CHCH\casey.raats].[SELECT_Darryl] '31/10/2004', '1/12/2004'

...however this string works fine in SQL Query Analyzer. Someone please correct me if I'm wrong, but I thought that the idea of a pass-through query was that all processing occurs on the DBMS(?)

Casey.

 

[ssecca]

Can you post both the Access VBA call statement and the SQL sp, It might make it easier to see what is going on.

 

[PHV]

A standard way to pass date literals to SQL server is the ANSI one:
'yyyy-mm-dd hh:nn:ss'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KCcasey]

Hi all,

Thankyou both for your suggestions, PHV you were dead right, it was the date format I was using that was causing the failure! Star for you!

(for any future unfortunates, heres what worked for me...)

EXEC [Payglobal].[MOF-CHCH\casey.raats].[SELECT_Darryl] '2004-10-31', '2004-12-1'

Kind Regards, Casey.