?
Solved

Could not find server 'EOP1' in sysservers.

Posted on 2006-04-18
3
Medium Priority
?
3,679 Views
Last Modified: 2007-12-19
I have two databases and a Linked Servers which connects between SQL Server 2000 and Oracle 8i.
When i try to access the tables and views under the Linked Server named EOP1 i can see all the tables and views from Oracle.
I have yet another Linked Server which connects the two SQL Databases residing on same server.One of these databases uses EOPI linked server to execute the queries against Oracle db.
When i fire a sproc from one SQL database to execute a sproc in other SQL database which in turn queries against Oracle Database i get the following error.
Server: Msg 7202, Level 11, State 2, Procedure spGetNewExhibitJSubContractorsDetails, Line 16
Could not find server 'EOP1' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Can anyone please help me on this one.I have created the EOP1 linked server and supplied all the required details but i still get this error as if it not created and not  present at all.
0
Comment
Question by:parveenmehta
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 375 total points
ID: 16480198
run any of these to check for the available linked servers

select * from master..sysservers

exec sp_linkedservers
0
 
LVL 4

Expert Comment

by:csachdeva
ID: 16480374
Dear Parveen,

Here is an example where I link a MySQL database to SQL Server:

sp_addlinkedserver
@server='YouServer', -- This could be anything
@srvproduct='MySQL', -- This could be anything
@provider='MSDASQL', -- This MUST be MSDASQL for ODBC links
@datasrc='ODBC_DSN_NAME', -- This should be the ODBC System DSN. Do
@provstr='DATABASE=mydb;DSN=My
DSN;OPTION=0;PWD=password;SERVER=localhost;UID=myuser'

I use a program called WinSQL ([url]http://synametrics.com/winsql[/url]), which creates the Connection string. I used this connection string for the 'provstr' parameter.

In other words, i can say that Symptoms of yoru problem is:

1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.

2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)

3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.

BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @server=N'CONVERTSDB', @droplogins='droplogins';

hope that by performing the above mentioned way, you will get a solution of your query.

Regards,
Chetan Sachdeva
0
 

Author Comment

by:parveenmehta
ID: 16480800
Well i am getting this error message irrespective of whether i have EOP1 existing or not.
I just dropped this linked and created a new one with a different name and updated the sproc to refer to this new linked server but i still see this error with reference to EOP1.
This is getting crazy!!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

749 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