MaximumErrorCount Problem with Copy Database Wizard

I am getting the MaximumErrorCount error when running the Copy Database Wizard. There is an error that I recognize with a stored procedure. I am not interested necessarily in fixing the stored proc error. Rather, I want to just simply copy it "as is" (working in production by the way) and then modify it locally.

I am trying to copy a production database to a develoment environment in order to test against some data. I keep getting the following error:

"The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount."

How do I configure the MaximumErrorCount on a particular Copy Database process?

I have looked through the documentation, and have not found any place in the UI to make this change.

I am copying a SQL 2000 db to a SQL 2005 (SP1) instance running on Windows XP SP2.

It makes no sense that I cannot make a configuration as recommended by the error message.

Thanks.

Dennis

I don't think the wizard UI makes this property available. However, if you save the package and open it in BIDS, you can change it there.

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32

i m getting this error when i m executing a stored procedure

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

can anyone help me out what exactly the problemIt looks like you call stack exceeds maximum allowable level. It means that you run a stored procedure that executes another stored procedure that executes yet another... and so on.

Maximum number of databases used for each query in 2005?

In MS Sql Server 2000, if you run a stored procedure that query's more
than 8 databases, you get this error gets raised:

Maximum number of databases used for each query has been exceeded. The
maximum allowed is 8.

In 2005, does this maximum number still exist? if so, is it still 8, or
has the number been increased?

Thanks,
MikeHoly Moly, what kind of queries are you writing!?|||Hi, I have the same problem with 2000. I am about to upgrade to 2005 and I am very concerned about this because our database requirements are rapidly growing.

Thanks,
benn

Quote:

Originally Posted by Michael.Suarez@.gmail.com

In MS Sql Server 2000, if you run a stored procedure that query's more
than 8 databases, you get this error gets raised:

Maximum number of databases used for each query has been exceeded. The
maximum allowed is 8.

In 2005, does this maximum number still exist? if so, is it still 8, or
has the number been increased?

Thanks,
Mike

Maximum Allowed Stored Procedure in SQL Server

Here is something that perhaps a lot of you out there wonder about..

My company is on a large Enterprise Project.
The database plans for this to be fully supported predict 1000+ stored procedures.

Question:
Will there be any limitation for an SQL Server 2000 to handle so many stored procs?

and if there is a limitation what will we have to do in order to work around it?The limitation for the total number of objects in a database is 2,147,483,647. This includes all the objects (Tables, views, functions, stored procedures etc) ...|||A fellow programmer said that there would be a problem with this number of procs. However he isn't famous for his experience :)

Thanks for the answer, it figures that there isn't any actual limitation of the number of objects you can handle. This guy was probably wrong.|||The practical limit is too high given the situation to be reached. The only issue I see is the maintenance of such huge number of stored procedures ... If you have a proper process to keep track, you are safe for sure building such solutions ...|||Agreed, you should not hit any physical limitation. I think you should carefully plan out naming conventions for your stored procedures so that you can keep things organized. One convention I like is:
sp
module name
underscore(_)
action (lower case)
noun (proper case)

For example:
spOrders_putOrderDetail
spMaintainUsers_deactivateUser
spReports_getZeroInventory

Also, you should strongly consider using standard documentation at the top of the stored procedures if you are not doing so already. I like to list the author, date created, purpose, where the sproc is used, any notes, and a revision history (with date, author, and summary of change).

Terri

max worker threads

We have some SQL Servers that receive high number of connections and stored
procedure calls.
Every once in a while, we may have a bad query plan and then one stored proc
blocks the other stored procs and in no time, we are out of worker threads
and no one can connect
Have you come across such situations ? If so, how did you handle it ? Is it
safe to increase the max worker threads ? What are the consequences ?The default setting for the 'max worker threads' option is 255,But if the
number of user connections surpasses this value the thread pooling will be
used. For example, if the maximum number of the user connections to your SQL
Server box is equal to 255, you can set the 'max worker threads' options to
255, this frees up resources for SQL Server to use elsewhere. If the maximum
number of the user connections to your SQL Server box is equal to 500, you
can set the 'max worker threads' options to 500, this can improve SQL Server
performance because thread pooling will not be used.
In your case , it sounds like it's worth investigating the query plan
problem , as increasing the worker threads will take up extra resources on
your server
Jack Vamvas
"Hassan" <hassan@.hotmail.com> wrote in message
news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> We have some SQL Servers that receive high number of connections and
stored
> procedure calls.
> Every once in a while, we may have a bad query plan and then one stored
proc
> blocks the other stored procs and in no time, we are out of worker threads
> and no one can connect
> Have you come across such situations ? If so, how did you handle it ? Is
it
> safe to increase the max worker threads ? What are the consequences ?
>|||Hi Jack
The number of connections is not related to the number of worker threads
quite as simply as you've described.
Connetions only use threads while they're actually executing. When
connections are in idle state (usually a majority of the time in typical
OLTP apps), there are no threads associated with the connection. This
asynchronous design allows any given number of threads to service a far
larger number of connections.
The way to tell if SQL Server is running short on threads is to use the
Windows Perfmon.exe & monitor the process object sqlservr instance's thread
count counter. If the process SQL Server is running has close to 255
threads, it might be worth increasing Max Worker Threads.
In my experience, this is typically when an OLTP application has thousands
of user connections running, as statistically, this translates to ~255
connections executing concurrently therefore requiring at least 255
threads..
As for the original question, it may help to increase the number of workers,
but keep in mind that this means that SQL Server will be able to schedule
that many more threads to perform concurrent work & you need to take into
account your server resources when making this decision. This is a gross
simplification, but in my experience increasing Max Worker Threads past 255
on systems with fewer than 4 CPUs has rarely helped much as these servers
usually don't have the capacity to process in increased concurrent workload
for example. It should hurt much to increase this setting & see for yourself
whether this helps SQL Server get its work done more effieciently or whether
it contributes further to the problem by increasing context switching etc.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Jack Vamvas" <info@.nospam.com> wrote in message
news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>
> The default setting for the 'max worker threads' option is 255,But if the
> number of user connections surpasses this value the thread pooling will be
> used. For example, if the maximum number of the user connections to your
> SQL
> Server box is equal to 255, you can set the 'max worker threads' options
> to
> 255, this frees up resources for SQL Server to use elsewhere. If the
> maximum
> number of the user connections to your SQL Server box is equal to 500, you
> can set the 'max worker threads' options to 500, this can improve SQL
> Server
> performance because thread pooling will not be used.
> In your case , it sounds like it's worth investigating the query plan
> problem , as increasing the worker threads will take up extra resources on
> your server
> Jack Vamvas
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>> We have some SQL Servers that receive high number of connections and
> stored
>> procedure calls.
>> Every once in a while, we may have a bad query plan and then one stored
> proc
>> blocks the other stored procs and in no time, we are out of worker
>> threads
>> and no one can connect
>> Have you come across such situations ? If so, how did you handle it ? Is
> it
>> safe to increase the max worker threads ? What are the consequences ?
>>
>|||I looked at the thread counter for sqlservr process and it states around
180.
What does that mean ?
Are there 180 threads running concurrently ?
If so, would that mean that when i run sp_who2 active, i should see a high
number of connections atleast around 180 or so..
Thats not the case.
Can you tell me more ?
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OwBcDBt8FHA.2576@.TK2MSFTNGP12.phx.gbl...
> Hi Jack
> The number of connections is not related to the number of worker threads
> quite as simply as you've described.
> Connetions only use threads while they're actually executing. When
> connections are in idle state (usually a majority of the time in typical
> OLTP apps), there are no threads associated with the connection. This
> asynchronous design allows any given number of threads to service a far
> larger number of connections.
> The way to tell if SQL Server is running short on threads is to use the
> Windows Perfmon.exe & monitor the process object sqlservr instance's
> thread count counter. If the process SQL Server is running has close to
> 255 threads, it might be worth increasing Max Worker Threads.
> In my experience, this is typically when an OLTP application has thousands
> of user connections running, as statistically, this translates to ~255
> connections executing concurrently therefore requiring at least 255
> threads..
> As for the original question, it may help to increase the number of
> workers, but keep in mind that this means that SQL Server will be able to
> schedule that many more threads to perform concurrent work & you need to
> take into account your server resources when making this decision. This is
> a gross simplification, but in my experience increasing Max Worker Threads
> past 255 on systems with fewer than 4 CPUs has rarely helped much as these
> servers usually don't have the capacity to process in increased concurrent
> workload for example. It should hurt much to increase this setting & see
> for yourself whether this helps SQL Server get its work done more
> effieciently or whether it contributes further to the problem by
> increasing context switching etc.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jack Vamvas" <info@.nospam.com> wrote in message
> news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>>
>> The default setting for the 'max worker threads' option is 255,But if the
>> number of user connections surpasses this value the thread pooling will
>> be
>> used. For example, if the maximum number of the user connections to your
>> SQL
>> Server box is equal to 255, you can set the 'max worker threads' options
>> to
>> 255, this frees up resources for SQL Server to use elsewhere. If the
>> maximum
>> number of the user connections to your SQL Server box is equal to 500,
>> you
>> can set the 'max worker threads' options to 500, this can improve SQL
>> Server
>> performance because thread pooling will not be used.
>> In your case , it sounds like it's worth investigating the query plan
>> problem , as increasing the worker threads will take up extra resources
>> on
>> your server
>> Jack Vamvas
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>> We have some SQL Servers that receive high number of connections and
>> stored
>> procedure calls.
>> Every once in a while, we may have a bad query plan and then one stored
>> proc
>> blocks the other stored procs and in no time, we are out of worker
>> threads
>> and no one can connect
>> Have you come across such situations ? If so, how did you handle it ? Is
>> it
>> safe to increase the max worker threads ? What are the consequences ?
>>
>>
>|||And after 5 mins, I looked at it and it was 247. But I did sp_who2 active
and saw just 2 user processes running.
Yes its a busy system with 4000 connections but did not appear i was running
under pressure or am I and dont know about it
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23djxvyt8FHA.1140@.tk2msftngp13.phx.gbl...
>I looked at the thread counter for sqlservr process and it states around
>180.
> What does that mean ?
> Are there 180 threads running concurrently ?
> If so, would that mean that when i run sp_who2 active, i should see a high
> number of connections atleast around 180 or so..
> Thats not the case.
> Can you tell me more ?
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OwBcDBt8FHA.2576@.TK2MSFTNGP12.phx.gbl...
>> Hi Jack
>> The number of connections is not related to the number of worker threads
>> quite as simply as you've described.
>> Connetions only use threads while they're actually executing. When
>> connections are in idle state (usually a majority of the time in typical
>> OLTP apps), there are no threads associated with the connection. This
>> asynchronous design allows any given number of threads to service a far
>> larger number of connections.
>> The way to tell if SQL Server is running short on threads is to use the
>> Windows Perfmon.exe & monitor the process object sqlservr instance's
>> thread count counter. If the process SQL Server is running has close to
>> 255 threads, it might be worth increasing Max Worker Threads.
>> In my experience, this is typically when an OLTP application has
>> thousands of user connections running, as statistically, this translates
>> to ~255 connections executing concurrently therefore requiring at least
>> 255 threads..
>> As for the original question, it may help to increase the number of
>> workers, but keep in mind that this means that SQL Server will be able to
>> schedule that many more threads to perform concurrent work & you need to
>> take into account your server resources when making this decision. This
>> is a gross simplification, but in my experience increasing Max Worker
>> Threads past 255 on systems with fewer than 4 CPUs has rarely helped much
>> as these servers usually don't have the capacity to process in increased
>> concurrent workload for example. It should hurt much to increase this
>> setting & see for yourself whether this helps SQL Server get its work
>> done more effieciently or whether it contributes further to the problem
>> by increasing context switching etc.
>> HTH
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Jack Vamvas" <info@.nospam.com> wrote in message
>> news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>>
>> The default setting for the 'max worker threads' option is 255,But if
>> the
>> number of user connections surpasses this value the thread pooling will
>> be
>> used. For example, if the maximum number of the user connections to your
>> SQL
>> Server box is equal to 255, you can set the 'max worker threads' options
>> to
>> 255, this frees up resources for SQL Server to use elsewhere. If the
>> maximum
>> number of the user connections to your SQL Server box is equal to 500,
>> you
>> can set the 'max worker threads' options to 500, this can improve SQL
>> Server
>> performance because thread pooling will not be used.
>> In your case , it sounds like it's worth investigating the query plan
>> problem , as increasing the worker threads will take up extra resources
>> on
>> your server
>> Jack Vamvas
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>> We have some SQL Servers that receive high number of connections and
>> stored
>> procedure calls.
>> Every once in a while, we may have a bad query plan and then one stored
>> proc
>> blocks the other stored procs and in no time, we are out of worker
>> threads
>> and no one can connect
>> Have you come across such situations ? If so, how did you handle it ?
>> Is
>> it
>> safe to increase the max worker threads ? What are the consequences ?
>>
>>
>>
>|||Hi Hassan
180 threads, means exactly that - there are 180 worker threads in SQL Server
windows process. Most would be worker threads (rather than dedicated system
threads), waiting for queries or i/o work to service. Having 180 threads
doesn't imply any system pressure on its own. It does suggest that, of your
~ 4000 connections, you might typically see peaks of ~ 180 queries actually
running concurrently.
It's possible for the # of threads to exceed connections and vice versa due
to the thread pooling model SQL Server uses in its work scheduling engine
(UMS). There's no directl correlation you can draw to assume that the number
of threads is either higher or lower than the number of connections at any
time. It's perfectly reasonable to see more threads than connections for
example where many connections may have been in use but close. The threads
will hang around in the pool for a while before SQL Server decides to trim
the worker pool. I don't have any info on exactly how or when it does this
trimming, but this is a fairly common design in multi-threaded server
programming..
It's certainly far more common to see the # of threads far lower than the #
of connections though, so if you see the # of threads > connections for
extended periods, there's possibly something wrong.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:uqs1e7t8FHA.3880@.TK2MSFTNGP12.phx.gbl...
> And after 5 mins, I looked at it and it was 247. But I did sp_who2 active
> and saw just 2 user processes running.
> Yes its a busy system with 4000 connections but did not appear i was
> running under pressure or am I and dont know about it
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23djxvyt8FHA.1140@.tk2msftngp13.phx.gbl...
>>I looked at the thread counter for sqlservr process and it states around
>>180.
>> What does that mean ?
>> Are there 180 threads running concurrently ?
>> If so, would that mean that when i run sp_who2 active, i should see a
>> high number of connections atleast around 180 or so..
>> Thats not the case.
>> Can you tell me more ?
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:OwBcDBt8FHA.2576@.TK2MSFTNGP12.phx.gbl...
>> Hi Jack
>> The number of connections is not related to the number of worker threads
>> quite as simply as you've described.
>> Connetions only use threads while they're actually executing. When
>> connections are in idle state (usually a majority of the time in typical
>> OLTP apps), there are no threads associated with the connection. This
>> asynchronous design allows any given number of threads to service a far
>> larger number of connections.
>> The way to tell if SQL Server is running short on threads is to use the
>> Windows Perfmon.exe & monitor the process object sqlservr instance's
>> thread count counter. If the process SQL Server is running has close to
>> 255 threads, it might be worth increasing Max Worker Threads.
>> In my experience, this is typically when an OLTP application has
>> thousands of user connections running, as statistically, this translates
>> to ~255 connections executing concurrently therefore requiring at least
>> 255 threads..
>> As for the original question, it may help to increase the number of
>> workers, but keep in mind that this means that SQL Server will be able
>> to schedule that many more threads to perform concurrent work & you need
>> to take into account your server resources when making this decision.
>> This is a gross simplification, but in my experience increasing Max
>> Worker Threads past 255 on systems with fewer than 4 CPUs has rarely
>> helped much as these servers usually don't have the capacity to process
>> in increased concurrent workload for example. It should hurt much to
>> increase this setting & see for yourself whether this helps SQL Server
>> get its work done more effieciently or whether it contributes further to
>> the problem by increasing context switching etc.
>> HTH
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Jack Vamvas" <info@.nospam.com> wrote in message
>> news:dm9qf9$o1r$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>>
>> The default setting for the 'max worker threads' option is 255,But if
>> the
>> number of user connections surpasses this value the thread pooling will
>> be
>> used. For example, if the maximum number of the user connections to
>> your SQL
>> Server box is equal to 255, you can set the 'max worker threads'
>> options to
>> 255, this frees up resources for SQL Server to use elsewhere. If the
>> maximum
>> number of the user connections to your SQL Server box is equal to 500,
>> you
>> can set the 'max worker threads' options to 500, this can improve SQL
>> Server
>> performance because thread pooling will not be used.
>> In your case , it sounds like it's worth investigating the query plan
>> problem , as increasing the worker threads will take up extra resources
>> on
>> your server
>> Jack Vamvas
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:OJXF8Sk8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>> We have some SQL Servers that receive high number of connections and
>> stored
>> procedure calls.
>> Every once in a while, we may have a bad query plan and then one
>> stored
>> proc
>> blocks the other stored procs and in no time, we are out of worker
>> threads
>> and no one can connect
>> Have you come across such situations ? If so, how did you handle it ?
>> Is
>> it
>> safe to increase the max worker threads ? What are the consequences ?
>>
>>
>>
>>
>