?
Solved

How do I link 2 sql 2005 servers?

Posted on 2009-04-30
17
Medium Priority
?
280 Views
Last Modified: 2012-05-06
Both are in my companies domain.  One is live the other is dev.  I want to run the code below (I know it's wrong) .  I want to do a 'select * into' a new db on a different server.
non working code...
 
select * into dmsqldev1.marantzServices.dbo.sessionData
from dmsqlcluster1\sqlinstance1.denonServices.dbo.sessionData

Open in new window

0
Comment
Question by:jweissdandm
  • 8
  • 7
  • 2
17 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24273338
select * into dmsqldev1.marantzServices.dbo.sessionData
from [dmsqlcluster1\sqlinstance1].denonServices.dbo.sessionData
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24273342
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24273345
select * into dbo.sessionData
from [dmsqlcluster1\sqlinstance1].denonServices.dbo.sessionData
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Expert Comment

by:chapmandew
ID: 24273348
you also have to make sure you have your distributed transaction coordinator service on with both servers.
0
 
LVL 2

Author Comment

by:jweissdandm
ID: 24273374
I hate the msdn articles.  From...

When you are setting up a linked server, register the connection information and data source information with SQL Server.

Can anyone help out with some more specific steps?
Thanks
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24273476
0
 
LVL 2

Author Comment

by:jweissdandm
ID: 24273763
chapmandew...  I do not have this available in Enterprise manager

+ Open the SQL Enterprise Manager and navigate to Security/Linked Servers. Underneath, (No items) should be visible.

+ Right click the Linked Server icon and select New Linked Server.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24273789
are you an admin?  are you using msde instead of the full version of sql server?
0
 
LVL 2

Author Comment

by:jweissdandm
ID: 24273822
It's the full version & I am logged in via windows authentication as an admin


sql.gif
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24273837
try management studio, rather than enterprise manager.  right clic on the db, and show me what you see.
0
 
LVL 2

Author Comment

by:jweissdandm
ID: 24273862
Here is a right click
sql-right-clisk.gif
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24273916
im sorry....go up a folder so you're at the server level the explorer. then go to Server Objects, and then Linked Servers.  You can set them up from there.
0
 
LVL 2

Author Comment

by:jweissdandm
ID: 24274003
can you show me a screen grab?
Thanks
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 1220 total points
ID: 24274051
attached.
snip.JPG
0
 
LVL 2

Author Comment

by:jweissdandm
ID: 24274195
there it is...
Thanks

Now I'll follow your link from databasejournal.com

If I stumble, I'll get back to you.
Thanks
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24274200
ok
0
 
LVL 2

Author Closing Comment

by:jweissdandm
ID: 31576607
thanks for going the extra effort
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

864 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