How to define an object in common place instead of every SP in SQL Server

hi,

I want to define a linked server object in one common place instead of every SP. (Because the UAT DB or SIT DB maybe the linked server with different name)

For Oracle we can define one common object in Package like following:

CREATE OR REPLACE PACKAGE FMS_PKG IS

TYPE T_CURSOR IS REF CURSOR;

END FMS_PKG;

Then we can use it in SP like FMS_PKG.T_CURSOR. So I wonder whether SQL Server 2005 has corresponding place can do this.

Thanks

Bily Jiang

Assuming that your Linked Server is another instance of SQL Server then you could create a Linked Server, named, for example, 'MyLinkedServer', and reference this in your stored procs / Views etc...

You can then use the CliConfg.exe application to create an Alias on the server hosting your primary SQL Server instance. Name the Alias 'MyLinkedServer' and specify the Server Alias (the instance of SQL Server to which your Linked Server should point) as appropriate to your environment.

This way you do not have to make changes to the code when you progress through each of the environments - all you have to do is update the Server Alias on the server and any Linked Server security settings on the primary SQL Server instance.

Chris

|||

Yes, I have found this tool.

Could you tell me how to use this tool to create one Linked Server Alias?

After I open it, I click Alias tab --> click Add --> should choose which option?

Thanks

Bily Jiang

|||

It depends on which network protocol you are using.

We use TCP/IP, so I would select 'TCP/IP', enter the Linked Server name in the Server Alias text box (e.g. MyLinkedServer), then enter the name of the server (plus instance name if necessary) that you wish to connect to in the 'Server Name' text box.

Chris

|||If you are using SQL Server 2005 you can create Synonyms for each table, view, procedure or function you are accessing via the linked server. Then you will just use the Synonym name to reference the object on the remote server, not the linked server syntax.|||

Thank you so much.

I feel synonym is the simplest resolution especially when we just access several remoting tables.

But I think package is the advantage of Oracle.

Regards

Ren Jie