"Hidden" Replication Stored Procedures?
in [Replication]
|
Prev: Help - the replication Failed to Run
Next: Deadlock between Distribution Agent and Distribution Agent Cleanup
From: Chris on 21 Dec 2006 10:57 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
From: Hilary Cotter on 21 Dec 2006 11:25 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(a)discussions.microsoft.com> wrote in message news:420DD987-9E55-46AB-8445-82FD01740996(a)microsoft.com... > 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
From: Chris on 21 Dec 2006 12:01 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(a)discussions.microsoft.com> wrote in message > news:420DD987-9E55-46AB-8445-82FD01740996(a)microsoft.com... > > 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 > > >
From: zerg2k on 21 Dec 2006 15:08 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(a)discussions.microsoft.com> wrote in message > > news:420DD987-9E55-46AB-8445-82FD01740996(a)microsoft.com... > > > 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 > > > > > >
From: Chris on 21 Dec 2006 15:53
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(a)yahoo.com" 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(a)discussions.microsoft.com> wrote in message > > > news:420DD987-9E55-46AB-8445-82FD01740996(a)microsoft.com... > > > > 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 > > > > > > > > > > > |