I created a query/event to do a yield calculation. The query is configured to look back in time a specified number of days using the dbo.Order_Master.TRNDTE_10 field. In the filter tab of the query, I have this filter:

DATEDIFF("dd", dbo.Order_Master.TRNDTE_10, GETDATE()) is less than or equal to ? Number

So, in the Event, you enter the number (compare value).

When I do a preview in the query, it works fine. When I try to test it from the event, I get and ODBC error complaining about datediff:

(1023) 37000: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid parameter 1 specified for datediff.

I don't see anything wrong with my syntax and I don't understand why it works from when I preview but not when I run from the event? Also, if I run this same query SQL Server Manager it works.

Thanks

Good luck with that one ... seems like you need to cast your datediff as an integer or something the event can understand.

I have successfully used DATEDIFF in a lot of locations. HOWEVER, I do not do it in Event Mgr. Because I am more comfortable in sql, I create a view in sql. Set the datediff to case as varchar and then fitler in your event. I find the more I do in sql, the easier time event manager has with complex queries.

Not sure if this will be of any help. I calculate the # of days between two dates in event manager using the datediff function, but I use the parameter "day" instead of "dd".
Example: datediff(day,dbo.cicmpy.syscreated,dbo.cicmpy.type_since)
I haven't tried doing a filter on the results, but since your error appears to be related to parameter 1 of the datediff function, perhaps changing to "day" instead will work?

Thanks Tony and Allison for your suggestions.

I like the idea of creating the query in SQL and then using a view to call from Event Manager. I have no idea how to do this right now but I think it is worth the effort to figure it out.

The suggestion to use "day" instead of "dd" actually worked - sort of. I didn't get the ODBC error, but I also did not getting any results from the event. Again, it worked when I previewed it, but when the event runs I get an email without data. I will figure that one out.

Great ideas. I really appreciate the response.

Lee

Couple of suggestions:

Make sure that you have dbo.Order_Master.TRNDTE_10 access rights granted for the user that you are using to log on to SQL server from Event manager.

To test this run the query using the same log on cridentials that you are using for Event manager.

Also not to be nosy but here you said that DATEDIFF("dd", dbo.Order_Master.TRNDTE_10, GETDATE()) is less than or equal to ? Number. Are you comparing date to number? If that is the case I would convert the date to a number using this query: convert(int,convert(varchar, @date, 112)) where @date is a paramater of type datetime...

This converts your date to YYYYMMDD format and makes it realy easy to compare with < and >...

Good luck!!!!