question

FrankAnellia-9381 avatar image
0 Votes"
FrankAnellia-9381 asked ErlandSommarskog commented

Send Results of Stored Procedure to Temp Table

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!

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered ErlandSommarskog commented

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)
         )
     )'
 );
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That was it, thx! Looks like the only thing changed was the driver? Any idea why the other driver was failing?

0 Votes 0 ·

Based on the documentation of OPENROWSET (Transact-SQL), the connection string "Server=SERVER_NAME;Trusted_Connection=yes;" is for the SQLNCLI provider. In my project I used that and it worked.

0 Votes 0 ·

SQLOLEDB is a very old driver that shipped with SQL 2000 and still ships with the OS: When you set up a linked server, I think it automatically remaps to the most recent provider, but maybe that does not happen with OPENROWSET.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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 '( )'.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

FrankAnellia-9381 avatar image
0 Votes"
FrankAnellia-9381 answered StefanHoffmann commented

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 ')'.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Probably it should be ...varchar(20)))').

0 Votes 0 ·

Does your OPENROWSET works without the INTO clause of the SELECT?

0 Votes 0 ·