SQL Server 2005 How to ignore errors during Create Procedure

Question

I need to create a Stored Procedure in SQL Server 2005. Somewhere in the procedure, I have to join to a table which does not exist in the test environment but in the live environment (in a database in a linked server). I will not run the procedure in the test environment, but I need it to exist in order to create the ORM code in the application.

Naturally, SQL Server raises the error "Could not find server 'xxx' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedur sp_addlinkedserver to add the server to sys.servers.". However, I know that I can't add this server to the test environment, as it is not accessible from outside.

So, my question is, how can I create my stored procedure by ignoring the errors? Is there a way for it?

You should use a synonym instead of hard-coding the linked server name in your queries. In the test environment this allows you to just set the synonym to some local object, and in production it points to the actual linked server. Now the code in both places can be the same, because they're referencing the "same" synonym. — Aaron Bertrand
– Aaron Bertrand, Commented Aug 6, 2014 at 14:33

Victor Schiavi · Accepted Answer · 2016-06-16 17:55:38Z

1

This is an old thread, but if other people are having the same problem, here's another solution:

You can have your server via text and the procedure will pass.

create proc test
as    
declare @myserver varchar(50) = '[myserver\myinst]'
exec('select * from '+@myserver+'.dbo.table')

This way, the proc will compile on any environment, but will only run successfully on production

answered Jun 16, 2016 at 17:55

Victor Schiavi

911 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:24:05Z

0

If you are certain that everything is correct and the procedure will work fine in live environment then create a fake linked server using sp_addlinkedserver.

What I mean is, if procedure body contains a linked server named test_linked and if it's not found then it will throw error.

Use sp_addlinkedserver and create a fake linked server named test_linked pointing to your test environment or even live environment. that will solve the issue cause it will try to check whether a linked server named test_linked does exist in sys.servers or not but unless you are running the procedure the actual linked server will not be accessed (AFAIK).

As Aaron Bertrand have mentioned in comment, Going by synonym would be a much cleaner approach though.

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Aug 6, 2014 at 14:08

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

11 Comments

Aaron Bertrand Over a year ago

A synonym is a much, much, much cleaner approach.

Rahul Over a year ago

I don't dare to disagree you :) but didn't got that idea out of my head. Answer edited to include what have you said in comment.

Zafer Sernikli Over a year ago

Thank you both. I'm gonna go with synonym as creating a fake linked server in a server that is not mine is not a good approach.

Zafer Sernikli Over a year ago

@AaronBertrand Now, the thing is, I am able to define the synonym even though I don't access it; however, when I try to create the procedure, I get the same error.

Rahul Over a year ago

@ZaferSernikli, have you tried the approach I suggested? I know in presence of synonym this approach may not look good but it definitely works. Moreover, even though the server not yours, just creating a linked server won't harm anything.

|

Collectives™ on Stack Overflow

SQL Server 2005 How to ignore errors during Create Procedure

2 Answers 2

Comments

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related