f



Linked Server Limit in SQL Server 2000

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.

0
1/21/2005 3:03:18 PM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

1 Replies
597 Views

Similar Articles

[PageSpeed] 0

TBlair (tblair23221@yahoo.com) writes:
> 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.

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@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
0
esquel (7068)
1/21/2005 10:42:55 PM
Reply:

Similar Artilces:

MicroSoft SQL Server 2000, Service Pack 2, AppDev SQL Server 2000 Accelerated Training [3 CDs], TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1, SQL Server 2000 Reporting Ser
MicroSoft SQL Server 2000, Service Pack 2, AppDev SQL Server 2000 Accelerated Training [3 CDs], TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1, SQL Server 2000 Reporting Services, (Enterprise), Developer Edition 64 Bit, other 16,000 more CDs, [ no dongles, no activations, etc ... needed ! ] (free donge)! No time limitation! CD NR 4203 MicroSoft SQL Server 2000 4in1 CD NR 9452 Microsoft SQL Server 2000 Service Pack 2 CD NR 11 298 AppDev SQL Server 2000 Accelerated Training [3 CDs] CD NR 12 000 Microsoft SQL Server 2000 Developer Edition 64 Bit CD NR 13 424 TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1 CD NR 14 688 SQL Server 2000 Reporting Services (c) Microsoft CD NR 15 412 SQL Server 2000 Reporting Services Enterprise (c) Microsoft please send e-mail, to ola2@mail.gr, zorba@mail.gr ...

Using MS SQL Server 2005 Enterprise Manager with MS SQL Server 2000
Hi Everyone, Most of our MS SQL Servers are still running on SQL 2000, but being I will soon be upgrading my workstation to Vista Business I'd like to install MS SQL 2005 Enterprise Manager to manage them all. When I tried to install MS SQL 2000 Enterprise Manager on Vista, it gave me a message saying it's not compatible, so I'll have to use 2005 I guess. We have LOTS of DTS scripts on our SQL 2000 server, and will 2005 EM work with these okay? Also I want to make sure SQL 2005 Enterprise Manager won't be adding to, changing, or 'upgrading' the DTS scripts or anything on the SQL 2000 servers as we still have folks using SQL 2000 Enterprise Manager. Thanks for any suggestions. I'm always leery when using cross-versions of Microsoft applications because they are often incompatible or they do squirly things that often break stuff. Thanks -- Alex Alex (samalex@gmail.com) writes: > We have LOTS of DTS scripts on our SQL 2000 server, and will 2005 EM > work with these okay? Also I want to make sure SQL 2005 Enterprise > Manager won't be adding to, changing, or 'upgrading' the DTS scripts or > anything on the SQL 2000 servers as we still have folks using SQL 2000 > Enterprise Manager. > > Thanks for any suggestions. I'm always leery when using cross-versions > of Microsoft applications because they are often incompatible or they > do squirly things that often break stuff. There isn't any Enterpr...

Database link to a MS SQL Server 2000
Hi NG, we have to replikate some data between an oracle DB and an application with MS SQL Server. There is no possibility to load and push the data on the client mashine. We have to replicate the data on the server mashines directly. Is there a possibility to create a DB link from oracle to MS SQL Server or otherwise? thanx abbas On Thu, 9 Jun 2005 00:13:02 +0200, "Abbas" <ar.newsgroup@web.de> wrote: >Hi NG, > >we have to replikate some data between an oracle DB and an application with MS SQL Server. > >There is no possibility to load and push the data on the client mashine. We have to replicate the data on the server mashines >directly. > >Is there a possibility to create a DB link from oracle to MS SQL Server or otherwise? > >thanx >abbas > Look into using Heterogeneous Services in your Net administrators manual. -- Sybrand Bakker, Senior Oracle DBA Are you willing to install a bridge program to create this "link"? If so, then try www.examinstudio.com Do as Sybrand told you, look at the Heterogenous services of Oracle. Oracle even supports natively DRDA towards a DB2 z/OS mainframe, and they also support SQL Server. They all end-up like a database link with a totally quallified names in the "from" clause. Be aware of that distributed joins between different DBMS systems might cause huge performance problems, keep the functionality when dealing with multiple DBMS systems simple, no dist...

SQL Server "Linked Server" to MS Access Database
I have a very frustrating problem. I have a situation that works fine on some computers, but not on others. I have SQL Server Express 2005 and Access 2003 loaded on 4 computers. I have set up a "Linked Server" in SQL Server linked to my Access database. I am running queries on t-SQL against the Access tables through the linked server. On all of the computers, I can do select queries and they all work great but what I need to do is run Update, Insert, Delete queries as well. On a couple of the computers, it works fine, locally and across the LAN, and a VPN as far as that goes. But on other computers I can do Select queries (also across the network) but no update queries. I get the error message "OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "securedjetls" returned message "Operation must use an updateable query." where securedjetls is the name of the linked server. The Access database on the computer that is not working is a copy of the database from the computer that is working and any of the tables that I'm trying to update have primary keys, so that's not the problem. ...

SQL Server 2000 SQL Agent Error [393] Waiting for SQL Server to recover databases
While I was out of the office the Lan Team moved one of my SQL Server 2000 servers to a new network domain. Since then the maintenance job has not ran. The error log for the SQL Agents has the message listed in the subject line. I have not found any useful articles on the MS SQL Server site. Anyone know what might be wrong and how to fix it. HTH -- Mark D Powell -- Mark D Powell (Mark.Powell@eds.com) writes: > While I was out of the office the Lan Team moved one of my SQL Server > 2000 servers to a new network domain. Since then the maintenance job > has not ran. > > The error log for the SQL Agents has the message listed in the subject > line. I have not found any useful articles on the MS SQL Server > site. Anyone know what might be wrong and how to fix it. I would try select * from master..sysdatabases where databaseproperty(name, 'IsInRecovery') = 1 and see if that finds something. There are a couple of more properties for Databaseproperty that you should search for. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx On Jan 30, 5:30 pm, Erland Sommarskog <esq...@sommarskog.se> wrote: > Mark D Powell (Mark.Pow...@eds.com) writes: > > > While I was out of the office the Lan Team ...

MS SQL Server 2000 and MS JDBC SQL Server driver problems. (Connection timed out, followed by Error establishing socket)
I have downloaded the MS SQL Server 2000 Enterprise Evaluation edition, and the MS SQL 2000 Server JDBC driver. I have installed and configured the MS SQL Server 2000 onto two computers, which reside on the same network. (In fact, these two computers comprise the entire network). The network server (computer name=SUNCDS1), and the lone node (SUNCDS2), each have their own SQL Server 2000 instance. The SQL Server 2000 Enterprise Manager console on SUNCDS1 shows both SQL Server instances, the (local) instance, which is SUNCDS1, and also SUNCDS2. It may be worthwhile to mention that the SUNCDS2 instance on the Enterprise console does not show the SQL Server Agent as being started, but I can navigate to the SUNCDS2 databases, and display the table information. If I try to start the SQL Server Agent for SUNCDS2, I receive the message "Access is Denied". The enterprise console on the SUNCDS2 machine indeed shows the SQL Server Agent as being started. I have written a sample Java application that uses both the MS SQL Server ODBC driver and a JDBC type 4 driver (MS SQL Server 2000 JDBC) to establish a connection to the SUNCDS2 instance. This application resides on the SUNCDS1 network server. The ODBC driver will connect to SUNCDS2 instance wihout a problem. The JDBC driver does not connect to SUNCDS2. The JDBC driver will, however, connect to the local SQL Server instance that resides on SUNCDS1. Following are the code snippet to est...

Sql Server 2000 linked server to Access 97/2000 incompatability
We are experiencing a problem with Sql Server 2000 linking to an Access 97 file. We have two machines that link to this .mdb file, and we recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. The link on this upgraded machine no longer works, giving this message: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: Cannot open a database created with a previous version of your application.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize r...

MS SQL Linked Servers(Linking MySQL to MS SQL) Collation problem.
Hello, I have a problem. I've linked MySql server to MsSql, in MySql I have a table with Latvian data(character set is ucs2, ucs2_general_ci) and the problem is that when I use openquery to read data from MySQL server, some characters are not translated correctly! I receive question symbols instead of Latvian special characters. Maybe someone had this kind of problem with collation? P?vels Mihailovs (iceravenlv@gmail.com) writes: > I have a problem. I've linked MySql server to MsSql, in MySql I have a > table with Latvian data(character set is ucs2, ucs2_general_ci) and > the problem is that when I use openquery to read data from MySQL > server, some characters are not translated correctly! I receive > question symbols instead of Latvian special characters. > Maybe someone had this kind of problem with collation? Since I don't work with MySQL myself, it's difficult to say what is going on. But if MySQL has a notion of collation, and the collation is ucs2_general_ci, it sounds like MySQL returns Unicode data. One would expect that to arrive unscathed. What is you collation on the SQL Server side? The stored procedure sp_serveroption permits you to set options for linked servers, and two of them relates to collations. You could play around with these and see what happens. Please look in Books Online for details. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/techn...

Trying to create a linked server from an SQL Server to another SQL Server on another machine.
I'm Trying to create a linked server from an SQL Server to another SQL Server on another machine. Can anyone suggest some good reference material or tutorials?? So far I'm only finding examples for a linked server to an ACCESS database. Thanks in Advance! gdev (paul.afamdi.okeke@gmail.com) writes: > I'm Trying to create a linked server from an SQL Server to another SQL > Server on another machine. > > Can anyone suggest some good reference material or tutorials?? So far > I'm only finding examples for a linked server to an ACCESS database. There are plenty of examples in the topic for sp_addlinkedserver. In many cases, it is as simple as: exec sp_addlinkedserver THATSERVEROVERHERE exec sp_serverioption THATSERVEROVERTHERE, 'data access', true Authentication does not always work though, and in this case sp_addlinkedsrvlogin comes to the rescue. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx ...

Multiples Instance of SQL server 2000/SQL server 7.0 on Windows server 2003
Hi, Is it possible to install on a Windows server 2003, SQL SERVER 2000 in the folowing configuration : SQL server 7.0 as a default Instance and SQL server 2000 as a named instance. Thanks for your answer. riless (g.carali@voila.fr) writes: > Is it possible to install on a Windows server 2003, > SQL SERVER 2000 in the folowing configuration : > > SQL server 7.0 as a default Instance > > and > > SQL server 2000 as a named instance. If SQL 7 can be installed on Win2003, yes. But I would not really expect SQL 7 to be welcome on Windows 2003, although I don't...

Running MS Access db as a Linked Server on SQL Server
Hi, Looking for a quick and dirty on running an Access database as a linked server in SQL Server. Basically, the majority of my stuff is in SQL Server, but there is one lingering nightmare-of-an-Access-database no one wants to touch. I just want to create a linked server to use the Access db. So far I have not found a way to connect (under Security -> Link Servers in SQL Server), though I tried all kinds of drivers, connection strings, etc. What do I put for Product Name, Data Source, Provider String, (Location, Catalog)? Thanks a bunch. This might help; note the table about halfway down that includes the connection options for various sources. http://msdn2.microsoft.com/en-us/library/ms190479.aspx Stu maxvalery@gmail.com wrote: > Hi, > > Looking for a quick and dirty on running an Access database as a linked > server in SQL Server. Basically, the majority of my stuff is in SQL > Server, but there is one lingering nightmare-of-an-Access-database no > one wants to touch. > > I just want to create a linked server to use the Access db. > > So far I have not found a way to connect (under Security -> Link > Servers in SQL Server), though I tried all kinds of drivers, connection > strings, etc. What do I put for Product Name, Data Source, Provider > String, (Location, Catalog)? > > Thanks a bunch. ...

SQL Server 2000 linked server to Db2 on z/OS
Can somebody point me to the right direction how to create a linked server to db2 on z/OS using ODBC drivers or OLEDB providers that are free. thanks ...

Installing MS sql server 2000 on win 2003 server
How do I do this when my instalation cd is an old veriosn with no SP�s? Win 2003 requires MS SQL 2000 SP3 sp what do I do??? Regards Anders > When you get the prompt saying that it must be SP3 you can continue to the > end of the installation as per normal. You should then install SP3 > immediately before you do anything else. SP3a can be download from the > microsoft web site. Actually not. It says: Ms SQL Server 2000 Enterprise Ediditon server componenet is not supportet on this operating system. Only client componentes will be avaible for instalation. I juts read that bef...

Unidata linked server with MS SQL 2000
Hi I am trying to connect to UniData using linked server from MS SQL. I tried using the following details, but could not get to view the tables. It comes with an error that "UVI connections to non Universe database not allowed. Details entered for creating a linked server: product name: Unidata data source: c:\ibm\ud60\demo provider string: Ardent.UniOLEDB location: myserver (my unidata server name) Can somebody help me out please, Thanks, ...

SQL Server Link Server
Is it possible to create a link server in SQL Server without using the default system.mdw file? Any help is appreciated. Thanks. Vincent (animedreamer@verizon.net) writes: > Is it possible to create a link server in SQL Server without using the > default system.mdw file? Any help is appreciated. Thanks. Eh, yes. I don't even know what the system.mdw file might be. Adding a linked server can be as easy as: sp_addlinkedserver THATSERVEROVERTHERE and then you might be able to say things like: SELECT ... FROM THATSERVEROVERTHERE.db.dbo.tbl It is not always that simple; this requires Windows authentication to work all the way. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx ...

insert fails
Hi, I configured a linked server in MS SQL Server v8 pointing to a remote AS/400 DB2 database. I used Client Access ODBC driver (v7), installed it from a Client Access Express v4.5. Select/update/delete seems to be working fine, I just cannot execute an insert statement. Here's the error I'm getting: update as400..U1.TB1 set a = 4 select * from as400..U1.TB1 insert into as400..U1.TB1 ( a ) values (1) ------------------------------------------------------ Server: Msg 7399, Level 16, State 1, Line 4 OLE DB provider 'MSDASQL' reported an error. The provider did not give any inf...

Windows 2000 Server / IIS / SQL Server 2000
Hi, We are using SQL Server 2000 on Windows 2000 Server with IIS installed. Can IIS be uninstalled without affecting SQL Server? Thank you! Mike wrote: > Hi, > > We are using SQL Server 2000 on Windows 2000 Server with IIS installed. > Can IIS be uninstalled without affecting SQL Server? > > Thank you! Just disable it in the services (unless it's being used for .net etc) ...

Re: Accessing legacy data via MS SQL Server linked servers
Chuck, You might talk to MBFoster. They may have a solution for you. Imagesql has odbclink/se but I'm not sure if this will do the job for you. Imagesql is available to 7.0 and 7.5 users. I have found in some cases that MSAccess does a quick job of access legacy data with odbclink/se...(but that is going thur Imagesql). JDBC, java, perl may be another solution. These have classes for image. Hope this helps Cathlene Mc Rae HP Sr Response Center Engineer. * To join/leave the list, search archives, change list settings, * * etc., please visit http://raven.utc.edu/arc...

Explicitly fail a SQL Server Job in SQL Server 2000
I have a SQL Server job, which runs mutiple steps. One of the steps (step 3) looks for a record in the database. How can I explicitly fail the SQL server job if the database record does not exist? You can use RAISERROR with a severity level of 11 or higher to indicate failure. Simon Thanks Simon. That worked. ...

Can Analysis Server and Report Server run on SQL Server 2000 Cluster
I have installed Analysis Server (AS) and Reporting Services (RS) on SQL Server 2000 before. Now I have been asked to install these on a failover Cluster we have. Previously I just ran the SQL Server 2000 install program off our network disk and then ran the SP4 patch to install AS. When I ran the install and chose AS there was no indication from the software that it recogned that I wanted it to install on a cluster. After it said it successfully installed the SP4 upgrade failed with a buffer overflow problem. Anyone out there know the proper procedure (url)? -- Mark D Powell -- On Aug 10, 2:57=A0pm, Mark D Powell <Mark.Pow...@eds.com> wrote: > I have installed Analysis Server (AS) and Reporting Services (RS) on > SQL Server 2000 before. =A0Now I have been asked to install these on a > failover Cluster we have. > > Previously I just ran the SQL Server 2000 install program off our > network disk and then ran the SP4 patch to install AS. =A0When I ran the > install and chose AS there was no indication from the software that it > recogned that I wanted it to install on a cluster. =A0After it said it > successfully installed the SP4 upgrade failed with a buffer overflow > problem. > > Anyone out there know the proper procedure (url)? > > -- Mark D Powell -- I had found a document that said RS 2000 can run on a cluster but what the document had not made clear is that while the RS 2000 database can be on a cluster that RS its...

migrate Progress database to MS SQL Server 2000 #2
I am afraid that our customer has already made up his mind about movin to MS SQL server and there is nothing that we can do about it Is there a migration tool -- panagiotis@mel ----------------------------------------------------------------------- panagiotis@melo's Profile: http://www.progresstalk.com/member.php?action=getinfo&userid=817 View this thread: http://www.progresstalk.com/showthread.php?threadid=7937 Yes ther is. You'll find it between your ears. Regards...Dan. "panagiotis@melo" <panagiotismelo.1gvuzm@progresstalk.com> wrote in message news:panagiotismelo.1gvuzm@progresstalk.com... > > I am afraid that our customer has already made up his mind about moving > to MS SQL server and there is nothing that we can do about it. > > Is there a migration tool? > > > -- > panagiotis@melo > ------------------------------------------------------------------------ > panagiotis@melo's Profile: > http://www.progresstalk.com/member.php?action=getinfo&userid=8173 > View this thread: > http://www.progresstalk.com/showthread.php?threadid=79374 > crossmerge.com ? Regards...Dan. "panagiotis@melo" <panagiotismelo.1gvuzm@progresstalk.com> wrote in message news:panagiotismelo.1gvuzm@progresstalk.com... > > I am afraid that our customer has already made up his mind about moving > to MS SQL server and there is nothing that we can do...

Server to server = Server client to server?
For a server to server connection, is the connecting server considered as a client of the accepting server or is it not? I have the following classes: Connection <--- base class of the following two ClientConnection <--- client ServerConnection <--- server ServerClientConnection <--- server as a client of another server. But then I came across something that states "A client is anything connected to a server that is not another client". Comments please... Just remember! Server ( programm ) is always listening a connections!!! - wrote: &g...

Error inserting into iSeries DB2 table with long name via SQL Server 2000 linked Server
I have a IBM iSeries (aka AS-400) running v5r3 of OS/400 that I access via a linked server from SQL Server 2000. The following select works fine: select * from prod400db.test.meldbf.InventoryHistory However, this insert statement fails: insert into prod400db.TEST.MELDBF.InventoryHistory (plantId, itemnumber, transactionType, transactionQty, fromStockRoom, fromStatusCode, fromBinLocation, toStockRoom, toStatusCode, toBinLocation, transactionReason, transactionSourceSystem) values ('100','CMW21','R',11, NULL, NULL, NULL, 'WIP',' ', NULL,'...

Once a week Kerberos failure between IIS6 web server and SQL Server 2000 db server
Hi, Regularly once a week we get problems with a Kerberos failure on ouintranet application. Kerberos is set up with Constrained Delegation and Protocol Transition. Configuration: S3 ...

Web resources about - Linked Server Limit in SQL Server 2000 - comp.databases.ms-sqlserver

Linked data structure - Wikipedia, the free encyclopedia
In computer science , a linked data structure is a data structure which consists of a set of data records ( nodes ) linked together and organized ...

Sarah Palin - Please take a look at the article linked... - Facebook
Please take a look at the article linked below to understand how the amnesty bill the Senate passed yesterday is a sad betrayal of working class... ...

Linked Media Group (@LinkedMedia) on Twitter
Sign in Sign up To bring you Twitter, we and our partners use cookies on our and other websites. Cookies help personalize Twitter content, tailor ...

How to Rock the Perfect Linked In profile ...
Thank you to everyone at LinkedIn and the BMB Agency. And all my connections. Spread The Love, MHx

London Road: Linked Stories on the App Store on iTunes
Get London Road: Linked Stories on the App Store. See screenshots and ratings, and read customer reviews.

smart linked thermostat - Flickr - Photo Sharing!
Explore athriftymrs.com's photos on Flickr. athriftymrs.com has uploaded 2243 photos to Flickr.

'Brazil drought linked to Amazon deforestation' - YouTube
Subscribe to BBC News HERE http://bit.ly/1rbfUog Brazil's biggest city, Sao Paulo, is experiencing its worst drought in decades.Now, a leading ...

US ambassador to UAE believes warning issued to American citizens not linked to Reem murder
US officials do not believe that a woman stabbed to death last week was targeted because of her American nationality, the US ambassador has said. ...

Spiritual healer linked to death of diabetic Sydney boy promotes 'self-torture' in promotional clip
... to attend an alternative Chinese medicine workshop in Sydney's south. A testimonial video from the website of a Chinese spiritual healer linked ...

Eating chocolate linked to improved memory, concentration and problem-solving skills, UniSA study reveals ...
WE always love a good scientific reason to eat chocolate. And here’s another reason to indulge in the sweet stuff.

Resources last updated: 3/22/2016 7:17:41 AM