title: SQL Server stored procedure - A severe error occurred in the current command
body: <p>I'm having an unusual error with a stored procedure I've wrote to generate telephony statistics. I'm running this sproc on a SQL Server 2005 clustered environment. The procedure gets called around 40 times sequentially (once per call queue) to populate a temp table and then insert this temp table into a table which stores one line per day per queue. </p>
  
  <p>I understand that there is a hotfix to apply to the sql boxes however I wanted to check if there was anything wrong with my sproc before getting the network lads to install patches etc</p>
  
  <p>The main sproc can be seen below (its a lengthy code nightmare). It uses 5 views which are connected to the main telephony database via a linked server (its a Cisco UCCX telephony system).</p>
  
  <p>The error I'm experiencing is as follows:</p>
  
  <hr>
  
  <p><strong>ERROR MESSAGE</strong></p>
  
  <blockquote>
    <p>Msg 0, Level 11, State 0, Line 0<br>
    A severe error occurred in the current command. The results, if any,
    should be discarded<br>
    Msg 0, Level 20, Stata 0, Line 0<br>
    A severe error occurred in the current command. The results, if any,
    should be discarded</p>
  </blockquote>
  
  <hr>
  
  <p><strong>PROCEDURE WHICH CALLS THE MAIN STORED PROCEDURE</strong></p>
  
  <pre><code>-- North
  EXEC [dbo].[SP_GetStatsMain_ByCSQName] @starttime = @OfficeOpen, @endtime = @OfficeClose, @queue = N'CSQ-N003A', @date = @runDate
  EXEC [dbo].[SP_GetStatsMain_ByCSQName] @starttime = @OfficeOpen, @endtime = @OfficeClose, @queue = N'CSQ-N004A', @date = @runDate
  EXEC [dbo].[SP_GetStatsMain_ByCSQName] @starttime = @OfficeOpen, @endtime = @OfficeClose, @queue = N'CSQ-N005A', @date = @runDate
  </code></pre>
  
  <hr>
  
  <p><strong>MAIN STORED PROCEDURE</strong></p>
  
  <pre><code>ALTER PROCEDURE [dbo].[SP_GetStatsMain_ByCSQName]
      -- Add the parameters for the stored procedure here
      @starttime DATETIME, 
      @endtime DATETIME, 
      @queue varchar(50),
      @date DATETIME
  AS
  BEGIN
      SET NOCOUNT ON;
      DECLARE @start DATETIME, @end DATETIME/*, @date DATETIME
      set @date = getdate()-1*/
      set @start = DATEADD(ms, DATEDIFF(ms, 0, @starttime), DATEADD(dd, 0, DATEDIFF(dd, 0, @date)))
      set @end = DATEADD(ms, DATEDIFF(ms, 0, @endtime), DATEADD(dd, 0, DATEDIFF(dd, 0, @date)))
  
  IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
  CREATE TABLE #Temp(
      [CSQName] [varchar](50) NULL,
      [Presented] [int] NULL,
      [Handled] [int] NULL,
      [Abandonded] [int] NULL,
      [Dequeued] [int] NULL,
      [HandledSLA] [float] NULL,
      [AbandondedSLA] [float] NULL,
      [AvgTalkTime] [float] NULL,
      [AvgWaitTime] [float] NULL,
      [Date] [datetime] NULL
  ) ON [PRIMARY]
  
  insert into #Temp([CSQName])
  SELECT    DISTINCT CSQ.CSQName
  FROM         CSQ INNER JOIN Queues ON CSQ.CSQName = Queues.CSQName
  WHERE     ((dateInactive IS NULL) 
  OR (dateInactive &lt; GETDATE() - 1)) AND Queues.csqname = @queue
  
  UPDATE #temp
  SET [Date] = dateadd(dd,0, datediff(dd,0,@Date))
  
  UPDATE #temp
  SET [Presented] = 0,
      [Handled] = 0,
      [Abandonded] = 0,
      [Dequeued] = 0,
      [HandledSLA] = 0,
      [AbandondedSLA] = 0,
      [AvgTalkTime] = 0,
      [AvgWaitTime] = 0
  
  UPDATE #temp
        SET Handled =
              (
                SELECT COUNT(CQD.targetType)
                FROM ACD 
                    INNER JOIN CCD ON ACD.sessionID = CCD.sessionID AND ACD.sessionSeqNum = CCD.sessionSeqNum 
                          AND ACD.nodeID = CCD.nodeID 
                          AND ACD.profileID = CCD.profileID 
                    INNER JOIN CQD ON ACD.sessionID = CQD.sessionID 
                          AND ACD.sessionSeqNum = CQD.sessionSeqNum 
                          AND ACD.profileID = CQD.profileID 
                          AND ACD.nodeID = CQD.nodeID 
                    INNER JOIN CSQ ON CQD.targetID = CSQ.recordID 
                          AND CQD.profileID = CSQ.profileID 
                    INNER JOIN Queues ON CSQ.CSQName = Queues.csqname 
                    INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
                WHERE     
                    (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) 
                    AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102)) 
                    AND (CCD.contactDisposition = 2) 
                    AND (ACD.talkTime &gt; 0) 
                    AND (CQD.targetType = 0) 
                    AND (CQD.disposition = 2) 
                    AND Queues.csqname = @queue
              )
        FROM ACD 
              INNER JOIN CCD ON ACD.sessionID = CCD.sessionID AND ACD.sessionSeqNum = CCD.sessionSeqNum 
                    AND ACD.nodeID = CCD.nodeID 
                    AND ACD.profileID = CCD.profileID 
              INNER JOIN CQD ON ACD.sessionID = CQD.sessionID 
                    AND ACD.sessionSeqNum = CQD.sessionSeqNum 
                    AND ACD.profileID = CQD.profileID 
                    AND ACD.nodeID = CQD.nodeID 
              INNER JOIN CSQ ON CQD.targetID = CSQ.recordID 
                    AND CQD.profileID = CSQ.profileID 
              INNER JOIN Queues ON CSQ.CSQName = Queues.csqname 
              INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
          WHERE     
              (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) 
              AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102)) 
              AND (CCD.contactDisposition = 2) 
              AND (ACD.talkTime &gt; 0) 
              AND (CQD.targetType = 0) 
              AND (CQD.disposition = 2) 
              AND Queues.csqname = @queue
  
  UPDATE #temp
        SET Presented =
              (
              SELECT     COUNT(CQD.sessionID) AS [Calls Presented]
              FROM         CQD INNER JOIN
                                    CCD ON CQD.sessionID = CCD.sessionID AND CQD.sessionSeqNum = CCD.sessionSeqNum AND CQD.profileID = CCD.profileID AND 
                                    CQD.nodeID = CCD.nodeID INNER JOIN
                                    CSQ ON CQD.profileID = CSQ.profileID AND CQD.targetID = CSQ.recordID INNER JOIN
                                    Queues ON CSQ.CSQName = Queues.csqname INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
              WHERE     (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102))
              AND Queues.csqname = @queue
              )
              FROM         CQD INNER JOIN
                                    CCD ON CQD.sessionID = CCD.sessionID AND CQD.sessionSeqNum = CCD.sessionSeqNum AND CQD.profileID = CCD.profileID AND 
                                    CQD.nodeID = CCD.nodeID INNER JOIN
                                    CSQ ON CQD.profileID = CSQ.profileID AND CQD.targetID = CSQ.recordID INNER JOIN
                                    Queues ON CSQ.CSQName = Queues.csqname INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
              WHERE     (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102))
              AND Queues.csqname = @queue
  
  UPDATE #temp
        SET Abandonded =
              (
              SELECT     COUNT(CQD.sessionSeqNum) AS [Calls Abandonded]
              FROM         CCD INNER JOIN
                                    CQD ON CCD.sessionID = CQD.sessionID AND CCD.sessionSeqNum = CQD.sessionSeqNum AND CCD.nodeID = CQD.nodeID AND 
                                    CCD.profileID = CQD.profileID INNER JOIN
                                    CSQ ON CQD.profileID = CSQ.profileID AND CQD.targetID = CSQ.recordID INNER JOIN
                                    Queues ON CSQ.CSQName = Queues.csqname INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
              WHERE     (CQD.targetType = 0) AND (CQD.disposition = 1) AND (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102))
              AND Queues.csqname = @queue
              )
              FROM CCD INNER JOIN
                                    CQD ON CCD.sessionID = CQD.sessionID AND CCD.sessionSeqNum = CQD.sessionSeqNum AND CCD.nodeID = CQD.nodeID AND 
                                    CCD.profileID = CQD.profileID INNER JOIN
                                    CSQ ON CQD.profileID = CSQ.profileID AND CQD.targetID = CSQ.recordID INNER JOIN
                                    Queues ON CSQ.CSQName = Queues.csqname INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
              WHERE     (CQD.targetType = 0) AND (CQD.disposition = 1) AND (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102))
              AND Queues.csqname = @queue
  
  UPDATE #temp
      SET Dequeued =
          (
          SELECT     COUNT(CQD.sessionSeqNum) AS [Calls Dequeued]
          FROM         CCD INNER JOIN
                                CQD ON CCD.sessionID = CQD.sessionID AND CCD.sessionSeqNum = CQD.sessionSeqNum AND CCD.nodeID = CQD.nodeID AND 
                                CCD.profileID = CQD.profileID INNER JOIN
                                CSQ ON CQD.profileID = CSQ.profileID AND CQD.targetID = CSQ.recordID INNER JOIN
                                Queues ON CSQ.CSQName = Queues.csqname INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
  
          WHERE     (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102))  
                                AND (CQD.targetType = 0) AND (CQD.disposition = 3)
          AND Queues.csqname = @queue
          )
          FROM CCD INNER JOIN
                                CQD ON CCD.sessionID = CQD.sessionID AND CCD.sessionSeqNum = CQD.sessionSeqNum AND CCD.nodeID = CQD.nodeID AND 
                                CCD.profileID = CQD.profileID INNER JOIN
                                CSQ ON CQD.profileID = CSQ.profileID AND CQD.targetID = CSQ.recordID INNER JOIN
                                Queues ON CSQ.CSQName = Queues.csqname INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
  
          WHERE     (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102))  
                                AND (CQD.targetType = 0) AND (CQD.disposition = 3)
          AND Queues.csqname = @queue
  
  UPDATE #temp
      SET AbandondedSLA =
          (
          SELECT     COUNT(CQD.sessionSeqNum) AS [Calls Abandonded SLA]
          FROM         CCD INNER JOIN
                                CQD ON CCD.sessionID = CQD.sessionID AND CCD.sessionSeqNum = CQD.sessionSeqNum AND CCD.nodeID = CQD.nodeID AND 
                                CCD.profileID = CQD.profileID INNER JOIN
                                CSQ ON CQD.profileID = CSQ.profileID AND CQD.targetID = CSQ.recordID INNER JOIN
                                Queues ON CSQ.CSQName = Queues.csqname INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
          WHERE     (CQD.targetType = 0) AND (CQD.disposition = 1) AND (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102))
          AND (CQD.metServiceLevel =1)
          AND Queues.csqname = @queue
          )
          FROM CCD INNER JOIN
                                CQD ON CCD.sessionID = CQD.sessionID AND CCD.sessionSeqNum = CQD.sessionSeqNum AND CCD.nodeID = CQD.nodeID AND 
                                CCD.profileID = CQD.profileID INNER JOIN
                                CSQ ON CQD.profileID = CSQ.profileID AND CQD.targetID = CSQ.recordID INNER JOIN
                                Queues ON CSQ.CSQName = Queues.csqname INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
          WHERE     (CQD.targetType = 0) AND (CQD.disposition = 1) AND (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102))
          AND (CQD.metServiceLevel =1)
          AND Queues.csqname = @queue
  
  UPDATE #temp
      SET HandledSLA = 
          (
  SELECT     COUNT(CQD.targetType) AS [Calls Handled SLA]
  FROM         ACD INNER JOIN
                        CCD ON ACD.sessionID = CCD.sessionID AND ACD.sessionSeqNum = CCD.sessionSeqNum AND ACD.nodeID = CCD.nodeID AND 
                        ACD.profileID = CCD.profileID INNER JOIN
                        CQD ON ACD.sessionID = CQD.sessionID AND ACD.sessionSeqNum = CQD.sessionSeqNum AND ACD.profileID = CQD.profileID AND 
                        ACD.nodeID = CQD.nodeID INNER JOIN
                        CSQ ON CQD.targetID = CSQ.recordID AND CQD.profileID = CSQ.profileID INNER JOIN
                        Queues ON CSQ.CSQName = Queues.csqname INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
  WHERE     (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102)) AND
                         (CCD.contactDisposition = 2) AND (ACD.talkTime &gt; 0) AND (CQD.targetType = 0) AND (CQD.disposition = 2) AND (CQD.metServiceLevel = 1)
  AND Queues.csqname = @queue
  )
  FROM         ACD INNER JOIN
                        CCD ON ACD.sessionID = CCD.sessionID AND ACD.sessionSeqNum = CCD.sessionSeqNum AND ACD.nodeID = CCD.nodeID AND 
                        ACD.profileID = CCD.profileID INNER JOIN
                        CQD ON ACD.sessionID = CQD.sessionID AND ACD.sessionSeqNum = CQD.sessionSeqNum AND ACD.profileID = CQD.profileID AND 
                        ACD.nodeID = CQD.nodeID INNER JOIN
                        CSQ ON CQD.targetID = CSQ.recordID AND CQD.profileID = CSQ.profileID INNER JOIN
                        Queues ON CSQ.CSQName = Queues.csqname INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
  WHERE     (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102)) AND
                         (CCD.contactDisposition = 2) AND (ACD.talkTime &gt; 0) AND (CQD.targetType = 0) AND (CQD.disposition = 2) AND (CQD.metServiceLevel = 1)
  AND Queues.csqname = @queue
  
  UPDATE #temp
      SET AvgTalkTime = 
          (
          SELECT     (SUM(CQD.queueTime)/Count(CQD.queueTime)) AS [AverageQueueTime]
          FROM         CCD INNER JOIN
                                CQD ON CCD.sessionID = CQD.sessionID AND CCD.sessionSeqNum = CQD.sessionSeqNum AND CCD.nodeID = CQD.nodeID AND 
                                CCD.profileID = CQD.profileID INNER JOIN
                                CSQ ON CQD.targetID = CSQ.recordID AND CQD.profileID = CSQ.profileID INNER JOIN
                                Queues ON CSQ.CSQName = Queues.csqname INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
          WHERE     (CQD.targetType = 0) AND (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102)) AND Queues.csqname = @queue
          )
          FROM CCD INNER JOIN
          CQD ON CCD.sessionID = CQD.sessionID AND CCD.sessionSeqNum = CQD.sessionSeqNum AND CCD.nodeID = CQD.nodeID AND 
          CCD.profileID = CQD.profileID INNER JOIN
          CSQ ON CQD.targetID = CSQ.recordID AND CQD.profileID = CSQ.profileID INNER JOIN
          Queues ON CSQ.CSQName = Queues.csqname INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
          WHERE     (CQD.targetType = 0) AND (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102)) AND Queues.csqname = @queue
  
  UPDATE #temp
      SET AvgWaitTime = 
          (
          SELECT    (SUM(ACD.talkTime) / COUNT(CQD.targetType)) AS [AverageTalkTime]
          FROM         ACD INNER JOIN
                                CCD ON ACD.sessionID = CCD.sessionID AND ACD.sessionSeqNum = CCD.sessionSeqNum AND ACD.nodeID = CCD.nodeID AND 
                                ACD.profileID = CCD.profileID INNER JOIN
                                CQD ON ACD.sessionID = CQD.sessionID AND ACD.sessionSeqNum = CQD.sessionSeqNum AND ACD.profileID = CQD.profileID AND 
                                ACD.nodeID = CQD.nodeID INNER JOIN
                                CSQ ON CQD.targetID = CSQ.recordID AND CQD.profileID = CSQ.profileID INNER JOIN
                                Queues ON CSQ.CSQName = Queues.csqname INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
          WHERE     (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102)) AND
                                 (CCD.contactDisposition = 2) AND (ACD.talkTime &gt; 0) AND (CQD.targetType = 0) AND (CQD.disposition = 2)
          AND Queues.csqname = @queue
          )
          FROM         ACD INNER JOIN
                                CCD ON ACD.sessionID = CCD.sessionID AND ACD.sessionSeqNum = CCD.sessionSeqNum AND ACD.nodeID = CCD.nodeID AND 
                                ACD.profileID = CCD.profileID INNER JOIN
                                CQD ON ACD.sessionID = CQD.sessionID AND ACD.sessionSeqNum = CQD.sessionSeqNum AND ACD.profileID = CQD.profileID AND 
                                ACD.nodeID = CQD.nodeID INNER JOIN
                                CSQ ON CQD.targetID = CSQ.recordID AND CQD.profileID = CSQ.profileID INNER JOIN
                                Queues ON CSQ.CSQName = Queues.csqname INNER JOIN #temp on CSQ.CSQName = #Temp.CSQName
          WHERE     (CCD.startDateTime &gt; CONVERT(DATETIME, @start, 102)) AND (CCD.startDateTime &lt; CONVERT(DATETIME, @end, 102)) AND
                                 (CCD.contactDisposition = 2) AND (ACD.talkTime &gt; 0) AND (CQD.targetType = 0) AND (CQD.disposition = 2)
          AND Queues.csqname = @queue
  
  INSERT INTO [db_cra].[dbo].[CallData]
             ([CSQName]
             ,[Presented]
             ,[Handled]
             ,[Abandonded]
             ,[Dequeued]
             ,[HandledSLA]
             ,[AbandondedSLA]
             ,[AvgTalkTime]
             ,[AvgWaitTime]
             ,[Date])
  SELECT * FROM #Temp
  
  END
  </code></pre>