MSDE shutting down and restarting when calling an SP

Question

MSDE shutting down and restarting when calling an SP

Dave Kocuba

SSC Veteran

Points: 210
More actions
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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

Dave Kocuba SSC Veteran Points: 210 More actions · Answer 2

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

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!