Description:
when I used Mysql ODBC 5.01.05 driver for SQL Server 2005(SP2 and SP3) linked server to a Mysql database (version 5.0.45-log)on Linux, everything worked fine. But after using Mysql connector ODBC 3.1.12 driver, there is a data error for datatime data type when pushing data from Sql Server database to Mysql using Sql Server openquery function. A datetime value becomes '0000-00-00 00:00:00' in mysql database. When I re-install Mysql ODBC 5.01.05 driver, the data is correct again.
This also happens on my Sql Server 2008(SP3) (with Mysql ODBC 5.2a driver) linked server to mysql version 5.0.77.
I am not able to get a ODBC trace file. But you should be able to repeat.
How to repeat:
-- in Mysql schema XX, create a table:
use XX;
create table testing_datetime_ODBC ( a int, b datetime, c varchar(50) ) ;
-- in SQL Server 2005 (SP3)
-- create a linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'linked_XX', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'Driver={MySQL ODBC 5.1 Driver};DB=XX;SERVER=xxxxx.yyyyy.com;uid=xxx;pwd=yyy; Option=1048576'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'linked_XX',@useself=N'False',@locallogin=NULL,@rmtuser=N'xxx',@rmtpassword='yyy'
GO
EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'collation name', @optvalue=N'SQL_Latin1_General_CP1_CI_AI'
GO
EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'use remote collation', @optvalue=N'true'
-- to test, run the following in SQL Server:
declare @now datetime
declare @now_char varchar(50)
set @now=getdate()
set @now_char=convert(varchar,@now , 120)
insert into openquery(linked_CAMEO,'select * from testing_datetime_ODBC where 1=2') select 202,@now ,@now_char;
insert into openquery(linked_CAMEO,'select * from testing_datetime_ODBC where 1=2') select 203,@now_char ,@now_char ;
-- in Mysql, run the following to see the result:
select * from testing_datetime_ODBC ;
you got
+------+---------------------+---------------------+
| a | b | c |
+------+---------------------+---------------------+
| 202 | 0000-00-00 00:00:00 | 2013-04-25 15:05:48 |
| 203 | 2013-04-25 15:05:48 | 2013-04-25 15:05:48 |
+------+---------------------+---------------------+
testing_datetime_ODBC.b column is a datetime type. when passing in a datetime in Sql Server, it is always convert the value to "0000-00-00 00:00:00". if using a varchar value to cast implicitly, it inserts the correct value in mysql(the 2nd record).
Description: when I used Mysql ODBC 5.01.05 driver for SQL Server 2005(SP2 and SP3) linked server to a Mysql database (version 5.0.45-log)on Linux, everything worked fine. But after using Mysql connector ODBC 3.1.12 driver, there is a data error for datatime data type when pushing data from Sql Server database to Mysql using Sql Server openquery function. A datetime value becomes '0000-00-00 00:00:00' in mysql database. When I re-install Mysql ODBC 5.01.05 driver, the data is correct again. This also happens on my Sql Server 2008(SP3) (with Mysql ODBC 5.2a driver) linked server to mysql version 5.0.77. I am not able to get a ODBC trace file. But you should be able to repeat. How to repeat: -- in Mysql schema XX, create a table: use XX; create table testing_datetime_ODBC ( a int, b datetime, c varchar(50) ) ; -- in SQL Server 2005 (SP3) -- create a linked server: EXEC master.dbo.sp_addlinkedserver @server = N'linked_XX', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'Driver={MySQL ODBC 5.1 Driver};DB=XX;SERVER=xxxxx.yyyyy.com;uid=xxx;pwd=yyy; Option=1048576' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'linked_XX',@useself=N'False',@locallogin=NULL,@rmtuser=N'xxx',@rmtpassword='yyy' GO EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'collation name', @optvalue=N'SQL_Latin1_General_CP1_CI_AI' GO EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'linked_XX', @optname=N'use remote collation', @optvalue=N'true' -- to test, run the following in SQL Server: declare @now datetime declare @now_char varchar(50) set @now=getdate() set @now_char=convert(varchar,@now , 120) insert into openquery(linked_CAMEO,'select * from testing_datetime_ODBC where 1=2') select 202,@now ,@now_char; insert into openquery(linked_CAMEO,'select * from testing_datetime_ODBC where 1=2') select 203,@now_char ,@now_char ; -- in Mysql, run the following to see the result: select * from testing_datetime_ODBC ; you got +------+---------------------+---------------------+ | a | b | c | +------+---------------------+---------------------+ | 202 | 0000-00-00 00:00:00 | 2013-04-25 15:05:48 | | 203 | 2013-04-25 15:05:48 | 2013-04-25 15:05:48 | +------+---------------------+---------------------+ testing_datetime_ODBC.b column is a datetime type. when passing in a datetime in Sql Server, it is always convert the value to "0000-00-00 00:00:00". if using a varchar value to cast implicitly, it inserts the correct value in mysql(the 2nd record).