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 the internet port number of MS RS Server

I tried to change the MS RS Server port from 80 to let's say 8080. But when
going back to MS RS home page I have an error page from MS RS with the
following message :
"The underlying connection was closed: Unable to connect to the remote
server."
I can't solve this
Could anybody help me.
Regards,
PS: OS is Win2K all Windows Updates, and IIS5Make sure to update the url in RSWebapplication.config to use the new port.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"jcredk" <jcredk@.discussions.microsoft.com> wrote in message
news:2F53430B-20F3-4BB4-A51F-2E60F5074592@.microsoft.com...
> I tried to change the MS RS Server port from 80 to let's say 8080. But
when
> going back to MS RS home page I have an error page from MS RS with the
> following message :
> "The underlying connection was closed: Unable to connect to the remote
> server."
> I can't solve this
> Could anybody help me.
> Regards,
> PS: OS is Win2K all Windows Updates, and IIS5
>|||Hi,
Thanks a lot that was the key I was searching.
Quick test has worked. I was accessing the homepage for reports ..!
"Daniel Reib [MSFT]" wrote:
> Make sure to update the url in RSWebapplication.config to use the new port.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "jcredk" <jcredk@.discussions.microsoft.com> wrote in message
> news:2F53430B-20F3-4BB4-A51F-2E60F5074592@.microsoft.com...
> > I tried to change the MS RS Server port from 80 to let's say 8080. But
> when
> > going back to MS RS home page I have an error page from MS RS with the
> > following message :
> > "The underlying connection was closed: Unable to connect to the remote
> > server."
> >
> > I can't solve this
> >
> > Could anybody help me.
> >
> > Regards,
> >
> > PS: OS is Win2K all Windows Updates, and IIS5
> >
>
>|||I try this,
but now i have a blank default page, with no poosibility ton configure
anything,
i have just Home, My subscription, help menu
no menu to add directory or ton configure security.
I have forgotten something ?
Thanks.
Bruno
"jcredk" wrote:
> Hi,
> Thanks a lot that was the key I was searching.
> Quick test has worked. I was accessing the homepage for reports ..!
> "Daniel Reib [MSFT]" wrote:
> > Make sure to update the url in RSWebapplication.config to use the new port.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "jcredk" <jcredk@.discussions.microsoft.com> wrote in message
> > news:2F53430B-20F3-4BB4-A51F-2E60F5074592@.microsoft.com...
> > > I tried to change the MS RS Server port from 80 to let's say 8080. But
> > when
> > > going back to MS RS home page I have an error page from MS RS with the
> > > following message :
> > > "The underlying connection was closed: Unable to connect to the remote
> > > server."
> > >
> > > I can't solve this
> > >
> > > Could anybody help me.
> > >
> > > Regards,
> > >
> > > PS: OS is Win2K all Windows Updates, and IIS5
> > >
> >
> >
> >|||Did you try to reset iis using command IISReset ?
Most of the times simply restarting IIS does not force IIS to read/reflect
registry or config changes.
I had same problem on CRM server using SRS after changing some of IIS
configuration, this trick resolved it.
"jcredk" wrote:
> I tried to change the MS RS Server port from 80 to let's say 8080. But when
> going back to MS RS home page I have an error page from MS RS with the
> following message :
> "The underlying connection was closed: Unable to connect to the remote
> server."
> I can't solve this
> Could anybody help me.
> Regards,
> PS: OS is Win2K all Windows Updates, and IIS5
>sql

Changing server collation

Hello there
I've tried to change collation of my server using rebuildm
It gave me an error: Rebuild master failed with error: -1
The database was created succeffuly, but the collation hasn't changed.
where can be the problem?
Roy
In that case I'd resinstall server
You change a collation per database/table/column
SELECT
'ALTER TABLE ' + TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME +
' ' + DATA_TYPE +' '+
CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
END
+' COLLATE Put here your colation '+
CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:u$jxwQFQGHA.3256@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I've tried to change collation of my server using rebuildm
> It gave me an error: Rebuild master failed with error: -1
> The database was created succeffuly, but the collation hasn't changed.
> where can be the problem?
>
|||Hi Roy
To add to Uri's post, if rebuildm does not work first time I usually resort
to re-installing as the time to run rebuildm twice is probably more than a
full install.
You may want to set the new collation to default rather than a specific one,
this will pick up the database collation (so change it first). Using '[' and
']' around your object names may also be necessary.
You may also want to check out other posts such as http://tinyurl.com/429qa
John
"Roy Goldhammer" wrote:

> Hello there
> I've tried to change collation of my server using rebuildm
> It gave me an error: Rebuild master failed with error: -1
> The database was created succeffuly, but the collation hasn't changed.
> where can be the problem?
>
>
|||Whell Uri
This procedure has errors due to collation conflict, which it was suppose to
fix.
I've solved it, but it doesn't work if the column has index, constraint or
primary key.
The only way to solve it, is or by changing it with EM if the amount of
cases is realy small, or use generate databases script, and on the query
anlyser remove all the collations.
Is there better way to do this?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ncWUmFQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Roy
> In that case I'd resinstall server
> You change a collation per database/table/column
> SELECT
> 'ALTER TABLE ' + TABLE_NAME +
> ' ALTER COLUMN ' + COLUMN_NAME +
> ' ' + DATA_TYPE +' '+
> CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
> THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
> ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
> END
> +' COLLATE Put here your colation '+
> CASE IS_NULLABLE
> WHEN 'YES' THEN 'NULL'
> WHEN 'No' THEN 'NOT NULL'
> END
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:u$jxwQFQGHA.3256@.TK2MSFTNGP15.phx.gbl...
>
|||Hi Roy
If you profiled what EM was doing you would probably see that it droped
constraints and indexes before issing the same statement. If your source code
is in version control this would not be a big issue. You could use DMO or EM
to script these or the INFORMATION_SCHEMA.KEY_COLUMN_USAGE and sysindexes
tables to generate a script using T-SQL.
John
"Roy Goldhammer" wrote:

> Whell Uri
> This procedure has errors due to collation conflict, which it was suppose to
> fix.
> I've solved it, but it doesn't work if the column has index, constraint or
> primary key.
> The only way to solve it, is or by changing it with EM if the amount of
> cases is realy small, or use generate databases script, and on the query
> anlyser remove all the collations.
> Is there better way to do this?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23ncWUmFQGHA.2628@.TK2MSFTNGP15.phx.gbl...
>
>

Changing server collation

Hello there
I've tried to change collation of my server using rebuildm
It gave me an error: Rebuild master failed with error: -1
The database was created succeffuly, but the collation hasn't changed.
where can be the problem?Roy
In that case I'd resinstall server
You change a collation per database/table/column
SELECT
'ALTER TABLE ' + TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME +
' ' + DATA_TYPE +' '+
CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
END
+' COLLATE Put here your colation '+
CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:u$jxwQFQGHA.3256@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I've tried to change collation of my server using rebuildm
> It gave me an error: Rebuild master failed with error: -1
> The database was created succeffuly, but the collation hasn't changed.
> where can be the problem?
>|||Hi Roy
To add to Uri's post, if rebuildm does not work first time I usually resort
to re-installing as the time to run rebuildm twice is probably more than a
full install.
You may want to set the new collation to default rather than a specific one,
this will pick up the database collation (so change it first). Using '[' and
']' around your object names may also be necessary.
You may also want to check out other posts such as http://tinyurl.com/429qa
John
"Roy Goldhammer" wrote:
> Hello there
> I've tried to change collation of my server using rebuildm
> It gave me an error: Rebuild master failed with error: -1
> The database was created succeffuly, but the collation hasn't changed.
> where can be the problem?
>
>|||Whell Uri
This procedure has errors due to collation conflict, which it was suppose to
fix.
I've solved it, but it doesn't work if the column has index, constraint or
primary key.
The only way to solve it, is or by changing it with EM if the amount of
cases is realy small, or use generate databases script, and on the query
anlyser remove all the collations.
Is there better way to do this?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ncWUmFQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Roy
> In that case I'd resinstall server
> You change a collation per database/table/column
> SELECT
> 'ALTER TABLE ' + TABLE_NAME +
> ' ALTER COLUMN ' + COLUMN_NAME +
> ' ' + DATA_TYPE +' '+
> CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
> THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
> ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
> END
> +' COLLATE Put here your colation '+
> CASE IS_NULLABLE
> WHEN 'YES' THEN 'NULL'
> WHEN 'No' THEN 'NOT NULL'
> END
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:u$jxwQFQGHA.3256@.TK2MSFTNGP15.phx.gbl...
>> Hello there
>> I've tried to change collation of my server using rebuildm
>> It gave me an error: Rebuild master failed with error: -1
>> The database was created succeffuly, but the collation hasn't changed.
>> where can be the problem?
>|||Hi Roy
If you profiled what EM was doing you would probably see that it droped
constraints and indexes before issing the same statement. If your source code
is in version control this would not be a big issue. You could use DMO or EM
to script these or the INFORMATION_SCHEMA.KEY_COLUMN_USAGE and sysindexes
tables to generate a script using T-SQL.
John
"Roy Goldhammer" wrote:
> Whell Uri
> This procedure has errors due to collation conflict, which it was suppose to
> fix.
> I've solved it, but it doesn't work if the column has index, constraint or
> primary key.
> The only way to solve it, is or by changing it with EM if the amount of
> cases is realy small, or use generate databases script, and on the query
> anlyser remove all the collations.
> Is there better way to do this?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23ncWUmFQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> > Roy
> > In that case I'd resinstall server
> > You change a collation per database/table/column
> >
> > SELECT
> > 'ALTER TABLE ' + TABLE_NAME +
> > ' ALTER COLUMN ' + COLUMN_NAME +
> > ' ' + DATA_TYPE +' '+
> > CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
> > THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
> > ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
> > END
> > +' COLLATE Put here your colation '+
> > CASE IS_NULLABLE
> > WHEN 'YES' THEN 'NULL'
> > WHEN 'No' THEN 'NOT NULL'
> > END
> >
> > FROM INFORMATION_SCHEMA.COLUMNS
> > WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')
> >
> >
> >
> > "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> > news:u$jxwQFQGHA.3256@.TK2MSFTNGP15.phx.gbl...
> >> Hello there
> >>
> >> I've tried to change collation of my server using rebuildm
> >>
> >> It gave me an error: Rebuild master failed with error: -1
> >>
> >> The database was created succeffuly, but the collation hasn't changed.
> >>
> >> where can be the problem?
> >>
> >
> >
>
>

Changing server collation

Hello there
I've tried to change collation of my server using rebuildm
It gave me an error: Rebuild master failed with error: -1
The database was created succeffuly, but the collation hasn't changed.
where can be the problem?Roy
In that case I'd resinstall server
You change a collation per database/table/column
SELECT
'ALTER TABLE ' + TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME +
' ' + DATA_TYPE +' '+
CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_L
ENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM
_LENGTH),' ')
END
+' COLLATE Put here your colation '+
CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:u$jxwQFQGHA.3256@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I've tried to change collation of my server using rebuildm
> It gave me an error: Rebuild master failed with error: -1
> The database was created succeffuly, but the collation hasn't changed.
> where can be the problem?
>|||Hi Roy
To add to Uri's post, if rebuildm does not work first time I usually resort
to re-installing as the time to run rebuildm twice is probably more than a
full install.
You may want to set the new collation to default rather than a specific one,
this will pick up the database collation (so change it first). Using '['
and
']' around your object names may also be necessary.
You may also want to check out other posts such as http://tinyurl.com/429qa
John
"Roy Goldhammer" wrote:

> Hello there
> I've tried to change collation of my server using rebuildm
> It gave me an error: Rebuild master failed with error: -1
> The database was created succeffuly, but the collation hasn't changed.
> where can be the problem?
>
>|||Whell Uri
This procedure has errors due to collation conflict, which it was suppose to
fix.
I've solved it, but it doesn't work if the column has index, constraint or
primary key.
The only way to solve it, is or by changing it with EM if the amount of
cases is realy small, or use generate databases script, and on the query
anlyser remove all the collations.
Is there better way to do this?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ncWUmFQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Roy
> In that case I'd resinstall server
> You change a collation per database/table/column
> SELECT
> 'ALTER TABLE ' + TABLE_NAME +
> ' ALTER COLUMN ' + COLUMN_NAME +
> ' ' + DATA_TYPE +' '+
> CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
> THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_L
ENGTH)+')' )
> ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM
_LENGTH),' ')
> END
> +' COLLATE Put here your colation '+
> CASE IS_NULLABLE
> WHEN 'YES' THEN 'NULL'
> WHEN 'No' THEN 'NOT NULL'
> END
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:u$jxwQFQGHA.3256@.TK2MSFTNGP15.phx.gbl...
>|||Hi Roy
If you profiled what EM was doing you would probably see that it droped
constraints and indexes before issing the same statement. If your source cod
e
is in version control this would not be a big issue. You could use DMO or EM
to script these or the INFORMATION_SCHEMA.KEY_COLUMN_USAGE and sysindexes
tables to generate a script using T-SQL.
John
"Roy Goldhammer" wrote:

> Whell Uri
> This procedure has errors due to collation conflict, which it was suppose
to
> fix.
> I've solved it, but it doesn't work if the column has index, constraint or
> primary key.
> The only way to solve it, is or by changing it with EM if the amount of
> cases is realy small, or use generate databases script, and on the query
> anlyser remove all the collations.
> Is there better way to do this?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23ncWUmFQGHA.2628@.TK2MSFTNGP15.phx.gbl...
>
>

Changing schemas already in an xml schema collection

One more question for the day.
I tried to alter the schema collection but only see the "ADD" keyword, which
then gives error about duplicate of same namespace. Seems like their needs
to be a "REPLACE" keyword instead of the ADD?
I also noticed that when you want to change a schema in the collection, you
have apparently have to do the following:
1) alter table/alter column to remove schema collection from column
2) drop schema collection
3) create schema collection
4) alter table/atler column to re-add schema collection
Any dangers in this procedure? Do you mess up the indexes or any
performance problems when you want to change a schema in a collection?
I guess it would be obvious that you could not remove an XML element if that
element is actually stored in the database, but i could see quite often
adding new elements to the schema.
Thanks in advance,
Neal Walters
http://Biztalk-Training.comHi Neal,
Yes, you're right. In SQL Server 2005 there is no replace or delete a schema
from a schema collection. It's popular as a "sought-after for next release"
feature. You may be able to add definitions to an existing schema in a
collection (using ALTER...ADD) as long as they don't redefine existing
types.
Your process seems right, and I'd drop/rebuild the indexes when I dropped
the schema collection from the column/and added the schema collection back
to the column. The documents in the XML column are re-validated when you
alter the table to add the schema collection back, this may take some time,
depending on how many rows you have.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Neal Walters" <NealWalters@.discussions.microsoft.com> wrote in message
news:ACA61EC4-D430-4E81-B4ED-021CD2A8ABB5@.microsoft.com...
> One more question for the day.
> I tried to alter the schema collection but only see the "ADD" keyword,
> which
> then gives error about duplicate of same namespace. Seems like their
> needs
> to be a "REPLACE" keyword instead of the ADD?
> I also noticed that when you want to change a schema in the collection,
> you
> have apparently have to do the following:
> 1) alter table/alter column to remove schema collection from column
> 2) drop schema collection
> 3) create schema collection
> 4) alter table/atler column to re-add schema collection
> Any dangers in this procedure? Do you mess up the indexes or any
> performance problems when you want to change a schema in a collection?
> I guess it would be obvious that you could not remove an XML element if
> that
> element is actually stored in the database, but i could see quite often
> adding new elements to the schema.
> Thanks in advance,
> Neal Walters
> http://Biztalk-Training.com
>|||Bob, Thanks for all your responses!
Neal

Changing schemas already in an xml schema collection

One more question for the day.
I tried to alter the schema collection but only see the "ADD" keyword, which
then gives error about duplicate of same namespace. Seems like their needs
to be a "REPLACE" keyword instead of the ADD?
I also noticed that when you want to change a schema in the collection, you
have apparently have to do the following:
1) alter table/alter column to remove schema collection from column
2) drop schema collection
3) create schema collection
4) alter table/atler column to re-add schema collection
Any dangers in this procedure? Do you mess up the indexes or any
performance problems when you want to change a schema in a collection?
I guess it would be obvious that you could not remove an XML element if that
element is actually stored in the database, but i could see quite often
adding new elements to the schema.
Thanks in advance,
Neal Walters
http://Biztalk-Training.com
Hi Neal,
Yes, you're right. In SQL Server 2005 there is no replace or delete a schema
from a schema collection. It's popular as a "sought-after for next release"
feature. You may be able to add definitions to an existing schema in a
collection (using ALTER...ADD) as long as they don't redefine existing
types.
Your process seems right, and I'd drop/rebuild the indexes when I dropped
the schema collection from the column/and added the schema collection back
to the column. The documents in the XML column are re-validated when you
alter the table to add the schema collection back, this may take some time,
depending on how many rows you have.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Neal Walters" <NealWalters@.discussions.microsoft.com> wrote in message
news:ACA61EC4-D430-4E81-B4ED-021CD2A8ABB5@.microsoft.com...
> One more question for the day.
> I tried to alter the schema collection but only see the "ADD" keyword,
> which
> then gives error about duplicate of same namespace. Seems like their
> needs
> to be a "REPLACE" keyword instead of the ADD?
> I also noticed that when you want to change a schema in the collection,
> you
> have apparently have to do the following:
> 1) alter table/alter column to remove schema collection from column
> 2) drop schema collection
> 3) create schema collection
> 4) alter table/atler column to re-add schema collection
> Any dangers in this procedure? Do you mess up the indexes or any
> performance problems when you want to change a schema in a collection?
> I guess it would be obvious that you could not remove an XML element if
> that
> element is actually stored in the database, but i could see quite often
> adding new elements to the schema.
> Thanks in advance,
> Neal Walters
> http://Biztalk-Training.com
>
|||Bob, Thanks for all your responses!
Neal

changing schema owner through SMO

I'm getting the can't drop user error. Is there a way to change the schema owner via smo? I've tried the following code to change the schema owner back to a different user. I don't get an error or exception but the schema owner doesn't change.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

Never mind, the following worked.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

database.Schemas["db_owner"].Alter();

|||

John, giving that your question is SMO related, I split it from the thread you posted it in and I moved it to the SMO forum.

Thanks
Laurentiu

Changing location of the error log file

I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
Server. When SQL Server was installed the error log file location used was
the default location. That path lists the local share and not the shared
cluster path. Now if the server has to fail over to the other node, SQL
will not start because the other node cannot find the path.
I want to change the error log location in the start up parameter to point
to the shared path that both nodes have access to, but I want to make sure
that making this change will not mean any downtime.
Will making this path change require SQL Server to restart, or anything else
that would mean it would be off line to end users?
Thanks in advance.
NancyYes you need to start and stop the instance to take the change into effect ,
try this only unless you have a problem with the default location's drive or
if error logs outgrow their current directories and you need to move them to
another drive.
SqlServer.exe -eerror_log_path
Refer:
http://www.sql-server-performance.c..._parameters.asp
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Nancy Lytle" wrote:

> I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
> Server. When SQL Server was installed the error log file location used wa
s
> the default location. That path lists the local share and not the shared
> cluster path. Now if the server has to fail over to the other node, SQL
> will not start because the other node cannot find the path.
> I want to change the error log location in the start up parameter to point
> to the shared path that both nodes have access to, but I want to make sure
> that making this change will not mean any downtime.
> Will making this path change require SQL Server to restart, or anything el
se
> that would mean it would be off line to end users?
> Thanks in advance.
> Nancy
>
>

Changing location of the error log file

I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
Server. When SQL Server was installed the error log file location used was
the default location. That path lists the local share and not the shared
cluster path. Now if the server has to fail over to the other node, SQL
will not start because the other node cannot find the path.
I want to change the error log location in the start up parameter to point
to the shared path that both nodes have access to, but I want to make sure
that making this change will not mean any downtime.
Will making this path change require SQL Server to restart, or anything else
that would mean it would be off line to end users?
Thanks in advance.
Nancy
Yes you need to start and stop the instance to take the change into effect ,
try this only unless you have a problem with the default location's drive or
if error logs outgrow their current directories and you need to move them to
another drive.
SqlServer.exe -eerror_log_path
Refer:
http://www.sql-server-performance.co...parameters.asp
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Nancy Lytle" wrote:

> I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
> Server. When SQL Server was installed the error log file location used was
> the default location. That path lists the local share and not the shared
> cluster path. Now if the server has to fail over to the other node, SQL
> will not start because the other node cannot find the path.
> I want to change the error log location in the start up parameter to point
> to the shared path that both nodes have access to, but I want to make sure
> that making this change will not mean any downtime.
> Will making this path change require SQL Server to restart, or anything else
> that would mean it would be off line to end users?
> Thanks in advance.
> Nancy
>
>

Changing location of the error log file

I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
Server. When SQL Server was installed the error log file location used was
the default location. That path lists the local share and not the shared
cluster path. Now if the server has to fail over to the other node, SQL
will not start because the other node cannot find the path.
I want to change the error log location in the start up parameter to point
to the shared path that both nodes have access to, but I want to make sure
that making this change will not mean any downtime.
Will making this path change require SQL Server to restart, or anything else
that would mean it would be off line to end users?
Thanks in advance.
NancyYes you need to start and stop the instance to take the change into effect ,
try this only unless you have a problem with the default location's drive or
if error logs outgrow their current directories and you need to move them to
another drive.
SqlServer.exe -eerror_log_path
Refer:
http://www.sql-server-performance.com/rd_sql_server_startup_parameters.asp
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Nancy Lytle" wrote:
> I am a defacto DBA and we have a 2 node clustered (active/passive) SQL
> Server. When SQL Server was installed the error log file location used was
> the default location. That path lists the local share and not the shared
> cluster path. Now if the server has to fail over to the other node, SQL
> will not start because the other node cannot find the path.
> I want to change the error log location in the start up parameter to point
> to the shared path that both nodes have access to, but I want to make sure
> that making this change will not mean any downtime.
> Will making this path change require SQL Server to restart, or anything else
> that would mean it would be off line to end users?
> Thanks in advance.
> Nancy
>
>

Changing IP of SQL virtual server

Check out KB 244980. There appears to be an error near the bottom where it
talks about changing the subnet mask. After step 10, it says "To make sure
that the SQL server IP address is changed, follow these steps:
a. Check the registry on both nodes for the key that is mentioned in step
11."
I cannot find step 11 anywhere on the page, nor can I find any reference to
a registry key.
Does anyone know this key I should check?
Thanks,
RB
I'd guess the registry key it's referring to is
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\/<instancename>/\Cluster
but that's just a guess (that's where the IP address & network name for
the cluster group resources for the virtual server are stored in the
registry).
*mike hodgson*
http://sqlnerd.blogspot.com
RB wrote:

>Check out KB 244980. There appears to be an error near the bottom where it
>talks about changing the subnet mask. After step 10, it says "To make sure
>that the SQL server IP address is changed, follow these steps:
> a. Check the registry on both nodes for the key that is mentioned in step
>11."
>I cannot find step 11 anywhere on the page, nor can I find any reference to
>a registry key.
>Does anyone know this key I should check?
>Thanks,
>

Changing DB

I'm getting error message "you can't use USE statement in stored procedure
or trigger".
So the problem is using USE statement in stored procedure.
Sorry what I wasn't clear enough.
Thank you. David P.
"Steve Kass" <skass@.drew.edu> wrote in message
news:eOYNeqmyDHA.1932@.TK2MSFTNGP09.phx.gbl...

quote:

---

> David,
> You can't use a parameter after USE, but you can do something like
> this, I believe, if you know the names of all the databases that might
> be chosen:
> if @.db = 'somedatabase'
> use somedatabase
> else if @.db = 'differentdatabase'
> use differentdatabase
> else if ...
> If you wish, you can also return silently or throw an error if the value
> passed for @.db is not one you've taken care of.
> SK
>
>
> David Potahisnsky wrote:
>
database.[QUOTE]
working[QUOTE]
it[QUOTE]
doesn't[QUOTE]
>

---

Sorry - my mistake.
SK
David Potahisnsky wrote:

quote:

---

>I'm getting error message "you can't use USE statement in stored procedure
>or trigger".
>So the problem is using USE statement in stored procedure.
>Sorry what I wasn't clear enough.
>
>Thank you. David P.
>
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:eOYNeqmyDHA.1932@.TK2MSFTNGP09.phx.gbl...
>
>database.
>
>working
>
>it
>
>doesn't
>
>
>

---

Changing Database Owner

When I try to connect to a database from my client/server application
I receive the following error: SQL SERVER ERROR 911 - Server rejected
the connection - access to the selected database has been denied.
When I run sp_helpdb I noticed that the db owner on this database is
different than the owner of the other databases I connect to. How do I
change the owner?
ThanksHi,
Please execute the below system stored procedure
use <dbname> go
sp_changedbowner 'username'
Note:
-- User name is the new owner name
-- dbname is the database name which you need to change the owner
Thanks
Hari
MCDBA
"Jim" <jromano@.phs-us.com> wrote in message
news:e1e1a687.0403261546.7c12e105@.posting.google.com...
> When I try to connect to a database from my client/server application
> I receive the following error: SQL SERVER ERROR 911 - Server rejected
> the connection - access to the selected database has been denied.
> When I run sp_helpdb I noticed that the db owner on this database is
> different than the owner of the other databases I connect to. How do I
> change the owner?
> Thanks