Exporting Data from MS SQL to MySQL using Linked Server

tommyz
tommyz used Ask the Experts™
on
I have MS SQL Server 7 on server #1 and MySQL 3.23 on server #2.  Both are Win NT platforms.  I setup the Sys DSN on both machines and used the "Linked Servers" wizard, under Security, on MS SQL (server #1) to provide connectivity between [both] database platforms.  Ultimately, I want to schedule regular table updates from server #1 to server #2 for an e-commerce site (to update prices, part numbers, etc.)

Using DTS, I can export database tables from server #1 to server #2, HOWEVER: these tables are not populated with the data from server #1 when they "arrive" at server #2.  The tables only contain the column names and datatypes -- but no data.

I receive various errors from DTS during the export process, including: "..multiple-step ole db opeation generated errors.  check each ole db status value.  no work was done" OR "query-based insertion or updating of blob values is not supported"

Data types are not an issue here and neither is connectivity as I can export tables from server #1 to server #2 and view them on server #2 -- but the tables dont contain any data.

Has anyone seen these errors before or have any idea how to troubleshoot/resolve them?  Any suggesstions are greatly appreciated.  Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hi,
I think this because of MS SQL server has it's own dialect like enclosing the DB owner/DB name everytime generating SQL. This would be like:
  "dbo"."table1" or something like that.

Just try to set MS SQL to generate ANSI SQL compliant on DTS.

Hope this would help you.
Regards, Dewo

Author

Commented:
Dewo,

thanks for your response.  i'll investigate and let you know.

Commented:
Hello,

No comment has been added lately, so it's time to clean up this question.
I will leave a recommendation in the Cleanup topic area that dewo's answer be accepted for this question.

Please leave any comments here within the next four days.

**PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!**

Cheers,

Zontar
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial