0

I have a local SQL Server table and a remote linked MySQL table.

I need to pull email addresses from the local table to update the remote.

This T-SQL statement works for a single update:

UPDATE openquery(SKYCOMLINK2, 'select tng_id, tng_account, tng_email from user_list where tng_account = 12345 and tng_status = ''A''') 
SET tng_email = 'blah@blah.com';

What I want to do is, for every RemoteTable record with a status of 'A', I want to pull an email address from a local SQL Server table (example for a single record):

select email 
from LocalTable 
where id = 12345

So in English: for every active record in RemoteTable (could be multiples) look for a corresponding record in LocalTable of the same account number (one record per account number) and pull the email address from it to populate the matching records in RemoteTable. If it makes it easier, the LocalTable can be the driver as below (in quasi SQL-English):

update RemoteTable 
set RemoteTable.email = LocalTable.email 
where RemoteTable.accountNum = LocalTable.accountNum 
  and LocalTable.status = 'a' 
  and RemoteTable.status = 'a'

How can I do that? Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Xi Vix
  • 1,381
  • 6
  • 24
  • 43
  • Do a update with join on 4 part names – paparazzo Jun 26 '15 at 18:28
  • I've seen the 4 part names but could never figure out what the fourth part is. I get as far as SKYCOMLINK2.tng.user_list ... which is linked server.database.table but what is the fourth? – Xi Vix Jun 26 '15 at 18:33
  • It is the owner and by default is is dbo – paparazzo Jun 26 '15 at 18:34
  • where does dbo go in the order? – Xi Vix Jun 26 '15 at 18:39
  • I you right click the table in SSMS and select create select it will show you. – paparazzo Jun 26 '15 at 18:40
  • I right-clicked it and the only option I have close to what you say is SCRIPT TO LINKED SERVER AS and the SELECT TO option is grayed out. Also, I tried this: update SKYCOMLINK2.tng.dbo.user_list set tng_email = 'blah@blah.com' where tng_id = 62873 and I got error: Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "SKYCOMLINK2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema. – Xi Vix Jun 26 '15 at 18:45
  • Did you look up the error message? SO is not a code generation service. – paparazzo Jun 26 '15 at 18:53
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/81678/discussion-between-xi-vix-and-blam). – Xi Vix Jun 26 '15 at 19:54

1 Answers1

0

If MySQL doesn't have four part names and SQL Server won't accept the reference without one, you might try serverName.databaseName..tableName. Leaving the schema name blank implies the default schema. However, per this question, that may not work with all versions of SQL Server, MySQL, and the ODBC driver.

Here is another question that provides multiple ways of solving the problem: Do I have to use OpenQuery to query a MySQL Linked Server from SQL Server?

Community
  • 1
  • 1
SQLEmil
  • 11
  • 4
  • your "someone else found a solution" link doesn't work – Xi Vix Jun 27 '15 at 04:21
  • My apologies - you're right. However, searching for it turned up another Stack Overflow question that has both the OpenQuery syntax as well as another syntax using the default identifiers in four-part names (linkedServer...yourTable). You can find it at http://stackoverflow.com/questions/8187234/do-i-have-to-use-openquery-to-query-a-mysql-linked-server-from-sql-server. – SQLEmil Jun 28 '15 at 16:00
  • That was also a question I asked and it didn't have an answer either. – Xi Vix Jun 28 '15 at 21:05