October 5, 2007 at 10:35 am
The following t-sql gave different result on production SQL server and with integration SQL DB with same data. As workaround we increase the size of @event from 4000 to 8000 and the result now are equal. Any idea what could be the issue. We thought that it is memory leak or Microsoft bug or environment parameter that different from production/integration.
Any idea comment will be appreciated
The querey
declare @Event varchar(4000)
set @Event=''
SELECT TOP 10
@Event=@Event+'"'+ isnull(tblEvent.Description,'')+'","'+ isnull(tblUsers.FullName,'')+'","' +Cast(isnull(tblIREvent.EventDate,'') as VARCHAR)+'","'+
Cast(tblIREvent.RecAddDate as VARCHAR)+'","' +isnull(tblIREvent.UserComment,'') +'"|'
FROM tblIREvent WITH (NOLOCK) INNER JOIN tblEvent WITH (NOLOCK) ON tblIREvent.EventCode = tblEvent.EventCode
LEFT JOIN tblUsers ON tblIREvent.RecAddBy=tblUsers.UserName
WHERE tblIREvent.IR ='23362280' AND tblIREvent.Deleted = 'N' AND PATINDEX ( '%'+tblIREvent.EventCode+'%' , 'LOCCHG' ) > 0
ORDER BY tblIREvent.EventDate
print @Event
The production result 121 char length
"LOCATION CHANGE","Cinthia Pineda","Dec 20 2006 10:21AM","Dec 20 2006 12:21PM","From Delivery Verification To SHIPPING"|
The integration result 920 char length correct result
"LOCATION CHANGE","Interface 315","Dec 11 2006 2:29PM","Dec 11 2006 4:01PM","From 'NOT APPLICABLE' to 'INTR LINE 05'."|"LOCATION CHANGE","Sean Miskimen","Dec 15 2006 11:01AM","Dec 15 2006 1:01PM","From 'INTR LINE 05' to 'INTR LINE 20'."|"LOCATION CHANGE","Armando Hernandez","Dec 18 2006 5:05AM","Dec 18 2006 7:05AM","to prod line 20"|"LOCATION CHANGE","Armando Hernandez","Dec 18 2006 5:05AM","Dec 18 2006 7:05AM","From INTR LINE 20 to PROD. LINE 20"|"LOCATION CHANGE","Luis Viveros","Dec 18 2006 9:59AM","Dec 18 2006 11:59AM","sent to the line"|"LOCATION CHANGE","Luis Viveros","Dec 18 2006 9:59AM","Dec 18 2006 11:59AM","From PROD. LINE 20 to PROD. LINE 05"|"LOCATION CHANGE","Dante Jimenez","Dec 19 2006 12:13PM","Dec 19 2006 2:13PM","From 'PROD. LINE 05' to 'DELIVERY VERIFICATION'."|"LOCATION CHANGE","Cinthia Pineda","Dec 20 2006 10:21AM","Dec 20 2006 12:21PM","From Delivery Verification To SHIPPING"|
October 5, 2007 at 2:20 pm
WITH (NOLOCK) Can produce dirty reads and invalid spureous ROWS.
* Noel
October 5, 2007 at 2:48 pm
Okay. So how come it works when we change the field length.
Thanks
October 7, 2007 at 9:19 pm
What is Integration SQL DB?
I'm not sure why the results would be different. Is there data that's greater than 4000 char and might be getting lost in an implicit conversion?
Also, this moved to the T-SQL forum.
October 8, 2007 at 9:11 am
Integration is the TEST SQL Environment.
Both Production and Test contain the same data.
The same Query returns different results on Production and Test as shown in the original post.
While all data results are returned on TEST, only the last string is returned in PROD.
When the length of @Event is changed from 4000 to 8000 on PROD, all data results are returned.
Wondering, what could be causing this to happen.
Thanks again.
October 8, 2007 at 9:30 am
Very strange.
Are you sure the data is the same? How have you verified? I don't mean to be difficult, but "test" environments often are shared and I've seen people change data or it get altered and someone doesn't realize it.
For production, I'd assume data is being changed, though I'm at a loss to explain how this could be different. If you're sure that things are the same, then I'd actually report this to PSS. There might be something weird with your system, but it might be a bug.
If you figure something out, appreciate an update here.
October 8, 2007 at 11:52 am
Yes, the data is the same.
It is indeed strange. The query returns different results each time the parameters are changed. For eg, if we do select top 8 or change event to char(8000), it returns the full set / expected results of 920 chars. If we do select top 10 or event is char(4000), it truncates the first part of the result set, returing the last 120 chars. It doesn't make any logical sense.
As you said, this could be a bug in the system, which we need to verify with MS support.
Thanks for all the feedback
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply