|Message:||Maximum number of databases used for each query has been exceeded. The maximum allowed is %d.|
|SQL Server 2000 allows up to eight active (open) database references to be tracked in an internal stack at any given time within a query. A reference to a database is tracked for a given query as long as activity is still occurring for that portion of the query that uses that database reference.
A database reference is tracked each time a database is accessed, even if there is already a separate reference to that database. The reference is no longer tracked when the query is no longer actively using the reference. Therefore, you can encounter this error if you have nested queries that cross back and forth across fewer than eight databases. An example of that would be a stored procedure in one database that calls a stored procedure in a second database that then calls a stored procedure in the first database. In that example there are three database references made and tracked even though there are only two databases involved. Whether all three are held open at once depends on several factors including the length of time it takes to complete each part of the query.
If any of your activities make use of tempdb, either explicitly or implicitly, then tempdb will count as one of the eight databases. Internal activities such as autoupdate statistics will also count as an additional reference.
A query that explicitly references eight or more databases does not automatically hit this limit. For instance this query does not have all eight database references tracked in its internal stack at one time, so it does not cause the above error:
Select * from db1..t1
|Follow these steps: