CODEWITHSUNDEEP
sql-servert-sql
Melanie
Melanie

Reputation: 3111

Query working differently on different servers

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?

Upvotes: 0

Views: 74

Answers (1)

Ruslan Tolkachev
Ruslan Tolkachev

Reputation: 644

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

Upvotes: 2

Related Questions