Send Results of Stored Procedure to Temp Table

Question

Hello,

I'm trying to programmatically monitor transactional replication of several SQL databases. So far, I have the following code snippet but it seems to be missing something:

SELECT * INTO #PublisherInfo
FROM OPENROWSET('SQLOLEDB', 
                'SERVER=servername;TRUSTED_CONNECTION=YES;',
                'SET FMTONLY OFF; SET NOCOUNT ON; EXEC distribution.dbo.sp_replmonitorhelppublisher WITH RESULT SETS
(publisher, distribution_db, status, warning, publicationcount, returnstamp)')

But I'm receiving the following error:

OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 12
Statement(s) could not be prepared.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'publisher'.

Any help would be appreciated. Thx!

Answer 1

Try this:

SELECT * INTO #PublisherInfo
FROM OPENROWSET(
    'SQLNCLI11', 
    'SERVER=localhost;TRUSTED_CONNECTION=YES;',
    'SET FMTONLY OFF; SET NOCOUNT ON; EXEC distribution.dbo.sp_replmonitorhelppublisher 
     WITH RESULT SETS (
        (
        publisher varchar(20), 
        distribution_db varchar(20), 
        status INT, warning INT, 
        publicationcount INT, 
        returnstamp varchar(20)
        )
    )'
);

Answer 2

It seems that WITH RESULT SETS requires datatypes, for example: WITH RESULT SETS (([publisher] varchar(max), [distribution_db] char(10), …)). Try specifying the appropriate types and use additional '( )'.

Answer 3

So I'm able to run the following with your change:

EXEC distribution.dbo.sp_replmonitorhelppublisher
WITH RESULT SETS
(
(publisher varchar(20), distribution_db varchar(20), status INT, warning INT, publicationcount INT, returnstamp varchar(20))
)

However, if I run it in its entirety...

SELECT * INTO #PublisherInfo
FROM OPENROWSET('SQLOLEDB', 
                'SERVER=servername;TRUSTED_CONNECTION=YES;',
                'SET FMTONLY OFF; SET NOCOUNT ON; EXEC distribution.dbo.sp_replmonitorhelppublisher WITH RESULT SETS
                ((publisher varchar(20), distribution_db varchar(20), status INT, warning INT, publicationcount INT, returnstamp varchar(20))')

I still receive the following error:

OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 12
Statement(s) could not be prepared.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ')'.