December 16, 2015 at 10:00 pm
Hi,
I am using some locally written scripts to grab some information from replication stored procs. In SQL 2008 this executes without issue. However the new stored procedure sp_describe_first_result_set in SQL 2012 is causing the code to error. If I try to insert the output of sp_replmonitorhelppublisher for example into a temp table: My code is this - (table already created):
insert into #pubinfo (publisher,distribution_db,status,warning,publicationcount,returnstamp)
select * from OPENROWSET('SQLOLEDB',
'Server=INSTANCE1\INSTANCE1;Trusted_Connection=Yes;',
'set fmtonly off;
exec sp_replmonitorhelppublisher')
under SQL 2012 I receive the error:
Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'exec @cmd N'if is_member(N''db_owner'') = 1 or isnull(is_member(N''replmonitor''),0) = 1 set @has_ac' in procedure 'sp_MSrepl_DistributorReplMonitorAccess' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.
I am having trouble figuring out the WITH RESULTS SETS and the OPENROWSETS query. Does anyone have any idea on a way around this at all? Many thanks in advance.
Scott
January 3, 2016 at 9:26 pm
Just in case anyone is interested I did find a solution. You have to specify the result set which is new to SQL 2012. The error with the stored procedure involved is because it is trying to find the result set and it is undefined. My code to fix it (and you have to create the Temp table first with the same columns as the results):
insert into #pubinfo
select * from OPENROWSET('SQLOLEDB',
'Server=INSTANCE1\INSTANCE1;Trusted_Connection=Yes;',
'set fmtonly off;
exec sp_replmonitorhelppublisher with result sets
(
(
publisher varchar(100) null,
distribution_db varchar(100) null,
status int null,
warning int null,
publicationcount int null,
returnstamp bigint null
)
)')
This becomes harder through if you want to do the same thing but use a stored proc like sp_replmonitorhelppublication as you have to pass a variable to it. This causes all sorts of trouble esp if you are inserting into a temp table. However if you wanted to do it - this is how - again assume I have declared all variables and created temp tables with the correct definition, here is the code to do it:
set @cmd='distribution..sp_replmonitorhelppublication @publisher = ''''' + @pub + ''''' with result sets
(
(
publisher_db varchar(100) null,
publication varchar(MAX) null,
publication_id int null,
publication_type int null,
status int null,
warning int null,
worst_latency int null,
best_latency int null,
average_latency int null,
last_distsync datetime null,
retention int null,
latencythreshold int null,
expirationthreshold int null,
agentnotrunningthreshold int null,
subscriptioncount int null,
runningdisagentcount int null,
snapshot_agentname varchar(MAX) null,
logreader_agentname varchar(MAX) null,
qreader_agentname varchar(255) null,
worst_runspeedPerf int null,
best_runspeedPerf int null,
average_runspeedPerf int null,
retention_period_unit int null,
publisher varchar(150) null
)
)'
set @cmd2 = 'insert into #pubinfo2
(publisher_db,publication,publication_id,publication_type,status,warning,worst_latency,best_latency,average_latency,last_distsync,retention,
latencythreshold,expirationthreshold,agentnotrunningthreshold,subscriptioncount,runningdisagentcount,snapshot_agentname,logreader_agentname,qreader_agentname,
worst_runspeedPerf,best_runspeedPerf,average_runspeedPerf,retention_period_unit,publisher)
select * from OPENROWSET(''SQLOLEDB'',
''Server=INSTANCE1\INSTANCE1;Trusted_Connection=Yes;'',
''set fmtonly off;
exec '+ @cmd +''')'
--Take the output of the stored procedure and execute inserting results into
-- #pubinfo2 table
exec sp_executesql @cmd2
I'm no developer and it took me a bit on and off to figure it out - but I hope this helps someone else in any case.
Cheers,
Scott
May 14, 2019 at 11:55 am
Hi Mossy-552515 ,
Thank you for this awesome solution.
This worked and I was able to successfully create the SP in SQL 2016. HOwever, further , while executing the procedure in 2016 SQL Server, I faced the below error message:
I am still trying to find a workaround or a perm fix for this. Any help with this is highly appreciated.
Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 [Batch Start Line 7]
The metadata could not be determined because statement 'exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout' in procedure 'sp_getapplock' invokes an extended stored procedure.
Thank You,
Santosh
Bangalore, India
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply