October 26, 2015 at 9:34 am
Hello all,
We've recently moved one of or databases from a 2005 server to a 2012 server, with no issues.
However, even more recently I decided to update the ODBC connection from 'SQL Server' (6.01.7601.17514) to 'SQL Server Native Client 11.0' (2011.110.2100.60), and this did cause an error.
The error in question is: [SQL Server Native Client 11.0]Invalid character value for cast specification (SQL-22018)
Switching back and forth through these drivers confirmed that the same data that caused the error with the new driver worked fine with the old driver (both connecting to the 2012 database)
Does anyone here have any idea what causes this behavior?
Kind regards
October 26, 2015 at 9:35 am
do you have the query that causes the error?
Gerald Britton, Pluralsight courses
October 26, 2015 at 9:42 am
SQL Server Native Client 11.0 IS based on OLEDB technology not ODBC.
Make sure that your Query complies to OLEDB rules.
October 26, 2015 at 9:46 am
Hi,
I'd hoped to skate by on this, as neither the DDL or the DML change, but here you go.
The data included is the data that causes me my headache.
DML:
INSERT
INTO Requests (
Demo,
Email,
Id,
InitiatorCustomerCode,
InitiatorProfileType,
IsoCode,
Operation,
OperationCustomerCode,
OperationInputAsXml,
OperationProfileType,
RequestDate,
RequestLanguage,
SendConfirmation,
Username,
handled
) VALUES (
'false',
'someone@company.com',
'2969',
'0022003212',
'1',
'707683',
'BLOCK_CARD',
'0060005580',
'<INPUT><CARD_SER_NUM>219</CARD_SER_NUM><EMIS_NUM>3</EMIS_NUM><OLD_BL_STATUS>K</OLD_BL_STATUS><NEW_BL_STATUS>N</NEW_BL_STATUS><BL_REASON_DESCR>test</BL_REASON_DESCR></INPUT>',
'2',
CONVERT(DATETIME,'2015-06-05T09:29:43',126),
'EN',
'true',
'someone@company.com',
'N'
)
DDL:
CREATE TABLE [dbo].[Requests](
[Id] [bigint] NOT NULL,
[Username] [varchar](255) NULL,
[IsoCode] [varchar](6) NULL,
[InitiatorCustomerCode] [char](10) NULL,
[OperationCustomerCode] [char](10) NULL,
[InitiatorProfileType] [char](1) NULL,
[OperationProfileType] [char](1) NULL,
[RequestLanguage] [char](2) NULL,
[RequestDate] [datetime] NULL,
[SendConfirmation] [bit] NULL,
[Operation] [varchar](256) NULL,
[OperationInputAsXml] [xml] NULL,
[Demo] [bit] NULL,
[Email] [varchar](256) NULL,
[handled] [char](1) NULL,
CONSTRAINT [PK_IDS10_Requests] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
October 26, 2015 at 9:56 am
Can you narrow down the problem to which column? E.g. do them one at a time until the error triggers?
Gerald Britton, Pluralsight courses
October 26, 2015 at 10:07 am
Hey Gerald,
I wish it was that easy.
Part of my problem is that the query is succesful in SSMS, I can only get the error values by debugging a run execution, and the surrounding code checks that the fields are filled or the query doesn't get run...
I'd hoped that one of you had run into this before, I guess I'm just going to have to rewrite the code to do as you suggest, attempt to fill the (non-PK) fields one at a time.
Kind regards,
October 26, 2015 at 12:27 pm
I can see that you are inserting a value for the id field as a character string. At the very least, that forces an unnecessary conversion. Try unquoting the id and see if you get further
Gerald Britton, Pluralsight courses
October 26, 2015 at 3:34 pm
I suspect the problem is to do with the datetime column RequestDate. About 3 years ago we had some problems moving a 1990's third party program, designed to work with both Oracle and SQL Server, from SQL2005 to SQL2008R2. Everything worked with the SQL Server driver or the Native Client for SQL2005. When the Native Client 10.0 was used temporary tables got created with date, instead of datetime, so failed to join back to the main tables. This sort of made sense as the date datatype became available in SQL2008. We assumed the program used ODBC date which orignally mapped to date in Oracle and datetime in SQL Server.
If the application was designed to use the SQL Server driver, I would continue to use it so that dates get translated as datetime. You could also experiment with changing all you datetimes to datetime2s and see if the Native Client 11.0 can cope.
October 27, 2015 at 6:07 am
Looking at this again, you could also try changing:
CONVERT(DATETIME,'2015-06-05T09:29:43',126)
to
'20150605 09:29:43'
October 27, 2015 at 9:33 am
Gerald and Ken,
First of all, thanks for your suggestions. 🙂
Gerald,
Changing the ID to an unquoted string made no difference.
Ken,
Changing the datetime string to the format you specified and removing the explicit conversion did not help.
I'm not sure I understand your first suggestion (changing to datetime2 type) correctly though.
Do you mean for me to change the explicit conversion in the DML, the datatype in the DDL, or both?
If you mean just the DML, this unfortunately didn't work either.
Kind regards
October 27, 2015 at 9:51 am
I suspect the XML. See if removing that column from the insert succeeds. if that's it, convert it to nvarchar(max), and then back to XML.
You can do the same with the rest of the character fields.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 9, 2015 at 10:18 am
Hi all,
Just a quick not to maybe help the next chump wrestling with this. 🙂
The problem appears to be in both the date (thx Ken) and the bit datatypes.
Unfortunately, both result in the same errormessage, so trying one or the other doesn't appear to change anything.
Ultimately, to get my SQL to work, I needed to remove the 'T' from the datetime2 input, and switching my bit-fields to 1/0 instead of true/false.
I'm not convinced this issue is in the ODBC, it may also be in the perl DBI module that's installed here.
Anyway, thanks for the support.
Kind regards,
Martin
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply