[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 495
  • Last Modified:

sp_addlinkedserver problem

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
nataliyamaks
Asked:
nataliyamaks
  • 6
  • 2
1 Solution
 
SireesCommented:
Try this in QA

select * from Master..Sysservers

see if 'ActualSrv' exists.

0
 
SireesCommented:
Are you linking two SQL Servers?

If so, @srvproduct should be 'SQL Server'

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
nataliyamaksAuthor Commented:
@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
 
SireesCommented:
Are you referring to Master db in your SPs?
0
 
nataliyamaksAuthor Commented:
no
0
 
SireesCommented:
You are missing '


EXEC sp_addlinkedsrvlogin @rmtsrvname='ActualSrv', @useself=false,--@locallogin=current_user,
               @rmtuser='test',< --you missed a quote here
               @rmtpassword='test00'
0
 
SireesCommented:
I tried to create a linked server with code in SP and it worked fine.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now