Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to update linked server properties

Posted on 2007-03-29
4
Medium Priority
?
1,638 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

618 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