MS SQL error - need a bit of help

(4 posts) (2 voices)

Tags:

  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.