Query Governor Cost Limit

I have enabled the query governor on our SQL2000 SP2 server with a
threshold of 3600. Now, some of the maintenance jobs fail due to the
limit being to low (e.g. one of the user databases integrity check
fails nightly).

I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' just
before the line in the step which reads 'EXECUTE
master.dbo.xp_sqlmaint N'-Plan etc'
but it has no effect.

Does anyone know how to get around this situation without using
sp_configure to change the query governor settings at a systemwide
level?

GC."Garry Clarke" <gclarke@.euro.banta.com> wrote in message
news:fed38413.0311060139.37e6f333@.posting.google.c om...
> I have enabled the query governor on our SQL2000 SP2 server with a
> threshold of 3600. Now, some of the maintenance jobs fail due to the
> limit being to low (e.g. one of the user databases integrity check
> fails nightly).
> I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' just
> before the line in the step which reads 'EXECUTE
> master.dbo.xp_sqlmaint N'-Plan etc'
> but it has no effect.
> Does anyone know how to get around this situation without using
> sp_configure to change the query governor settings at a systemwide
> level?
> GC.

I'm not entirely sure about this, however xp_sqlmaint is a wrapper for
sqlmaint.exe, and I assume that sqlmaint.exe connects to the server as a
separate connection. According to BOL, the SET option only affects the
current connection, ie. the one where you execute xp_sqlmaint. sqlmaint.exe
is effectively an entirely separate client program.

One workaround would be to write your own maintenance procedures with all
the DBCC commands etc. in a single batch and executing in the same
connection. Or set the server default back to zero, and use the SET option
only in the code that needs it, if that's less work.

Simon|||Thanks - what I've tried to do is use the below commands but it still
ignores the QUERY GOVERNOR COST LIMIT setting so it's back to the
drawing board...

SET QUERY_GOVERNOR_COST_LIMIT 0
exec xp_cmdshell '"C:\Program Files\Microsoft SQL
Server\MSSQL\Binn\sqlmaint.exe" -S SERVER3 -D data -Rpt
e:\mssql\MSSQL\LOG\data_DB_Maintenance_Plan2.txt -DelTxtRpt 4WEEKS
-WriteHistory -CkDB'

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<3faa94e6$1_2@.news.bluewin.ch>...
> "Garry Clarke" <gclarke@.euro.banta.com> wrote in message
> news:fed38413.0311060139.37e6f333@.posting.google.c om...
> > I have enabled the query governor on our SQL2000 SP2 server with a
> > threshold of 3600. Now, some of the maintenance jobs fail due to the
> > limit being to low (e.g. one of the user databases integrity check
> > fails nightly).
> > I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' just
> > before the line in the step which reads 'EXECUTE
> > master.dbo.xp_sqlmaint N'-Plan etc'
> > but it has no effect.
> > Does anyone know how to get around this situation without using
> > sp_configure to change the query governor settings at a systemwide
> > level?
> > GC.
> I'm not entirely sure about this, however xp_sqlmaint is a wrapper for
> sqlmaint.exe, and I assume that sqlmaint.exe connects to the server as a
> separate connection. According to BOL, the SET option only affects the
> current connection, ie. the one where you execute xp_sqlmaint. sqlmaint.exe
> is effectively an entirely separate client program.
> One workaround would be to write your own maintenance procedures with all
> the DBCC commands etc. in a single batch and executing in the same
> connection. Or set the server default back to zero, and use the SET option
> only in the code that needs it, if that's less work.
> Simon

Query for service pack version

How can I get the service pack version from SQL2000?
thanksTry:
select serverproperty ('ProductLevel')
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jeff Kendrick" <Jeff Kendrick@.discussions.microsoft.com> wrote in message
news:EBB3CE7F-8C53-4016-BF83-3E8066BA3755@.microsoft.com...
How can I get the service pack version from SQL2000?
thankssql

Query Design in SQL 2000

Hello everyone,
I need to convert Access 2000 dbs (just tables) to SQL
2000. I am in the process of learning how to work with
tables (import,delete,append,update..etc) I am new at SQL
and like many of us, with little or none time to spend
trying to figure out the online help. So I decided to
write to you asking for help. A few basic but important
questions:
Is there a feature in SQL that allows me to graphically
manipulate tables? Just like with queries in Access...or
do I need to learn SQL language to do so?
How do I set the relations among my tables?
Where do I look for help to write DPS packages to import
csv files and update my tables?
Once I am done moving everything to SQL..is it worth it
to link this tables to Access and keep working with it as
usual?...or it would be detrimental in terms of speed?
The only reason I upgrade to SQL is because my dbs grew
too much for Access to handle and I need to build a Data
Warehouse.
I thank in advance to anybody who can answer all or any
of my questions...or maybe just guide me a little.
Gustavo
(ex-Access-wizard now SQL-dumb)
For imports/export Access > SQL Server,
look into the DTS (data transfer services) in
the manager under Tools,
For Access like Tables, Right click on your
table and "Design"
Quick relationships can be made Access-like
by putting your tables into a "Diagram" and
you can link tables together.
It is amazing how little SQL you really have
to know to use SQL server. It is almost like
Access but all grown up...
I'm sure you'll get other tips/tricks from this
message.
Bob M.
"Gustavo" <anonymous@.discussions.microsoft.com> wrote in message
news:282ed01c46396$28707780$a301280a@.phx.gbl...
> Hello everyone,
> I need to convert Access 2000 dbs (just tables) to SQL
> 2000. I am in the process of learning how to work with
> tables (import,delete,append,update..etc) I am new at SQL
> and like many of us, with little or none time to spend
> trying to figure out the online help. So I decided to
> write to you asking for help. A few basic but important
> questions:
> Is there a feature in SQL that allows me to graphically
> manipulate tables? Just like with queries in Access...or
> do I need to learn SQL language to do so?
> How do I set the relations among my tables?
> Where do I look for help to write DPS packages to import
> csv files and update my tables?
> Once I am done moving everything to SQL..is it worth it
> to link this tables to Access and keep working with it as
> usual?...or it would be detrimental in terms of speed?
> The only reason I upgrade to SQL is because my dbs grew
> too much for Access to handle and I need to build a Data
> Warehouse.
> I thank in advance to anybody who can answer all or any
> of my questions...or maybe just guide me a little.
> Gustavo
> (ex-Access-wizard now SQL-dumb)
>
|||Thanks a lot for the tips Bob. It certainly is the Access
big brother
>--Original Message--
>For imports/export Access > SQL Server,
>look into the DTS (data transfer services) in
>the manager under Tools,
>For Access like Tables, Right click on your
>table and "Design"
>Quick relationships can be made Access-like
>by putting your tables into a "Diagram" and
>you can link tables together.
>It is amazing how little SQL you really have
>to know to use SQL server. It is almost like
>Access but all grown up...
>I'm sure you'll get other tips/tricks from this
>message.
>Bob M.
>
>"Gustavo" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:282ed01c46396$28707780$a301280a@.phx.gbl...
SQL[vbcol=seagreen]
Access...or[vbcol=seagreen]
import[vbcol=seagreen]
as[vbcol=seagreen]
Data
>
>.
>