1. 

   ajdjackson, Member

   Hi

   I'm in the process of moving off MySQL to a MS SQL environment and I'm re-writing all the SPs.

   This error has me stumped.
   
   [Microsoft][ODBC Driver 17 for SQL Server]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.Cannot continue the execution because the session is in the kill state.
   SQLSTATE: 08S01, code: 233
   - Shared Memory Provider: No process is on the other end of the pipe. SQLSTATE: 08S01, code: 233
   - Communication link failure
   SQL: sp_MyDBR_Stat_AddEnd 629

   The query I'm trying to run is

   select iif(a.FPONo = '',0,iif (a.InvNumber not in (select CreditNumber from [PH-HV-BISSQL].[Company001].dbo.cnheader), iif (f.LHours is null, iif(a.ItemCode not in (select par_code from [pharma].dbo.tbllabour_overview where tot_hours > 0), (isnull((a.QTYInvoiced*e.num_ops/e.tar_output),0)), (isnull((a.QTYInvoiced*d.tot_hours/d.tot_output),0))), (f.LHours*a.QTYInvoiced/c.fQty)),0)) as "Hours[LHrs]" from #order_tmp a left outer join #fpo_qty c on a.FPONo = c.fFPONo left outer join [pharma].dbo.tbllabour_overview d on a.ItemCode = d.par_code left outer join [pharma].dbo.tbllabour_prod e on a.ItemCode = e.par_code left outer join [pharma].dbo.vw_lab_fpono f on a.FPONo = f.LFPO;

   This runs ok in the MySql version of the system

   Any help would most appreciated.

   Cheers

   Jake
2. 

   myDBR Team, Key Master

   Do you get the same error when you run the query in SSMS?

   --
   myDBR Team
3. 

   ajdjackson, Member

   Hi

   I can't run it directly directly in SSMS as it uses 2 temporary tables so I created a stripped back SP. This SP run OK until the final query.

   create table #order_tmp (
   
     CustName varchar(50) DEFAULT NULL,
   
     YourOrder varchar(50) DEFAULT NULL,
   
     InvNumber varchar(50) DEFAULT NULL,
   
     InvoiceDate date DEFAULT NULL,
   
     ItemCode varchar(50) DEFAULT NULL,
   
     Description varchar(100) DEFAULT NULL,
   
     QTYInvoiced int DEFAULT null,
   
     Price float DEFAULT NULL,
   
     LineTot float DEFAULT NULL,
   
     FPONo varchar(20) DEFAULT NULL,
   
     Mats decimal(15,5) DEFAULT NULL);
   
   insert into #order_tmp (CustName,YourOrder,InvNumber,InvoiceDate,ItemCode,Description,QTYInvoiced,Price,LineTot,FPONo)
   
   select b.CustName,
   
           a.YourOrder ,
   
           c.InvNumber,
   
           convert(date,a.InvoiceDate) ,
   
           c.ItemCode ,
   
           c.Description,
   
           c.QTYInvoiced,
   
           c.Price,
   
           isnull((c.LineTot),0),
   
           c.FPONo
   
   from [PH-HV-BISSQL].[Company001].dbo.invheader a
   
       join [PH-HV-BISSQL].[Company001].dbo.customer b on a.Customer = b.[Customer Code]
   
       join [PH-HV-BISSQL].[Company001].dbo.invdetails c on a.InvNumber = c.InvNumber
   
   where year(InvoiceDate) = 2020 and month(InvoiceDate) = 6 and c.ItemCode not in ('TRANS','MISC')
   
   order by b.CustName,a.YourOrder,a.InvoiceDate;
   
   create table #fpo_qty (
   
     fFPONo varchar(20) DEFAULT NULL,
   
     fQty decimal(15,5) DEFAULT 0
   
   );
   
   insert into #fpo_qty
   
   select FPONo,
   
           sum(QTYInvoiced)
   
           from #order_tmp
   
   	where FPONo is not null
   
           group by FPONo; 
   
   select iif(a.FPONo = '',0,iif (a.InvNumber not in (select CreditNumber from [PH-HV-BISSQL].[Company001].dbo.cnheader),
   
          iif (f.LHours is null, iif(a.ItemCode not in (select par_code from [pharma].dbo.tbllabour_overview where tot_hours > 0),
   
              (isnull((a.QTYInvoiced*e.num_ops/e.tar_output),0)),
   
              (isnull((a.QTYInvoiced*d.tot_hours/d.tot_output),0))), (f.LHours*a.QTYInvoiced/c.fQty)),0)) as "Jake[LHrs]"
   
   from #order_tmp a
   
   left outer join #fpo_qty c on  a.FPONo = c.fFPONo
   
   left outer join [pharma].dbo.tbllabour_overview d on a.ItemCode = d.par_code
   
   left outer join [pharma].dbo.tbllabour_prod e on a.ItemCode = e.par_code
   
   left outer join [pharma].dbo.vw_lab_fpono f on a.FPONo = f.LFPO
   
   where a.FPONo is not null;

   I get the following error in SSMS:

   Msg 596, Level 21, State 1, Line 2
   Cannot continue the execution because the session is in the kill state.
   Msg 0, Level 20, State 0, Line 2
   A severe error occurred on the current command. The results, if any, should be discarded.

   Thanks for the help

   Jake
4. 

   myDBR Team, Key Master

   The error is a generic SQL Server error can it can be caused by multiple of things. Not related to myDBR though.

   Check this answer from StackOverflow.

   --
   myDBR Team

Reply

You must log in to post.