Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In learning about replication in SQL Server and having problems creating a subscription, I looked at the definition for sp_addsubscription. It calls sys.sp_MSrepl_getpublisherinfo, which I cannot find on the server. This stored procedure obviously exists, as it no errors occur regarding it not existing. Where does it live, and can its code be viewed?

share|improve this question
    
check this out g-productions.nl/… –  Nitin Goyal Jul 25 '12 at 17:58
add comment

migrated from stackoverflow.com Aug 15 '12 at 15:23

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 0 down vote accepted

So after more research I found that sys.sp_MSrepl_getpublisherinfo lives in a database named mssqlsystemresource, which lives in Sql Server's binn folder. This database is invisible to SSMS, but you can copy and attach it as a different name to have full access. This process is described here:

http://www.sqlnewsgroups.net/sqlserver/t21348-browse-mssqlsystemresource-mdf-sql-servers-resource-database.aspx

I have copied that text here, in case that page goes away in the future:

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).
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.