How to get results from "exec sp_showpendingchanges" into a table

Question

EDIT: The things I've tried below came directly from the alleged duplicate. The solutions actually do work fine with a user defined sp (and probably most system sp's), but for whatever reason it doesn't work with this one.

I can run exec sp_showpendingchanges on the ~~distribution~~ publication database without any issues. However I want to capture the results in a table

I've tried:

SELECT * INTO #tmpTable
FROM OPENROWSET('SQLNCLI', 'Server=SERVER; Trusted_Connection=yes;', 
                'EXEC sp_showpendingchanges')

and:

SELECT * INTO #tmpTable
FROM OPENQUERY(SERVER, 'exec sp_showpendingchanges')

Both of these statements return an error that says: Invalid object name 'sysmergepublications'.

I tried to specify the initial catalog in the connection string and even tried adding a USE statement in the last parameter of each statement (i.e. I used an embedded EXEC statement with double-single quotes and all that). But I still end up with the same error.

So how can I get the results from exec sp_showpendingchanges into a temporary table, and preferably without having to define the table myself? If all else fails I will make a program in C#, but really hoping there's a simpler way to just do this with just SQL.

possible duplicate of [How to SELECT \* INTO \[temp table\] FROM \[stored procedure\]](http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure) — MikkaRin, Mar 04 '14 at 07:51
@MikkaRin I'm a little disappointed that you marked this a duplicate so quickly without reading the solutions in the post you linked and what I've already tried. If you had, it would be clear that what I've tried probably came directly from that post. ;) — BVernon, Mar 04 '14 at 18:37

score 2 · Answer 1 · edited Mar 05 '14 at 10:56

2

Here is a working example

You create a table

DECLARE @result_table TABLE
(
  destination_server SYSNAME ,
  pub_name SYSNAME ,
  destination_db_name SYSNAME ,
  is_dest_subscriber BIT ,
  article_name SYSNAME ,
  pending_deletes INT ,
  pending_ins_and_upd INT
)

execute the script

INSERT  INTO @result_table
        EXEC sp_showpendingchanges

view the results

SELECT * FROM @result_table

edited Mar 05 '14 at 10:56

NePh

966
9
21

answered Mar 04 '14 at 08:52

Andras

56
3

Thanks Andras. I knew I could do it this way but I was hoping to avoid having to declare the table. I don't think there will be a better solution though so this will have to do. – BVernon Mar 04 '14 at 18:55
I will probably mark this the answer, but I would really like to see if anyone can answer why I'm getting the error I'm getting first. – BVernon Mar 04 '14 at 18:56
Sorry BVernon, this is a solution i know about. – Andras Mar 05 '14 at 06:44

score 0 · Answer 2 · answered Mar 05 '14 at 07:36

I read your question but definetly cannot understand what the problem to create temp table. Anyway, if you can execute SP but get an error when you do it through linkedserver or openrowset - than problem is in permissions.

Check permissions on sysmergepublications table. If user, which you use for linked server or openrowset, has no grant on do select this table you need to add this permission to user.

I hope it will help you.

How to get results from "exec sp_showpendingchanges" into a table

2 Answers2