Solved

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

Posted on 2007-12-05
25
218 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
 
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 142

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 142

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 142

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 142

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 142

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 142

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now