title: Invalid length parameter passed to SUBSTRING function
body: <p>Having a bit of a run in with SQL and Transactions... hoping some of you can shine a light on the problem. (and maybe even fix my screwed html! Does this site support formatting for different languages? Perl, SQL, Java?) This server runs on SQL Server 2005 and was very recently upgraded from SQL Server 2000. I'll keep an eye on this post throughout the day. Cheers</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[sp_AddRequest] 
      -- Add the parameters for the stored procedure here
      @Message TEXT,
      @RequestId VARCHAR(20),
      @StatusCode CHAR(1),
      @StatusText VARCHAR(255),
      @AddedDate DATETIME,
      @MessageTimestamp DATETIME
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      -- Direction is incoming (i.e. Client -&gt; WEBAPP)
      DECLARE @Direction VARCHAR(50)
      SET @Direction = 'Client -&gt; WEBAPP'
  
      -- Type is derived from:
      -- a) MessageType Element value OR
      -- b) Data Element first child OR
      -- c) Root Element name
      DECLARE @Type VARCHAR(50)
      SELECT @Type = dbo.fnGetValue('MessageType', @Message)
      IF @Type IS NULL SELECT @Type = dbo.fnGetFirstChild('Data', @Message)
      IF @Type IS NULL SELECT @Type = dbo.fnGetFirstChild(NULL, @Message) 
  
      -- MessageStatus is retrieved from the lookup table
      DECLARE @Status VARCHAR(50)
      SELECT @Status = Description FROM MessageStatus WHERE MessageStatusCode = @StatusCode
  
      -- Examine the Message root element
      IF dbo.fnGetFirstChild(NULL, @Message) = 'RequestMessage'
      BEGIN
          -- Insert values into the Transaction table
          INSERT INTO tblTransaction (RequestID, Direction, [Type], Status, StatusText, Sent, Received, Body)
          VALUES (@RequestId, @Direction, @Type, @Status, @StatusText, @MessageTimestamp, @AddedDate, @Message)
          RETURN @@IDENTITY
      END
      ELSE
      BEGIN
          -- Transaction is linked using the RequestId
          DECLARE @TransactionID INT
          SELECT @TransactionID = dbo.fnFindTransaction(@RequestId)
  
          -- Insert values into the RelatedMessage table
          INSERT INTO tblRelatedMessage (TransactionID, RequestID, Direction, [Type], Status, StatusText, Sent, Received, Body)
          VALUES (@TransactionID, @RequestId, @Direction, @Type, @Status, @StatusText, @MessageTimestamp, @AddedDate, @Message)
          RETURN @@IDENTITY
      END
  END
  </code></pre>
  
  <p>Regards,</p>