May 27, 2008 at 7:24 am
Has anyone else had issues with running stored procedures on SQL2005 compatibility when written in SQL2000 compatibility? I migrated several reports from 2000 to 2005 and I am finding that the date parameters are causing the stored procedure to not complete. No errors, they just do not complete. These same stored procedures are executing in 5 secs. in 2000 compatibility mode.
Steve
May 27, 2008 at 7:30 am
Hi,
Please could you give us an example of the proc?
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 27, 2008 at 10:35 am
Here is an example of a stored procedure that passes a date parameter
ALTER PROCEDURE bmssa.sp_VENDOR_ONTIME
/*
Vendor on time performance anaylsys
1/7/08 mm
*/
@Date1 DateTime,
@Date2 DateTime
AS
-- query 1
SELECT LTRIM(PURCHLINE.PURCHID) AS PURCHID, PURCHLINE.LINENUM, LTRIM(PURCHLINE.VENDACCOUNT) AS VENDACCOUNT, VENDTABLE.NAME,
PURCHLINE.ITEMID, PURCHLINE.QTYORDERED, PURCHLINE.CREATEDDATE, PURCHLINE.DELIVERYDATE, PURCHLINE.DATEREQUESTED,
PURCHLINE.CONFIRMEDDLV, PURCHLINE.DATEDELIVERYORIG
INTO #Vontime1
FROM PURCHLINE RIGHT OUTER JOIN
VENDTABLE ON PURCHLINE.VENDACCOUNT = VENDTABLE.ACCOUNTNUM
WHERE (PURCHLINE.PURCHSTATUS = 3) AND (VENDTABLE.DATAAREAID = '100') AND (PURCHLINE.DATAAREAID = '100')
ORDER BY PURCHID, PURCHLINE.LINENUM
--Query 2
IF(SELECT NAME FROM SYSOBJECTS WHERE NAME = 'KJLTMPVendOntime') is not null
DROP TABLE KJLTMPVendOntime
SELECT #Vontime1.*, CUSTVENDEXTERNALITEM.EXTERNALITEMTXT, CUSTVENDEXTERNALITEM.EXTERNALITEMID
INTO KJLTMPVendOntime
FROM #Vontime1 FULL OUTER JOIN
CUSTVENDEXTERNALITEM ON #Vontime1.ITEMID = CUSTVENDEXTERNALITEM.ITEMID
WHERE (CUSTVENDEXTERNALITEM.DATAAREAID = '100') AND
(#Vontime1.CREATEDDATE >= @Date1) AND (#Vontime1.CREATEDDATE <= @Date2)
SELECT * FROM KJLTMPVendOntime
ORDER BY KJLTMPVendOntime.PURCHID, KJLTMPVendOntime.LINENUM
RETURN
May 27, 2008 at 10:54 am
Have you tried running these queries outside of a stored procedure and seeing what results they produce?
May 27, 2008 at 11:01 am
Yes. the select statements execute timely outside of the sp. The issue is pinpointed to the passing of the date parameters in the stored procedure, since my other 2000 stored procedures with no date parameters execute fine.
May 27, 2008 at 11:06 am
With your SELECT INTO FROM statement, your stored procedure will get recompiled every time it runs. When you ran them, did you run the entire contents of the SP, including the table drop and add or just the SELECTs?
May 27, 2008 at 11:10 am
This sounds like an issue with a bad execution plan for the stored procedure, or it can be related to parameter sniffing.
Try adding the following to the procedure:
AS
DECLARE @beginDate datetime,
,@endDate datetime
SET @begindDate = @Date1;
SET @endDate = @Date2;
-- query 1
SELECT LTRIM(PURCHLINE.PURCHID) AS PURCHID, PURCHLINE.LINENUM, LTRIM(PURCHLINE.VENDACCOUNT) AS VENDACCOUNT, VENDTABLE.NAME,
PURCHLINE.ITEMID, PURCHLINE.QTYORDERED, PURCHLINE.CREATEDDATE, PURCHLINE.DELIVERYDATE, PURCHLINE
...
Then use the new date variables in your query and see if that corrects the problem.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 27, 2008 at 11:14 am
Just the select statements.
May 27, 2008 at 11:37 am
Hi Jeff, Thanks for the reply. So I am declaring 4 variables and setting them to null prior to the execution? To wash them out?
DECLARE all four variables as date time
set begindt - date1
set enddt - date2
Then use the begindt and enddt variables in the select statement?
May 27, 2008 at 2:24 pm
No, you will still have your two input parameters declared as datetime. You are adding two new variables (@beginDate and @endDate) as datetime variables. You are then going to set those new variables equal to the date parameters being passed into the procedure - and use the new variables in your procedure.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2008 at 1:57 pm
Hi Jeff,
Is this the syntax you were describing?
ALTER PROCEDURE bmssa.sp_VENDOR_ONTIME
/*
Vendor on time performance anaylsys
1/7/08 mm
*/
AS
DECLARE
@BeginDt Datetime,
@EndDt Datetime,
@Date1 Datetime,
@Date2 Datetime
SET @BeginDt = @Date1;
SET@EndDt = @Date2;
-- query 1
SELECT LTRIM(PURCHLINE.PURCHID) AS PURCHID, PURCHLINE.LINENUM, LTRIM(PURCHLINE.VENDACCOUNT) AS VENDACCOUNT, VENDTABLE.NAME,
PURCHLINE.ITEMID, PURCHLINE.QTYORDERED, PURCHLINE.CREATEDDATE, PURCHLINE.DELIVERYDATE, PURCHLINE.DATEREQUESTED,
PURCHLINE.CONFIRMEDDLV, PURCHLINE.DATEDELIVERYORIG
INTO #Vontime1
FROM PURCHLINE RIGHT OUTER JOIN
VENDTABLE ON PURCHLINE.VENDACCOUNT = VENDTABLE.ACCOUNTNUM
WHERE (PURCHLINE.PURCHSTATUS = 3) AND (VENDTABLE.DATAAREAID = '100') AND (PURCHLINE.DATAAREAID = '100')
ORDER BY PURCHID, PURCHLINE.LINENUM
--Query 2
IF(SELECT NAME FROM SYSOBJECTS WHERE NAME = 'KJLTMPVendOntime') is not null
DROP TABLE KJLTMPVendOntime
SELECT #Vontime1.*, CUSTVENDEXTERNALITEM.EXTERNALITEMTXT, CUSTVENDEXTERNALITEM.EXTERNALITEMID
INTO KJLTMPVendOntime
FROM #Vontime1 FULL OUTER JOIN
CUSTVENDEXTERNALITEM ON #Vontime1.ITEMID = CUSTVENDEXTERNALITEM.ITEMID
WHERE (CUSTVENDEXTERNALITEM.DATAAREAID = '100') AND
(#Vontime1.CREATEDDATE >= @BeginDt) AND (#Vontime1.CREATEDDATE <= @EndDt)
SELECT * FROM KJLTMPVendOntime
ORDER BY KJLTMPVendOntime.PURCHID, KJLTMPVendOntime.LINENUM
RETURN
May 28, 2008 at 3:15 pm
No - you need to keep the parameters, and add the local variables:
CREATE PROCEDURE bmssa.usp_VENDOR_ONTIME
@Date1datetime
,@Date2datetime
AS
DECLARE@BeginDt Datetime
,@EndDt Datetime
SET @BeginDt = dateadd(day, datediff(day, 0, @Date1), 0); -- round to midnight
SET @EndDt = dateadd(day, datediff(day, 0, @Date2), 0); -- round to midnight
-- query 1
SELECT LTRIM(p.PURCHID) AS PURCHID
,p.LINENUM
,LTRIM(p.VENDACCOUNT) AS VENDACCOUNT
,v.NAME
,p.ITEMID
,p.QTYORDERED
,p.CREATEDDATE
,p.DELIVERYDATE
,p.DATEREQUESTED
,p.CONFIRMEDDLV, PURCHLINE.DATEDELIVERYORIG
INTO #Vontime1
FROM PURCHLINE p
RIGHT OUTER JOIN VENDTABLE v ON p.VENDACCOUNT = v.ACCOUNTNUM
WHERE p.PURCHSTATUS = 3
AND v.DATAAREAID = '100'
AND p.DATAAREAID = '100'
/* don't need to order by while inserting into a table, tables are unordered sets */
--ORDER BY PURCHID
--,PURCHLINE.LINENUM
--Query 2
SELECT t.*
,item.EXTERNALITEMTXT
,item.EXTERNALITEMID
FROM #Vontime1 t
FULL OUTER JOIN CUSTVENDEXTERNALITEM item ON t.ITEMID = item.ITEMID
WHERE item.DATAAREAID = '100'
AND t.CREATEDDATE >= @BeginDt
AND t.CREATEDDATE < @EndDt
ORDER BY t.PURCHID
,t.LINENUM
RETURN
I reformatted the above query, added aliases and removed the time from the input parameters. Test the above and make sure the parameter @Date2 is the day following the date range you want. For example, if you want the data for yesterday - you could call the procedure with:
DECLARE @date1 datetime;
DECLARE @date2 datetime;
SET @date1 = dateadd(day, -1, getdate()); -- yesterday
SET @date2 = getdate(); -- today
EXECUTE bmmsa.usp_VENDOR_ONTIME @date1, @date2;
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2008 at 3:53 pm
Hi Jeff,
Thank you for the help. Much appreciated.
Steve
May 28, 2008 at 4:07 pm
Did this solve your problem? Please post back and let me know the results.
Thanks,
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2008 at 4:38 pm
Long story short, this sp was not the issue. We have another sp that is running without returning a result set. I am going to apply what you've shared to that one and see if we have a solution. I will let you know.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply