Changing the OPEN OBJECTS variable

SQL 2000 error in event log... 2 events as follows:
1. OPEN OBJECTS parameter may be set to low
2. Run sp_configure to change parameter.
How can I see what it is set to now? How can I change this value?
TIAThis is documented in Books on line... - search for 'Setting Configuration
Options'
It is fairly unusual ( in my experience) to need to change this in sql
2000...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corp (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"_M_" <here@.gone.com> wrote in message
news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> How can I see what it is set to now? How can I change this value?
> TIA
>|||(1) exec sp_configure
(2) exec sp_configure 'open objects', <new value>
You may also have to RECONFIGURE WITH OVERRIDE after running step 2.
-- Keith
"_M_" <here@.gone.com> wrote in message =news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> > 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> > How can I see what it is set to now? How can I change this value?
> > TIA
> >|||Ran the "exec sp_configure"
Check the value for 'open objects'
Was set to 1000
Ran a query in QA as follows...
Exec sp_configure 'show advanced options', 1
Go
RECONFIGURE WITH OVERRIDE
Go
Exec sp_configure 'open objects', 0
Go
RECONFIGURE WITH OVERRIDE
Go
Re-Ran the "exec sp_configure"
Value is now shown as 0, WHICH IS DYNAMIC, CORRECT '
The thing that bothers me is that it said to run RECONFIGURE 2-3 times in
the output in QA... Did it actually change it, or do I have to run
RECONFIGURE WITH OVERRIDE by itself ?
TIA
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:#JRyCeXbDHA.2412@.TK2MSFTNGP10.phx.gbl...
(1)
exec sp_configure
(2)
exec sp_configure 'open objects', <new value>
You may also have to RECONFIGURE WITH OVERRIDE after running step 2.
--
Keith
"_M_" <here@.gone.com> wrote in message
news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> How can I see what it is set to now? How can I change this value?
> TIA
>|||It prints it event if you run it, as long as you "know" you ran it you can
ignore the extra messages.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"_M_" <here@.gone.com> wrote in message
news:uH1No6YbDHA.2672@.tk2msftngp13.phx.gbl...
Ran the "exec sp_configure"
Check the value for 'open objects'
Was set to 1000
Ran a query in QA as follows...
Exec sp_configure 'show advanced options', 1
Go
RECONFIGURE WITH OVERRIDE
Go
Exec sp_configure 'open objects', 0
Go
RECONFIGURE WITH OVERRIDE
Go
Re-Ran the "exec sp_configure"
Value is now shown as 0, WHICH IS DYNAMIC, CORRECT '
The thing that bothers me is that it said to run RECONFIGURE 2-3 times in
the output in QA... Did it actually change it, or do I have to run
RECONFIGURE WITH OVERRIDE by itself ?
TIA
"Keith Kratochvil" <keith.kratochvil.back2u@.novusprintmedia.com> wrote in
message news:#JRyCeXbDHA.2412@.TK2MSFTNGP10.phx.gbl...
(1)
exec sp_configure
(2)
exec sp_configure 'open objects', <new value>
You may also have to RECONFIGURE WITH OVERRIDE after running step 2.
--
Keith
"_M_" <here@.gone.com> wrote in message
news:ejZi8SWbDHA.2668@.TK2MSFTNGP09.phx.gbl...
> SQL 2000 error in event log... 2 events as follows:
> 1. OPEN OBJECTS parameter may be set to low
> 2. Run sp_configure to change parameter.
> How can I see what it is set to now? How can I change this value?
> TIA
>

Changing ownership on all objects in a DB

Hi,
I have to change ownership of all the tables/storedprocedures/views in a
database.
Can some tell me a good and easy way to do it.
Thnx in advance.I don't know of anything easy.
Take a look at:
select * from information_schema.tables (tables and views)
select * from information_schema.routines (stored procedures)
You could,
open a cursor,
populate it with the names of the tables/views/stored procedures
loop through the cursor
use dynamic SQL to change ownership
Ugly...
Don't forget that you can 2 different objects with the same name and
different owners. This procedure will crash in this case.
Also, if you reference other objects from inside views and stored procedures
using the object owner, this does nothing to solve it.
One easier method to change view/stored procedure owners is to script out
all of them into a text file, do a search and replace on the owner and run
the script in Query Analyser. You could use this method to solde the
"referenced object owner" problem too. If you don't mind loosing all data in
your tables, you could use this method on tables.
For the tables, take a look at the "undocumented" stored procedure
sp_MSforeachtable. Google it for more details.
"dotnettester" <dotnettester@.discussions.microsoft.com> wrote in message
news:8C1ECABA-5302-4D34-93BF-015AF2A6916F@.microsoft.com...
> Hi,
> I have to change ownership of all the tables/storedprocedures/views in a
> database.
> Can some tell me a good and easy way to do it.
> Thnx in advance.|||You might want to use the script given here >>
http://weblogs.asp.net/owscott/arch...1/30/65229.aspx
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
"dotnettester" wrote:

> Hi,
> I have to change ownership of all the tables/storedprocedures/views in a
> database.
> Can some tell me a good and easy way to do it.
> Thnx in advance.|||Well, you could start with this script, in Query Analyzer:
DECLARE @.newOwner SYSNAME;
SET @.newOwner = 'new_owner_name'; -- I assume you are changing to dbo?
IF USER_ID(@.newOwner) IS NOT NULL
BEGIN
SELECT 'EXEC sp_changeobjectowner
'''+QUOTENAME(name)+''','''+@.newOwner+''
''
FROM sysobjects
WHERE xtype IN ('P','U','IF','FN','TR','V')
AND uid != USER_ID(@.newOwner);
END
ELSE
RAISERROR('User %s does not exist.', 11, 1, @.newOwner);
This will yield a script in the bottom pane that you can copy and paste to
the top pane, syntax check, prune, and execute.
"dotnettester" <dotnettester@.discussions.microsoft.com> wrote in message
news:8C1ECABA-5302-4D34-93BF-015AF2A6916F@.microsoft.com...
> Hi,
> I have to change ownership of all the tables/storedprocedures/views in a
> database.
> Can some tell me a good and easy way to do it.
> Thnx in advance.

Changing owner on a SQLServer DB ?

We have received a DB from one of our customer.
We want to change the DB and tables (and all objects) owner to sa
How can we do that ?
ThanksYou could use the procedures 'sp_changedbowner' and 'sp_changeobjectowner' to change the owner for the database and objects (tables, views, etc) resp.|||can I do that in 1 commands for all tables (I have more 100 tables !!!) ?|||As far as I know there is no standard procedure that changes the owner of all objects to another.|||Databases are owned by a SQL Server login. Objects within the database are owned by a database user. I strongly suspect that when you change the database owner login to sa, all of the objects are probably owned by dbo so they will appear as though they are owned by the sa login.

-PatP

Changing owner of DB objects

I have a database where all objects within it and owned by a user not dbo.
Is there any way I can change all the object ownerships to dbo? I'm talking
thousands of objects so I'm thinking some form of script.
thanks
GavGav
SELECT 'EXEC sp_changeobjectowner '''+ROUTINE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMsShipped')=0
AND ROUTINE_SCHEMA != 'dbo'
"Gav" <gav@.nospam.com> wrote in message
news:455c2cce$0$696$88260bb3@.news.teranews.com...
>I have a database where all objects within it and owned by a user not dbo.
>Is there any way I can change all the object ownerships to dbo? I'm talking
>thousands of objects so I'm thinking some form of script.
> thanks
> Gav
>|||What version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gav" <gav@.nospam.com> wrote in message news:455c2cce$0$696$88260bb3@.news.teranews.com...[vb
col=seagreen]
>I have a database where all objects within it and owned by a user not dbo.
Is there any way I can
>change all the object ownerships to dbo? I'm talking thousands of objects s
o I'm thinking some form
>of script.
> thanks
> Gav
>[/vbcol]|||Sorry, forgot the obvious. :o) 2000 SP3a.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eVnJq0WCHHA.3604@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gav" <gav@.nospam.com> wrote in message
> news:455c2cce$0$696$88260bb3@.news.teranews.com...
>|||See Uri's post (sp_changeobjectowner).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gav" <gav@.nospam.com> wrote in message news:455c42c8$0$709$88260bb3@.news.teranews.com...[vb
col=seagreen]
> Sorry, forgot the obvious. :o) 2000 SP3a.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eVnJq0WCHHA.3604@.TK2MSFTNGP03.phx.gbl...
>[/vbcol]

Changing owner of DB objects

I have a database where all objects within it and owned by a user not dbo.
Is there any way I can change all the object ownerships to dbo? I'm talking
thousands of objects so I'm thinking some form of script.
thanks
Gav
Gav
SELECT 'EXEC sp_changeobjectowner '''+ROUTINE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTIN E_NAME),
'IsMsShipped')=0
AND ROUTINE_SCHEMA != 'dbo'
"Gav" <gav@.nospam.com> wrote in message
news:455c2cce$0$696$88260bb3@.news.teranews.com...
>I have a database where all objects within it and owned by a user not dbo.
>Is there any way I can change all the object ownerships to dbo? I'm talking
>thousands of objects so I'm thinking some form of script.
> thanks
> Gav
>
|||Sorry, forgot the obvious. :o) 2000 SP3a.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eVnJq0WCHHA.3604@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gav" <gav@.nospam.com> wrote in message
> news:455c2cce$0$696$88260bb3@.news.teranews.com...
>

Changing owner of DB objects

I have a database where all objects within it and owned by a user not dbo.
Is there any way I can change all the object ownerships to dbo? I'm talking
thousands of objects so I'm thinking some form of script.
thanks
GavGav
SELECT 'EXEC sp_changeobjectowner '''+ROUTINE_NAME+''',''dbo'''
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTINE_NAME),
'IsMsShipped')=0
AND ROUTINE_SCHEMA != 'dbo'
"Gav" <gav@.nospam.com> wrote in message
news:455c2cce$0$696$88260bb3@.news.teranews.com...
>I have a database where all objects within it and owned by a user not dbo.
>Is there any way I can change all the object ownerships to dbo? I'm talking
>thousands of objects so I'm thinking some form of script.
> thanks
> Gav
>|||What version of SQL Server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gav" <gav@.nospam.com> wrote in message news:455c2cce$0$696$88260bb3@.news.teranews.com...
>I have a database where all objects within it and owned by a user not dbo. Is there any way I can
>change all the object ownerships to dbo? I'm talking thousands of objects so I'm thinking some form
>of script.
> thanks
> Gav
>|||Sorry, forgot the obvious. :o) 2000 SP3a.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eVnJq0WCHHA.3604@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gav" <gav@.nospam.com> wrote in message
> news:455c2cce$0$696$88260bb3@.news.teranews.com...
>>I have a database where all objects within it and owned by a user not dbo.
>>Is there any way I can change all the object ownerships to dbo? I'm
>>talking thousands of objects so I'm thinking some form of script.
>> thanks
>> Gav
>|||See Uri's post (sp_changeobjectowner).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gav" <gav@.nospam.com> wrote in message news:455c42c8$0$709$88260bb3@.news.teranews.com...
> Sorry, forgot the obvious. :o) 2000 SP3a.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eVnJq0WCHHA.3604@.TK2MSFTNGP03.phx.gbl...
>> What version of SQL Server?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Gav" <gav@.nospam.com> wrote in message news:455c2cce$0$696$88260bb3@.news.teranews.com...
>>I have a database where all objects within it and owned by a user not dbo. Is there any way I can
>>change all the object ownerships to dbo? I'm talking thousands of objects so I'm thinking some
>>form of script.
>> thanks
>> Gav
>>
>

Changing object ownership back to dbo

Hi:
I have a database where objects are owned by different users. I would like
to revert ownership of all objects to dbo. What is the best way to do this?
Thanks,
Charliesp_changeobjectowner
David Portas
SQL Server MVP
--
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:Oe9b3gfzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi:
> I have a database where objects are owned by different users. I would
> like
> to revert ownership of all objects to dbo. What is the best way to do
> this?
> Thanks,
> Charlie
>|||Hi,
Use the below script. Execute the script by replacing oldobjectowner and new
user. This query will give you script for changing the object.
Just cut and paste the output to a new query analyzer window and execute to
change the object owner.
set quoted_identifier off
select 'sp_changeobjectowner '+name+"'"+'newusername' +"'"+char(10)+'go'
from sysobjects where user_name(uid)='oldobjectowner'
set quoted_identifier on
Thanks
Hari
SQL Server MVP
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:Oe9b3gfzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi:
> I have a database where objects are owned by different users. I would
> like
> to revert ownership of all objects to dbo. What is the best way to do
> this?
> Thanks,
> Charlie
>