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
clnum COLLATE SQL_Latin1_General_CP1_CI_AS AS clnum,
clname1 COLLATE SQL_Latin1_General_CP1_CI_AS AS clname1
clnum NOT LIKE '%[A-Z]%'
OrderedNumbers (clnum, clnum_int, clname1) AS
SELECT TOP 500000
clnum, CAST(clnum AS int) AS clnum_int, clname1
CAST(clnum AS int) >= @ModelNumber
SELECT TOP 1
REPLACE(STR(previd + 1, 6), ' ', '0') AS previd
clnum_int, LAG(clnum_int) OVER (ORDER BY clnum) previd
previd <> clnum_int - 1
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:
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?