Linked Server Limit in SQL Server 2000

[TBlair]

I am about to start working on a project where I would be required to
dynamically create linked servers during the execution of an
application. One of the requirements is for the Link Servers to be
created and dropped before and after the retrieval of the data. My
question is about any type of cap on the number of linked servers SQL
Server 2000 can have registered at any single time. If I find out that
there is some type of cap, I would need to look into another way to
deal with my linked server needs. Thanks for any help/information you
might be able to provide.

[Erland Sommarskog]

Re: Linked Server Limit in SQL Server 2000

TBlair (tblair23221@ya hoo.com) writes:[color=blue]
> I am about to start working on a project where I would be required to
> dynamically create linked servers during the execution of an
> application. One of the requirements is for the Link Servers to be
> created and dropped before and after the retrieval of the data. My
> question is about any type of cap on the number of linked servers SQL
> Server 2000 can have registered at any single time. If I find out that
> there is some type of cap, I would need to look into another way to
> deal with my linked server needs. Thanks for any help/information you
> might be able to provide.[/color]

I have no idea whether there are any restriction, but since a linked
server is really just an alias which is being looked up in sysservers
at run time, it is not likely that there should be an issue.

But, hm, what is there is a numeric id in sysservers? Ah, there is,
and it's smallint. So that gives you an upper limit of 32767 linked
servers. Hm, if you add and remove servers constantly, could you still
run out of ids? I tested by dropping a server with id = 5 (I have 19
linked servers), and adding a new. The new server got id = 5, so gaps
are being filled.

Looks like you will be able to sleep well at night.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp