Problems with datatypes while callling MS-SQL stored procedure

[Former Member]

Hi

We have a stored procedure in MS-SQL server ( Sql Server 2000 ) that we are invoking from XI 3. ( SP18) using a receiver adapter communication channel . One of the parameters of the stored procedure has a decimal data type . In XI design time - in the mapping - to the stored procedure data type - we have specified 'DECIMAL' as the type for attribute of the import parameter of the stored procedure . My source field in the mapping is of type string.

This is resulting in an error on the Adapter Engine - 'Cannot convert datetime to decimal' ( as seen in RWB ) -

Are we right in specifying DECIMAL as the type in the XI mapping to the type attribute of the stored procedure parameter ?

Any pointers, thoughts shared on this is appreciated. Correct answers will be rewarded.

[Former Member]

Hi

I guess there is a some problem with input data type or input value

You are watching in RWB so the problem may not be in mapping.

Thanks

Sai

[Former Member]

Sai and Others

The problem was that - among the big number of stored procedure parameters - that were to be passed from XI to the stored procedure call - we had to disable a few of the fields inbetween - inorder to test the stored procedure call with the main parameters that were to be passed.

This disabling of the selective fields inbetween - messed up the order inbetween the XI stored procedure datatype and the actual stored procedure definition. We learnt a lesson not to disable fields within a stored procedure - now the call to the stored procedure is going through without using any conversion function for the string to decimal - as long as a decimal value keeps appearing at the source field always ( which is the case here )

Thanks for your pointers, thoughts and suggestions...

[Former Member]

Hi

Please check source field is datetime or string because you are getting 'Cannot convert datetime to decimal'

Thanks

Sai

[Former Member]

Hi Sai

Thats one more misleading piece of information coming up on the RWB - My source field datatype is String and not datetime - Infact all of my source field datatypes are String. Why is this message involving datetime coming up then ?

Also, I assume that its a simple java line of code to convert my string to a decimal value ?

[Former Member]

Check whether you are seeing in correct communication channel or not? If you are looking into the JDBC receiver communicaiton channel then check your stored procedure is executing correctly or not?

Also check the same values from XI to stored procedure at the database level whether its working correctly or not.

---Satish

[Former Member]

Karthik,

Its compalining about converting the datetime to decimal. So dont change in your mapping for this, let it be datetime only. But you change is stored procedure because how can datetime can be decimal in stored procedure? Then I think it should go through.

---Satish

[Former Member]

Hi

In message mapping you can use a simple UDF for converting string to decimal value

Thanks

Sai

[Former Member]

Sai

Thanks for the reply. My source xml already has a decimal value - .382 ( though the source field datatype is string ) . So I don't see the need to convert the value - Am I right ?

[VijayKonam]

>> My source xml already has a decimal value - .382 ( though the source field >>datatype is string ) . So I don't see the need to convert the value - Am I right ?

Dear Karthik,

thought the number looks like .382, it is internally represented as a string rather than a number. The . is not the decimal but a character in the represntation. So as mentioned by sathish, you should have an UDF written to convert the string value to a decimal number so that, the argument error would not be thrown by the Stored Procedure runtime.

Hope you are convinced.

Vijay