Collation Changed after restore SQL7 DB into SQL2000 DB

Hi Good Day everybody.
Thanks a lot for Hari and I managed resolved the problem on restore SQL7 DB
into SQL2000 DB.
I am encountered the problem is the default collation was changed after I
restore the data from SQL7 into SQL2000. Meaning that when I resotred from
SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS" and not
"Latin1_General_CI_AS".
a) Default collation for SQL7 DB Server is:
"SQL_Latin1_General_CP1_CI_AS".
b) Default collation for SQL2000 DB Server is (this is required collation
for the new upgrade and use by application):
" Latin1_General_CI_AS" .
I am wondering whether have any impact to the database if I execute the
command below to change the collation.
ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
Please advise.
Polar Bear
=?Utf-8?B?UG9sYXIgQmVhcg==?= (PolarBear@.discussions.microsoft.com) writes:
> Hi Good Day everybody.
> Thanks a lot for Hari and I managed resolved the problem on restore SQL7
> DB into SQL2000 DB.
> I am encountered the problem is the default collation was changed after
> I restore the data from SQL7 into SQL2000. Meaning that when I resotred
> from SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS"
> and not "Latin1_General_CI_AS".
That is because the sortorder in the SQL 7 corresponds to SQL collations
in SQL 2000. There is nothing corresponding to the Windows collations in
SQL 7.

> I am wondering whether have any impact to the database if I execute the
> command below to change the collation.
> ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
The immediate impact of the change is little. New tables and columns
will use that collation, as will variables in stored procedures etc.
However, existing tables will not, but will retain the SQL collation.
Thus a query like:
SELECT ... WHERE col = @.value
could fail with a collation conflict.
Most likely you want to change the collation throughout the database.
In this case, you need to bulk out the data, build a new database
from scripts, and bulk data back.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Collation Changed after restore SQL7 DB into SQL2000 DB

Hi Good Day everybody.
Thanks a lot for Hari and I managed resolved the problem on restore SQL7 DB
into SQL2000 DB.
I am encountered the problem is the default collation was changed after I
restore the data from SQL7 into SQL2000. Meaning that when I resotred from
SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS" and not
"Latin1_General_CI_AS".
a) Default collation for SQL7 DB Server is:
"SQL_Latin1_General_CP1_CI_AS".
b) Default collation for SQL2000 DB Server is (this is required collation
for the new upgrade and use by application):
" Latin1_General_CI_AS" .
I am wondering whether have any impact to the database if I execute the
command below to change the collation.
ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
Please advise.
Polar Bearexamnotes (PolarBear@.discussions.microsoft.com) writes:
> Hi Good Day everybody.
> Thanks a lot for Hari and I managed resolved the problem on restore SQL7
> DB into SQL2000 DB.
> I am encountered the problem is the default collation was changed after
> I restore the data from SQL7 into SQL2000. Meaning that when I resotred
> from SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS"
> and not "Latin1_General_CI_AS".
That is because the sortorder in the SQL 7 corresponds to SQL collations
in SQL 2000. There is nothing corresponding to the Windows collations in
SQL 7.

> I am wondering whether have any impact to the database if I execute the
> command below to change the collation.
> ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
The immediate impact of the change is little. New tables and columns
will use that collation, as will variables in stored procedures etc.
However, existing tables will not, but will retain the SQL collation.
Thus a query like:
SELECT ... WHERE col = @.value
could fail with a collation conflict.
Most likely you want to change the collation throughout the database.
In this case, you need to bulk out the data, build a new database
from scripts, and bulk data back.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Collation Changed after restore SQL7 DB into SQL2000 DB

Hi Good Day everybody.
Thanks a lot for Hari and I managed resolved the problem on restore SQL7 DB
into SQL2000 DB.
I am encountered the problem is the default collation was changed after I
restore the data from SQL7 into SQL2000. Meaning that when I resotred from
SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS" and not
"Latin1_General_CI_AS".
a) Default collation for SQL7 DB Server is:
"SQL_Latin1_General_CP1_CI_AS".
b) Default collation for SQL2000 DB Server is (this is required collation
for the new upgrade and use by application):
" Latin1_General_CI_AS" .
I am wondering whether have any impact to the database if I execute the
command below to change the collation.
ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
Please advise.
Polar Bear=?Utf-8?B?UG9sYXIgQmVhcg==?= (PolarBear@.discussions.microsoft.com) writes:
> Hi Good Day everybody.
> Thanks a lot for Hari and I managed resolved the problem on restore SQL7
> DB into SQL2000 DB.
> I am encountered the problem is the default collation was changed after
> I restore the data from SQL7 into SQL2000. Meaning that when I resotred
> from SQL7 DB the collation is actually is "SQL_Latin1_General_CP1_CI_AS"
> and not "Latin1_General_CI_AS".
That is because the sortorder in the SQL 7 corresponds to SQL collations
in SQL 2000. There is nothing corresponding to the Windows collations in
SQL 7.
> I am wondering whether have any impact to the database if I execute the
> command below to change the collation.
> ALTER DATABASE MyDatabase COLLATE Latin1_General_CI_AS
The immediate impact of the change is little. New tables and columns
will use that collation, as will variables in stored procedures etc.
However, existing tables will not, but will retain the SQL collation.
Thus a query like:
SELECT ... WHERE col = @.value
could fail with a collation conflict.
Most likely you want to change the collation throughout the database.
In this case, you need to bulk out the data, build a new database
from scripts, and bulk data back.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.aspsqlsql

collation & compatibility issues

1) how to check the colllation settings on SQL 7 ?
2) I have 2 SQL Srv - SQL7 and SQL2000.
A client application does the following:
- retrieves data from SQL2000 to the SQL7 for queries.
- data updated in SQL7 is also periodically syn with the SQL2000 srv.
there is no direct user acess with the tables, all is interfaced thru the
client app. So must the SQL Server 2000 compatibility settings (under server
properties) be changed to 7.0 ?
Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
SQL2000 from being accessed by other app.
The WAIT TYPE is usually networkIO for the blocking connection from SQL7 to
SQL2000.
So I suspect could it be collation settings or some other in-compatibility
between the 2 SQL Servers.
>
> 1) how to check the collation settings on SQL 7 ?
The error log will tell you this information.

> 2) I have 2 SQL Srv - SQL7 and SQL2000.
> A client application does the following:
> - retrieves data from SQL2000 to the SQL7 for queries.
> - data updated in SQL7 is also periodically syn with the SQL2000 srv.
> there is no direct user acess with the tables, all is interfaced thru the
> client app. So must the SQL Server 2000 compatibility settings (under
server
> properties) be changed to 7.0 ?
> Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
> SQL2000 from being accessed by other app.
> The WAIT TYPE is usually networkIO for the blocking connection from SQL7
to
> SQL2000.
> So I suspect could it be collation settings or some other in-compatibility
> between the 2 SQL Servers.
You should run a blocker script to identify the top of blocking chains. The
script from this article might help:
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
Eric Crdenas
SQL Server senior support professional

collation & compatibility issues

1) how to check the colllation settings on SQL 7 ?
2) I have 2 SQL Srv - SQL7 and SQL2000.
A client application does the following:
- retrieves data from SQL2000 to the SQL7 for queries.
- data updated in SQL7 is also periodically syn with the SQL2000 srv.
there is no direct user acess with the tables, all is interfaced thru the
client app. So must the SQL Server 2000 compatibility settings (under server
properties) be changed to 7.0 ?
Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
SQL2000 from being accessed by other app.
The WAIT TYPE is usually networkIO for the blocking connection from SQL7 to
SQL2000.
So I suspect could it be collation settings or some other in-compatibility
between the 2 SQL Servers.>
> 1) how to check the collation settings on SQL 7 ?
--
The error log will tell you this information.

> 2) I have 2 SQL Srv - SQL7 and SQL2000.
> A client application does the following:
> - retrieves data from SQL2000 to the SQL7 for queries.
> - data updated in SQL7 is also periodically syn with the SQL2000 srv.
> there is no direct user acess with the tables, all is interfaced thru the
> client app. So must the SQL Server 2000 compatibility settings (under
server
> properties) be changed to 7.0 ?
> Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
> SQL2000 from being accessed by other app.
> The WAIT TYPE is usually networkIO for the blocking connection from SQL7
to
> SQL2000.
> So I suspect could it be collation settings or some other in-compatibility
> between the 2 SQL Servers.
--
You should run a blocker script to identify the top of blocking chains. The
script from this article might help:
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
Eric Crdenas
SQL Server senior support professional

collation & compatibility issues

1) how to check the colllation settings on SQL 7 ?
2) I have 2 SQL Srv - SQL7 and SQL2000.
A client application does the following:
- retrieves data from SQL2000 to the SQL7 for queries.
- data updated in SQL7 is also periodically syn with the SQL2000 srv.
there is no direct user acess with the tables, all is interfaced thru the
client app. So must the SQL Server 2000 compatibility settings (under server
properties) be changed to 7.0 ?
Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
SQL2000 from being accessed by other app.
The WAIT TYPE is usually networkIO for the blocking connection from SQL7 to
SQL2000.
So I suspect could it be collation settings or some other in-compatibility
between the 2 SQL Servers.>
> 1) how to check the collation settings on SQL 7 ?
--
The error log will tell you this information.
> 2) I have 2 SQL Srv - SQL7 and SQL2000.
> A client application does the following:
> - retrieves data from SQL2000 to the SQL7 for queries.
> - data updated in SQL7 is also periodically syn with the SQL2000 srv.
> there is no direct user acess with the tables, all is interfaced thru the
> client app. So must the SQL Server 2000 compatibility settings (under
server
> properties) be changed to 7.0 ?
> Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
> SQL2000 from being accessed by other app.
> The WAIT TYPE is usually networkIO for the blocking connection from SQL7
to
> SQL2000.
> So I suspect could it be collation settings or some other in-compatibility
> between the 2 SQL Servers.
--
You should run a blocker script to identify the top of blocking chains. The
script from this article might help:
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
--
Eric Cárdenas
SQL Server senior support professional

coexistence

can sql2000 and sql express 2005 coexist on the same machine?

if so, are there any rules or restrictions I must follow when installing sql express?

TIA!

Just specify another instance name and another port (if using TCP/IP), thats all.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

If you have control over such things, make sure to install SQL 2000 before SQL 2005. There is an issue with SQL Browser and instance discovery when you install them in the wrong order. Check out the KB at http://support.microsoft.com/kb/905618/en-us for details.

Mike

code for re-index

hi,
SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql code.
DECLARE db_cursor CURSOR
FOR
SELECT name FROM master..sysdatabases where name not like 'test%' and
dbid>4 order by 1
OPEN db_cursor
......
DECLARE @.dbname sysname
set @.sql = 'use ' + @.dbname + char(13) + 'GO'
exec ( @.SQL)
I declare table cursor to run the reindex ...
It's re-indexing the same db(first one)... don't know why?
any ideas?
Thanks
Hi
EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
Note it uses undocumented stored procedure just be aware
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>
|||Thanks,
How do I find out if it's running table by table... Can I print something>
Thanjks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%234jJEXMuHHA.4504@.TK2MSFTNGP05.phx.gbl...
> Hi
> EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
> Note it uses undocumented stored procedure just be aware
>
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
>
|||How about below?
EXEC sp_msForEachTable @.COMMAND1= 'PRINT ''?'' DBCC DBREINDEX ( "?") PRINT '' '''
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:Ol0FSnMuHHA.1052@.TK2MSFTNGP05.phx.gbl...
> Thanks,
> How do I find out if it's running table by table... Can I print something>
>
> Thanjks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%234jJEXMuHHA.4504@.TK2MSFTNGP05.phx.gbl...
>
|||works now--thanks a lot
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>
|||See BOL for DBCC SHOWCONTIG. There is a script there for what you want to
do.
TheSQLGuru
President
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>

code for re-index

hi,
SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql code.
DECLARE db_cursor CURSOR
FOR
SELECT name FROM master..sysdatabases where name not like 'test%' and
dbid>4 order by 1
OPEN db_cursor
......
DECLARE @.dbname sysname
set @.sql = 'use ' + @.dbname + char(13) + 'GO'
exec ( @.SQL)
I declare table cursor to run the reindex ...
It's re-indexing the same db(first one)... don't know why?
any ideas?
ThanksHi
EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
Note it uses undocumented stored procedure just be aware
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>|||Thanks,
How do I find out if it's running table by table... Can I print something>
Thanjks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%234jJEXMuHHA.4504@.TK2MSFTNGP05.phx.gbl...
> Hi
> EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
> Note it uses undocumented stored procedure just be aware
>
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
>> hi,
>> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
>> code.
>>
>> DECLARE db_cursor CURSOR
>> FOR
>> SELECT name FROM master..sysdatabases where name not like 'test%'
>> and dbid>4 order by 1
>> OPEN db_cursor
>> ......
>> DECLARE @.dbname sysname
>> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
>> exec ( @.SQL)
>> I declare table cursor to run the reindex ...
>> It's re-indexing the same db(first one)... don't know why?
>> any ideas?
>> Thanks
>|||How about below?
EXEC sp_msForEachTable @.COMMAND1= 'PRINT ''?'' DBCC DBREINDEX ( "?") PRINT '' '''
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:Ol0FSnMuHHA.1052@.TK2MSFTNGP05.phx.gbl...
> Thanks,
> How do I find out if it's running table by table... Can I print something>
>
> Thanjks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%234jJEXMuHHA.4504@.TK2MSFTNGP05.phx.gbl...
>> Hi
>> EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
>> Note it uses undocumented stored procedure just be aware
>>
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
>> news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
>> hi,
>> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
>> code.
>>
>> DECLARE db_cursor CURSOR
>> FOR
>> SELECT name FROM master..sysdatabases where name not like 'test%'
>> and dbid>4 order by 1
>> OPEN db_cursor
>> ......
>> DECLARE @.dbname sysname
>> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
>> exec ( @.SQL)
>> I declare table cursor to run the reindex ...
>> It's re-indexing the same db(first one)... don't know why?
>> any ideas?
>> Thanks
>>
>|||works now--thanks a lot
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>|||See BOL for DBCC SHOWCONTIG. There is a script there for what you want to
do.
--
TheSQLGuru
President
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>

code for re-index

hi,
SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql code.
DECLARE db_cursor CURSOR
FOR
SELECT name FROM master..sysdatabases where name not like 'test%' and
dbid>4 order by 1
OPEN db_cursor
......
DECLARE @.dbname sysname
set @.sql = 'use ' + @.dbname + char(13) + 'GO'
exec ( @.SQL)
I declare table cursor to run the reindex ...
It's re-indexing the same db(first one)... don't know why?
any ideas?
ThanksHi
EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
Note it uses undocumented stored procedure just be aware
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>|||Thanks,
How do I find out if it's running table by table... Can I print something>
Thanjks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%234jJEXMuHHA.4504@.TK2MSFTNGP05.phx.gbl...
> Hi
> EXEC sp_msForEachTable @.COMMAND1= 'DBCC DBREINDEX ( "?")'
> Note it uses undocumented stored procedure just be aware
>
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
>|||How about below?
EXEC sp_msForEachTable @.COMMAND1= 'PRINT ''?'' DBCC DBREINDEX ( "?") PRINT '
' '''
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:Ol0FSnMuHHA.1052@.TK2MSFTNGP05.phx.gbl...[v
bcol=seagreen]
> Thanks,
> How do I find out if it's running table by table... Can I print something>
>
> Thanjks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%234jJEXMuHHA.4504@.TK2MSFTNGP05.phx.gbl...
>[/vbcol]|||works now--thanks a lot
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>|||See BOL for DBCC SHOWCONTIG. There is a script there for what you want to
do.
TheSQLGuru
President
Indicium Resources, Inc.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:u6OAYSMuHHA.768@.TK2MSFTNGP04.phx.gbl...
> hi,
> SQL2000 with > 20 DBs, I need all dbs to be reindex-ed weekly by t-sql
> code.
>
> DECLARE db_cursor CURSOR
> FOR
> SELECT name FROM master..sysdatabases where name not like 'test%'
> and dbid>4 order by 1
> OPEN db_cursor
> ......
> DECLARE @.dbname sysname
> set @.sql = 'use ' + @.dbname + char(13) + 'GO'
> exec ( @.SQL)
> I declare table cursor to run the reindex ...
> It's re-indexing the same db(first one)... don't know why?
> any ideas?
> Thanks
>

ClusterService Log On Account and SQL2000

It is my understanding that this Active Directory account takes on the
role(s) of SQL's BuiltIn Administrator in a clustered environment.
Should the ClusterService Log On account password be reset in AD, without
reseting in the properties tab for the service on the physical nodes cluster
fail overs can still occur because the ClusterService has already been
started. BUT, what activity in SQL2000 could the AD account be doing on
behalf of the SQL BuiltIn Administrator account that would affect the
cluster and initiate a failover if it could not authenticate on the Domain?
Dave
That is not right. By default (God, I hate Microsoft's defaults), the
Cluster Service MUST BE a member of the Local Administrators group on each
cluster node, by which it gains access to the SQL Server installations
through the default BUILTIN\Administrators membership to the SQL Server
system admin default server role.
If you follow the security best practices, and you should, you will remove
the BUILTIN\Administrators group for the installation; however, the Cluster
Service account will still need access to run the resource monitor "Is
Alive" check, which, in the case for the SQL Server resource, is nothing but
the SELECT @.@.SERVERNAME query; thus, the Cluster Service account merely
needs access to the installation, but no special rights beyond this, as the
guest database user in the master database has permissions to execute this
query.
Sincerely,
Anthony Thomas

"David Currie" <decurrie@.rogers.com> wrote in message
news:%23aT3qmQBHHA.204@.TK2MSFTNGP04.phx.gbl...
> It is my understanding that this Active Directory account takes on the
> role(s) of SQL's BuiltIn Administrator in a clustered environment.
> Should the ClusterService Log On account password be reset in AD, without
> reseting in the properties tab for the service on the physical nodes
cluster
> fail overs can still occur because the ClusterService has already been
> started. BUT, what activity in SQL2000 could the AD account be doing on
> behalf of the SQL BuiltIn Administrator account that would affect the
> cluster and initiate a failover if it could not authenticate on the
Domain?
> Dave
>

Clustering Resource depencies

Hello,

With SQL2000 configured with MSCS, we had to stop the SQL service to add additional disk in the SQL Cluster group (in order to use these disks for databases for example).. this was due to the dependencies between the resources.
Do we still have the same issue with SQL2005 running on the cluster? Does the new mountpoint support help in this scenario?
Thanks
Jerome

Yes, the same issue does exist with SQL Server 2005 when adding disks. The new mountpoint support does not really help in this scenario as the mountpoint disk should also be added as a dependent disk to the resource group. It is possible to add the mountpoint without adding the disk to the resource group, but in that case the loss of the disk would not be detected by the cluster service; the SQL Server would attempt to continue running even without the disk (and mountpoint).

Thanks,

Don

Clustered W2K3 Server Memory

Hi,
We have a W2K3 cluster with 3.6Gb memory on each of the 2 servers running
SQL2000 SP3A. We have the /PAE switch on in the boot.ini files on both
nodes. Is SQL2000 able to use more than 2Gb of memory, with this setting, or
do we need the /3GB switch as well with maybe a /USERRVA setting?
Thanks
Chris Wood
Alberta Department of Energy
CANADA/PAE will only be useful if you have over 4Gb Memory . . unfortunately this
switch is redundant is this situation given your memory configuration.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:ez6Nyiq8EHA.2060@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have a W2K3 cluster with 3.6Gb memory on each of the 2 servers running
> SQL2000 SP3A. We have the /PAE switch on in the boot.ini files on both
> nodes. Is SQL2000 able to use more than 2Gb of memory, with this setting,
or
> do we need the /3GB switch as well with maybe a /USERRVA setting?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>|||Yes the /3GB would be required if you wanted to use more than 2GB for SQL
Server. The /PAE is not used until you have > 4GB.
--
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:ez6Nyiq8EHA.2060@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have a W2K3 cluster with 3.6Gb memory on each of the 2 servers running
> SQL2000 SP3A. We have the /PAE switch on in the boot.ini files on both
> nodes. Is SQL2000 able to use more than 2Gb of memory, with this setting,
> or
> do we need the /3GB switch as well with maybe a /USERRVA setting?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>|||Andrew,
Just want to make sure of this as some of our Windows people think that W2K3
is different than W2K for using /3Gb switch.
I have reviewed a number of KB article and it is as clear as mud but I
believe that the /3Gb switch works the same way as in W2K and we would need
it in Boot.Ini for SQL to use more than 2Gb.
Thanks
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ufFb8es8EHA.1392@.tk2msftngp13.phx.gbl...
> Yes the /3GB would be required if you wanted to use more than 2GB for SQL
> Server. The /PAE is not used until you have > 4GB.
> --
> Andrew J. Kelly SQL MVP
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:ez6Nyiq8EHA.2060@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > We have a W2K3 cluster with 3.6Gb memory on each of the 2 servers
running
> > SQL2000 SP3A. We have the /PAE switch on in the boot.ini files on both
> > nodes. Is SQL2000 able to use more than 2Gb of memory, with this
setting,
> > or
> > do we need the /3GB switch as well with maybe a /USERRVA setting?
> >
> > Thanks
> >
> > Chris Wood
> > Alberta Department of Energy
> > CANADA
> >
> >
>|||I am actually having a brain fart<g> at the moment as I can't recall what I
want to answer you properly. There is something about Win2K3 that it does
automatically but I think it is the setting of the /PAE switch and not /3GB.
I believe the /3GB is still required to be set manually.
--
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uF$n$w08EHA.3700@.tk2msftngp13.phx.gbl...
> Andrew,
> Just want to make sure of this as some of our Windows people think that
> W2K3
> is different than W2K for using /3Gb switch.
> I have reviewed a number of KB article and it is as clear as mud but I
> believe that the /3Gb switch works the same way as in W2K and we would
> need
> it in Boot.Ini for SQL to use more than 2Gb.
> Thanks
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ufFb8es8EHA.1392@.tk2msftngp13.phx.gbl...
>> Yes the /3GB would be required if you wanted to use more than 2GB for SQL
>> Server. The /PAE is not used until you have > 4GB.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:ez6Nyiq8EHA.2060@.TK2MSFTNGP10.phx.gbl...
>> > Hi,
>> >
>> > We have a W2K3 cluster with 3.6Gb memory on each of the 2 servers
> running
>> > SQL2000 SP3A. We have the /PAE switch on in the boot.ini files on both
>> > nodes. Is SQL2000 able to use more than 2Gb of memory, with this
> setting,
>> > or
>> > do we need the /3GB switch as well with maybe a /USERRVA setting?
>> >
>> > Thanks
>> >
>> > Chris Wood
>> > Alberta Department of Energy
>> > CANADA
>> >
>> >
>>
>|||Andrew,
If you can remember that would be great. I still have to convince one
analyst that we need the /3Gb switch and not the /PAE switch set.
Thanks
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eAxdW618EHA.1400@.TK2MSFTNGP11.phx.gbl...
> I am actually having a brain fart<g> at the moment as I can't recall what
I
> want to answer you properly. There is something about Win2K3 that it does
> automatically but I think it is the setting of the /PAE switch and not
/3GB.
> I believe the /3GB is still required to be set manually.
> --
> Andrew J. Kelly SQL MVP
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:uF$n$w08EHA.3700@.tk2msftngp13.phx.gbl...
> > Andrew,
> >
> > Just want to make sure of this as some of our Windows people think that
> > W2K3
> > is different than W2K for using /3Gb switch.
> >
> > I have reviewed a number of KB article and it is as clear as mud but I
> > believe that the /3Gb switch works the same way as in W2K and we would
> > need
> > it in Boot.Ini for SQL to use more than 2Gb.
> >
> > Thanks
> >
> > Chris
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:ufFb8es8EHA.1392@.tk2msftngp13.phx.gbl...
> >> Yes the /3GB would be required if you wanted to use more than 2GB for
SQL
> >> Server. The /PAE is not used until you have > 4GB.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> >> news:ez6Nyiq8EHA.2060@.TK2MSFTNGP10.phx.gbl...
> >> > Hi,
> >> >
> >> > We have a W2K3 cluster with 3.6Gb memory on each of the 2 servers
> > running
> >> > SQL2000 SP3A. We have the /PAE switch on in the boot.ini files on
both
> >> > nodes. Is SQL2000 able to use more than 2Gb of memory, with this
> > setting,
> >> > or
> >> > do we need the /3GB switch as well with maybe a /USERRVA setting?
> >> >
> >> > Thanks
> >> >
> >> > Chris Wood
> >> > Alberta Department of Energy
> >> > CANADA
> >> >
> >> >
> >>
> >>
> >
> >
>|||Well if they try it without the /3GB and it doesn't give you more than 2GB
it obviously wont work<g>. I will see if I can dig that up.
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%23P58gmA9EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Andrew,
> If you can remember that would be great. I still have to convince one
> analyst that we need the /3Gb switch and not the /PAE switch set.
> Thanks
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eAxdW618EHA.1400@.TK2MSFTNGP11.phx.gbl...
>> I am actually having a brain fart<g> at the moment as I can't recall what
> I
>> want to answer you properly. There is something about Win2K3 that it
>> does
>> automatically but I think it is the setting of the /PAE switch and not
> /3GB.
>> I believe the /3GB is still required to be set manually.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:uF$n$w08EHA.3700@.tk2msftngp13.phx.gbl...
>> > Andrew,
>> >
>> > Just want to make sure of this as some of our Windows people think that
>> > W2K3
>> > is different than W2K for using /3Gb switch.
>> >
>> > I have reviewed a number of KB article and it is as clear as mud but I
>> > believe that the /3Gb switch works the same way as in W2K and we would
>> > need
>> > it in Boot.Ini for SQL to use more than 2Gb.
>> >
>> > Thanks
>> >
>> > Chris
>> >
>> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> > news:ufFb8es8EHA.1392@.tk2msftngp13.phx.gbl...
>> >> Yes the /3GB would be required if you wanted to use more than 2GB for
> SQL
>> >> Server. The /PAE is not used until you have > 4GB.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> >> news:ez6Nyiq8EHA.2060@.TK2MSFTNGP10.phx.gbl...
>> >> > Hi,
>> >> >
>> >> > We have a W2K3 cluster with 3.6Gb memory on each of the 2 servers
>> > running
>> >> > SQL2000 SP3A. We have the /PAE switch on in the boot.ini files on
> both
>> >> > nodes. Is SQL2000 able to use more than 2Gb of memory, with this
>> > setting,
>> >> > or
>> >> > do we need the /3GB switch as well with maybe a /USERRVA setting?
>> >> >
>> >> > Thanks
>> >> >
>> >> > Chris Wood
>> >> > Alberta Department of Energy
>> >> > CANADA
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Andrew,
I may have found the answer in KB 838765. It mentions under point 6 of
Workaround
Note On a computer that is running Windows Server 2003 the PAE specification
is automatically turned on if the computer is configured to use hot-add
memory devices.
Not sure how I can see if the option is set.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:unegDGB9EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Well if they try it without the /3GB and it doesn't give you more than 2GB
> it obviously wont work<g>. I will see if I can dig that up.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23P58gmA9EHA.3236@.TK2MSFTNGP15.phx.gbl...
> > Andrew,
> >
> > If you can remember that would be great. I still have to convince one
> > analyst that we need the /3Gb switch and not the /PAE switch set.
> >
> > Thanks
> >
> > Chris
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:eAxdW618EHA.1400@.TK2MSFTNGP11.phx.gbl...
> >> I am actually having a brain fart<g> at the moment as I can't recall
what
> > I
> >> want to answer you properly. There is something about Win2K3 that it
> >> does
> >> automatically but I think it is the setting of the /PAE switch and not
> > /3GB.
> >> I believe the /3GB is still required to be set manually.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> >> news:uF$n$w08EHA.3700@.tk2msftngp13.phx.gbl...
> >> > Andrew,
> >> >
> >> > Just want to make sure of this as some of our Windows people think
that
> >> > W2K3
> >> > is different than W2K for using /3Gb switch.
> >> >
> >> > I have reviewed a number of KB article and it is as clear as mud but
I
> >> > believe that the /3Gb switch works the same way as in W2K and we
would
> >> > need
> >> > it in Boot.Ini for SQL to use more than 2Gb.
> >> >
> >> > Thanks
> >> >
> >> > Chris
> >> >
> >> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> > news:ufFb8es8EHA.1392@.tk2msftngp13.phx.gbl...
> >> >> Yes the /3GB would be required if you wanted to use more than 2GB
for
> > SQL
> >> >> Server. The /PAE is not used until you have > 4GB.
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >>
> >> >>
> >> >> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> >> >> news:ez6Nyiq8EHA.2060@.TK2MSFTNGP10.phx.gbl...
> >> >> > Hi,
> >> >> >
> >> >> > We have a W2K3 cluster with 3.6Gb memory on each of the 2 servers
> >> > running
> >> >> > SQL2000 SP3A. We have the /PAE switch on in the boot.ini files on
> > both
> >> >> > nodes. Is SQL2000 able to use more than 2Gb of memory, with this
> >> > setting,
> >> >> > or
> >> >> > do we need the /3GB switch as well with maybe a /USERRVA setting?
> >> >> >
> >> >> > Thanks
> >> >> >
> >> >> > Chris Wood
> >> >> > Alberta Department of Energy
> >> >> > CANADA
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||I was pretty sure this was the case although I don't recall ever seeing the
condition of Hot Add memory devices. In either case I think it is worth
placing the /PAE in the boot.ini so there is no mistaking. If it happens to
be there already so be it but at least someone went through the exercise to
ensure it was set. I am almost 100% sure the /3GB is a manual option.
--
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:eF3yxxN9EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> I may have found the answer in KB 838765. It mentions under point 6 of
> Workaround
> Note On a computer that is running Windows Server 2003 the PAE
> specification
> is automatically turned on if the computer is configured to use hot-add
> memory devices.
> Not sure how I can see if the option is set.
> Chris
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:unegDGB9EHA.3416@.TK2MSFTNGP09.phx.gbl...
>> Well if they try it without the /3GB and it doesn't give you more than
>> 2GB
>> it obviously wont work<g>. I will see if I can dig that up.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:%23P58gmA9EHA.3236@.TK2MSFTNGP15.phx.gbl...
>> > Andrew,
>> >
>> > If you can remember that would be great. I still have to convince one
>> > analyst that we need the /3Gb switch and not the /PAE switch set.
>> >
>> > Thanks
>> >
>> > Chris
>> >
>> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> > news:eAxdW618EHA.1400@.TK2MSFTNGP11.phx.gbl...
>> >> I am actually having a brain fart<g> at the moment as I can't recall
> what
>> > I
>> >> want to answer you properly. There is something about Win2K3 that it
>> >> does
>> >> automatically but I think it is the setting of the /PAE switch and not
>> > /3GB.
>> >> I believe the /3GB is still required to be set manually.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> >> news:uF$n$w08EHA.3700@.tk2msftngp13.phx.gbl...
>> >> > Andrew,
>> >> >
>> >> > Just want to make sure of this as some of our Windows people think
> that
>> >> > W2K3
>> >> > is different than W2K for using /3Gb switch.
>> >> >
>> >> > I have reviewed a number of KB article and it is as clear as mud but
> I
>> >> > believe that the /3Gb switch works the same way as in W2K and we
> would
>> >> > need
>> >> > it in Boot.Ini for SQL to use more than 2Gb.
>> >> >
>> >> > Thanks
>> >> >
>> >> > Chris
>> >> >
>> >> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> >> > news:ufFb8es8EHA.1392@.tk2msftngp13.phx.gbl...
>> >> >> Yes the /3GB would be required if you wanted to use more than 2GB
> for
>> > SQL
>> >> >> Server. The /PAE is not used until you have > 4GB.
>> >> >>
>> >> >> --
>> >> >> Andrew J. Kelly SQL MVP
>> >> >>
>> >> >>
>> >> >> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> >> >> news:ez6Nyiq8EHA.2060@.TK2MSFTNGP10.phx.gbl...
>> >> >> > Hi,
>> >> >> >
>> >> >> > We have a W2K3 cluster with 3.6Gb memory on each of the 2 servers
>> >> > running
>> >> >> > SQL2000 SP3A. We have the /PAE switch on in the boot.ini files on
>> > both
>> >> >> > nodes. Is SQL2000 able to use more than 2Gb of memory, with this
>> >> > setting,
>> >> >> > or
>> >> >> > do we need the /3GB switch as well with maybe a /USERRVA setting?
>> >> >> >
>> >> >> > Thanks
>> >> >> >
>> >> >> > Chris Wood
>> >> >> > Alberta Department of Energy
>> >> >> > CANADA
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||This is a good one!
Here are two registry keys that you can use to assist you:
HKLM\SYSTEM\CurrentControlSet\Control\SystemStartOptions, will tell you
which BOOT.INI configuration was choosen as the current startup.
HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\Memory
Management\PhysicalAddressExtensions, will tell you whether or not the PAE
has been enabled for the current startup.
Now, as to your question regarding SQL Server configuration and Win2K3, /3GB
is still required, but there is a new option, /USERVA that will allow you
more granularity of control, BETWEEN 2GB and 3GB, but it REQUIRES the /3GB
to also be present.
/PAE will only assist you with memory above 4GB. All it does is tell the
kernel to use a 36-bit address space, which would give you the ability to
address UP TO 64GB, instead of the default 32-bit address space, which will
allow you to address up to 4GB. Clearly, a 32-bit address is just a subset
of a 36-bit; so, using a 36-bit address space make no sense unless you
actually use the 33rd bit or higher.
If you are using SQL Server Standard Edition, SS will never create a Buffer
Pool larger than 2 GB, period. However, as I have had this conversation
with those here before, SS also uses another memory segment know as MEM TO
LEAVE, which is everything above the 2 GB limit but less than the USER MODE
address space, minus 512MB x Worker Threads, minus -g startup parameter. UM
will be up to 2GB if you have not enabled the /3GB startup option.
So, for SS SE, with the /3GB option enabled and no use of /USERVA, there
will be a 3GB VIRTUAL UM address space. SS SE will create a Buffer Pool of
2GB, maximum, but MEM TO LEAVE will be around 1GB - 384MB. If you have
sufficient physical ram, then most of this will be real memory, but there
will always be some swap space usage.
Now, for Enterprise Edition. What is different here is the ability to use
the AWE features, but also, the real Buffer Pool can also be extended to the
full UM address space. So, with between 2GB and 4GB of physical memory, you
have two options. You can leave AWE disabled, but extend the real Buffer
Pool up to 3GB, or, you can enable AWE, use up to 3GB for the real Buffer
Pool, minus MEM TO LEAVE, and then use the remainder, up to 1GB, of AWE
address space.
With more than 4GB, then you MUST use PAE, whether or not Win2K3 enables
this for you or not, use the registry settings to determine if it was
detected, but AWE will require PAE to make use of any address space above
4GB. In fact, the OS and SS will not even recognize memory greater than 4GB
even exists without PAE enabled.
Hope this helps.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Oh%23rZCP9EHA.3236@.TK2MSFTNGP15.phx.gbl...
I was pretty sure this was the case although I don't recall ever seeing the
condition of Hot Add memory devices. In either case I think it is worth
placing the /PAE in the boot.ini so there is no mistaking. If it happens to
be there already so be it but at least someone went through the exercise to
ensure it was set. I am almost 100% sure the /3GB is a manual option.
--
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:eF3yxxN9EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> I may have found the answer in KB 838765. It mentions under point 6 of
> Workaround
> Note On a computer that is running Windows Server 2003 the PAE
> specification
> is automatically turned on if the computer is configured to use hot-add
> memory devices.
> Not sure how I can see if the option is set.
> Chris
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:unegDGB9EHA.3416@.TK2MSFTNGP09.phx.gbl...
>> Well if they try it without the /3GB and it doesn't give you more than
>> 2GB
>> it obviously wont work<g>. I will see if I can dig that up.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:%23P58gmA9EHA.3236@.TK2MSFTNGP15.phx.gbl...
>> > Andrew,
>> >
>> > If you can remember that would be great. I still have to convince one
>> > analyst that we need the /3Gb switch and not the /PAE switch set.
>> >
>> > Thanks
>> >
>> > Chris
>> >
>> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> > news:eAxdW618EHA.1400@.TK2MSFTNGP11.phx.gbl...
>> >> I am actually having a brain fart<g> at the moment as I can't recall
> what
>> > I
>> >> want to answer you properly. There is something about Win2K3 that it
>> >> does
>> >> automatically but I think it is the setting of the /PAE switch and not
>> > /3GB.
>> >> I believe the /3GB is still required to be set manually.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> >> news:uF$n$w08EHA.3700@.tk2msftngp13.phx.gbl...
>> >> > Andrew,
>> >> >
>> >> > Just want to make sure of this as some of our Windows people think
> that
>> >> > W2K3
>> >> > is different than W2K for using /3Gb switch.
>> >> >
>> >> > I have reviewed a number of KB article and it is as clear as mud but
> I
>> >> > believe that the /3Gb switch works the same way as in W2K and we
> would
>> >> > need
>> >> > it in Boot.Ini for SQL to use more than 2Gb.
>> >> >
>> >> > Thanks
>> >> >
>> >> > Chris
>> >> >
>> >> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> >> > news:ufFb8es8EHA.1392@.tk2msftngp13.phx.gbl...
>> >> >> Yes the /3GB would be required if you wanted to use more than 2GB
> for
>> > SQL
>> >> >> Server. The /PAE is not used until you have > 4GB.
>> >> >>
>> >> >> --
>> >> >> Andrew J. Kelly SQL MVP
>> >> >>
>> >> >>
>> >> >> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> >> >> news:ez6Nyiq8EHA.2060@.TK2MSFTNGP10.phx.gbl...
>> >> >> > Hi,
>> >> >> >
>> >> >> > We have a W2K3 cluster with 3.6Gb memory on each of the 2 servers
>> >> > running
>> >> >> > SQL2000 SP3A. We have the /PAE switch on in the boot.ini files on
>> > both
>> >> >> > nodes. Is SQL2000 able to use more than 2Gb of memory, with this
>> >> > setting,
>> >> >> > or
>> >> >> > do we need the /3GB switch as well with maybe a /USERRVA setting?
>> >> >> >
>> >> >> > Thanks
>> >> >> >
>> >> >> > Chris Wood
>> >> >> > Alberta Department of Energy
>> >> >> > CANADA
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

Clustered SQL7 upgrade to SQL2000 fails to find default server

We are trying to upgrade to SQL2000 in a failover cluster
environment but the upgrade process is failing to identify
the default server and is instead creating a new named
instance of SQL2000 alongside SQL7 (the 'default' checkbox
on the upgrade wizard is greyed out and unchecked). The
production database is 125Gb so I don't want to use the
copy database wizard if possible. Can anyone suggest how
to get round this? Is it a registry setting?
TIA
JohnI am not sure what process you are taking to do this but here is the proper
sequence:
1. Uncluster SQL Server 7.0 cluster
2. Upgrade SQL Server 7.0 to a SQL Server 2000 default instance (putting
binaries on a local drive)
3. Upgrade the default instance of SQL Server 2000 ti a clustered intance
of SQL Server.
This is documetned in Books on Line:
Upgrading to a SQL SErver 2000 Failover Cluster
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Rand,
this is exactly the process we are trying to follow, but
the 2nd step fails because the upgrade process can't find
the default SQL7 server to upgrade in place and instead
creates a new instance of SQL2000. We have tried this over
and over again, even to the point of completely removing
and reinstalling SQL7. I've had no trouble before with
upgrading a standalone server so it's probably something
to do with the clustering. Any suggestions would be
greatly appreciated...

>--Original Message--
>I am not sure what process you are taking to do this but
here is the proper
>sequence:
>1. Uncluster SQL Server 7.0 cluster
>2. Upgrade SQL Server 7.0 to a SQL Server 2000 default
instance (putting
>binaries on a local drive)
>3. Upgrade the default instance of SQL Server 2000 ti a
clustered intance
>of SQL Server.
>This is documetned in Books on Line:
>Upgrading to a SQL SErver 2000 Failover Cluster
>Rand
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>

Cluster:Win2003_R2_X64_SP1+SQL2000Enterprised_SP4, SQL2000 can not run on NodeB

1、A win2003 Cluster has two node:Node A and NodeB.
2、There are two group on Cluster:
GroupA:ClusterIP,ClusterName,ClusterDisk;
GruopB:SQLServer,DataDisk;
3、The problem is :
GruopA can switch between Node A and NodeB freely;but GruopB can not switch to NodeB,Why?
(1) NodeB's Application event log:
Event ID 17052:[sqsrvres] ODBC sqldriverconnect failed
Event ID 17052:[sqsrvres] checkODBCConnectError:sqlstate=IM002;native error=0;message=[Microsoft][ODBC driver manager]datasource not found and no default driver.

Thanks for your help!Is there an alias for group 2's SQL Server listed in cliconfg.exe? (Client Network Config tool)|||I'm sorry to late.
There is no alias in cliconfg.exe.
I phoned to Microsoft's supporter,we had used tools to fixed ODBC driver,but not function;All so, we had unstalled it,re-install it,no function.The MS's supporter could not find the problem,he thought it's OS's problem.Because the SQL server is an on service server,finally I had to reinstall Win2003.Now it work.
Thanks for MCrowley's advice.Thans everyone.|||Smells like a service pack problem to me. See the previous post (http://www.dbforums.com/showthread.php?t=1612152) on this subject.

-PatP