?
Solved

How to update linked server properties

Posted on 2007-03-29
4
Medium Priority
?
1,627 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1500 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

771 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