|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Upgrade from SQL Server 7.0 to SQL Server 2000I am faced with an unusual SQL Server upgrade related problem.I was using the SQL Server 7.0 until about a few days ago when we upgraded to SQL Server 2000. I had the following query in one of the views. "SELECT UT.EMAIL_ID FROM DPS_APP_DB.DBO.DPS_USERS UT WHERE UT.USER_REF_ID=@T_PATHOLOGIST_ID AND UT.USER_TYPE='PATHOLOGIST'" In this query, UT.USER_REF_ID sometimes has a comma separated value, for example '123,456,5657'.This is an exceptional case of the data. But when we were working on 7.0 there were no exceptions/errors raised on execution of the view with the comma separated data. But after our upgrade to SQL Server 2000, the error "Error converting varchar to int is thrown" as essentially, @T_PATHOLOGIST_ID is an integer field and UT.USER_REF_ID has a comma separated value. We deduced that this is due to the upgrade and that 7.0 suppressed the error, while 2000 throws the exception.To further strengthen our claim, we reverted back to 7.0, tested the view and it didnt throw any error for the same data. We then re-installed SQL 2000 and we found the same error being throw. However i find no evidence either in the SQL 2000 Release notes or bug fix lists to support our thinking. Is what i am thinking right? Can someone help me out? Thanks, Poorna Poorna wrote on Thu, 30 Jun 2005 03:19:07 -0700:
Show quote > Hi, SQL Server 2000 changed the way implicit conversion is handled - we had a > I am faced with an unusual SQL Server upgrade related problem.I was using > the SQL Server 7.0 until about a few days ago when we upgraded to SQL > Server 2000. I had the following query in one of the views. > > "SELECT UT.EMAIL_ID FROM DPS_APP_DB.DBO.DPS_USERS UT > WHERE UT.USER_REF_ID=@T_PATHOLOGIST_ID AND UT.USER_TYPE='PATHOLOGIST'" > > In this query, UT.USER_REF_ID sometimes has a comma separated value, for > example '123,456,5657'.This is an exceptional case of the data. But when > we were working on 7.0 there were no exceptions/errors raised on execution > of the view with the comma separated data. But after our upgrade to SQL > Server 2000, the error "Error converting varchar to int is thrown" as > essentially, @T_PATHOLOGIST_ID is an integer field and UT.USER_REF_ID has > a comma separated value. > > We deduced that this is due to the upgrade and that 7.0 suppressed the > error, while 2000 throws the exception.To further strengthen our claim, we > reverted back to 7.0, tested the view and it didnt throw any error for the > same data. We then re-installed SQL 2000 and we found the same error being > throw. > > However i find no evidence either in the SQL 2000 Release notes or bug fix > lists to support our thinking. Is what i am thinking right? Can someone > help me out? > > Thanks, > Poorna couple of queries here written by the other IT guy that got tripped up by this. What you're seeing is the result of SQL 2K attempting to converting the varchar value '123,456,5657' to an int to compare to the int column UT.USER_REF_ID and throwing the error. In SQL 7 you didn't get the error because the UT.USER_REF_ID column would have been implictly converted to a varchar before comparing to '123,456,5657'. Check BOL for datatype precedence to see how implicit conversions have changed between the versions. Dan Check this for more info.
http://support.microsoft.com/default.aspx?scid=kb;en-us;271566 BTW, I got the column and value parts back to front in my earlier reply. SQL7 would always convert the @T_PATHOLOGIST_ID value to a varchar as the column UT.USER_REF_ID is a varchar, but SQL2K forces data type precedence handling. As a solution, change your parameter to be a varchar. Dan If, as in your example, a row in UT.USER_REF_ID has the value
'123,456,5657' and @T_PATHOLOGIST_ID = 456, how do you want to handle this?Should this row be returned? If not, Daniel's solution should work for you. If yes, then you had a problem in version 7 because the row was not returned. Show quote "Poorna" <Poo***@discussions.microsoft.com> wrote in message news:E27F1266-E28E-46DF-BAE6-6AD5145D242A@microsoft.com... > Hi, > I am faced with an unusual SQL Server upgrade related problem.I was using > the SQL Server 7.0 until about a few days ago when we upgraded to SQL > Server > 2000. I had the following query in one of the views. > > "SELECT UT.EMAIL_ID FROM DPS_APP_DB.DBO.DPS_USERS UT > WHERE UT.USER_REF_ID=@T_PATHOLOGIST_ID AND UT.USER_TYPE='PATHOLOGIST'" > > In this query, UT.USER_REF_ID sometimes has a comma separated value, for > example '123,456,5657'.This is an exceptional case of the data. But when > we > were working on 7.0 there were no exceptions/errors raised on execution of > the view with the comma separated data. But after our upgrade to SQL > Server > 2000, the error "Error converting varchar to int is thrown" as > essentially, > @T_PATHOLOGIST_ID is an integer field and UT.USER_REF_ID has a comma > separated value. > > We deduced that this is due to the upgrade and that 7.0 suppressed the > error, while 2000 throws the exception.To further strengthen our claim, we > reverted back to 7.0, tested the view and it didnt throw any error for the > same data. We then re-installed SQL 2000 and we found the same error being > throw. > > However i find no evidence either in the SQL 2000 Release notes or bug fix > lists to support our thinking. Is what i am thinking right? Can someone > help > me out? > > Thanks, > Poorna Sorry, hit the wrong button and sent the message before finishing.
If you do want to handle that possibility, get back to us. Show quote "Poorna" <Poo***@discussions.microsoft.com> wrote in message news:E27F1266-E28E-46DF-BAE6-6AD5145D242A@microsoft.com... > Hi, > I am faced with an unusual SQL Server upgrade related problem.I was using > the SQL Server 7.0 until about a few days ago when we upgraded to SQL > Server > 2000. I had the following query in one of the views. > > "SELECT UT.EMAIL_ID FROM DPS_APP_DB.DBO.DPS_USERS UT > WHERE UT.USER_REF_ID=@T_PATHOLOGIST_ID AND UT.USER_TYPE='PATHOLOGIST'" > > In this query, UT.USER_REF_ID sometimes has a comma separated value, for > example '123,456,5657'.This is an exceptional case of the data. But when > we > were working on 7.0 there were no exceptions/errors raised on execution of > the view with the comma separated data. But after our upgrade to SQL > Server > 2000, the error "Error converting varchar to int is thrown" as > essentially, > @T_PATHOLOGIST_ID is an integer field and UT.USER_REF_ID has a comma > separated value. > > We deduced that this is due to the upgrade and that 7.0 suppressed the > error, while 2000 throws the exception.To further strengthen our claim, we > reverted back to 7.0, tested the view and it didnt throw any error for the > same data. We then re-installed SQL 2000 and we found the same error being > throw. > > However i find no evidence either in the SQL 2000 Release notes or bug fix > lists to support our thinking. Is what i am thinking right? Can someone > help > me out? > > Thanks, > Poorna Hello Dan and Raymond,
Thanks for the posts.It clarified a lot of what we were thinking. We modified the query as shown below: SELECT UT.EMAIL_ID FROM DPS_APP_DB.DBO.DPS_USERS UT WHERE ( UT.[USER_REF_ID] = STR( @T_PATHOLOGIST_ID ) OR UT.[USER_REF_ID] LIKE STR( @T_PATHOLOGIST_ID ) + ',%' OR UT.[USER_REF_ID] LIKE '%,' + STR( T_PATHOLOGIST_ID ) OR UT.[USER_REF_ID] LIKE '%,' + STR( T_PATHOLOGIST_ID ) + ',%' ) AND UT.USER_TYPE='PATHOLOGIST' Is there a better way to achieve the same result? Do let me know if there is. Thanks once again, Poorna Show quote "Poorna" wrote: > Hi, > I am faced with an unusual SQL Server upgrade related problem.I was using > the SQL Server 7.0 until about a few days ago when we upgraded to SQL Server > 2000. I had the following query in one of the views. > > "SELECT UT.EMAIL_ID FROM DPS_APP_DB.DBO.DPS_USERS UT > WHERE UT.USER_REF_ID=@T_PATHOLOGIST_ID AND UT.USER_TYPE='PATHOLOGIST'" > > In this query, UT.USER_REF_ID sometimes has a comma separated value, for > example '123,456,5657'.This is an exceptional case of the data. But when we > were working on 7.0 there were no exceptions/errors raised on execution of > the view with the comma separated data. But after our upgrade to SQL Server > 2000, the error "Error converting varchar to int is thrown" as essentially, > @T_PATHOLOGIST_ID is an integer field and UT.USER_REF_ID has a comma > separated value. > > We deduced that this is due to the upgrade and that 7.0 suppressed the > error, while 2000 throws the exception.To further strengthen our claim, we > reverted back to 7.0, tested the view and it didnt throw any error for the > same data. We then re-installed SQL 2000 and we found the same error being > throw. > > However i find no evidence either in the SQL 2000 Release notes or bug fix > lists to support our thinking. Is what i am thinking right? Can someone help > me out? > > Thanks, > Poorna Poorna wrote:
> We modified the query as shown below: Actually, this solution is among of the worst solutions, in terms of> [...] T.[USER_REF_ID] LIKE '%,' + STR( T_PATHOLOGIST_ID ) + ',%' ) [...] > Is there a better way to achieve the same result? performance. For better solutions, see this article by Erland Sommarskog, SQL Server MVP: http://www.sommarskog.se/arrays-in-sql.html Razvan  |
|||||||||||||||||||||||
Other interesting topics