I tried to insert values from one server to another server and I got the error:
Msg 7202, Level 11, State 2, Line 1 Could not find server 'SNRJDI\SLAMANAGEMENT' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
Add a comment
|
5 Answers
I got it. It worked fine
Thank you for your help:
EXEC sp_addlinkedserver @server='Servername'
EXEC sp_addlinkedsrvlogin 'Servername', 'false', NULL, 'username', 'password@123'
1 Comment
nam
For the benefit of other users, you may want to mark your response as an
Answer.Add the linked server first with
exec sp_addlinkedserver
@server = 'SNRJDI\SLAMANAGEMENT',
@srvproduct=N'',
@provider=N'SQLNCLI'
3 Comments
Domnic
When I tried to execute above I got error like 'Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89 The server 'SNRJDI\SLAMANAGEMENT' already exists.'
Domnic
Then When I execute the Query I got another error like'Msg 18452, Level 14, State 1, Line 1 Login failed for user ''. The user is not associated with a trusted SQL Server connection.'
hkf
How are you connecting to the server you are running from? You should be providing the server credentials as opposed to using a network service or windows logon.
I had the same issue connecting an SQL_server 2008 to an SQL_server 2016 hosted in a remote server. @Domnic 's answer didn't worked for me straightforward. I write my tweaked solution here as I think it may be useful for someone else.
An extended answer for remote IP db connections:
Step 1: Link servers
EXEC sp_addlinkedserver @server='SRV_NAME',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'aaa.bbb.ccc.ddd';
EXEC sp_addlinkedsrvlogin 'SRV_NAME', 'false', NULL, 'your_remote_db_login_user', 'your_remote_db_login_password'
...where SRV_NAME is an invented name. We will use it to refer to the remote server from our queries. aaa.bbb.ccc.ddd is the ip address of the remote server hosting your SQLserver DB.
Step 2: Run your queries For instance:
SELECT * FROM [SRV_NAME].your_remote_db_name.dbo.your_table
...and that's it!
Syntax details: sp_addlinkedserver and sp_addlinkedsrvlogin
Comments
-- check if server exists in table sys.server
select * from sys.servers
-- set database security
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
-- add the external dbserver
EXEC sp_addlinkedserver @server='#servername#'
-- add login on external server
EXEC sp_addlinkedsrvlogin '#Servername#', 'false', NULL, '#username#', '#password@123"'
-- control query on remote table
select top (1000) * from [#server#].[#database#].[#schema#].[#table#]
1 Comment
Devolus
Thanks! This was the only usefull answer for me. :)
FOR SQL SERVER
EXEC sp_addlinkedserver @server='servername'
No need to specify other parameters. You can go through this article.
1 Comment
8forty
Apologies for the -1, looks like I clicked it by accident but noticed too late to undo. Apparently if you do any edit I'll be able to change it.