insert into mysql from mssql

Question

Post reply

insert into mysql from mssql

boredmind

SSC Veteran

Points: 201
More actions
December 1, 2014 at 2:23 pm

#295516

Hello, I have 2 tables on 2 databases. The 1st is on a MSSQL server and the second is on a Linked Server MySQL database server.
I want to insert all table data from MSSQL to the Mysql linked server
i am using this query
INSERT INTO [SQLTEST].[karagian_dokims2]..[oc_product] select * FROM [homeputer8].[eshop].[dbo].[oc_product]
and I get the following result:
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "SQLTEST".
I am sorry if I do not phrase it exactly but i am a newbie.
the select, update or delete openquery works fine. only the insert does not.
Thank you,
Tasos

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

g.britton SSChampion Points: 13863 More actions · Answer 1

if you run the select part of your query on the mysql instance, does it work?

Gerald Britton, Pluralsight courses

boredmind SSC Veteran Points: 201 More actions · Answer 2

yes it works,

for example these query s works

delete from openquery(SQLTEST, 'select * from oc_product')

or this

select * from openquery(SQLTEST, 'select * from oc_product')

are working just fine

Lowell SSC Guru Points: 323547 More actions · Answer 3

could it be related to implied/deferred schema? SQL lets you be lazy with not explicitly identifying columns, but it gets a bit tighter when you work with other linked server types:

if you explicitly identify the columns, does it work?

ie:

INSERT INTO [SQLTEST].[karagian_dokims2]..[oc_product](Column1,Column2,Column3)

select SomeColumn1,SomeColumn2,SomeColumn3 FROM [homeputer8].[eshop].[dbo].[oc_product]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

boredmind SSC Veteran Points: 201 More actions · Answer 4

thank you for the reply Lowell

no it does not work also.

Lowell (12/1/2014)
could it be related to implied/deferred schema? SQL lets you be lazy with not explicitly identifying columns, but it gets a bit tighter when you work with other linked server types:
if you explicitly identify the columns, does it work?
ie:
INSERT INTO [SQLTEST].[karagian_dokims2]..[oc_product](Column1,Column2,Column3)
select SomeColumn1,SomeColumn2,SomeColumn3 FROM [homeputer8].[eshop].[dbo].[oc_product]

the code in this that it does not work also is :

INSERT INTO [SQLTEST].[karagian_dokims2]..[oc_product](product_id

,model

,sku

,upc

,ean

,jan

,isbn

,mpn

,location

,quantity

,stock_status_id

,image

,manufacturer_id

,shipping

,price

,points

,tax_class_id

,date_available

,weight

,weight_class_id

,length

,width

,height

,length_class_id

,subtract

,minimum

,sort_order

,status

,date_added

,date_modified

,viewed)

select product_id