Hello,
I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.
When I try to exectue a similar task passing those variables as parameters, I get an error:
Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1
The stored proc is expecting datetime parameters.
Thanks for the help.
Mike
As a stopgap, I'm building the exec statement in an expression - converting the dates to single quoted strings.
I would still like to see the correct way to pass date parameters, so if anybody can help it would be much appreciated.
Thankee.
Mike
|||Any chance your passing the value in as type "DBTimestamp"? Try changing the parameter to type DATE
This looks like a bug to me:
setup:
1. Extract date from query (SSIS will show type as DBTIMESTAMP..)
2. Pass that same date taken from SQL Server as a parameter of type DBTimestamp back into the same SQL Server (using same connection) and it will error with message like you have above.
I tried changing the stored procedure to take nvarchar(2000) so I could see what it was passing for the DBTimestamp parameter to SQL and it was passing '' (empty set.. not to be confused with NULL)
