February 5, 2009 at 5:59 am
Hi All,
I am executing the following query on SQL 2000 as well as SQL 2005
but its behaving oddly in SQL 2000. It does not return me same results.
In SQL 2005 it updates 13 rows whereas in SQL 2000 it updates no rows.
The data as well as structure of tables is exactly the same since I detached the database on SQL 2000 and attached it on SQL 2005.
Any idea on the same?
DECLARE @FEEDDATE DATETIME
DECLARE @BSTVal INT
SET @FEEDDATE = Convert(varchar, '01/21/2009', 101)
EXECUTE @BSTVal = prcBSTOn @FEEDDATE, 0
UPDATE tblselectedrate
SET Bid = tblCurrencyQuote.Bid,
BankCodeBid = tblCurrencyQuote.BankCode,
TimeBid = tblCurrencyQuote.Time,
LowBid = tblCurrencyQuote.Bid,
LowTimeBid = tblCurrencyQuote.Time,
LowBankCode = tblCurrencyQuote.BankCode
FROM tblselectedrate with (NOLOCK)
INNER JOIN tblCurrencyQuote WITH (NOLOCK) ON tblselectedrate.ISOCurrencyCode = tblCurrencyQuote.ISOCurrencyCode
INNER JOIN tblCurrency WITH (NOLOCK) ON tblCurrencyQuote.ISOCurrencyCode = tblCurrency.ISOCurrencyCode
WHERE
(
( CONVERT(varchar(10),tblCurrencyQuote.[Time],108) BETWEEN CONVERT(varchar(10), DateAdd(hh, -@BSTVal, tblCurrency.StartTime), 108) AND CONVERT(varchar(10),DateAdd(hh, -@BSTVal, tblCurrency.EndTime),108))
AND tblCurrencyQuote.Bid = (
SELECT MIN (Bid) FROM tblCurrencyQuote tmp1 WITH (NOLOCK)
WHERE
( CONVERT(varchar(10),tmp1.[Time],108) BETWEEN CONVERT(varchar(10),DateAdd(hh, -@BSTVal, tblCurrency.StartTime),108) AND CONVERT(varchar(10),DateAdd(hh, -@BSTVal, tblCurrency.EndTime),108))
AND CONVERT(varchar(10),tmp1.[Time],101) = Convert(varchar(10),@FEEDDATE,101)
AND tmp1.ISOCurrencyCode = tblCurrencyQuote.ISOCurrencyCode
AND tmp1.ISOCurrencyCode = tblCurrency.ISOCurrencyCode
AND tmp1.ISOCurrencyCode IN
(
SELECT DISTINCT (tblselectedrate.ISOCurrencyCode)
FROM
tblselectedrate WITH (NOLOCK)
INNER JOIN tblcurrency WITH (NOLOCK) ON tblselectedrate.ISOCurrencyCode = tblcurrency.ISOCurrencyCode
WHERE
UPPER(LTRIM(RTRIM(tblcurrency.SourceBid))) = 'F'
AND
DATEDIFF(Day,tblselectedrate.FeedDate, @FEEDDATE) = 0
AND
(LEN(RTRIM(LTRIM(tblcurrency.RefCurrencyCode))) = 0 OR RTRIM(LTRIM(tblcurrency.RefCurrencyCode)) IS NULL)
)
AND tmp1.Status = 0
)
AND tblCurrencyQuote.Status = 0
AND DATEDIFF(Day,tblselectedrate.FeedDate, @FEEDDATE) = 0
AND DATEDIFF(Day,tblCurrencyQuote.[Time], @FEEDDATE) = 0
)
Thanks in Advance...
February 6, 2009 at 5:19 am
Remove WITH (NOLOCK) and then compare.
MJ
February 6, 2009 at 5:28 am
I would check the settings for language and date first. There is a lot of date to string conversion going on, and my guess would be a language setting making nn/nn/nnnn meane either dd/mm/yyyy or mm/dd/yyyy. Eg near the start you are converting a string date to varchar, and then there is an implicit conversion to put it in the variable. There are lots of similar things happening later as well.
Hope that helps
Mike
February 6, 2009 at 5:48 am
Thanks Mike...
But I checked well all the date conversions where probably the code could crash down on different versions of SQL Server.
Also checked with the date settings on the servers.
February 8, 2009 at 5:02 pm
Which compatibility level are you running on the 2005 database?
Cheers,
Win
February 8, 2009 at 9:44 pm
I am using SQL Server 2000(80) as the compatibilty level for SQL 2005 database which is the same as in SQL 2000 database.
One important point I would like to mention is that the results are appropriate on SQL 2005 database but there seems to be some error or bug in SQL 2000 database.
February 9, 2009 at 7:26 am
Just to be sure, you might want to go ahead and compare the tables between servers to be absolutely sure they are indeed identical. To quote Sherlock Holmes, "When all else is eliminated, whatever remains, however improbable, must be true".
Never be afraid to question your assumptions, and see where that leads you.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 9, 2009 at 12:06 pm
I am still fairly convinced that we have a date formatting issue here. EG:
SET @FEEDDATE = Convert(varchar, '01/21/2009', 101)
asks to convert 01/21/2009 to VARCHAR, using style 101, and there is then an imolicit conversion to datetime to convert to feeddate. So while the 101 style will be applied to the initial conversion it will not apply to the conversion to feeddate. I woul dbe happier if it read
SET @FEEDDATE = Convert(datetime, '01/21/2009', 101)
or
SET @FEEDDATE = '20090121' would be OK as it is unambiguous. All the formats that contain separators are subject to the date time confog settings in SQL Server so are always best avoided.
Can you check that select @@language returns the same thing on both installations?
Mike
February 9, 2009 at 4:38 pm
If the update works properly in the new 2005 environment, but not in the old 2000 environment I would take a practical approach and move everything to 2005. Then sit down, take a deep breath and delve into the 2000 database and examine what else went wrong and why. This way you prevent more errors while you search for the existing errors.
Cheers,
Win
June 15, 2009 at 10:53 pm
Hi! Try using this CAST(CONVERT(VARCHAR,yourdate,101) AS DATETIME)
June 17, 2009 at 8:25 am
Have you checked that the value of @BSTVal is being set correctly in both cases?
I assume prcBSTOn is a proc, the code for which is not listed.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply