I’m looking for some help in being able to query the data in SuiteCRM’s MySql database via a linked server in MS Sql Server. The reason for the odd request is that once in MSSQL we can then build SSRS reports which plug direct into a SharePoint server.
I have it working but some of the queries return an error in MSSQL and it’s due to the datatypes used in MySQL. Here is an example of the error when I do a query on the Account table:-
SELECT id, name, CONVERT(datetime, date_entered) AS date_entered, CONVERT(datetime, date_modified) AS date_modified, modified_user_id, created_by, description, deleted, assigned_user_id, account_type, industry,
annual_revenue, phone_fax, billing_address_street, billing_address_city, billing_address_state, billing_address_postalcode, billing_address_country, rating, phone_office, phone_alternate, website, ownership,
employees, ticker_symbol, shipping_address_street, shipping_address_city, shipping_address_state, shipping_address_postalcode, shipping_address_country, parent_id, sic_code, campaign_id
FROM OPENQUERY(SUITECRM, ‘SELECT * FROM accounts’) AS Accounts_1
We have to convert the date into an SQL datetime but that all works. I’ve found that this returns the follow error:
Msg 7347, Level 16, State 1, Line 2
OLE DB provider ‘MSDASQL’ for linked server ‘SUITECRM’ returned data that does not match expected data length for column ‘[MSDASQL].parent_id’. The (maximum) expected data length is 72, while the returned data length is 0.
The fault lies in the column types that are ‘char(36)’. If you alter the type to ‘varchar(36)’ the problem goes away and everything works. I obviously don’t want to go and change all the CHAR types to VARCHAR, so there must be a way to handle the error.
Any help would be appreciated.
Thanks,
Ian