Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

What's the syntax for querying a linked server in sql 2005 sp3?

I have setup a linked server to a mysql successfully.  But I need to know what's the
syntax like select, update...  Thanks.
0
lapucca
Asked:
lapucca
2 Solutions
 
käµfm³d 👽Commented:
IIRC, it should be:

    select * from [server name].[database name].[database owner].[table name]
0
 
lapuccaAuthor Commented:
[server name] is the linked server name?  how do I find out what's the database owner name?  Would that be my mysql remote user id?
0
 
derekkrommCommented:
http://msdn.microsoft.com/en-us/library/ms188427(v=sql.90).aspx

select * from openquery(<yourlinkedservername>, 'select * from <yourtables>')
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
BrandonGalderisiCommented:
Correction to kaufmed:

select * from [server name].[database name].[schema name].[table name]


[schema name] not [database owner]
ie. dbo
0
 
käµfm³d 👽Commented:
@BrandonGalderisi

Thanks. It's been years since I've actually done it    = )
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in mysql, there is no "database" name in that 4-part-name syntax.
what mysql names as databases, are translated into schemas.

however, as I only had problems with the 4-part-name syntax for mysql, I exclusively used the openquery method.
0
 
lapuccaAuthor Commented:
Thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now