Using MySql as Linked Server in Sql Server 2008
Using MySql as Linked Server in Sql Server 2008
Recently I was given an assignment to create some Reports using SSRS.
They system were in use was osTicket (its an open source php, mysql) ticket management system.
I decided to use SQL server linked server object. By doing this I can easily query and write stored procedures
in sql server which I can then access into SSRS.
I found by googling on internet that if you do not use mysql database as linked server then you will not be able to pass parameters to the SSRS report. So I just beleive it and made mysql as linked server.
First I created an ODBC connection to mysql server (I installed ODBC connector from mysql.com)
Then I executed following stored proc to make a linked server in Sql server 2008
–Create linked server
EXEC sp_addlinkedserver ‘mysqlDB’, ‘MySQL’, ‘MSDASQL’, Null, Null, ‘Driver={MySQL ODBC 5.1 Driver};DB=osticket;SERVER=localhost;uid=root;pwd=password’
GO
–Get all tables from the linked server
EXEC sp_tables_ex ‘mysqlDB’
GO
When I queried the mysql linked database
select ticket_id, email,[subject], [source] from osTicket…ost_ticket
it threw following error
Msg 7347, Level 16, State 1, Line 1
OLE DB provider ‘MSDASQL’ for linked server ‘osTicket’ returned data that does not match expected data length for column ‘[osTicket]…[ost_ticket].source’. The (maximum) expected data length is 10, while the returned data length is 6.
[Source] is an Enum data type in MySql Server osticket databse which Sql server was unable to understand.
I started googling the solution and afetr spending sometime i found somewhere that if I use DBCC TRACEON(8765) then
I can query my databse I was a happy bunny and I used
DBCC TRACEON(8765)
select ticket_id, email,[subject], [source] from osTicket…ost_ticket
It worked gerat… But DBCC TRACEON(8765) is an extra statement I personally think I must turn it off too so finally I decided to use
DBCC TRACEON(8765)
select ticket_id, email,[subject], [source] from osTicket…ost_ticket
DBCC TRACEOFF(8765)
First DBCC TRACEON(8765) turn on then query and then turn it off DBCC TRACEOFF(8765)
I used above to get data but then there were issue with date being null so I used IFNULL() function and it resloved the issue
Also you can use IFNULL(Updated,”nodate”)
Tags: returned data that does not match expected data length for column ', The (maximum) expected data, Using MySql as Linked Server in Sql Server 2008
