Problem using parameters in a stored procedures when querying against a AS400 Linked Server
Posted on 2006-06-25
I have the following stored procedure,
CREATE PROCEDURE [dbo].[SelectAccidentContacts]
SELECT FieldA, FieldB, FieldC
WHERE (FieldA= @Param)
When I execute this passing the @Param as a paramater it generates the following error.
OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandText::Execute returned 0x8000ffff: The provider reported an unexpected catastrophic failure.].
If I change @Param to a fixed value it works OK (IE - WHERE (FieldA= 1234567).
I can set the fixed value as a string by encapsulating it in ' ' or just as a number and both work but no matter what datatype I use for the parameter it still returns the same error.
The DataType on the As400 is numerical with a length of 7 with no decimals.
I am currently trying to prove a concept for a new project and if I can't get this to work then my concept goes out the window so any help to resolve this would be greatly appreciated!