Help with a query that is crashing our SQL Server

[jenny30534]

I need some help guys, one of our developers is running this query against our database, and it keeps crashing our server, other queries, packages, etc. work with no issue. Can anyone see something that may be causing the issue?? Thanks. Our server is running SQL 2000 service pack 4
Jennifer

--SMAN850P SUMMARY VALIDATION FOR SQLBPDETAIL TABLES VS SMAN850P----------------------------------
--
--

DECLARE @FROM_DATE CHAR (8),
@TO_DATE CHAR (8),
@PERIOD CHAR (3),
@YEAR CHAR (4)
--------------------------------
SET @PERIOD ='MAR'
SET @YEAR ='2006'
SET @FROM_DATE ='12/31/05'
SET @TO_DATE ='03/10/06'
--------------------------------

PRINT @PERIOD+' FROM '+@FROM_DATE+' TO '+@TO_DATE
--/*
DROP TABLE #SQLBPD
SELECT * INTO #SQLBPD FROM SQLBPDETAIL
WHERE REC_CODE NOT IN ('A','C') AND
(CONVERT(DATETIME,inv_date) >= CONVERT(DATETIME,@FROM_DATE) AND
CONVERT(DATETIME,inv_date) <= CONVERT(DATETIME,@TO_DATE))

--SMAN MTD BELOW THE SUBTOTAL LINE IN SMAN850P
DROP TABLE #SQLBPD2
SELECT * INTO #SQLBPD2 FROM SQLBPDETAIL
WHERE (CONVERT(DATETIME,INV_DATE) >= CONVERT(DATETIME,@FROM_DATE) AND
CONVERT(DATETIME,INV_DATE) <= CONVERT(DATETIME,@TO_DATE))

DROP TABLE #SQLBPT
SELECT * INTO #SQLBPT FROM SQLBPTOTALS
WHERE (CONVERT(DATETIME,DATE) >= CONVERT(DATETIME,@FROM_DATE) AND
CONVERT(DATETIME,DATE) <= CONVERT(DATETIME,@TO_DATE))
--*/
----------------------------------------------------------------------------------------------

--PASSENGER
DROP TABLE #TOTALSMAN
SELECT '01' AS 'LINE','PASSENGER ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
INTO #TOTALSMAN
FROM #sqlbpd WHERE GRP = '01'

--LIGHT TRUCK
INSERT INTO #TOTALSMAN
SELECT '02' AS 'LINE','LIGHT TRUCK ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
FROM #sqlbpd WHERE GRP = '11'

--LARGE TRUCK
INSERT INTO #TOTALSMAN
SELECT '03' AS 'LINE','LARGE TRUCK ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
FROM #sqlbpd WHERE GRP = '22'

--FARM
INSERT INTO #TOTALSMAN
SELECT '04' AS 'LINE','FARM ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
FROM #sqlbpd WHERE GRP IN ('35')

--SPECIALTY RUBBER
INSERT INTO #TOTALSMAN
SELECT '05' AS 'LINE','SPECIALTY RUBBER ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
FROM #sqlbpd WHERE GRP IN ('30','31','38','40','43','51','89')

--EQUIPMENT
INSERT INTO #TOTALSMAN
SELECT '06' AS 'LINE','EQUIPMENT ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
FROM #sqlbpd WHERE GRP IN ('60','63')

--TOOLS/SM EQ
INSERT INTO #TOTALSMAN
SELECT '07' AS 'LINE','TOOLS / SM EQ ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
FROM #sqlbpd WHERE GRP = '65'

--SUPPLIES
INSERT INTO #TOTALSMAN
SELECT '08' AS 'LINE','SUPPLIES ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
FROM #sqlbpd WHERE GRP IN ('69','73') OR (GRP='93' AND VENDOR='890')

--WHEEL PRODUCTS
INSERT INTO #TOTALSMAN
SELECT '09' AS 'LINE','WHEEL PRODUCTS ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
FROM #sqlbpd WHERE GRP IN ('53','54','55','56')

--NATL ACCT COMM
INSERT INTO #TOTALSMAN
SELECT '10' AS 'LINE','NATL ACCT COMM ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
FROM #sqlbpd WHERE GRP ='45'

--PART PRODUCTS
INSERT INTO #TOTALSMAN
SELECT '11' AS 'LINE','PART PRODS ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
FROM #sqlbpd WHERE GRP ='75'

--SUB TOTAL
INSERT INTO #TOTALSMAN
SELECT '12' AS 'LINE',' SUB-TOTAL',SUM(CONVERT(INTEGER,QTY)),SUM(SALES)
FROM #TOTALSMAN

--MISC GRPS 98 & 99
INSERT INTO #TOTALSMAN
SELECT '13' AS 'LINE','MISC GRPS 98 & 99 ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
FROM #sqlbpd2 WHERE GRP IN ('98','99') AND REC_CODE NOT IN ('A','C')
AND PRODUCT <> '995000800'

--CR/DSC/ADJ
INSERT INTO #TOTALSMAN
SELECT '14' AS 'LINE','CR/DSC/ADJ ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
FROM #sqlbpd2 WHERE REC_CODE IN ('A','C') AND REASON NOT IN ('18','19','20')
AND PRODUCT <> '995000800'

--FET - ADJ
INSERT INTO #TOTALSMAN
SELECT '15' AS 'LINE','FET - ADJ ' as 'CATEGORY','0',
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
FROM #sqlbpd2 WHERE PRODUCT = '995000800'

--ROAD HAZARD FEE
INSERT INTO #TOTALSMAN
SELECT '16' AS 'LINE','ROAD HAZARD FEE ' as 'CATEGORY','0',
sum(CONVERT(MONEY,ROAD_HAZARD))as 'SALES'
FROM #sqlbpt

--ROAD HAZARD ADJ
INSERT INTO #TOTALSMAN
SELECT '17' AS 'LINE','ROAD HAZARD ADJ ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
FROM #sqlbpd2 WHERE REC_CODE IN ('A','C')
AND REASON IN ('18','19','20')
AND PRODUCT <> '995000800'

--GRAND TOTAL
INSERT INTO #TOTALSMAN
SELECT '18' AS 'LINE',' GRAND-TOTAL',SUM(CONVERT(INTEGER,QTY)),SUM(SALES)
FROM #TOTALSMAN WHERE CONVERT(INTEGER,LINE)>11

SELECT * FROM #TOTALSMAN ORDER BY LINE

 

[SQLDenis]

What do you mean by crashing our server?
Is this proc called from another proc and is the table v created in that proc? If not then why are you dropping the table #TOTALSMAN? It doesn't exist!

--PASSENGER
[COLOR=red]DROP TABLE #TOTALSMAN[/color]
SELECT    '01' AS 'LINE','PASSENGER           ' as 'CATEGORY',SUM(CONVERT(INTEGER,QTY)) as "QTY",
    SUM(CONVERT(MONEY,SALES))+SUM(CONVERT(MONEY,EXCISE))as 'SALES'
INTO #TOTALSMAN
FROM #sqlbpd WHERE GRP = '01'

You can not just drop these table without checking for their existence, they will be dropped on completion of the stored proc anyway

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jenny30534]

Our server crashes with a stop error when the query is ran.

I didn't write the query, one of our developers did. I have asked her the same question, and she says she put the statements there in case the table didn't drop before, she won't listen to much that anyone says, that is why I want other opinions on this query. The proc is not being called from another proc.

Jennifer Perez
MCSA, MCSE, MCT

 

[SQLDenis]

well you can't drop stuff that doesn't exist can you?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jenny30534]

Right, but that shouldn't crash the server. Anything else look off to you?

Jennifer Perez
MCSA, MCSE, MCT

 

[SQLDenis]

>>Our server crashes with a stop error when the query is ran.


Do you mean the query produces a non trapable error?
The server doesn't go down does it, do you need to restart it

Also this is horrible code
(CONVERT(DATETIME,inv_date) >= CONVERT(DATETIME,@FROM_DATE)

This will produce a table scan for sure, meaning the query will be very expensive to run, what kind of data type is inv_date?




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

And also where is the error handling, what happens when an insert fails?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

I would have done this whole thing differently. There are a couple things that bother me, but I don't see anything that would be causing a stop message.

A couple thoughts....

1. Stop using temp tables.
Instead, use @Table variables. Performance will likely be better, but more importantly, you won't have to drop the temp table 'in case the table didn't drop before'. The syntax for table variables is different than temp tables, but only a little.

2. Stop using 'Select ... Into ' to create temp tables.
Instead, you should be creating them with the structure you want, the way you want it.

3. When inserting data, always specify the field names.
It is a little more typing sure, but it removes ambiguity and is therefore just a little safer.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jenny30534]

The server restarts with a stop error, that is why I am involved in this, she is killing my server

inv_date is data type char

I don't know what she does if her inserts fail

Jennifer Perez
MCSA, MCSE, MCT

 

[SQLBill]

Have you tested it yourself? Run the script in Query Analyzer and see what happens.

If it kills the server, it's the script.
If it doesn't kill the server, she's doing something else she's not telling you about.

(Hint, don't tell her you are testing the script. If she's doing something else, you don't want her doing it when you are testing.)

-SQLBill

Posting advice: FAQ481-4875

 

[jenny30534]

Oh, I have tested it, and it kills the server everytime

Jennifer Perez
MCSA, MCSE, MCT

 

[SQLBill]

Okay, now try running each statement separately (for example, first run the statement for Passenger, then for light truck, etc). Highlight one statement, run it. When it's done, highlight the next, run it. Do that until you are done or the server crashes. If it crashes, you will know which statement causes it.

Also, what is this:

--/*
--*/

Commenting out the start and end of comments? Get rid of the dashes. Also, get rid of those lines that have all the dashes as separators.

-SQLBill

Posting advice: FAQ481-4875