[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL 2005 connectiion link

Posted on 2007-11-16
3
Medium Priority
?
146 Views
Last Modified: 2012-05-05
i am using sql server 2005. i have another database which is on the other network. and i have odbc connection settings if i want to link to that database in my sql how can i link that database and start writing queries.
0
Comment
Question by:romeiovasu
[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
  • 2
3 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20303815
this can get you started: (let's say the servers are svr1 and svr2)
on svr1
sp_addlinkedserver 'svr2'
then to run a query
    select top 10 * from svr2.database.dbo.tablename
you can even join to local tables, like
    select top 10 * from svr2.database.dbo.tablename inner join dbo.localtable on ...

check books online for sp_addlinkedserver. the example above will use the current login (to svr1) to authenticate with svr2. so if you're on a domain and using windows authentication, shouldn't be a problem
0
 

Author Comment

by:romeiovasu
ID: 20304462
i am getting this error The OLE DB provider "MSDASQL" has not been registered.
0
 
LVL 25

Accepted Solution

by:
imitchie earned 2000 total points
ID: 20324942
http://msdn2.microsoft.com/en-us/library/aa259589(SQL.80).aspx

does the other server have an instance name? the following adds it to be referred locally as myserver, but is actually connecting to server\instance1

EXEC sp_addlinkedserver  
   @server='myserver',
   @srvproduct='',
   @provider='SQLNCLI',
   @datasrc='server\instance1'

usage:
 select top 10 * from myserver.database.dbo.table
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

649 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