Solved

Problem with a procedure not working using variables with dates in a WHERE clause.

Posted on 2007-12-05
25
229 Views
Last Modified: 2016-08-29
I have the code below in a procedure. A couple of weeks ago, it stopped working. The procedure is executed as a job step. There are no error indications, but no data gets loaded into the table. Notice that there are two lines of code commented out. They are the lines that read:
   ... BETWEEN @HeaderDateStart and @HeaderDateEnd

I have the line of code:
-- Show calculated dates
      SELECT      @HeaderDateStart, @HeaderDateEnd

that displays the correct date(s) for the procedure. However, as stated, no data gets loaded. If I comment the line with the variables, and hard-code the dates (as show) everything works properly. Two weeks ago, when I first became aware of the problem, I tried running the procedure in QA to see if I could figure out what was happening.

However, QA consistently returns the error:
    [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
    Server: Msg 11, Level 16, State 1, Line 0
    General network error. Check your network documentation.

    Connection Broken

At first, I figured that maybe it was because I was on vacation at the time and was connecting into work via VPN and that had something to do with it. But it still gives me that error even with a local connection.

I have tried replacing the virtual tables with temp tables (not really expecting that to help), and also tried replacing the line with the code:
    AND det.fld_HeaderDate BETWEEN (SELECT MIN(DateToLoad) FROM @LoadDates) AND (SELECT MAX(DateToLoad) FROM @LoadDates)

I still get the error in QA and/or no data loaded when executed as a step in a job. As indicated, when executed as a job step, I do not get any error messages at all, although the procedure does not appear to complete (I don't get the message that execution has completed.)

This is driving me crazy, especially since I need to manually run the steps of the job every night and hard-code the date for each run. Any ideas???????    
DECLARE @CurDate 		SMALLDATETIME
	DECLARE @HeaderDateStart 	SMALLDATETIME
	DECLARE @HeaderDateEnd 	SMALLDATETIME
	DECLARE @ErrorCode		INT
	DECLARE @Message 		VARCHAR(200)
	DECLARE @LoadDateTime 	SMALLDATETIME
	DECLARE @Dates TABLE (ThisDate SMALLDATETIME)
	DECLARE @LoadDates TABLE (DateToLoad SMALLDATETIME)
 
	SET @LoadDateTime = GETDATE()
 
	SET @CurDate = CAST(CONVERT(CHAR(10), DATEADD(DAY, -30, GETDATE()), 101) AS SMALLDATETIME)
 
	WHILE @CurDate <= GETDATE()
	    BEGIN
		INSERT INTO @Dates SELECT @CurDate
		SET @CurDate = DATEADD(DAY, 1, @CurDate)
	    END
 
	INSERT INTO @LoadDates (DateToLoad)
	SELECT ThisDate
	FROM @Dates td
	LEFT JOIN
	    (
		SELECT DISTINCT DateSentToDish
		FROM tbl_COMX_RejectResearch WITH (NOLOCK)
	    ) AS rej
	    ON td.ThisDate = rej.DateSentToDish
	WHERE rej.DateSentToDish IS NULL
 
	SELECT	@HeaderDateStart = MIN(DateToLoad),
			@HeaderDateEnd = MAX(DateToLoad)
	FROM 		@LoadDates
 
-- Show calculated dates
	SELECT	@HeaderDateStart, @HeaderDateEnd
 
	IF @HeaderDateStart IS NULL
	    BEGIN
		EXEC dbo.spWriteImportJobMessage 'Data already loaded for all dates.'
		GOTO ExitWithSuccess
	    END
 
	SET @Message = 'Executing Procedure for Header Date' + CASE WHEN @HeaderDateStart = @HeaderDateEnd THEN
			' ' + CONVERT(CHAR(10), @HeaderDateStart, 101) ELSE
			's ' + CONVERT(CHAR(10), @HeaderDateStart, 101) + ' through '
			+ CONVERT(CHAR(10), @HeaderDateEnd, 101) END
	EXEC dbo.spWriteImportJobMessage @Message
 
	BEGIN TRANSACTION
-- Insert transactions into reject research table
		INSERT INTO dbo.tbl_RejectResearch WITH (ROWLOCK, READCOMMITTED)
		    (
			col1,
			col2,
			col3,
			col4,
			col5,
			col6,
			col7,
			col8
		    )
			SELECT	acct.col1In,
					acct.col2In,
					dbo.fn_getCOMX_GroupChildID('RejectResearchSource', 'Final Reject'),
					det.col4In,
					dbo.GetParmsSubGroupFromParentChild('Reject Reason Code', 'No Research Done'),
					dbo.GetParmsSubGroupFromParentChild('Reject Action', 'No Research Done'),
					dbo.GetDueDate(acct.fld_DISHAccountNumber, det.HeaderDate),
					@LoadDateTime
			FROM		dbo.tbl_Detail AS det WITH (NOLOCK)
			INNER JOIN 	dbo.tbl_Accounts AS acct WITH (NOLOCK) ON (det.fld_COMXID = acct.fld_comxid)
			WHERE 	det.fld_ReasonCode <> '00'
			AND		det.fld_HeaderDate BETWEEN '2007-12-05' and '2007-12-05'
--			AND		det.fld_HeaderDate BETWEEN @HeaderDateStart and @HeaderDateEnd
			AND		det.fld_TransactionDate >= CAST('2007-08-01' AS DATETIME)
			GROUP BY 	acct.fld_AccountNumber, acct.fld_ATN, det.fld_HeaderDate
 
			SELECT @ErrorCode = @@ERROR
			IF @ErrorCode <> 0
			    BEGIN
				GOTO ExitWithRollback
			    END
 
			UPDATE 	tbl_RejectResearch
			SET 		ReasonCode = 1
			WHERE 	EXISTS  
			    (
				SELECT 	AcctNbr
				FROM 		tbl_RejectResearch a 
				WHERE 	a.AcctNbr = tbl_RejectResearch.AcctNbr 
				AND 	DATEDIFF(dd, a.DateSent, tbl_RejectResearch.DateSent) BETWEEN 1 AND 7 
			    ) 
			AND		 tbl_RejectResearch.DateSent BETWEEN '2007-12-05' and '2007-12-05'
--			AND		 tbl_RejectResearch.DateSent BETWEEN @HeaderDateStart and @HeaderDateEnd
 
		SELECT @ErrorCode = @@ERROR
		IF @ErrorCode <> 0
		    BEGIN
			GOTO ExitWithRollback
		    END
 
		EXEC dbo.spWriteImportJobMessage 'Execution of procedure complete.'
		COMMIT TRANSACTION
 
	GOTO ExitWithSuccess
 
ExitWithRollback:
	WHILE (@@TRANCOUNT > 0)
	    BEGIN
		ROLLBACK TRANSACTION
	    END           
 
	SET @Message = 'Procedure failed with error code ' 
		+ CAST(@ErrorCode AS VARCHAR(10))
	EXEC dbo.spWriteImportJobMessage @Message, 1
 
ExitWithSuccess:
GO

Open in new window

0
Comment
Question by:dbbishop
  • 14
  • 6
  • 5
25 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20416207
one note:

If @LoadDates is empty, then this will cause nothing to be set, i.e.
@HeaderDateStart = NULL and same for end.

        SELECT  @HeaderDateStart = MIN(DateToLoad),
                        @HeaderDateEnd = MAX(DateToLoad)
        FROM            @LoadDates

Is it possible during the night due to other jobs/processes, for
        WHERE rej.DateSentToDish IS NULL
to result in 0 records?
0
 
LVL 15

Author Comment

by:dbbishop
ID: 20416479
IF @HeaderDateStart IS NULL
          BEGIN
            EXEC dbo.spWriteImportJobMessage 'Data already loaded for all dates.'
            GOTO ExitWithSuccess
          END

will handle a scenario where @LoadDates is empty and will successfully terminate the procedure. There could be no records found (as would happen should someone try to run the process a second time on tha same date. In the scenarios I describe above, I have either a single date (where start and end date would be set to the same value, or I have a range of dates (which occurs if the process is not run for one or more dates. Ahead of all this are procedures that import text files into SQL Server. Everything runs okay except for this one procedure.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20416496
Okay, I think I see a potential problem. Because the query is using variables for the dates, the procedure plan could have been cached (parameter sniffing) not knowing that the range will be small.  If your date range is guaranteed to be small, and you have an index on it that should be used, try this

FROM    dbo.tbl_Detail AS det WITH (NOLOCK, INDEX(<indexnamehere>))
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 15

Author Comment

by:dbbishop
ID: 20416529
I'll give it a try. It will be tomorrow afternoon before I can actually run it (after we receive the import fiels and they are processed) and it may even be Friday since I need to leave early tomorrow, possibly before the files arrive. I have tickets to see Billy Joel in concert and that takes priority over a trivial little matter like a production job not working properly :-)
0
 
LVL 15

Author Comment

by:dbbishop
ID: 20420063
I added the index hint. Just to test things, I broke out just the SELECT portion of the INSERT statement, and set values for @HeaderDateStart and @HeaderDateEnd and provided a date range. I was beginning to think I would see the query that didn't have the index hint fail, but it did succeed in returning data in about 40 seconds. With the index hint, it took under a second.

It will be interesting to see what happens when it runs for real.
0
 
LVL 15

Author Comment

by:dbbishop
ID: 20434726
Your suggestion did not help. I still get no data inserted into the table when I use variables for the dates.
0
 
LVL 15

Author Comment

by:dbbishop
ID: 20467640
Need some more help trying to resolve this...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20467684
can I assume that the field dbo.tbl_Detail.fld_HeaderDate  is of data type smalldatetime also?

could you upload somewhere the graphical explain plan when using the variables?
0
 
LVL 15

Author Comment

by:dbbishop
ID: 20467731
Yes it is. All dates involved are SMALLDATETIME format.
0
 
LVL 15

Author Comment

by:dbbishop
ID: 20467859
How do I get a graphical plan?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20467907
sql server 2000? QA -> Show Execution plan + run query
sql server 2005? Management Studio -> Query Sheet -> Show Execution plan + run query
0
 
LVL 15

Author Comment

by:dbbishop
ID: 20468011
0
 
LVL 15

Author Comment

by:dbbishop
ID: 20468045
I've also done a screen-shot of the text for the first seek using the date variables. All is at https://filedb.experts-exchange.com/incoming/ee-stuff/6152-QueryPlan.jpghttps://filedb.experts-exchange.com/incoming/ee-stuff/6153-IndexSeek.jpg
 
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20468064
unfortunately, www.ee-stuff.com is unaccessible to me from home, I have not yet found a way to access it.
you might try to send the files by email, check my profile, there you find my email address...
0
 
LVL 15

Author Comment

by:dbbishop
ID: 20468224
Sent
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20468549
mail arrived. execution plan looks like "perfect"...

now, could you try
* doing the select into a table variable, and insert to the actual table in a second step?
* remove the rowlock and readcommitted hints while inserting?
0
 
LVL 15

Author Comment

by:dbbishop
ID: 20468812
I'm sorry, I don't understand the first part of your question, "doing the select into a table variable, and insert to the actual table in a second step?"
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20468965
For the select for insert, try this.
Another option is to disregard the GROUP BY and make the select a SUBQUERY and perform the GROUP BY after you have collected data. SQL Server sometimes performs poorly if you attempt to group by by trying to collect in the order of the group by
SELECT  acct.col1In,
                                        acct.col2In,
                                        dbo.fn_getCOMX_GroupChildID('RejectResearchSource', 'Final Reject'),
                                        det.col4In,
                                        dbo.GetParmsSubGroupFromParentChild('Reject Reason Code', 'No Research Done'),
                                        dbo.GetParmsSubGroupFromParentChild('Reject Action', 'No Research Done'),
                                        dbo.GetDueDate(acct.fld_DISHAccountNumber, det.HeaderDate),
                                        @LoadDateTime
                        FROM            dbo.tbl_Detail AS det WITH (NOLOCK)
                        INNER JOIN      dbo.tbl_Accounts AS acct WITH (NOLOCK) ON (det.fld_COMXID = acct.fld_comxid)
                        WHERE   det.fld_ReasonCode <> '00'
                        AND             det.fld_HeaderDate BETWEEN '2007-12-05' and '2007-12-05'
--                      AND             det.fld_HeaderDate BETWEEN @HeaderDateStart and @HeaderDateEnd
                        AND             det.fld_TransactionDate >= CAST('2007-08-01' AS DATETIME)
                        GROUP BY        acct.fld_AccountNumber, acct.fld_ATN, det.fld_HeaderDate
option (force order, maxdop 1)

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20470062
>I'm sorry, I don't understand the first part of your question, "doing the select into a table variable, and insert to the actual table in a second step?"

I mean like below.
you might do the INSERT INTO @temp even before the transaction.
you might also try to do the group by in the second insert and not during the first insert...
declare @temp TABLE ( col1 int , col2 int, col3 varchar(30) , ... etc ... )
 
INSERT INTO @temp (
                        col1,
                        col2,
                        col3,
                        col4,
                        col5,
                        col6,
                        col7,
                        col8
                    )
 
                        SELECT  acct.col1In,
                                        acct.col2In,
                                        dbo.fn_getCOMX_GroupChildID('RejectResearchSource', 'Final Reject'),
                                        det.col4In,
                                        dbo.GetParmsSubGroupFromParentChild('Reject Reason Code', 'No Research Done'),
                                        dbo.GetParmsSubGroupFromParentChild('Reject Action', 'No Research Done'),
                                        dbo.GetDueDate(acct.fld_DISHAccountNumber, det.HeaderDate),
                                        @LoadDateTime
                        FROM            dbo.tbl_Detail AS det WITH (NOLOCK)
                        INNER JOIN      dbo.tbl_Accounts AS acct WITH (NOLOCK) ON (det.fld_COMXID = acct.fld_comxid)
                        WHERE   det.fld_ReasonCode <> '00'
                        AND             det.fld_HeaderDate BETWEEN '2007-12-05' and '2007-12-05'
--                      AND             det.fld_HeaderDate BETWEEN @HeaderDateStart and @HeaderDateEnd
                        AND             det.fld_TransactionDate >= CAST('2007-08-01' AS DATETIME)
                        GROUP BY        acct.fld_AccountNumber, acct.fld_ATN, det.fld_HeaderDate
 
 
INSERT INTO dbo.tbl_RejectResearch WITH (ROWLOCK, READCOMMITTED)
                    (
                        col1,
                        col2,
                        col3,
                        col4,
                        col5,
                        col6,
                        col7,
                        col8
                    )
select col1, col2, col3, col4, col5, col6, col7, col8  from @temp

Open in new window

0
 
LVL 15

Author Comment

by:dbbishop
ID: 20470078
Gottcha. There could be a 3,000 to 4,000 records if I don't do the grouping until the 2nd INSERT. Typically, with the grouping, there are typically between 50 and 400 records.

What has me confused is that this use to work and stopped working a few weeks ago. I can't figure out why it would not work with variables used for the dates but will when I hardcode the dates.

I'll try to get to this tomorrow (later today). I had some other problems crop up later in the day that have me scratching my head.
0
 
LVL 15

Author Comment

by:dbbishop
ID: 20476375
angelIII. This seems to work. Any explanation as to why it needed to be chaged to work this way? I'd really like to try to figure out how to get the original code to work again. It seems like a waste of effort to go about i8t this way, and seems unclear to someone who follows up as to why it is being done this way.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20476650
Is http://#20468965 easier for you to follow, and therefore someone else trying to maintain it after you?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20476967
>Any explanation as to why it needed to be chaged to work this way?
not a scientific explanation per se. I just had a similar situation, where a direct insert into a linked server seemed to hang, although run manually, executed in +-30 seconds.
I then tried the same workaround, which make the select into the table variable still +-30 seconds, but the insert into the linked server (oracle) instantly. I assumed that the openquery method already opened the linked server, but then timed out as the select took over 30 seconds, but failed to handle that situation properly.
I am not sure if the same explanation applies to you also, but what works should be used...
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20477203
As the saying goes, there's more than one way to cook an egg! Consider your options, and pick the best one to go forward with, all things weighted.
0
 
LVL 15

Author Closing Comment

by:dbbishop
ID: 31412998
Thanks for helping looking outside the box. Probably not a solution I would have come up with but for now it works. No linked servers, but I am kind of glad you experienced a problem and came up with a solution that could help me.

Doug
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question