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;
/* 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.
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!