OPENQUERY and SP_HELPPUBLICATION

Question

Can someone tell me what's wrong with setting up a loopback linked server and executing SP_HELPPUBLICATION

I was thinking I could just do this:

EXEC sp_helppublication
if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLNCLI10', 
    @datasrc = @@servername
go
SELECT *
FROM OPENQUERY(loopback,'exec sp_helppublication
')

But selecting from that OPENQUERY just gives me the following error:

Msg 208, Level 16, State 1, Procedure sp_MSrepl_getdistributorinfo, Line 76
Invalid object name 'msdb.dbo.MSdistpublishers'.

What am I missing?  Oh, and it DOES NOT help to spell out the full db path and schema with sp_helppublication either.

---

Number2 - (John Nelson)
Microsoft MVP (2009) - System Center Configuration Manager
http://number2blog.com

Answer 1

Openquery is not able to run one other SP! So please adjust it above.

Reference this

http://sqlserverplanet.com/tsql/using-openquery

& see this

UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101') 
SET name = 'ADifferentName';

---

Many Thanks & Best Regards, Hua Min

Answer 2

OK, so it's not able to run SP's?  If that's a limitation, I guess I'll live with that, but I thought the documentation said it's not able to execute EXTENDED stored procedures, which to me implied that it COULD execut a regular stored procedure...no?

Anyway, I'm not entirely sure where you're going with that...I don't want to adjust my query to turn it into an update statement...

Maybe I should back up, perhaps there's a simpler way to do what I'm trying to do. 

What I'm trying to do is just enumerate all of the publications on my server so I can alter them and set them to use snapshot compression.  I was going to start by just selecting the NAME from SP_HELPPUBLICATION.  I don't see a SYSPUBLICATIONS on the publisher, or I'd just use that.  Is that because the distributor is remote and I have to go to the distributor to get that information?

---

Number2 - (John Nelson)
Microsoft MVP (2009) - System Center Configuration Manager
http://number2blog.com

Answer 3

It is just to adjust your way to embed your whole SQL (even so complicated one) into Openquery instead of calling one other SP!

---

Many Thanks & Best Regards, Hua Min

Answer 4

Oh, and SQL 2008 R2 SP1 + CU6

Just want to select all publication names from somewhere so I can do stuff with them.

WAIT...I just found it...I think my problem was I was looking for SYS.Publications not syspublications. 

Thanks for helping me talk this through, Hua.

---

Number2 - (John Nelson)
Microsoft MVP (2009) - System Center Configuration Manager
http://number2blog.com

Answer 5

And here's what I was TRYING to do:

DECLARE @pubs TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, NAME VARCHAR(256)) 
INSERT INTO @pubs(NAME) select name from syspublications;
DECLARE 
  @Position INT = 0,
  @Publication VARCHAR(256),
  @MaxPosition INT = (SELECT MAX(ID) FROM @pubs)
WHILE @Position < @MaxPosition
BEGIN
  SET @Position = @Position + 1
  SELECT @Publication = name FROM @pubs WHERE ID = @Position 
  --<do stuff with publication here>
END

---

Number2 - (John Nelson)
Microsoft MVP (2009) - System Center Configuration Manager
http://number2blog.com