SQL Server docking MySQL database

1. Install the MySQL ODBC driver on the SQL Server server;

Drive: http://dev.mysql.com/downloads/connector/odbc/

2. After installation, in the management tool-ODBC data Source-System dsn-Add-Select MYSQL ODBC 5.3 Unicode driver-complete

Will jump out MySQL connection parameters

Data Source Name: DataSource names

Decription: Description, write casually

IP of the Server:mysql server

Port:mysql port, the default is 3306. can be changed.

User: Connect the account (the account authorized on MySQL, and give the appropriate permission)

Password: Password

Database: Select the linked databases

After filling it out, click Test. If it succeeds, it will jump out of the prompt box.

3. Open the SQL database and locate the linked server. Create a linked server, everyone will, so I'll just send the parameters. The rest of the general options are not required. OK, that's it.

Here, it is important to note that the provider chooses "Microsoft OLE DB Provider for ODBC Drivers".

Data Source: Select an ODBC interface that was previously created.

Then select Use this security context in security to create a connection fill in the MySQL database login name and password

4. SQL statements Use

Copy Code
Copy Code
– Query
SELECT * from OPENQUERY (mysql_dblink, ' select * from TableName where id= "1")

– Modify
UPDATE OPENQUERY (Mysql_dblink, ' select * from TableName where id= ' 1 "') set cname= ' test '
–or
UPDATE OPENQUERY (Mysql_dblink, ' select * from TableName ') set cname= ' test ' where id=1

– add
INSERT into OPENQUERY (Mysql_dblink, ' select * from TableName where 1=0 ') VALUES (' xx ', ' xx ', ' xx ');

– Delete
DELETE from OPENQUERY (Mysql_dblink, ' select * from TableName where id= "1")

Copy Code
Copy Code
* * "Note" When queried: the OLE DB provider ' Msdasql ' of the linked server ' Mysql_dblink ' returns data that is not valid for column ' [Msdasql].cname '.

You need to set up your encoding format on the MySQL ODBC data source,

5. OpenQuery usage
Executes the specified pass-through query on the given linked server. The server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table for an INSERT, UPDATE, or DELETE statement. However, this depends on the functionality of the OLE DB access interface. Although the query may return more than one result set, OPENQUERY returns only the first one.

Transact-SQL Syntax conventions

Grammar

OPENQUERY (linked_server, ' query ')
Parameters

Linked_server
Represents the identifier for the linked server name.

' Query '
The query string executed in the linked server. The maximum length of the string is 8 KB.

Note

OPENQUERY A variable that does not accept its arguments.

In SQL Server 2000 and later versions, OPENQUERY cannot be used to perform extended stored procedures on linked servers. However, you can execute an extended stored procedure on a linked server by using the four-part name. For example:

EXEC SeattleSales.master.dbo.xp_msver

Permissions

Any user can execute OPENQUERY. The permissions used to connect to the remote server are obtained from the settings defined for the linked server.

SQL Server docking MySQL database