Could not find server !

Question

Post reply

Could not find server !

SQLPain

Hall of Fame

Points: 3522
More actions
September 30, 2015 at 1:38 pm

#321632

I have three servers FS1,FS2 and DSQL. The following query works on the server FS1 and FS2 but gives me error on DSQL, my question is if it works on FS3 than why not on DSQL
SELECT [DocumentCode]
,[CompanyName]
,[MailingName]
,[MailingStreet]
,[MailingCity]
,[MailingState]
,[MailingZip]
FROM FS1.MP100.dbo.v_CD
Error:
Msg 7202, Level 11, State 2, Line 1
Could not find server 'FS1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125565 More actions · Answer 1

To eliminate anything in the query as the cause, next see if a simple pass-through query against server FS1 will work.

For example:

EXEC ('select 1') AT FS1

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

SQLPain Hall of Fame Points: 3522 More actions · Answer 2

I ran the following under DSQL master:

EXEC ('select 1') AT FS1

still getting exactly the same error:

Msg 7202, Level 11, State 2, Line 1

Could not find server 'FS10' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Checked your Linked Servers to see if a link to that server has been created.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

michal.lisinski SSCertifiable Points: 7201 More actions · Answer 4

Hi

Please set up a linked server first to access data from FS1 instance on DSQL. Or already created with different name, so check

SELECT * FROM sys.servers;

Br.

Mike

SQLPain Hall of Fame Points: 3522 More actions · Answer 5

Thanks , yes you are right when I ran the following query on DSQL it only listed itself, there was no FS1 or FS3

SELECT * FROM sysservers

I have never added any linked servers in the past, what would be the code to add those on DSQL, this info might be helpful

srvname: FS1

srvproduct: SQLOLEDB

datasource: FS1

Thanks alot

michal.lisinski SSCertifiable Points: 7201 More actions · Answer 6

Here is simple tutorial how to:

https://msdn.microsoft.com/en-us/library/ff772782.aspx

and another one:

http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

Imho it should help you.

Have a nice day

Mike

SQLPain Hall of Fame Points: 3522 More actions · Answer 7

Thank you guys. I tried the GUI way SSMS and get the following error when I click ok:

--> SQL server native client 11.0 doesnot support connections to SQL server 2000 or earlier version

Does anyone know how to resolve this

DSQL ( SQL Server 11.0.5343)

FS1 (SQL Server 8.0.760)

michal.lisinski SSCertifiable Points: 7201 More actions · Answer 8

Hi again

I'm afraid you are unable to create a link with Server Native Client "SQLNCLI11", try another one,

eg. ODBC Data Source and specify MSDASQL as provider name.

Br.

Mike

SQLPain Hall of Fame Points: 3522 More actions · Answer 9

It happens to work with FS3 which is FS3(SQL Server 10.50.2550)

Now the problem I am facing is with Security:

Under local login I am putting myself (DMG\abc) should I check impersonate box or create remote user and password?

when I am checking the impersonate box I get the error:

Login failed for the user 'NT AUTHORITY\ANONYMOUS LOGON'

when I create some random remote user and remote password I get the error:

login failed for user 'user'

should my remote user and remote password be same as my windows username and password?

also for login not defined in the list above, connections will:

1) Not be made

2) be made without using a security context

3) Be made using the login's current security context

4) Be made using this security context

which one out of these 4 should I choose?

Extremely confused.