| Author |
Topic |
|
pug2694328
Posting Yak Master
166 Posts |
 Posted - 2007-05-20 : 12:22:33
|
| I'm running SQL Server 2000.I have a proc that submits other procedures, one of which is rather large and submits procedures of it's own.If I submit the large 'sub' proc on it's own, it runs fine in a few seconds, happily inserting/updating various tables.If I submit the 'master' proc, I get a long hang that kicks out a: 'Msg 925, Level 19, State 1, Line 1Maximum number of databases used for each query has been exceeded. The maximum allowed is 8.' error.These procedures load data to my server. They can hit several databases, but a query never uses more than 8 databases at a time.It seems like I'm hitting some kind of system limitation on procedures, but the error gives little information.Note, this procedure accepts an @database parameter, as the proc needs to know which client database it has to update. Thus there are some dynamic SQL statements that couldn't be avoided. These are prefaced by 'USE ' + @DATABASE + 'blah blah'.Any help would be greatly appreciated. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-20 : 12:25:52
|
| when you run the master sproc your cumulative db changes get over the number 8._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2007-05-20 : 12:27:40
|
| I've tried to simulate this by creating a proc that bounces from one to another db. but it doesn't recreate the error.Is this limitation only on add/update/deletes?Is there any good doc on the limitation?Thanks for your help! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-20 : 12:32:43
|
| not that i know of. this used to be a bug in 6.5 for extended use of temporary tables...does your sql error log show any info?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2007-05-20 : 12:33:40
|
| is there a way to pull the cumulative db changes for a proc? this would tell me which database changes it's counting. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-20 : 12:36:58
|
| well see how many use statements you have plus number of different databases in joins plus 1 for tempdb.sproc text is in syscomments i guess you could write a script that would find that info..._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2007-05-20 : 12:40:36
|
| Nope, the error log sticks to the basics:Error: 925, Severity: 19, State: 1Maximum number of databases used for each query has been exceeded. The maximum allowed is 8. |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2007-05-20 : 12:57:12
|
| I'm on Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1) btw, hoping they fixed that 6.5 bug. : ) |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2007-05-20 : 18:38:00
|
| Here's a good description of why I am hitting this problem.http://manuals.sybase.com/onlinebooks/group-as/asg1250e/svrtsg/@Generic__BookTextView/21743;pt=21661 |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2007-05-22 : 11:58:13
|
| I broke out he code to several smaller procs and this error stopped showing up. |
|
|
|
|
Hanging on big procedure only...