Post reply

MSDE shutting down and restarting when calling an SP

  • April 18, 2007 at 7:52 am

    #98275

    I am having an issue with some machines.  I am calling the following SP-

    create procedure [mdbs].[mdbs_crystal_dyrsumpt1](@distno numeric, @year numeric) as

    select

    sum(tptaxsales)- sum(skipsales) as tptaxablesales, sum(tpnontaxsales) as tpnontaxablesales, sum(cashtaxsales) as cashtaxablesales,sum(cashnontaxsales) as cashnontaxablesales,

    sum

    (opentaxsales) as opentaxablesales,sum(opennontaxsales) as opennontaxablesales, sum(psataxsales) as psataxablesales, sum(psanontaxsales) as psanontaxablesales,

    sum

    (skipsales) as skipsales, sum(tpsalestax)- sum(skiptax) as tpsalestax, sum(opensalestax) as opensalestax, sum(cashsalestax) as cashsalestax, sum(psasalestax) as psasalestax,sum(skiptax) as skiptax,

    sum

    (compbusiness) as compbusiness, sum(cibtotalcollections) as CIBTotalCollections, sum(cibtotalcommission) as CIBTotalCommission,

    sum

    (tpcolamt) as tpcollections, sum(opencolamt) as opencollections, sum(cashsalesamt) as cashcollections, sum(psasalesamt)as contractsales,sum(psacollections) as psacollections,

    sum

    (psasalestax) as contracttax, sum(psacredits) as contractcredits, sum(psadownpay) as contractdownpayments,

    (select top 1 tpbalance from mdbs.distwkly where year = @year - 1 and distno = @distno order by seqno desc) as Begin_TPBalance,

    (select top 1 tpbalance from mdbs.distwkly where year = @year and distno = @distno order by seqno desc) as End_TPBalance,

    (select top 1 openbalance from mdbs.distwkly where year = @year - 1 and distno = @distno order by seqno desc) as Begin_OpenBalance,

    (select top 1 openbalance from mdbs.distwkly where year = @year and distno = @distno order by seqno desc) as End_OpenBalance,

    (select top 1 skipbalance from mdbs.distwkly where year = @year - 1 and distno = @distno order by seqno desc) as Begin_SkipBalance,

    (select top 1 skipbalance from mdbs.distwkly where year = @year and distno = @distno order by seqno desc) as End_SkipBalance,

    sum

    (skipcol) as skipcollections,

    (select top 1 wkstartdate from mdbs.distwkly where year = @year and distno = @distno order by seqno) as BeginDate,

    (select top 1 wkclosedate from mdbs.distwkly where year = @year and distno = @distno order by seqno desc) as EndDate,

    (select top 1 invbalavgcost from mdbs.distwkly where year = @year - 1 and distno = @distno order by seqno desc) as Begin_Inventory,

    (select top 1 invbalavgcost from mdbs.distwkly where year = @year and distno = @distno order by seqno desc) as End_Inventory,

    sum

    (tpbalance - tpsalesamt - tpsalestax + tpcolamt)- mdbs.mdbs_ReturnPreviousTPBalance(@distno,@year) as TPAdjust,

    sum

    (openbalance - opensalesamt - opensalestax + opencolamt)- mdbs.mdbs_ReturnPreviousOPENBalance(@distno,@year) as OPENAdjust,

    sum

    (psabalance - psanewsalesamt + psacollections)- mdbs.mdbs_ReturnPreviousPSABalance(@distno,@year) as PSAAdjust

    from

    mdbs.distwkly where year = @year and distno = @distno

    Everytime I call this SP from either with in my vb6 app or from osql from certain machines SQL Server shuts down and restarts and in the app I get disconnected from the SQL Server and from osql I get an [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData(CheckforData()) error aget blown back to a DOS prompt.

    Strange thing is this works on my development machine and on other machines running the app.  Also If I replace all the variables with their value and run it as a select statement from osql it works fine.  I am then able to run the SP from osql and also run the report that calls this SP from my app.  But then if I reboot the machine and try the report again I error out again.

    Anyone got ANY ideas?  Event Viewer just says that MSSQLSERVER just down unexpectedly and the SQL error logs say basically the same thng.

     

    Thanks in Advance

    Dave

  • April 19, 2007 at 10:46 am

    #701507

    i can only suggest some general/generic things to look at; nothing obvious or strange in your query, so I'd look at things like service pack on the machines that fail vs the service pack on the machines that don't first. To me, that would probably be the easiest thing to chekc...simply install SP4 on a machine that fails and retry.

     

     

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 26, 2007 at 8:15 am

    #702611

    Lowell,

     

    Thanks for the response.  I'm sorry it took so long to get back to this.  But that did work on the 3 machines that I did try.

     

    Thanks Again

    Dave

  • April 26, 2007 at 11:33 am

    #702717

    Glad you worked it out , Dave;  keep reading SSC; lots of good stuff here.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply