Home Query working differently on different servers

# Query working differently on different servers

Melanie
1#
Melanie Published in 2017-12-05 21:31:29Z
 I've got a development server where my T-SQL code is working fine, but when I port it to my production server it's breaking. Here's the code: DECLARE @ModelNumber AS int = 67787 ;WITH UsedNumbers (clnum, clname1) AS ( SELECT clnum COLLATE SQL_Latin1_General_CP1_CI_AS AS clnum, clname1 COLLATE SQL_Latin1_General_CP1_CI_AS AS clname1 FROM ELITE.son_db.dbo.client WHERE clnum NOT LIKE '%[A-Z]%' ), OrderedNumbers (clnum, clnum_int, clname1) AS ( SELECT TOP 500000 clnum, CAST(clnum AS int) AS clnum_int, clname1 FROM UsedNumbers WHERE CAST(clnum AS int) >= @ModelNumber ORDER BY clnum ) SELECT TOP 1 REPLACE(STR(previd + 1, 6), ' ', '0') AS previd FROM (SELECT clnum_int, LAG(clnum_int) OVER (ORDER BY clnum) previd FROM OrderedNumbers) q WHERE previd <> clnum_int - 1 ORDER BY clnum_int  When I run this code on the development server, it returns results without a problem. When I run it on the production server, I get this error: Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the varchar value '57235A' to data type int. On both servers, if I replace the last SELECT statement with: SELECT * FROM UsedNumbers WHERE clnum LIKE '57%'  I get no results on the development server or the production server, so I'm totally confused by the error message. Side points: ELITE in the initial SELECT statement refers to linked servers found on both servers. Both linked servers point to the same database, i.e., there are not development and production versions pointed to by the linked servers. 67787 is a seed value that was given to me by the end users; it has no significance to the program other than they want to find the next number after that number. Can anyone suggest what could be going on here?
Ruslan Tolkachev
2#
Ruslan Tolkachev Reply to 2017-12-05 22:17:19Z
 Obviously the error generated here when you are casting OrderedNumbers (clnum, clnum_int, clname1) AS ( SELECT TOP 500000 clnum, CAST(clnum AS int) AS clnum_int, clname1 FROM UsedNumbers WHERE CAST(clnum AS int) >= @ModelNumber ORDER BY clnum )  some how clnum leaks VARCHARs and CAST() to int breaks. Production can execute cte in parallel. Try to use ISNUMERIC ( clnum ) = 1 OrderedNumbers (clnum, clnum_int, clname1) AS ( SELECT TOP 500000 clnum, CAST(clnum AS int) AS clnum_int, clname1 FROM UsedNumbers WHERE ISNUMERIC ( clnum ) = 1 AND CAST(clnum AS int) >= @ModelNumber ORDER BY clnum )  and maybe use ISNUMERIC() in the first cte
 You need to login account before you can post.
Processed in 0.32793 second(s) , Gzip On .