Bug in INFORMATION_SCHEMA.SCHEMATA?

This is SQL Server 2000 with SP3a.
When I do
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
I get only a subset of databases listed. I did check the permission to the
non-listed databases, and without knowing exactly what permission "chain"
the above view uses, the user definitely "has access" to the missing
databases. I tried both with an account with very limited permissions (SQL
Server login), "sa" (SQL Server login) and Integrated Security login from a
Windows account in the (Local) Administrators group on the SQL Server
machine. The login "BUILTIN\Administrators" has SA access, and has
permissions to all the databases.
If I connect as the same user to a different ("unlisted") database, the
current database shows up in the above query, and the one that I was
previously connected to (and therefore showed up) is gone from the results.
I also tried
SELECT HAS_DBACCESS('<unlisted db>')
and received 1 as a result (in QA, while connected as a limited user to
another database - also unlisted).
Most (or all?) of the "unlisted" databases have been created by a script run
in QA and/or with osql.exe (command line parameters). Is there something I
am missing? My script assignes specific privilleges to a role that I
create, as well as to the "public" role.
The assignment of privilleges are done per the following script excerpt:
if not exists (select * from master.dbo.syslogins where loginname =
N'SomeUser')
BEGIN
declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
N'master', @.loginlang = N'us_english'
if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)
select @.logindb = N'master'
if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'us_english')
select @.loginlang = @.@.language
exec sp_addlogin N'SomeUser', null, @.logindb, @.loginlang
END
GO
if not exists (select * from dbo.sysusers where name = N'SomeUser' and uid <
16382)
EXEC sp_grantdbaccess N'SomeUser', N'SomeUser'
GO
exec sp_addrolemember N'db_datareader', N'SomeUser'
GO
exec sp_addrolemember N'db_datawriter', N'SomeUser'
GO
exec sp_addrolemember N'my_app_role', N'SomeUser'
GO
(You may recognize this as (based on) output from EM Generate SQL Script...)
I know I can use master..sysdatabases, but I am really trying to follow MS'
advice not to access the system tables directly...
While I found some similar questions through Google, I couldn't find any
that addressed this as an actual issue - apparently the "don't have access"
was more or less accepted without much protest...
Help, please...
Tore.Is the 'auto close' database option turned on? Apparently, closed database
are not included in this view.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tore" <pointzero@.vbdreamteam.net> wrote in message
news:u1n7AXrRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> This is SQL Server 2000 with SP3a.
> When I do
> SELECT CATALOG_NAME
> FROM INFORMATION_SCHEMA.SCHEMATA
> I get only a subset of databases listed. I did check the permission to
> the non-listed databases, and without knowing exactly what permission
> "chain" the above view uses, the user definitely "has access" to the
> missing databases. I tried both with an account with very limited
> permissions (SQL Server login), "sa" (SQL Server login) and Integrated
> Security login from a Windows account in the (Local) Administrators group
> on the SQL Server machine. The login "BUILTIN\Administrators" has SA
> access, and has permissions to all the databases.
> If I connect as the same user to a different ("unlisted") database, the
> current database shows up in the above query, and the one that I was
> previously connected to (and therefore showed up) is gone from the
> results.
> I also tried
> SELECT HAS_DBACCESS('<unlisted db>')
> and received 1 as a result (in QA, while connected as a limited user to
> another database - also unlisted).
> Most (or all?) of the "unlisted" databases have been created by a script
> run in QA and/or with osql.exe (command line parameters). Is there
> something I am missing? My script assignes specific privilleges to a role
> that I create, as well as to the "public" role.
> The assignment of privilleges are done per the following script excerpt:
> if not exists (select * from master.dbo.syslogins where loginname =
> N'SomeUser')
> BEGIN
> declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
> N'master', @.loginlang = N'us_english'
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
> master.dbo.syslanguages where name = @.loginlang) and @.loginlang <>
> N'us_english')
> select @.loginlang = @.@.language
> exec sp_addlogin N'SomeUser', null, @.logindb, @.loginlang
> END
> GO
> if not exists (select * from dbo.sysusers where name = N'SomeUser' and uid
> < 16382)
> EXEC sp_grantdbaccess N'SomeUser', N'SomeUser'
> GO
> exec sp_addrolemember N'db_datareader', N'SomeUser'
> GO
> exec sp_addrolemember N'db_datawriter', N'SomeUser'
> GO
> exec sp_addrolemember N'my_app_role', N'SomeUser'
> GO
> (You may recognize this as (based on) output from EM Generate SQL
> Script...)
> I know I can use master..sysdatabases, but I am really trying to follow
> MS' advice not to access the system tables directly...
> While I found some similar questions through Google, I couldn't find any
> that addressed this as an actual issue - apparently the "don't have
> access" was more or less accepted without much protest...
> Help, please...
> Tore.
>|||Thanks,
Yes, autoclose is on (which is a reasonable option for these databases).
I guess it's back to the system tables... :-<
Regards,
Tore.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OoS3%23ftRFHA.580@.TK2MSFTNGP15.phx.gbl...
> Is the 'auto close' database option turned on? Apparently, closed
> database are not included in this view.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tore" <pointzero@.vbdreamteam.net> wrote in message
> news:u1n7AXrRFHA.2784@.TK2MSFTNGP12.phx.gbl...
>|||Hi,
The root cause of this is that the DATABASEPROPERTY() and
DATABASEPROPERTYEX() functions return NULL in cases where the database is
off-line or auto-closed. The INFORMATION_SCHEMA.SCHEMATA includes a JOIN
expression on one of these functions; it returns null, and the so the row
is not seen in the result set.
This "Returns NULL" behavior of those functions is documented in the SQL
2005 BOL.
In any case, the INFORMATION_SCHEMA.SCHEMATA view has undergone a breaking
change in SQL 2005. It now returns all the schemas in the current catalog
(database) rather than all catalogs (databases) in the server. This
changes brings our implementation into compliance with the SQL standard.
Sadly, our SQL 2000 implementation of that view was in error. This
breaking change is documented in SQL 2005 BOL and our "Upgrade Analyzer"
will detect it for you.
Unforutnately, the SQL standard does not provide as way to discover all
catalogs (databases) in the server. From the standard's perspective, that
is an "implementation detail." I can tell you that it is OK to use
master.dbo.sysdatabases in your SQL2K application and it will upgrade to
SQL 2005. NB: We have modified the security rules around who can "see" rows
in catalog views in SQL 2005. In this particular case, we've introduced a
new server-level permission called VIEW ANY DATABASE, and it is GRANT-ed to
PUBLIC by default. The net result is that your application should continue
to work after you upgrade to SQL 2005.
IN general, you are OK to use system tables as long as you STAY AWAY FROM
UNDOCUMENTED OR INTERNAL OR RESERVED tables and columns!! We make no
guarantees to support them in SQL 2005.
So, for example, avoid things like:
* sysproperties - undocumented
* sysxlogins - undocumented
* sysdatabases.mode - internal only
* sysdatabases.reserved - reserved
The rule of thumb is this:
If the system table has a topic page devoted to it in the BOL, it is OK.
If the column is documented and it does not say "internal" or "reserved",
it is OK.
Regards,
Cliff Dibble
Program Manager, SQL Server Engine
MSFT
Message posted via http://www.webservertalk.com|||Thanks for the detailed response. I find your hint about how to decide what
to stay away from especially useful.
However, the way I read the SQL Standard definition of
INFORMATION_SCHEMA.SCHEMATA is (quote):
"Identify the schemata that are owned by a given user."
Inspection of the underlying query also indicates that it refers to the
catalog that contains the information schema.
Since the SQL standard allows for adding objects to the INFORMATION_SCHEMA,
it seems that INFORMATION_SCHEMA.DATABASES (or INFORMATION_SCHEMA.CATALOGS?)
would be a prime candidate... :->
Thanks and Regards,
Tore.
"Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:eb54e4cc92aa4ec7bd82c20143fb12f2@.SQ
webservertalk.com...
> Hi,
> The root cause of this is that the DATABASEPROPERTY() and
> DATABASEPROPERTYEX() functions return NULL in cases where the database is
> off-line or auto-closed. The INFORMATION_SCHEMA.SCHEMATA includes a JOIN
> expression on one of these functions; it returns null, and the so the row
> is not seen in the result set.
> This "Returns NULL" behavior of those functions is documented in the SQL
> 2005 BOL.
> In any case, the INFORMATION_SCHEMA.SCHEMATA view has undergone a breaking
> change in SQL 2005. It now returns all the schemas in the current catalog
> (database) rather than all catalogs (databases) in the server. This
> changes brings our implementation into compliance with the SQL standard.
> Sadly, our SQL 2000 implementation of that view was in error. This
> breaking change is documented in SQL 2005 BOL and our "Upgrade Analyzer"
> will detect it for you.
> Unforutnately, the SQL standard does not provide as way to discover all
> catalogs (databases) in the server. From the standard's perspective, that
> is an "implementation detail." I can tell you that it is OK to use
> master.dbo.sysdatabases in your SQL2K application and it will upgrade to
> SQL 2005. NB: We have modified the security rules around who can "see"
> rows
> in catalog views in SQL 2005. In this particular case, we've introduced a
> new server-level permission called VIEW ANY DATABASE, and it is GRANT-ed
> to
> PUBLIC by default. The net result is that your application should
> continue
> to work after you upgrade to SQL 2005.
> IN general, you are OK to use system tables as long as you STAY AWAY FROM
> UNDOCUMENTED OR INTERNAL OR RESERVED tables and columns!! We make no
> guarantees to support them in SQL 2005.
> So, for example, avoid things like:
> * sysproperties - undocumented
> * sysxlogins - undocumented
> * sysdatabases.mode - internal only
> * sysdatabases.reserved - reserved
> The rule of thumb is this:
> If the system table has a topic page devoted to it in the BOL, it is OK.
> If the column is documented and it does not say "internal" or "reserved",
> it is OK.
> Regards,
> Cliff Dibble
> Program Manager, SQL Server Engine
> MSFT
> --
> Message posted via http://www.webservertalk.com|||> Since the SQL standard allows for adding objects to the INFORMATION_SCHEMA, it seems that

> INFORMATION_SCHEMA.DATABASES (or INFORMATION_SCHEMA.CATALOGS?) would be a prime ca
ndidate... :->
But that would make it proprietary, just the same as sysdatabases and the fu
ture sys.databases
are...?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tore" <pointzero at vbdreamteam dot net> wrote in message
news:Oo5FacUSFHA.248@.TK2MSFTNGP15.phx.gbl...
> Thanks for the detailed response. I find your hint about how to decide wh
at to stay away from
> especially useful.
> However, the way I read the SQL Standard definition of INFORMATION_SCHEMA.
SCHEMATA is (quote):
> "Identify the schemata that are owned by a given user."
> Inspection of the underlying query also indicates that it refers to the ca
talog that contains the
> information schema.
> Since the SQL standard allows for adding objects to the INFORMATION_SCHEMA
, it seems that
> INFORMATION_SCHEMA.DATABASES (or INFORMATION_SCHEMA.CATALOGS?) would be a
prime candidate... :->
> Thanks and Regards,
> Tore.
>
> "Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in messa
ge
> news:eb54e4cc92aa4ec7bd82c20143fb12f2@.SQ
webservertalk.com...
>|||Initially we were against adding anything new that was not explicit in the
standard. However, there does appear to be an escape clause in the
standard. See section 4.2, p.8 of Part 11.
"An SQL-implementation may define objects that are associated with
INFORMATION_SCHEMA that are not defined in this Clause. An SQL-
implementation or any future version of ISO/IEC 9075 may also add columns
to tables that are defined in this Clause."
It's not entirely clear how we'd do that without risking some future
incompatibility with the standard. We'd probably have to define some MSFT-
specific naming convention.
In any case, we did not get around to adding I/S customizations in Yukon.
We will consider adding more extensions doing a fuller implementation of
the I/S views in the next product release. The I/S views encapsulate a lot
of JOINs required in the underlying catalog.
Regards,
Cliff Dibble
PM, SQL Engine
Message posted via http://www.webservertalk.com|||Just to close on the topic of system table compatibility in Yukon, here is
the complete list of previously documented columns that are no longer
compatible in Yukon, along with an explanation why. You should avoid
these, too.
view column_name value rationale
========================================
====================================
==
syscomments compressed 0 ctext is always uncompressed in Yukon.
sysdevices size 0 This has been deprecated since SQL2K
sysindexes statblob NULL Security reasons.
sysindexes keys NULL Severe perf hit. The format of the
binary
data was not documented.
sysobjects schema_ver 0 Due to architectural change, can not
guarantee strict monotonicity due to
round
off errors when converting/mapping from
8-byte datetime to 4-byte integer.
Use sys.objects.modify_date instead.
sysoledbusers rmtpassword NULL Security reasons.
sysremotelogins status 0 The format of the options was not
documented.
sysservers topologyy 0 The UI no longer uses this.
There are also a few incompatibilities in sysindexes (which are doc'ed in
the most recent SQL 2005 CTP builds):
a) No more indid=255 due to storage engine changes.
b) The page counters like "dpages" don't row-overflow.
c) The behavior of rowmodctr is not exactly the same due to storage engine
changes (we now carry per-column modification counters).
d) maxirow values are not identical due to storage engine changes.
e) keys column is NULL, as previously mentioned
f) The name column is NULL for heaps because we now have a unique index on
that column and we need to avoid possible name conflicts.
Finally - note that if you use NEW features in Yukon (e.g. partitioning or
any of the new user/schema DDL like CREATE USER), you will absolutely need
to migrate to the new catalog views. For example, sysindexes.root is NULL
for partitioned indexes because each partition has its own B-tree. Another
example:
select * from sysobjects where uid=1 and name = 'X'
might return >1 row due to user/schema separation (suppose dbo owned two
schemas S1 and S2, each of which contained a table named X).
Regards,
Cliff Dibble
PM SQL Engine
Message posted via http://www.webservertalk.com|||That's a good list, Clifford. Any plans to document it? (Or did I miss it in
April CTP BOL?)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:3d4abaedc650452998ebec042f092996@.SQ
webservertalk.com...
> Just to close on the topic of system table compatibility in Yukon, here is
> the complete list of previously documented columns that are no longer
> compatible in Yukon, along with an explanation why. You should avoid
> these, too.
> view column_name value rationale
> ========================================
==================================
====
> syscomments compressed 0 ctext is always uncompressed in Yukon.
> sysdevices size 0 This has been deprecated since SQL2K
> sysindexes statblob NULL Security reasons.
> sysindexes keys NULL Severe perf hit. The format of the
> binary
> data was not documented.
> sysobjects schema_ver 0 Due to architectural change, can not
> guarantee strict monotonicity due to
> round
> off errors when converting/mapping from
> 8-byte datetime to 4-byte integer.
> Use sys.objects.modify_date instead.
> sysoledbusers rmtpassword NULL Security reasons.
> sysremotelogins status 0 The format of the options was not
> documented.
> sysservers topologyy 0 The UI no longer uses this.
> There are also a few incompatibilities in sysindexes (which are doc'ed in
> the most recent SQL 2005 CTP builds):
> a) No more indid=255 due to storage engine changes.
> b) The page counters like "dpages" don't row-overflow.
> c) The behavior of rowmodctr is not exactly the same due to storage engine
> changes (we now carry per-column modification counters).
> d) maxirow values are not identical due to storage engine changes.
> e) keys column is NULL, as previously mentioned
> f) The name column is NULL for heaps because we now have a unique index on
> that column and we need to avoid possible name conflicts.
>
> Finally - note that if you use NEW features in Yukon (e.g. partitioning or
> any of the new user/schema DDL like CREATE USER), you will absolutely need
> to migrate to the new catalog views. For example, sysindexes.root is NUL
L
> for partitioned indexes because each partition has its own B-tree. Anothe
r
> example:
> select * from sysobjects where uid=1 and name = 'X'
> might return >1 row due to user/schema separation (suppose dbo owned two
> schemas S1 and S2, each of which contained a table named X).
> Regards,
> Cliff Dibble
> PM SQL Engine
> --
> Message posted via http://www.webservertalk.com|||> It's not entirely clear how we'd do that without risking some future
> incompatibility with the standard. We'd probably have to define some MSF
T-
> specific naming convention.
My .02: Don't.
I think can see the temptation: "By just adding this column, the view will b
e usable in a lot more
scenarios."
To me, the purpose of these views are that you know you are within the stand
ard. If I would need
something outside these views, I prefer diving into the catalog views or sys
tem tables, as I would
still need a re-write in the unlikely case I need to port the app. Also, the
catalog views have a
much better design than the old style system tables, so ease of use (info sc
hema vs. system tables)
isn't an argument either.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Clifford Dibble via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:8ee5d4d5a35947ec98ee43006ed152cd@.SQ
webservertalk.com...
> Initially we were against adding anything new that was not explicit in the
> standard. However, there does appear to be an escape clause in the
> standard. See section 4.2, p.8 of Part 11.
> "An SQL-implementation may define objects that are associated with
> INFORMATION_SCHEMA that are not defined in this Clause. An SQL-
> implementation or any future version of ISO/IEC 9075 may also add columns
> to tables that are defined in this Clause."
> It's not entirely clear how we'd do that without risking some future
> incompatibility with the standard. We'd probably have to define some MSF
T-
> specific naming convention.
> In any case, we did not get around to adding I/S customizations in Yukon.
> We will consider adding more extensions doing a fuller implementation of
> the I/S views in the next product release. The I/S views encapsulate a lo
t
> of JOINs required in the underlying catalog.
> Regards,
> Cliff Dibble
> PM, SQL Engine
> --
> Message posted via http://www.webservertalk.com

Bug in Copy database routine

I copied some databases from a SQL 2000 server to a SQL 2005 server. All the tables in the databases with auto incrementing identity columns (not for replication) had the identity information removed from the table definition. I used the SQL Management Object method.This is a known issue in SMO that is expected to be fixed in Server Pack 1.

Peter|||So what is the recommended way to copy databases from SQL 2000 to SQL 2005?

Browse through a list of databases on a given server

Hi,
I would like to allow the user of my ADO.NET application to browse through a list of the available databases on a given server. INFORMATION_SCHEMA in SQL Server Books Online doesn't describe how to do this. How can I do this?
TIA,
RoyceSELECT * FROM master..sysdatabases|||In VB6,use traditional adodb object.We can get any level schema infomation of sql server by OpenSchema method.
I pasted VB6 sample codes ,hope it should be helpful to u.

example:
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset

cn.Open "Provider=SQLOLEDB.1;Password="""";Persist Security Info=True;User ID=sa;Data Source=wmcw"
Set rs = cn.OpenSchema(adSchemaCatalogs)

Do While Not rs.EOF
Debug.Print rs.Fields("CATALOG_NAME").Value & vbTab & rs.Fields("DESCRIPTION").Value
rs.MoveNext
Loop

Browse mssqlsystemresource.mdf (SQL Server's Resource Database)

Is there a way to have the Resource Database (mssqlsystemresource.mdf) appear
in the list of Databases in SQL Server Management Studio for easy browsing of
it's contents, or is the only browse option to open the MDF file with a text
editor such as notepad?
Thanks.
Chris Jones
Application Development Consultant
Giving credit where credit is due, I found the following info I am about to
post at the following location:
http://www.aspfaq.com/sql2005/show.asp?id=28
Since the engine has hooks that hide the mssqlsystemresource database from
users, you don't have direct access to it through the GUI.
However, there is a way around this:
1.)Determine where the system databse files live, and keep this path handy:
USE master
GO
SELECT REPLACE(filename, 'master.mdf', '')
FROM sys.sysfiles
WHERE fileid = 1
2.)Stop the SQL Server service;
3.)Copy the files mssqlsystemresource.*df -> resource_copy.*df.
NOTE: do *not* rename or remove the mssqlsystemresource files!
4.)Start the SQL Server service;
5.)Run the following code in a new query window:
EXEC sp_attach_db
'Resource_Copy',
'<path from above>resource_copy.mdf',
'<path from above>resource_copy.ldf'
6.)Now, the system will no longer identify this database as a "special"
database
7.)You can easily scan through the names of system objects that might
otherwise be unknown (and browse any code associated with these objects) from
a GUI (like SQL Server Management Studio).
Thanks.
Chris Jones
Application Development Consultant
"Chris" wrote:

> Is there a way to have the Resource Database (mssqlsystemresource.mdf) appear
> in the list of Databases in SQL Server Management Studio for easy browsing of
> it's contents, or is the only browse option to open the MDF file with a text
> editor such as notepad?
> --
> Thanks.
> Chris Jones
> Application Development Consultant
|||Yes, if you start your SQL Server is single user mode.
Why do you need to see the contents? It just contains definitions of stored
procedures and views, and you can get to all those using the
OBJECT_DEFINITION function.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:83427BEB-8A5E-4A21-B8CC-EF65E1BEF9DD@.microsoft.com...
> Is there a way to have the Resource Database (mssqlsystemresource.mdf)
> appear
> in the list of Databases in SQL Server Management Studio for easy browsing
> of
> it's contents, or is the only browse option to open the MDF file with a
> text
> editor such as notepad?
> --
> Thanks.
> Chris Jones
> Application Development Consultant

Browse mssqlsystemresource.mdf (SQL Server's Resource Database)

Is there a way to have the Resource Database (mssqlsystemresource.mdf) appea
r
in the list of Databases in SQL Server Management Studio for easy browsing o
f
it's contents, or is the only browse option to open the MDF file with a text
editor such as notepad?
Thanks.
Chris Jones
Application Development ConsultantGiving credit where credit is due, I found the following info I am about to
post at the following location:
http://www.aspfaq.com/sql2005/show.asp?id=28
Since the engine has hooks that hide the mssqlsystemresource database from
users, you don't have direct access to it through the GUI.
However, there is a way around this:
1.)Determine where the system databse files live, and keep this path handy:
USE master
GO
SELECT REPLACE(filename, 'master.mdf', '')
FROM sys.sysfiles
WHERE fileid = 1
2.)Stop the SQL Server service;
3.)Copy the files mssqlsystemresource.*df -> resource_copy.*df.
NOTE: do *not* rename or remove the mssqlsystemresource files!
4.)Start the SQL Server service;
5.)Run the following code in a new query window:
EXEC sp_attach_db
'Resource_Copy',
'<path from above>resource_copy.mdf',
'<path from above>resource_copy.ldf'
6.)Now, the system will no longer identify this database as a "special"
database
7.)You can easily scan through the names of system objects that might
otherwise be unknown (and browse any code associated with these objects) fro
m
a GUI (like SQL Server Management Studio).
--
Thanks.
Chris Jones
Application Development Consultant
"Chris" wrote:

> Is there a way to have the Resource Database (mssqlsystemresource.mdf) app
ear
> in the list of Databases in SQL Server Management Studio for easy browsing
of
> it's contents, or is the only browse option to open the MDF file with a te
xt
> editor such as notepad?
> --
> Thanks.
> Chris Jones
> Application Development Consultant|||Yes, if you start your SQL Server is single user mode.
Why do you need to see the contents? It just contains definitions of stored
procedures and views, and you can get to all those using the
OBJECT_DEFINITION function.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:83427BEB-8A5E-4A21-B8CC-EF65E1BEF9DD@.microsoft.com...
> Is there a way to have the Resource Database (mssqlsystemresource.mdf)
> appear
> in the list of Databases in SQL Server Management Studio for easy browsing
> of
> it's contents, or is the only browse option to open the MDF file with a
> text
> editor such as notepad?
> --
> Thanks.
> Chris Jones
> Application Development Consultant

Breaking Replication??

Hi All
I have servers A, B and C with databases. Server A is a Publisher and Server
B is the Distributor/Subscriber. Server C is a Subscriber. Server A
replicates 7 publications to both server B and C. Initially when the
publications were very small, there were snapshot replications from A to both
B and C. Thereafter there have been transactional replications. The total
size of the databases has grown to about 30G.
My problem is I want to replace server C with a new server D. I have only 12
hrs to do this. If I do a snapshot replication to server D, it might take me
about 60hrs which I can’t do as this has to be done on a Sunday and ready for
Monday morning. I have a 150K pipe. I would like to avoid breaking the
replication on server B if possible. How can I do this replication within
12hours without breaking replication on server B?
Currently:
A>>>B
A>>>C
Future:
A>>>B
A>>>D
Thank you in advance.
You could create the snapshot, zip it up (WinZip 9.0), ftp, unzip and
restore, then specify an alternative snapshot location when initializing
server D. Alternatively you could zip up a backup of the database, ftp,
unzip restore then synchronize any changes to the data (if it is possible
there could be some). Prevent any further changes then do a nosync
initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Since server D is a new server, why don't you just set this new server as an
additional subscriber while you are still replicating to server C? Once D
has received the snapshot and is replicating normally you can pull server C.
I'm not sure how far away from your datacenter server D will be, but you
could also do the snapshot stuff on the local network (where server A&B are)
and then ship it to it's final location. One sticking point may be if you
are planning on using the same machine name for server D that you have for
server C, but it may not be an issue if you are using anonymous subscribers.
"MittyKom" wrote:

> Hi All
> I have servers A, B and C with databases. Server A is a Publisher and Server
> B is the Distributor/Subscriber. Server C is a Subscriber. Server A
> replicates 7 publications to both server B and C. Initially when the
> publications were very small, there were snapshot replications from A to both
> B and C. Thereafter there have been transactional replications. The total
> size of the databases has grown to about 30G.
> My problem is I want to replace server C with a new server D. I have only 12
> hrs to do this. If I do a snapshot replication to server D, it might take me
> about 60hrs which I can’t do as this has to be done on a Sunday and ready for
> Monday morning. I have a 150K pipe. I would like to avoid breaking the
> replication on server B if possible. How can I do this replication within
> 12hours without breaking replication on server B?
> Currently:
> A>>>B
> A>>>C
> Future:
> A>>>B
> A>>>D
> Thank you in advance.
>