Solved

sp_addlinkedserver problem

Posted on 2006-06-16
10
455 Views
Last Modified: 2006-11-18
I want to copy data from another server to local server. I use sp fopr thois purpose. When I put following code outside of sp, everything works fine. When I put the same code at the beginning of sp - it gives me an error "Could not find server 'ActualSrv' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."

EXEC sp_addlinkedserver @Server='ActualSrv',
                      @srvproduct='SQLServer OLEDB Provider',
                  @provider='SQLOLEDB',
                  @datasrc='SQL_SERV',
                  @catalog='test'

EXEC sp_addlinkedsrvlogin @rmtsrvname='ActualSrv', @useself=false,--@locallogin=current_user,
                  @rmtuser='test,
                  @rmtpassword='test00'

Any ideas what the problem might be ? Thank you.
0
Comment
Question by:nataliyamaks
  • 6
  • 2
10 Comments
 
LVL 20

Expert Comment

by:Sirees
ID: 16922930
Try this in QA

select * from Master..Sysservers

see if 'ActualSrv' exists.

0
 
LVL 20

Expert Comment

by:Sirees
ID: 16922939
Are you linking two SQL Servers?

If so, @srvproduct should be 'SQL Server'

0
 
LVL 20

Expert Comment

by:Sirees
ID: 16922954
0
 

Author Comment

by:nataliyamaks
ID: 16923260
@srvproduct  doesn't really matter. It works both ways with no difference. The problem is that the same code works outside of sp and doesn't work in the sp.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 20

Expert Comment

by:Sirees
ID: 16923299
Are you referring to Master db in your SPs?
0
 

Author Comment

by:nataliyamaks
ID: 16923373
no
0
 
LVL 20

Accepted Solution

by:
Sirees earned 250 total points
ID: 16923492
You are missing '


EXEC sp_addlinkedsrvlogin @rmtsrvname='ActualSrv', @useself=false,--@locallogin=current_user,
               @rmtuser='test',< --you missed a quote here
               @rmtpassword='test00'
0
 
LVL 20

Expert Comment

by:Sirees
ID: 16923506
I tried to create a linked server with code in SP and it worked fine.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

863 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now