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: , ,

Leave a Reply