Topic
3 replies Latest Post - ‏2013-07-25T16:45:15Z by M3NG_Richard_Wolters
electricsk8
electricsk8
2 Posts
ACCEPTED ANSWER

Pinned topic SQL Server Stored Procedure Parameter Size Limitation

‏2013-07-10T15:27:33Z |

I have extremely limited experience with Cast Iron, and am a SQL Server Dev, so please excuse my ignorance of the product.  

My company has an onsite resource developing orchestrations using Cast Iron Studio editor.  

We have decided to use well-formed XML to pass data payload between Cast Iron and SQL Server.  A series of stored procedures has been created to perform the "get | set" operations.  The well-formed XML is passed as either an input parameter for "sets" or output parameter for "gets" to the stored procedures.  An example is below:

/* for gets */

DECLARE @XML VARCHAR(MAX);

EXEC dbo.GetProc @XML OUTPUT;

SELECT @XML; 

/* for sets */

EXEC dbo.SetProc @XML;

In all cases, we have defined the @XML parameter as data type VARCHAR(MAX), which Cast Iron is mapping to LONGVARCHAR with a length of (0).  However, we have noticed that Cast Iron is implicitly truncating the XML string data at 4000 characters.

Lastly, we tried changing the data type of the @XML parameter to the native XML data type supported by SQL Server, which Cast Iron is mapping to SQLXML with a length of (0).  This did not work either.

My question(s):

1. Is the 4000 character limit expected behavior?

2. Is the LONGVARCHAR data type deprecated?

3. Is there a different data type that we should be mapping VARCHAR(MAX) to in Cast Iron Studio?

4. Are there recommendations for passing data payloads between SQL Server and Cast Iron?  We've had many iterations up to this point, and assumed that we would be able to consume XML or string (VARCHAR(MAX)) data types in Cast Iron successfully.

Any assistance is greatly appreciated!

Sean Fitzgerald 

  • M3NG_Richard_Wolters
    18 Posts
    ACCEPTED ANSWER

    Re: SQL Server Stored Procedure Parameter Size Limitation

    ‏2013-07-17T20:51:16Z  in response to electricsk8

    Hi Sean,


    Have you had any luck configuring the sets/gets with Cast Iron and SQL Server?  We're looking to do something similar, replace some individual SQL statments and logic in our orchestration with a single Stored Procedure with in/out parameters to help improve processing time.

    If you're still having this issue I'll keep working on it and let you know if I see the 4000 character limit, or any other issues with the datatypes.

    Best Regards,


    Richard Wolters

    • electricsk8
      electricsk8
      2 Posts
      ACCEPTED ANSWER

      Re: SQL Server Stored Procedure Parameter Size Limitation

      ‏2013-07-18T13:05:01Z  in response to M3NG_Richard_Wolters

      Hi Richard,

      To clarify, we have been successful in implementing stored procedures for sets|gets with Cast Iron and SQL Server.  The character limitation seems to be only for string (N)VARCHAR and SQLXML data types for outbound (OUTPUT) parameters.  Input parameters as well as result-sets seem to work fine.

      We have a couple of tickets open with support for this issue, but are working around it by using a series of input output parameters, as opposed to using a string containing an XML fragment for returning data sets for the gets.  Cast Iron has indicated that this is an issue on their side, and are working to address it.

      Sincerely,

      Sean Fitzgerald

       

      • M3NG_Richard_Wolters
        18 Posts
        ACCEPTED ANSWER

        Re: SQL Server Stored Procedure Parameter Size Limitation

        ‏2013-07-25T16:45:15Z  in response to electricsk8

        Thanks for clarifying Sean.  I don't suppose you would want to elaborate more would you :) ?

        I saw examples of passing in the data as XML (string), then parsing the data out into a temporary table using the nodes() method like below

        INSERT INTO @TempTable
        SELECT M.Item.query('./parameter1').value('.','VARCHAR(50)') parameter1,
        M.Item.query('./parameter2').value('.','VARCHAR(50)') parameter2
        M.Item.query('./parameter3').value('.','VARCHAR(50)') parameter3
        FROM @XML.nodes('/root/parent') AS M(Item)

        If we cant use (N)VARCHAR or XML datatypes, did you end up using varchar(max), text or ntext? If it doesn't like XML formatted data, have you though of sending it in a comma delimited (or similar) form and have Cast Iron parse it?