Parameter mismatch between ACCESS and SQL server

[Guest]

I have a stored procedure in SQL server with a date parameter input that
runs fine on the server itself.
But when I try calling the same stored proc from ACCESS 2000
using the ADO execute command, I get the error:
cannot convert varchar to smalldatetime.
The date is actually used in a query in the WHERE statement in the stored
procedure and the date input is a varchar. I know SQL server does an implicit
conversion of varchar to smalldatetime so why is ACCESS so particular ? I
even tried changing the stored proc's input to a date variable but that did
not help.
Any help would be appreciated

 

[Joe Fallon]

I think it depends on whether or not Jet is involved in the process.

In a SPT query you would use SQL Server syntax and send the date in like
this:
'10/26/2004'

In a JET query, you have to use Jet syntax for the date:
#10/26/2004#

Then Jet will "translate" it for you.

 

[Guest]

I used the ADODB's execute command to call the stored proc as follows :

cmd.ActiveConnection = "Data Source=Landrec_stat;Initial
Catalog=Land_Stat;UID=user;PWD=mypasswd"
cmd.CommandText = "dbo.Cataloging_Report " & "'Format(input_date,
JetDateFmt)'"
cmd.CommandTimeout = 120
cmd.Execute , , ADODB.adExecuteNoRecords

I had previously used the '10/26/2004' format but after reading your mail,
tried the #10/26/2004# foramt too. It gives the same error that the varchar
cannot be converted to smalldatetime.
Would appreciate any ideas.
Thanks.

 

[Douglas J. Steele]

The problem is that you've got the Format(input_date, JetDateFmt) inside of
quotes. You want:


cmd.CommandText = "dbo.Cataloging_Report " & "'" &
Format(input_date,
JetDateFmt) & "'"