none
SQL Adapter to stored proc with uniqueidentifier/guid parameter RRS feed

  • Question

  • Hi,

    I created a sql adapter in BizTalk to invoke a stored procedure which has couple of uniqueidentifier parameters.

    I am getting an error [The adapter "SQL" raised an error message. Details "HRESULT="0x80040e07" Description="Error converting data type nvarchar to uniqueidentifier."] while executing from BizTalk.

    My input is from a flat file which has the guids and other values.

    How do I fix this?

    Friday, June 4, 2010 4:21 PM

Answers

  • I know a varchar conversion to uniqueIdentifier works, but nvarchar does not because it has all the charachters that make it a double byte string.

    If flat file is has a double byte (like UTF-16) encoding (perhaps why SQL thinks it is nvarchar), you need to remove the \0 characters that occur after every character. A .NET method is good for doing this. I tried to get the Guid class to parse a UTF-16 string for a Guid and it did not work, so I think you may have to remove the extra "\0" characters from the string prior to sending it to the database call. It would be safe to use the Guid class once it is a single byte input.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Monday, June 7, 2010 9:16 PM
    Moderator

All replies

  • Never tried this so only able to suggest.

    Did you try setting them to xs:string in the import schema so that the adapter won't cause the error and internally convert them into a real guid (scripting functiod or expression shape)? There you can use .NET to generate real GUIDs. Should solve the problem even it might be not the best way to do so.


    If you like my post or consider it as a valid answer, please use the buttons to show me - Oliver
    Saturday, June 5, 2010 7:09 AM
  • I know a varchar conversion to uniqueIdentifier works, but nvarchar does not because it has all the charachters that make it a double byte string.

    If flat file is has a double byte (like UTF-16) encoding (perhaps why SQL thinks it is nvarchar), you need to remove the \0 characters that occur after every character. A .NET method is good for doing this. I tried to get the Guid class to parse a UTF-16 string for a Guid and it did not work, so I think you may have to remove the extra "\0" characters from the string prior to sending it to the database call. It would be safe to use the Guid class once it is a single byte input.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Monday, June 7, 2010 9:16 PM
    Moderator