การแปลงล้มเหลวเมื่อแปลงค่า nvarchar '01HP011' เป็นประเภทข้อมูล int

ฉันได้รับข้อผิดพลาดในการแปลงบนเซิร์ฟเวอร์เครื่องหนึ่ง แต่ไม่ใช่เซิร์ฟเวอร์อื่น ทั้งสองมีฐานข้อมูลเดียวกัน เซิร์ฟเวอร์หนึ่งคือการทดสอบ และอีกเซิร์ฟเวอร์หนึ่งใช้งานได้จริง การทดสอบนั้นเป็นสำเนาของเซิร์ฟเวอร์จริงแต่เก่าเล็กน้อย เซิร์ฟเวอร์สดเปิดใช้งาน AlwaysOn เมื่อฉันรัน Print @@version บนเซิร์ฟเวอร์ทั้งสอง ฉันได้รับการติดตาม

Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64) 
    Jan  6 2017 14:24:37 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor)

นี่คือแบบสอบถาม sql ที่ฉันใช้อยู่


exec sp_executesql N'select * From DataTable
WHERE DT1 = @P1 AND DT2 = @P2
order by
cp1',N'@P1 smallint,@P2 smallint',0,0

คอลัมน์ DT2 มีค่าผสม 1 และ 1BAC และ NULL แต่ฉันไม่เข้าใจว่าทำไมมันถึงทำงานบนเซิร์ฟเวอร์เครื่องหนึ่ง แต่ไม่ใช่เซิร์ฟเวอร์อื่น


sql string sql-server tsql where-clause
person Declan Junior    schedule 28.09.2020    source แหล่งที่มา
comment
ประเภทของคุณจะต้องตรงกันด้วยเหตุผลหลายประการ ถ้า DT2 ไม่ใช่ Smallint ทำไมคุณถึงใช้ Smallint ในส่วนคำสั่ง WHERE ของคุณ นั่นแค่หัก   -  person pmbAustin    schedule 28.09.2020
comment
ขอบคุณ โค้ดถูกเขียนในแอปและกำลังส่งคำขอเช่นนั้น แต่มันล้มเหลวในการรันเทียบกับการถ่ายทอดสด แต่ใช้งานได้กับการทดสอบ และข้อแตกต่างเพียงอย่างเดียวคือการทดสอบนั้นเก่ากว่า Live เล็กน้อยและนั่งอยู่บนเซิร์ฟเวอร์อื่น 90% ของข้อมูลในตารางนั้น ตรงกัน แล้วทำไมมันถึงใช้งานได้ test ?   -  person Declan Junior    schedule 28.09.2020
comment
เพื่อให้ข้อความชัดเจน แต่ข้อผิดพลาด กำลัง แจ้งให้คุณทราบถึงปัญหาที่นี่ '01HP011' ไม่ใช่ค่า int ที่ถูกต้อง ints ไม่มีตัวอักษรอยู่   -  person Larnu    schedule 28.09.2020
comment
ขอบคุณ Larun ฉันเดาได้ว่ามีข้อผิดพลาดอะไรเกิดขึ้น แต่คำถามของฉันยังคงเหมือนเดิม เหตุใดจึงใช้งานได้กับอันหนึ่งแต่ใช้ไม่ได้กับอีกอัน...   -  person Declan Junior    schedule 28.09.2020
comment
เหตุใดมันจึงทำงานในอันหนึ่งและไม่ใช่อันอื่น อันดับแรก เราต้องถือว่าสิ่งที่คุณอ้างว่าเป็นความจริง - ฐานข้อมูลมีโครงสร้างเหมือนกัน เรารู้ว่าเนื้อหาไม่เหมือนกันซึ่งเป็นปัจจัยหนึ่ง แต่ถึงแม้จะสมมติว่าพวกเขาทำ LUCK ก็คือสาเหตุที่มันใช้งานได้กับอันหนึ่งและไม่ใช่กับอีกอันหนึ่ง รหัสของคุณอาศัยการแปลงโดยนัยที่ไม่ปลอดภัย ในกรณีที่โชคดี แผนการดำเนินการจะหลีกเลี่ยงแถวที่ไม่สามารถแปลงได้ ทำไมมันถึงทำอย่างนั้น? เปรียบเทียบแผนและเปรียบเทียบแถวที่เข้าถึง   -  person SMor    schedule 29.09.2020
comment
และแม้ว่าคุณจะรู้ว่าทำไม แล้วอะไรล่ะ? รหัสแอปพลิเคชันของคุณต้องได้รับการแก้ไข - การหลีกเลี่ยงจุดบกพร่องไม่ใช่กลยุทธ์ที่จะชนะ   -  person SMor    schedule 29.09.2020
comment
ตามกฎสำหรับ ลำดับความสำคัญของประเภทข้อมูล เมื่อคุณรวมประเภทข้อมูล SmallInt และสตริงในนิพจน์ เช่น DT2 = @P2 สตริงจะถูกแปลงเป็น SmallInt คุณสามารถใช้ Cast เพื่อบังคับการแปลงด้วยวิธีอื่น เช่น DT2 = Cast( @P2 as VarChar(10) ).   -  person HABO    schedule 29.09.2020
comment
ขอบคุณเพื่อน ๆ เมื่อฉันพบว่ามันใช้งานได้กับฐานข้อมูลที่มีโครงสร้างเดียวกัน แต่มีข้อมูลน้อยกว่า ฉันคิดว่าอาจเป็นการตั้งค่าระดับฐานข้อมูล/เซิร์ฟเวอร์บางอย่างที่อาจทำให้เกิดปัญหานี้ ฉันเลยหันไปหาพวกคุณ... ขอบคุณอีกครั้ง   -  person Declan Junior    schedule 29.09.2020
comment
เคล็ดลับ: คุณสามารถใช้ Try_Convert เพื่อระบุค่าที่ไม่ดี   -  person HABO    schedule 29.09.2020

คำตอบ (1)


arrow_upward
0
arrow_downward

ดูเหมือนว่า lke dt2 ถูกกำหนดให้เป็นสตริง ไม่ใช่ตัวเลข คุณควรจัดประเภทข้อมูลของพารามิเตอร์ของคุณให้ตรงกับประเภทข้อมูลที่ถูกต้อง - มิฉะนั้น SQL Server จะพยายามบังคับสตริงให้เป็นตัวเลข ซึ่งล้มเหลวในค่าเช่น '1ABC':

พิจารณา:

exec sp_executesql 
    N'select * From DataTable WHERE DT1 = @P1 AND DT2 = @P2 order by cp1',
    N'@P1 smallint, @P2 nvarchar(10)', 0, '0'

คุณอาจต้องการตรวจสอบขนาดของสตริงพารามิเตอร์ และตรวจสอบให้แน่ใจว่าพารามิเตอร์อื่นไม่มีปัญหาประเภทเดียวกัน

เหตุใดโค้ดของคุณจึงล้มเหลวบนเซิร์ฟเวอร์เครื่องหนึ่งแต่ไม่อยู่บนอีกเครื่องหนึ่ง มีแนวโน้มว่าจะเกี่ยวข้องกับข้อมูลของคุณมากที่สุด: ค่าทั้งหมดใน dt2 สามารถแปลงเป็นตัวเลขได้ หรืออาจใช้เพรดิเคต where อีกตัวเพื่อกำจัดค่าที่ไม่ถูกต้องก่อนที่เซิร์ฟเวอร์จะพยายามแปลงค่าเหล่านั้น

นี่คือการสาธิตที่สาธิตสิ่งนี้

ข้อมูลตัวอย่าง:

dt1 | dt2  |  cp1
--: | :--- | ---:
  0 | 1    | null
  0 | 1ABC | null
  0 | null | null

สิ่งนี้ล้มเหลวโดยมีข้อผิดพลาดในการแปลง:

exec sp_executesql 
    N'select * From DataTable WHERE DT1 = @P1 AND DT2 = @P2 order by cp1',
    N'@P1 int, @P2 int', 0, 10

ใช้งานได้ (และส่งคืนชุดผลลัพธ์ที่ว่างเปล่า):

exec sp_executesql 
    N'select * From DataTable WHERE DT1 = @P1 AND DT2 = @P2 order by cp1',
    N'@P1 int, @P2 int', 1, 10
person GMB    schedule 28.09.2020
comment
การทดสอบมีอายุหนึ่งเดือนและได้รับข้อมูลผสม (หมายเลข, สตริง) มีการตั้งค่าระดับฐานข้อมูล / เซิร์ฟเวอร์ซึ่งอาจบังคับให้เซิร์ฟเวอร์ sql ทำงานแตกต่างออกไปหรือไม่ นอกจากนี้ Where เงื่อนไขจะเหมือนกันบนเซิร์ฟเวอร์ทั้งสอง ไม่แน่ใจว่าทำไมโค้ดของแอปถึงเขียนแบบนี้ แต่ทั้งหมดที่ฉันพยายามค้นหาว่าทำไมมันถึงใช้งานได้ โดยที่เงื่อนไขจะเหมือนกันเสมอด้วยค่าเดียวกัน 0 , 0 และ 1,000 แถวบนสุดในตารางนั้นจะเหมือนกัน วิธีเขียนโค้ดมันจะส่งคืน 0 แถวเสมอ - person Declan Junior; 28.09.2020