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.
nataliyamaksAsked:
Who is Participating?
 
SireesConnect With a Mentor Commented:
You are missing '


EXEC sp_addlinkedsrvlogin @rmtsrvname='ActualSrv', @useself=false,--@locallogin=current_user,
               @rmtuser='test',< --you missed a quote here
               @rmtpassword='test00'
0
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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:
I tried to create a linked server with code in SP and it worked fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.