August 31, 2011 at 3:36 am
Hello All,
I am doing a database migration from SQL Server 2000 to 2008.
During migration, I found one of the SPs had a patindex function which is causing difference in functionality in the new SQL server 2008.
Patindex results are not matching in SQL 2000 and SQL 2008.
A simple example is here:
SELECT PATINDEX('%%', 'Test')
In SQL 2000, I am getting "0" as the result while SQL 2008 is giving me "1".
Any of you have an idea how to resolve this issue? Can any server/ database level settings causing this difference?
Appreciate any help on this.
Regards,
Sandeep
August 31, 2011 at 7:56 am
Looks like this is something that's been fixed in SQL 2005 - I've played around with compatibility and you can't change it.
Technically speaking, SQL 2000's answer was incorrect as the first occurence of the pattern (which is any or no characters) is indeed at position 1.
Plenty of ways to fix it, but all I can think of involve a code change - what are you doing that requires this to return 0?
August 31, 2011 at 8:28 am
Thanks Howard
Its just that a business logic is written in the current SP based on the output of the patindex. If I have to rewrite the code based on corrected behavior of SQL 2008, I have to analyze the current logic, change the code in multiple places and test it.
Was looking if there a solution to just retain the queries as is, if any of the setting can be changed.
But, I think it is not possible; fixing the stored procedure seems to be the only solution.
Thanks,
Sandeep
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply