insert into mysql from mssql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

insert into mysql from mssql

Author

Topic

boredmind
Starting Member

11 Posts

Posted - 2014-12-01 : 16:33:08

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.

for example these querys works just fine

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

Thank you,

Tasos

thank,
Tasos -{boredm!nd}-

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 09:17:47

Can you run this:


select * FROM [homeputer8].[eshop].[dbo].[oc_product]

from MySQL?

boredmind
Starting Member

11 Posts

Posted - 2014-12-02 : 14:45:16

thank you for the reply,

No I can not run it, because the linked server is the MySQL, the MSSQL is local and the MySQL is on the net

thank,
Tasos -{boredm!nd}-

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 15:10:16

OK -- post the equivalent query that you can run on MySql.

boredmind
Starting Member

11 Posts

Posted - 2014-12-02 : 15:16:58

I can not run a query on MySQL to select data from my local machine that has the MSSQL

The MySQL server is online on a hosting company

thank you,
Tasos -{boredm!nd}-

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 15:19:29

Sorry I read the direction wrong.

Can you run select * FROM [homeputer8].[eshop].[dbo].[oc_product]
on its own from SQL Server?

Also the error message:

Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "SQLTEST".

probably means that the linked server is defined incorrectly. What schema and catalog have you specified in the linked server definition.

boredmind
Starting Member

11 Posts

Posted - 2014-12-02 : 15:41:36

yes I can run it,
the query executed successfully

(4384 row(s) affected)

-----------------------------

also this query do not work :

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
      ,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 FROM [homeputer8].[eshop].[dbo].[oc_product]

thank,
Tasos -{boredm!nd}-

boredmind
Starting Member

11 Posts

Posted - 2014-12-02 : 15:43:07

I dont know what schema or catalog is the linked server, where I can check it?

quote:
Originally posted by gbritton

Sorry I read the direction wrong.

Can you run select * FROM [homeputer8].[eshop].[dbo].[oc_product]
on its own from SQL Server?

Also the error message:

Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "SQLTEST".

probably means that the linked server is defined incorrectly. What schema and catalog have you specified in the linked server definition.

thank,
Tasos -{boredm!nd}-

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 15:51:56

Look at the definition. In SSMS, connect to the server you are linking *from*. Under Server Objects you will find Linked Servers. Find the one for SQLTEST and expand the Catalogs node. Be sure that your "catalog" (Schema in MySql terms IIRC) contains karagian_dokims2. Note that in MySql, schemas and databases are interchangeable concepts. So, you don't need the double dot in your INSERT statement, I believe.

boredmind
Starting Member

11 Posts

Posted - 2014-12-02 : 16:12:11

in the linked servers on MSSQL under the SQLTEST does not show anything, but when I checked it in the phpmyadmin from the hosts cpanel i got this:


+ Options
TABLE_CATALOG 	TABLE_SCHEMA 	TABLE_NAME 	TABLE_TYPE 	ENGINE 	VERSION 	ROW_FORMAT 	TABLE_ROWS 	AVG_ROW_LENGTH 	DATA_LENGTH 	MAX_DATA_LENGTH 	INDEX_LENGTH 	DATA_FREE 	AUTO_INCREMENT 	CREATE_TIME 	UPDATE_TIME 	CHECK_TIME 	TABLE_COLLATION 	CHECKSUM 	CREATE_OPTIONS 	TABLE_COMMENT 	
def 	information_schema 	CHARACTER_SETS 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	384 	0 	16434816 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=43690 	
def 	information_schema 	CLIENT_STATISTICS 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	274 	0 	16417532 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=61230 	
def 	information_schema 	COLLATIONS 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	231 	0 	16704765 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=72628 	
def 	information_schema 	COLLATION_CHARACTER_SET_APPLICABILITY 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	195 	0 	16357770 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=86037 	
def 	information_schema 	COLUMNS 	SYSTEM VIEW 	MyISAM 	10 	Dynamic 	NULL	0 	0 	281474976710655 	1024 	0 	NULL	2014-12-02 16:09:02 	2014-12-02 16:09:02 	NULL	utf8_general_ci 	NULL	max_rows=2802 	
def 	information_schema 	COLUMN_PRIVILEGES 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	2565 	0 	16757145 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=6540 	
def 	information_schema 	INDEX_STATISTICS 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	1739 	0 	16727441 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=9647 	
def 	information_schema 	ENGINES 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	490 	0 	16574250 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=34239 	
def 	information_schema 	EVENTS 	SYSTEM VIEW 	MyISAM 	10 	Dynamic 	NULL	0 	0 	281474976710655 	1024 	0 	NULL	2014-12-02 16:09:02 	2014-12-02 16:09:02 	NULL	utf8_general_ci 	NULL	max_rows=618 	
def 	information_schema 	FILES 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	2677 	0 	16758020 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=6267 	
def 	information_schema 	GLOBAL_STATUS 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	3268 	0 	16755036 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=5133 	
def 	information_schema 	GLOBAL_VARIABLES 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	3268 	0 	16755036 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=5133 	
def 	information_schema 	KEY_COLUMN_USAGE 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	4637 	0 	16762755 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=3618 	
def 	information_schema 	PARAMETERS 	SYSTEM VIEW 	MyISAM 	10 	Dynamic 	NULL	0 	0 	281474976710655 	1024 	0 	NULL	2014-12-02 16:09:02 	2014-12-02 16:09:02 	NULL	utf8_general_ci 	NULL	max_rows=6050 	
def 	information_schema 	PARTITIONS 	SYSTEM VIEW 	MyISAM 	10 	Dynamic 	NULL	0 	0 	281474976710655 	1024 	0 	NULL	2014-12-02 16:09:02 	2014-12-02 16:09:02 	NULL	utf8_general_ci 	NULL	max_rows=5579 	
def 	information_schema 	PLUGINS 	SYSTEM VIEW 	MyISAM 	10 	Dynamic 	NULL	0 	0 	281474976710655 	1024 	0 	NULL	2014-12-02 16:09:02 	2014-12-02 16:09:02 	NULL	utf8_general_ci 	NULL	max_rows=11328 	
def 	information_schema 	PROCESSLIST 	SYSTEM VIEW 	MyISAM 	10 	Dynamic 	NULL	0 	0 	281474976710655 	1024 	0 	NULL	2014-12-02 16:09:02 	2014-12-02 16:09:02 	NULL	utf8_general_ci 	NULL	max_rows=23899 	
def 	information_schema 	PROFILING 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	308 	0 	16562084 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=54471 	
def 	information_schema 	REFERENTIAL_CONSTRAINTS 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	4814 	0 	16767162 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=3485 	
def 	information_schema 	ROUTINES 	SYSTEM VIEW 	MyISAM 	10 	Dynamic 	NULL	0 	0 	281474976710655 	1024 	0 	NULL	2014-12-02 16:09:02 	2014-12-02 16:09:02 	NULL	utf8_general_ci 	NULL	max_rows=583 	
def 	information_schema 	SCHEMATA 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	3464 	0 	16738048 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=4843 	
def 	information_schema 	SCHEMA_PRIVILEGES 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	2179 	0 	16736899 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=7699 	
def 	information_schema 	SESSION_STATUS 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	3268 	0 	16755036 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=5133 	
def 	information_schema 	SESSION_VARIABLES 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	3268 	0 	16755036 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=5133 	
def 	information_schema 	STATISTICS 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	5753 	0 	16752736 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=2916 	
def 	information_schema 	TABLES 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	9450 	0 	16764300 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=1775 	
def 	information_schema 	TABLESPACES 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	6951 	0 	16772763 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=2413 	
def 	information_schema 	TABLE_CONSTRAINTS 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	2504 	0 	16721712 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=6700 	
def 	information_schema 	TABLE_PRIVILEGES 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	2372 	0 	16748692 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=7073 	
def 	information_schema 	TABLE_STATISTICS 	SYSTEM VIEW 	MEMORY 	10 	Fixed 	NULL	1169 	0 	16676954 	0 	0 	NULL	2014-12-02 16:09:02 	NULL	NULL	utf8_general_ci 	NULL	max_rows=14351

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 16:26:35

Sorry I don't know anything about PHP. However, if "linked servers on MSSQL under the SQLTEST does not show anything", then you certainly cannot execute a query against the linked server.

boredmind
Starting Member

11 Posts

Posted - 2014-12-02 : 16:41:32

but how I can run select, update or delete commands?

as you can see in the picture here: http://karagianni.com/images/1234.jpg I can expand successfully the linked server but i do not see anything

PS.I m sorry but i'm a newbie.

thank,
Tasos -{boredm!nd}-

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 17:29:13

Can you post one of your good update queries?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-03 : 08:48:36

I can't test this, but here's a thought. If this works:


delete from openquery(SQLTEST, 'select * from oc_product')
[/code

maybe this will:

[code]
with cte as (select * from openquery(SQLTEST, 'select * from oc_product'))
insert into cte ...

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-03 : 10:07:34

Also try this:


INSERT INTO [SQLTEST]...[oc_product] select * FROM [homeputer8].[eshop].[dbo].[oc_product]

boredmind
Starting Member

11 Posts

Posted - 2014-12-03 : 13:04:26

thank you for the replies my friend,

a)
an update that works is :

update openquery("SQLTEST",'Select * from oc_product')
set quantity = 88 where quantity=88889
-------------------------

b)
with cte as (select * from openquery(SQLTEST, 'select * from oc_product'))
insert into cte ...

result:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.

---------------------------

c)

I also have the same message writting this way the insert into

INSERT INTO [SQLTEST]...[oc_product] select * FROM [homeputer8].[eshop].[dbo].[oc_product]

result

Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "SQLTEST".

thank,
Tasos -{boredm!nd}-

boredmind
Starting Member

11 Posts

Posted - 2014-12-03 : 13:13:28

b) update,

writing this way the cte command

with cte as (select * from openquery(SQLTEST, 'select * from oc_product'))
insert into cte select * FROM [homeputer8].[eshop].[dbo].[oc_product]

the first line inserted but i got this message:

OLE DB provider "MSDASQL" for linked server "SQLTEST" returned message "[MySQL][ODBC 5.3(a) Driver][mysqld-5.5.40-cll]Duplicate entry '142' for key 'PRIMARY'".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "SQLTEST" could not INSERT INTO table "[MSDASQL]".

thank you,
Tasos -{boredm!nd}-

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-03 : 13:16:27

OK -- looks like we fixed the connectivity then. Now you've got a PK violation, which you'll need to sort out, I think

boredmind
Starting Member

11 Posts

Posted - 2014-12-03 : 15:10:38

my friend thank you so much!!!!
you are great!!

this worked !!!

with cte as (select * from openquery(SQLTEST, 'select * from oc_product'))
insert into cte select * FROM [homeputer8].[eshop].[dbo].[oc_product]

thank you,
Tasos -{boredm!nd}-

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-03 : 15:53:43

yw, though I don't understand why

insert into sqltest...oc_product etc.

didn't work.

boredmind
Starting Member

11 Posts

Posted - 2014-12-03 : 16:19:29

I don't know my friend, but really i so happy that this worked

thank you,
Tasos -{boredm!nd}-

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources