CODEWITHSUNDEEP
sqlsql-server-2005stored-procedures
Zafer Sernikli
Zafer Sernikli

Reputation: 173

SQL Server 2005 How to ignore errors during Create Procedure

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?

Upvotes: 1

Views: 1558

Answers (2)

Victor Schiavi
Victor Schiavi

Reputation: 91

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

Upvotes: 1

Rahul
Rahul

Reputation: 77876

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.

Upvotes: 0

Related Questions