Oracle Error 7202
Contents |
remote query to an Oracle server could not find server in sys.servers linked server using an OPENQUERY that makes reference to a linked server, but the developer received the following error
Sql Add Linked Server
when he executed the OPENQUERY: Error: 7202, Severity: 11, State: 2 Could not find server 'XXXX' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to could not find server 'localhost' in sysservers add the server to sys.servers. Cause The linked server existed on the SQL Server instance of the production environment but didn't exist on the SQL Server instance assigned to the test environment. Solution The solution was to configure the Oracle client on the SQL Server instance of the test environment to be able to connect to the Oracle database server, then configure an ODBC data source, and finally use the ODBC DSN to create the linked server with the same name used in the production environment. .Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.
? Ask a question, help others, and get answers from the community Discussions Start a thread and discuss today's topics with top experts Blogs Read the latest tech blogs written by experienced community members SQL Server Error 7202 Could
Sp_addlinkedserver Example
not find server Jabeam 5 pts. Tags: Thanks! We'll email youwhen relevant content isadded
Remove Linked Server
and updated. Following Follow SQL error messages Thanks! We'll email youwhen relevant content isadded and updated. Following Follow SQL Server Thanks! ole db provider "sqlncli11" for linked server returned message "login timeout expired". We'll email youwhen relevant content isadded and updated. Following Follow SQL Server errors Thanks! We'll email youwhen relevant content isadded and updated. Following Follow SQL Server stored procedures Hi all, I am currently running into http://www.sqlcoffee.com/Troubleshooting134.htm the following error when run a stored procedure with BEGIN TRANS: "Could not find server 'VS-DEVELOPERDEVELOPER_SQL' in sysservers. Execute sp_addlinkedserver to add the server to sysservers." The server shown above is the a local sql server instance that is running the stored procedure and therefore shouldn't need to be linked. Am I misunderstanding the linked server definition or is there another underlying issue with the stored procedure?Software/Hardware used: SQL Server 2005 http://itknowledgeexchange.techtarget.com/itanswers/error-7202-could-not-find-server/ Asked: August 18, 20112:12 PM Last updated: March 31, 20126:09 PM Related Questions Retrieving System information How do I move all stored procedures to a new SQL server? Calling an Oracle stored procedure in SQL Server Migrating stored procedures from SQL Server 2000 compatibility mode to a SQL Server 2005 database How do you create stored procedures in SQL Server 2000? Answer Wiki Last updated: August 19, 20116:18 AM GMT Denny Cherry67,555 pts. History Contributors Ordered by most recent Denny Cherry67,555 pts. Thanks. We'll let you know when a new response is added. Sounds like the stored procedure is looking for a linked server as part of the code, but that linked server doesn't exist. You'll need to look at the source code of the stored procedure to be sure. Sounds like the stored procedure is looking for a linked server as part of the code, but that linked server doesn't exist. You'll need to look at the source code of the stored procedure to be sure. Please enter an answer. Send me notifications when members answer or reply to this question. Register Hereor login if you are already a member E-mail User Name Password Forgot Password? By submitting you agree to receive email from TechTarget and its partners. If
procedure (SQL2005+) Siebel and BizTalk HTTP Send Port Error: The server committed a protocol violation. Section=ResponseHeader Detail=CR must be followed byLF → When your BizTalk Server Doesn't Know https://christianloris.wordpress.com/2011/04/20/when-your-biztalk-server-doesnt-know-its-name-aka-error-7202/ Its Name (aka [Error7202]) April 20, 2011 2 Comments So we've https://www.mssqltips.com/sqlservertip/2560/configure-generic-sql-server-linked-server-names/ been being pestered by our DBAs (you know the ones) to move our BizTalk Server 2006's Data Tier from our aging SQL Server 2000 Cluster to our new shiny SQL Server 2005 Cluster. The end of our fiscal year was approaching so we brought in a consultant linked server to help us. I highly recommend Catapult Systems if you need any BizTalk help. They're smart guys and have reasonable rates (in comparison to Oracle, who doesn't?). We met with our consultant Mark and gave him the tour of our environment and he came back a week later with a solid procedure… BAM, MsgBox, WSS, Jobs, DBs… all of could not find it. We executed the procedure along with enough red lines to maintain their human status. All seemed to go well - hosts started, jobs started, Group Hub back up, but wait! Couple of jobs are failing? Hmmm. Update some physical paths in the backup job and all is well. Wait. Tracked Message Copy keeps failing: Executed as user: HARRIS\MLBSQLVLM1. Could not find server ‘mlbxxx\sql3' 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. [SQLSTATE 42000] (Error 7202). The step failed. Now this is very confusing. We're all on one server. No need for any linked servers. Why does it think mlbxxx\sql3 is a linked server it can't connect with. Is it a permission issue? I checked and double checked the database configuration from the old server and nothing jumped out at me. OK. Time to step into the deep dark reaches of the BizTalk stored procedures… the starting point: bts_CopyTrackedMessagesToDTA at line 70: exec (‘INSERT INTO [‘ + @dtaDbServer + ‘].[‘
1 | 2 | 3 | 4 | More > Linked Servers Problem Every DBA is faced with the task of occasionally refreshing their databases from Staging to QA to Development. A while back I started working on a project where we were building out a new environment and our DBA team was frequently tasked with refreshing the new environment with data from the existing one. In this environment there were quite a few linked servers and every time we would restore a database to the new environment testers would start to get errors similar to the following: Msg 7202, Level 11, State 2, Line 1 Could not find server 'STAGING_CRMDBSERVER' 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. Msg 4413, Level 16, State 1, Line 1 Could not use view or function 'AllContacts' because of binding errors. Solution Initial solution to find and replace SQL Server Linked Servers On the surface this is a pretty simple issue to fix. First thing I would do is search all of my stored procedures, functions, views and triggers for the linked server listed in the error using the following TSQL: SELECT name,type_desc FROM sys.objects WHERE OBJECT_DEFINITION(object_id) LIKE '%LINKEDSERVER%' AND type IN ('FN','IF','TF','V','P','TR') Related Tips: Different Ways to Search for SQL Server database objects After I had found all the objects, assuming that the corresponding linked server is already created in the new environment, you can script the update of all the objects to use this linked server as follows: SELECT name,type_desc, OBJECT_DEFINITION(object_id) AS oldcode, REPLACE(OBJECT_DEFINITION(object_id),'[LINKEDSERVER]','[NEWLINKEDSERVER]') AS newcode FROM sys.objects WHERE object_definition(object_id) LIKE '%LINKEDSERVER%' AND type IN ('FN','IF','TF','V','P','TR') Now, if your environment is fairly stagnant and you aren't refreshing databases very often, the above process is a good solution, but during this project we were refreshing one database or another daily. Also factor into the equation that we couldn't just script all the objects one time and reuse the script since our current environment was still being updated by developers. This meant we had to complete this entire process for every restore which became quite time consuming. Final solution to address SQL Server Linked Server in Multiple Environments The final solution I came up with was to recreate all the linked servers in our current e