Solved

How can i add linked server using Enterprise Manager ?

Posted on 2010-09-19
19
670 Views
Last Modified: 2013-11-24
Hi;

I have two databases on separate servers and I need to combine the data from the two servers.

I tried adding linked server using Enterprise Manager and I chose other data source
--> Microsoft OLE DB provider for SQL server, product name --> SQL Server, data source --> the IP address of the other server (also tried the server name) , provider string -->SQLOLEDB, Catalog -->the DB I need to retrieve the data from.
In the security tap I wrote the local account and the remote account and both of them have the same credentials same user name and same password, from the connections I chose be made using this security context and I typed the SA user name and password (the SA password of database on the other server I am trying to connect).
I got an error message access is denied.
I don't know what the problem is as this is the first time for me to create linked server.
0
Comment
Question by:bateg
[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
  • 7
  • 5
  • 2
  • +2
19 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 33710727
You should not use the "Other source" for MSSQL. Choose the SQL Server as source instead. That will hide unnecessary fields, and make it easier for you to connect.
Nevertheless, what you have configured sounds correct. The remote SA account should work in "Security Context" section.
0
 
LVL 5

Expert Comment

by:Priya Perumpilavil
ID: 33710814
trythis
EXEC sp_addlinkedserver  'linkedServename',  N'SQL Server'
 EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N''linkedServename'', @locallogin = NULL , @useself = N'False', @rmtuser = N'" & sLinkUser & "', @rmtpassword = N'" & sLinkPWD & "'
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 33710817
PriyaPadman,
If you provide T-SQL scripts, please do so completely. The above is missing (a) the declares of vars and (b) the particular values for this case, as far as known.
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 5

Expert Comment

by:Priya Perumpilavil
ID: 33710840
dear qlemo: sorry 4 ma mistake
details can be seen n this link
http://msdn.microsoft.com/en-us/library/ms190479(SQL.90).aspx
0
 

Author Comment

by:bateg
ID: 33710936
The problem is that when i tried SQL server , only the master database apperead in the tables list , i need to use our database not the default one that's why i used other datasouces so i can choose the DB i want to connect to .
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 33710947
Below link is my tested for MySQL to SQL Migration using Linked Server, You can use it for SQL Server Linking

http://www.codeproject.com/KB/database/migrate-mysql-to-mssql.aspx

0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33710986
when you try to get data via link server.
you have to put database name also

for ex.

SELECT * FROM [ServerName].[Database].[dbo].[tablename]

with SA password entering for security context.
you should able to access almost all objects via Linked Server
0
 

Author Comment

by:bateg
ID: 33711167
I created the data source successfully.
but when i typed your code after changing the parameters.
EXEC master.dbo.sp_addlinkedserver
@server = N'MYSQL', --> i typed the data source name that created
@srvproduct=N'MySQL', --> SQLServer
@provider=N'MSDASQL', -->SQLOLEDB
@provstr=N'DRIVER={SQL Server}; SERVER=the ip address of the server; _
      DATABASE=Mydatabase; USER=sa; PASSWORD=Password; OPTION=3'

I got a message saying one row created successfully. Then when tried to query the results I got another message:-

<<OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'IX_RouteMaster' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error:  OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].>>
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 33711348
And you are certain that the same SQL (without using the linked server) works on the remote server?
0
 

Author Comment

by:bateg
ID: 33714074
Yes, i just tried logging in , it's working , both servers.
When i opened the linked server node i couldn't find any servers created under that list .
I really don't know what is the problem .
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 33714218
That's more than odd, you should see your newly created entry there.

Try to drop it with
sp_dropserver N'YourDatasourceName'
and recreate it via Enterprise Manager with the SQL Server type.
You still will need to fully qualify your table:
select * from server.db.schema.table
and will only see the system tables and views when you use the tree view in Enterprise Manager.

Your changes to the above T-SQL for creating a linked server are wrong. You are using SqlOleDB, but providing an ODBC MSSQL connection string. If you want to go that way, use
@provstr=N'Provider=SqlOleDB; SERVER=the ip address of the server; _        DATABASE=Mydatabase; USER=sa; PASSWORD=Password;'
0
 

Author Comment

by:bateg
ID: 33714801
I created the linked server using your modified T-SQL and it appeared under the linked server node (myDB) the tables and the views but I was not able to run the query result directly so I  tried to run a simple select statement using query analyzer and I got this  strange error message:-

Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'IX_RouteMaster' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error:  OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 33715205
We should check if the SqlOleDB provider options include something unwanted or conflicting.

In Enterprise Manager, start creating a new linked server, choosing the "Microsoft OLE DB Provider for SQL Server". That will enable the "Provider Options" button below the provider name. Press it, and untick anything. Close this dialog by taking the changes over. You can cancel the linked server creation - this action was taken only to be able to set the provider options.

Now try the query again with the same linked server.
0
 

Author Comment

by:bateg
ID: 33723267
Apparently there is a problem with SQL OLEDB provider with the server I am trying to connect, so as soon as we fix the problem
I will try the query again with the linked server I created and I will get back to you.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 35176458
http:#a33714218 answers the original question. There are issues outside the scope of the question which do not allow to use the database link.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 35715049
Objection:
a) The original question has been answered (see http:#35176458)
b) Despite a Cleanup Volunteer's ping 6 weeks ago, the asker only returned to the question after 7 months, when he needed to post a new question. That is inproper handling of questions.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 ?
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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