June 28, 2012 at 1:24 pm
I've been wracking my brain over this one and it is slowly sending me into a spiral of madness...
I am sending data from a Barcode Scanner input through a System DNS Data Source using the SQL Server Native Client 11.0.
I am executing a custom stored procedure that accepts 4 parameters and inserts a single record into a table.
Depending on the nature of the string data passed as a parameter it will either work fine or fail with the following error (returned through the ODBC connection)
SQL command = EXEC usp_addscandata @station='PDC1AB', @pvi='11PVI000001', @trace1='18PART123456789011', @trace2=NULL
SQL Error:
42000: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'PVI000001'.
42000: [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.
This error occurs if the data for @station, @pvi, @trace1 or @trace2 STARTS with numbers. If the strings are all numbers, all letters or a combination that STARTS with letters all is fine. The error only occurs through the ODBC call. Executing the stored procedure from Management Studio works fine. I have also used the SQL Server Driver and the SQL Server Native Client (from SQL 2005) and these provide the same error. When this existed on a SQL Server 2005 database I didn't encounter the error. Below is the stored procedure.
CREATE PROCEDURE [dbo].[usp_addscandata]
@station nvarchar(6),
@pvi nvarchar(11),
@trace1 nvarchar(50),
@trace2 nvarchar(50)
AS
INSERT INTO PDCTraceData (RecordID, Station, PVI, Trace1, Trace2, ScanDate)
VALUES (NEWID(), @station, @pvi, @trace1, @trace2, GETDATE())
June 28, 2012 at 2:16 pm
Since you indicate that the code works fine when run with the given input in SSMS, I would start looking at the code that is making the call to SQL Server. This does not appear to be a problem on the SQL Server end.
July 6, 2012 at 8:25 am
Well I kind of solved the issue while not realizing it. The SQL Command I posted worked fine. The command I was actually running didn't.
This command runs fine in SQL Server 2012 Express using the SQL Server Native Client 11.0:
EXEC usp_addscandata @station='PDC1AB', @pvi='11PVI000001', @trace1='18PART123456789011', @trace2=NULL
This command doesn't:
EXEC usp_addscandata PDC1AB, 11PVI000001, 18PART123456789011, NULL
Apparently I need to explicitly assign the values to the variables now? The second command used to work fine. One of those things that can drive someone nuts.
Thanks for the assistance anyway!
July 6, 2012 at 8:46 am
No, but you do need to put the strings in quotes.
EXEC usp_addscandata 'PDC1AB', '11PVI000001', '18PART123456789011', NULL
That's not a SQL 2012 thing, it's always been that way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2012 at 9:03 am
I guess this is one of those situations where my poor coding habits caught up to me. The quotes aren't required, but behaviour is not always what you expect if you don't.
These execute fine in SSMS:
EXEC usp_addscandata PDC1AB, PVI000001, PART123456789011, NULL
EXEC usp_addscandata 'PDC1AB', 'PVI000001', 'PART123456789011', NULL
EXEC usp_addscandata PDC1AB, '11PVI000001', '18PART123456789011', NULL
(notice two of my stings start with numbers and the first string value PDC1AB isn't in quotes)
This doesn't execute in SSMS:
EXEC usp_addscandata PDC1AB, 11PVI000001, 18PART123456789011, NULL
I'm guessing SQL is doing some kind of implicit conversion/cast of the data? I find it interesting that it can identify the string when a value starts with letters but if it begins with numbers it is problematic. Regardless. It is a none issue if I use better coding habits... I guess I still have lots to learn.
Thanks for your response! As they say, you learn more through failure than success.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply