Solved

How to update linked server properties

Posted on 2007-03-29
4
1,582 Views
Last Modified: 2010-05-18
I have a linked MySQL Server it's IP Address has recently changed and I need to update this. How do I update the Linked Server Properties Provider string?

SQL Server 2005
0
Comment
Question by:brooksmato
4 Comments
 
LVL 10

Expert Comment

by:TAB8
ID: 18821139
IN Enterprise manager on SQL , under Security , linked servers and changed the properties of the linked server you are dealing with ...
0
 

Author Comment

by:brooksmato
ID: 18821170
There is no Enterprise manager. I have SQL Server Management Studio. Can I make the changes in there?
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 18822119
Here is the full she-bang (from sclservercentral.com):
should give you the info you need to make your amendments.

Creating a Linked Server in SSMS for a MySQL database

1. Download the MySQL ODBC driver from mysql.com
2. Install MySQL ODBC driver on Server where SQL Server resides
-Double Click Windows Installer file and follow directions.

3. Create a DSN using the MySQL ODBC driver
Start-> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC)
-Click on the System DSN tab
-Click Add
-Select the MySQL ODBC Driver
-Click Finish
On the Login Tab:
-Type a descriptive name for your DSN.
-Type the server name or IP Address into the Server text box.
-Type the username needed to connect to the MySQL database into the user text box.
-Type the password needed to connect to the MySQL database into the password text box.
-Select the database you'd like to start in.
On the Advance Tab:
Under Flags 1:
-Check Don't Optimize column width.
-Check Return Matching Rows
-Check Allow Big Results
-Check Use Compressed protocol
-Check BIGINT columns to INT
-Check Safe
Under Flags 2:
-Check Don't Prompt Upon Connect
-Check Ignore # in Table Name
Under Flags 3:
-Check Return Table Names for SQLDescribeCol
-Check Disable Transactions
Now Test your DSN by Clicking the Test button


4. Create a Linked Server in SSMS for the MySQL database
SSMS (SQL Server Management Studio -> Expand Server Objects
-Right Click Linked Servers -> Select New Linked Server
On the General Page:
-Linked Server: Type the Name for your Linked Server
-Server Type: Select Other Data Source
-Provider: Select Microsoft OLE DB Provider for ODBC Drivers
-Product name: Type MySQLDatabase
-Data Source: Type the name of the DSN you created
On The Security Page
-Map a login to the Remote User and provide the Remote Users Password
-Click Add under Local server login to remote server login mappings:
-Select a Local Login From the drop down box
-Type the name of the Remote User
-Type the password for the Remote User

5. Change the Properties of the Provider MSDASQL
Expand Providers -> Right Click MSDASQL -> Select Properties
-Enable Nested queries
-Enable Level zero only (this one's the kicker)
-Enable Allow inprocess
-Enable Supports 'Like' operator

6. Change settings in SQL Server Surface Area Configuration for Features
-Enable OPENROWSET and OPENDATASOURCE support.
7. Change settings in SQL Server Surface Area Configuration for Services and Connections
-Enable Local and Remote connections via TCP/IP and named pipes

8. Stop SQL Server and SQL Server Agent
9. Start SQL Server and SQL Server Agent
0
 
LVL 5

Accepted Solution

by:
MrNetic earned 500 total points
ID: 18822396
brooksmato,

It seems that you are using MSSQLSERVER2005.

In SSMS, you just to have to right click the object ( linked server ) > Script linked server as > create to > new query window editor.

Afterwards just edit the created script with the new server IP ADDRESS.

Hope it helps.

Best Regards,

Paulo Condeça.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now