ODBC Connection problem with SQL Server native client

[visionpro]

Hi All,
ODBC connection for SQL Server (SQLSRV32.ALL) works well on all SQL Server versions ( From SQLServer 7.0 to SQLServer 2016)

After changing the ODBC to SqlServer native Client (SQLNCI10.DLL) , my application running on Team developer 2 & Team developer 5 does not work.

Error Message [Microsoft][SQL Server Native Client 11.0]Invalid character value for cast specification

connection problem.png

It affects fields DATE/TIME. example: UPDATE ACCESS.dbo.SYSUSR SET LSTLOGIN= :dlbpwd.dtDate WHERE USRCOD = :p_sUsrcod
(dlbpwd.dtDate) is defined as DATE/TIME.

Can I have a solution for this.

Thanks

Samad

[thomas.uttendorfer]

Hi,
we also had several problems using native client conerning locking behaviour (but not those you wrote about Date/Time),
so we use SqlServer ODBC-Driver.
Kind regards
Thomas

[visionpro]

Hi
The problem is still same with ODBC driver 11 for SqlServer(msodbcsql11.dll).

An example:
SELECT F1.DELNUM, F1.STKCOD, F1.STKDEL, F1.DELCOD
FROM MF_DELDET F1
WHERE F1.DELDATE >= :f_Date AND
F1.DELDATE <= :t_Date

Fields f_Date & t_Date are Data fields input on screen with format dd/MM/yyyy.


I have same problem: [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification

Please help me to solve it.

example-odbc.png

[FRBhote]

If the field is declared as DATE, try changing it to DATETIME?

[visionpro]

Hi

I check the table in SQL SERVER 2012 ( the field name is datetime)

But when I change the ODBC Setup from ODBC SqlServer 11 (MSODBCSQL11.DLL) to SQLSRV32.dll it works fine.
My application with SQLSRV32.dll has been running smoothly for more than 10 years on all WINDOWS (XP,7,10) and all SQLSERVER versions (2000-2014)

I now upgraded a client database from SQLSERVER 2000 TO SQLSERVER 2012. The ODBC SQL Server MSODBCSQL11.DLL fails.

Is there a specific configuration I need to do in SQL.INI ?


[odbcrtr]
buffrow=256000
longbuffer=1000000
remotedbname=WORLDS,dsn=worldsdsn

In ODBC Setup I created a dsn worldsdsn?

Thanks for your valuable help

[mergs]

I solved this by creating a DSN that uses the SQL Server driver SQLSRV32.DLL (see image below). Is that an option or are you forced to use the newer drivers?