>Something is wrong. Whenenver I try to pass a variable to my stored procedure it gives me this error when I try to execute it...
>
>[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
>
>Im trying to pass a regular character string and then covert it to a date and use it as part of the sql. Here is my procedure...
>
>
>CREATE PROCEDURE GetEvents (@srcDate varchar(20))
>AS
>
>DECLARE @objDate datetime
>SELECT @objDate = CONVERT(datetime, @srcDate)
>
>SELECT eventName, eventType, eventStart, eventEnd, location, description, organization
>FROM events
>WHERE calOK = 1 and DATEDIFF(day, @objDate, eventStart) = 0
>GO
>
>
>Here is the code I use to execute it...
>
>strQuery = "GetEvents '" & srcDate & "'"
>Set objEventsRS = objEventsConn.execute(strQuery, ,adCmdStoredProc)
>
>No matter what I do I keep getting that error. I hardcoded the date in the execution string to see if it was a variable thing but I got the same error. I tried hardcoding a date within the stored proc and not passing anything and it worked. I also tried executing it from the query analyzer, passing a date, and it worked fine then. Does anyone know whats going on? I have been looking at messageboards and web sites all day to find an answer. I have tried many different things like, not coverting it, using a different comparison function, taking the variable out of the sql completly, and hardcoded a date... nothing worked. I think it all comes down to just passing a parameter... Is there some server setting that I have to enable to pass parameters?
>
>I am new to SQL server but I have been using asp/ado for years now.
>
>Help...
>
>Thanks in advance.....(I hope)

If you want to use this calling syntax you need to specify a command type of adCmdText. With adCmdStoredProc you need to define the parameters yourself in a command object or refresh them from the server.

If you only want to use the connection object, you can alternatively call the sp as if it was a method of the connection object:

objEventsConn.GetEvents(srcDate, objEventsRS)

This syntax is a little different in that you need to pass the recordset var at the end of all your parameters.
Hope this helps.