Re: "Hidden" Replication Stored Procedures?

Yip...you are absolutely correct....I just discovered that and was coming
back here to post the instructions. Here's what I found....

First, 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


"zerg2k@xxxxxxxxx" wrote:

Just make a copy of the database files, attach it with a different name
and... 'voila'!

:-)

Chris wrote:
Great, that was exactly what I was looking for and when opening the
aforementioned MDF file in Notepad and doing a quick search, I found the SPs
I had mentioned in my earlier post. Thanks for the good info!

I have one follow up question based on your answer:
Is there a way to have the Resource Database 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


"Hilary Cotter" wrote:

they are in mssqlsystemresource.mdf. Open it in a text editor to read what
this proc does.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



"Chris" <Chris@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:420DD987-9E55-46AB-8445-82FD01740996@xxxxxxxxxxxxxxxx
In looking thru the code for the replication related stored procedure
sp_addarticle, I noticed that there were 2 stored procedures called from
sp_addarticle that I can't seem to find anywhere on my SQL Server:
sys.sp_MSrepl_getpublisherinfo & sys.sp_MSrepl_addarticle. These are
stored
procedures that appear to be undocumented and I have read comments on
Microsoft sites stating things like: "Undocumented stored procedures are
only
for the use of internal replication components and should not be used to
administer replication.". I just want to know more about these stored
procs
(and others like these) as far as where they live, what they do, how
sp_addarticle calls these Stored Procs without the SP blowing up.....as I
have tried to call sys.sp_MSrepl_getpublisherinfo in a 10 line script I
made
and all I got back was that the sys.sp_MSrepl_getpublisherinfo couldn't be
found no matter what DB I tried to execute against. I don't see anything
helpful about these SPs when I Google (and I am a Google master) or
anywhere
else. If anyone has anything relevant & helpful, I would appreciate the
info.
--
Thanks.

Chris Jones
Application Development Consultant





.

Relevant Pages

  • Re: Problem with updating MDF Data file as opposed to updating a running SQL Server DB
    ... In Visual C# Express, I created a windows application, then "Added" a ... Doing this created an MDF file in my project folder, ... database into my database explorer window in the IDE. ... It would seem with this option, then one could use a SQL Server MDF ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ASPNETDB.MDF when no SQLExpress, only SQL Server 2005?
    ... You will need to upload the mdf file and have your webhost attach it to the sql server. ... In which case you will need to recreate the database into the database which is available to the hosted domain if you have signed up ... The connection will be closed. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Error 823
    ... Regards. ... Microsoft SQL Server Storage Engine ... > I cannot extract much data out of the database and I can't view the most ... The problem is in the .MDF file, ...
    (microsoft.public.sqlserver.tools)
  • Re: Lost Log File !
    ... Create a new database with same MDF and LDF name ... stop sql server and delete the new MDF file and copy the old MDF file ... Start SQL Server and see the status. ... > Create a new database with same MDF and LDF name ...
    (microsoft.public.sqlserver.clients)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)