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 2005 Forums
 Transact-SQL (2005)
 Linked Server query Error from MySql to MS sql 200

Author  Topic 

poratips
Posting Yak Master

105 Posts

Posted - 2013-06-18 : 14:34:28
I created Linked server in Sql server 2005 and tried Data Migration from MySql to MS Sql 2005 but some of the data got rejetced and i need to find out that which data got rejected?
I was trying to use following query but looks like i am having correct syntex issue:

select * from OPENQUERY (MyLinkedServer, 'select * from CInfo
where ID NOt IN (select ID from Cinfo)')A

ID is my PK column, i have both the table name is same both the DB - MySql and MS Sql.
Only DB Name is different like - MySQLCInfoDB and in MS Sql - InfoSqlDB.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 01:12:49
try the below first in MySQL query window and see whether its working fine

select * from CInfo
where ID NOt IN (select ID from Cinfo)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-19 : 02:06:36
-- I think that should be as follows:
select *
from MySQLCInfoDB..CInfo -- MySQL DB Table Name
where ID NOt IN (select ID from InfoSqlDB..Cinfo) -- MSSQL DB Table Name



--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 02:17:00
in that case it should be this i guess

select * from OPENQUERY (MyLinkedServer, 'select * from CInfo')A
where ID NOT IN (select ID from InfoSqlDB..Cinfo)


as linked server query cant contain reference to sql server table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

poratips
Posting Yak Master

105 Posts

Posted - 2013-07-05 : 10:12:54
Thanks both of you, i will try and see.
Go to Top of Page
   

- Advertisement -