June 23, 2010 at 11:54 pm
Hi guys ,
I am working Replication monitoring scripts and scripted code for replication latency output .
The interesting part is I am able to get the output most of the time and some time it fails i can
3/10 failure .The main issue is occuring while executing the below part of code .
DECLARE replmonitor CURSOR FOR
select b.srvname,
a.publisher_db,
a.publication
from distribution.dbo.MSpublications a,
master.dbo.sysservers b
where a.publisher_id=b.srvid
OPEN replmonitor FETCH NEXT
FROM replmonitor into @srvname,@pub_db,@pubname WHILE @@FETCH_STATUS = 0
BEGIN
insert into #replmonitor exec distribution.dbo.sp_replmonitorhelpsubscription
@publisher = @srvname ,
@publisher_db = @pub_db ,
@publication = @pubname ,
@publication_type = 0
FETCH NEXT FROM replmonitor
into
@srvname,
@pub_db,
@pubname
END
CLOSE replmonitor DEALLOCATE replmonitor
Error output is :
Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested.
I will highly appreciate If some one really has solution for this .
Thanks
June 24, 2010 at 1:04 am
As the message says, INSERT - EXEC cannot be nested.
This probably means that sp_replmonitorhelpsubscription internally uses INSERT - EXEC.
Some workaround could be using OPENROWSET to wrap the procedure:
insert into #replmonitor
select data.*
from openrowset('SQLOLEDB','SERVER=(local);Trusted_Connection=yes;','
set fmtonly off;
exec distribution.dbo.sp_replmonitorhelpsubscription
') AS data
You should enable ad-hoc queries to do so.
You could also create a loopback linked server and use OPENQUERY, but I think it's simpler this way.
-- Gianluca Sartori
June 24, 2010 at 9:54 am
Thanks for reply , but after I execute I get this below error : -
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 2, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [2].
June 24, 2010 at 10:02 am
Is it a named instance? Try changing (local) into your actual server\instance.
-- Gianluca Sartori
June 24, 2010 at 2:20 pm
the distribution server is named istance ... xxxx/dist1..
All linked server looks good
June 24, 2010 at 2:25 pm
same error replaced local with servername/instance
June 24, 2010 at 2:28 pm
instances use the other slash...dunno if it is a typo:
my server is LOWELL\SQLEXPRESS, or LOWELL\SQL2008 for my other instance....could that be the issue?
Lowell
June 24, 2010 at 2:36 pm
even with local its failing : --
The complete code is :
-------------------------------------------------------------------------------------
create Proc [dbo].[usp_ReplicationLatency_n]
as
SET NOCOUNT ON
declare @srvname varchar(100)
declare @pub_db varchar(100)
declare @pubname varchar(100)
create table #replmonitor(
status int null,
warning int null,
subscriber sysname null,
subscriber_db sysname null,
publisher_db sysname null,
publication sysname null,
publication_type int null,
subtype int null,
latency int null,
latencythreshold int null
,agentnotrunning int null,
agentnotrunningthreshold int null,
timetoexpiration int null,
expirationthreshold int null,
last_distsync datetime,
distribution_agentname sysname null,
mergeagentname sysname null,
mergesubscriptionfriendlyname sysname null,
mergeagentlocation sysname null,
mergeconnectiontype int null,
mergePerformance int null,
mergerunspeed float,
mergerunduration int null,
monitorranking int null,
distributionagentjobid binary(16),
mergeagentjobid binary(16),
distributionagentid int null,
distributionagentprofileid int null,
mergeagentid int null,
mergeagentprofileid int null,
logreaderagentname varchar(100))
DECLARE replmonitor CURSOR FOR
select b.srvname,a.publisher_db,a.publication from distribution.dbo.MSpublications a, master.dbo.sysservers b
where a.publisher_id=b.srvid
OPEN replmonitor FETCH NEXT
FROM replmonitor into @srvname,@pub_db,@pubname WHILE @@FETCH_STATUS = 0
BEGIN
insert into #replmonitor
select data.*
from openrowset('SQLOLEDB','SERVER=(local);Trusted_Connection=yes;','
set fmtonly off;
exec distribution.dbo.sp_replmonitorhelpsubscription @publisher = @srvname , @publisher_db = @pub_db , @publication = @pubname , @publication_type = 0
') AS data
/*insert into #replmonitor exec distribution.dbo.sp_replmonitorhelpsubscription @publisher = @srvname , @publisher_db = @pub_db , @publication = @pubname , @publication_type = 0
*/
FETCH NEXT FROM replmonitor into
@srvname,
@pub_db,
@pubname
END
CLOSE replmonitor DEALLOCATE replmonitor
Select * from #replmonitor
---------------------------------------------------------------------------------
June 24, 2010 at 2:40 pm
edited because now i'm not so sure......
Trusted_Connection=yes = I thought was not possible...unless you change the login used for the SQL Service account.
I know SQL does not use YOUR credentials to access any other object outside of the actual database you are connected to...I thought that mean network shares, local harddrives, xp_cmdshell etc... i was under the impression it was the same for openrowset as well....but i found that exact command saved in my snippets, and tested it locally as well...
SELECT *
FROM OPENROWSET('SQLOLEDB','Server=lowell\SQLExpress;Trusted_Connection=Yes;Database=Master',
'Set FmtOnly OFF; EXEC dbo.sp_Who')
i thought you should just change to use a SQL login and you will be all set.
see this post for screenshots and detailed explanations i saved about accessing external stuff:
http://www.sqlservercentral.com/Forums/FindPost942642.aspx
Lowell
June 24, 2010 at 7:33 pm
Hi ,
I changed the security context and tried it fails again . any other pointers please .
June 25, 2010 at 12:44 am
Does it return a logon error?
You could also try the other way with a linked server.
-- Gianluca Sartori
February 16, 2011 at 3:04 pm
This is the error:
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@srvname".
February 17, 2011 at 12:55 am
DBA-640728 (2/16/2011)
This is the error:OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@srvname".
[font="Courier New"]Must declare the scalar variable "@srvname"[/font]
Means that you didn't declare it. Can you post the complete code you are running?
-- Gianluca Sartori
February 17, 2011 at 1:40 pm
thanks for your reply, here you go:
create Proc [dbo].[usp_ReplicationLatency_n]
as
SET NOCOUNT ON
declare @srvname varchar(100)
declare @pub_db varchar(100)
declare @pubname varchar(100)
create table #replmonitor(
status int null,
warning int null,
subscriber sysname null,
subscriber_db sysname null,
publisher_db sysname null,
publication sysname null,
publication_type int null,
subtype int null,
latency int null,
latencythreshold int null
,agentnotrunning int null,
agentnotrunningthreshold int null,
timetoexpiration int null,
expirationthreshold int null,
last_distsync datetime,
distribution_agentname sysname null,
mergeagentname sysname null,
mergesubscriptionfriendlyname sysname null,
mergeagentlocation sysname null,
mergeconnectiontype int null,
mergePerformance int null,
mergerunspeed float,
mergerunduration int null,
monitorranking int null,
distributionagentjobid binary(16),
mergeagentjobid binary(16),
distributionagentid int null,
distributionagentprofileid int null,
mergeagentid int null,
mergeagentprofileid int null,
logreaderagentname varchar(100))
DECLARE replmonitor CURSOR FOR
select b.srvname,a.publisher_db,a.publication from distribution.dbo.MSpublications a, master.dbo.sysservers b
where a.publisher_id=b.srvid
OPEN replmonitor FETCH NEXT
FROM replmonitor into @srvname,@pub_db,@pubname WHILE @@FETCH_STATUS = 0
BEGIN
insert into #replmonitor
select data.*
from openrowset('SQLOLEDB','SERVER=(local);Trusted_Connection=yes;','
set fmtonly off;
exec distribution.dbo.sp_replmonitorhelpsubscription @publisher = @srvname , @publisher_db = @pub_db , @publication = @pubname , @publication_type = 0
') AS data
/*insert into #replmonitor exec distribution.dbo.sp_replmonitorhelpsubscription @publisher = @srvname , @publisher_db = @pub_db , @publication = @pubname , @publication_type = 0
*/
FETCH NEXT FROM replmonitor into
@srvname,
@pub_db,
@pubname
END
CLOSE replmonitor DEALLOCATE replmonitor
Select * from #replmonitor
February 17, 2011 at 4:05 pm
The variables you are using inside OPENROWSET have to be declared and initialized inside the same code block (in other words, inside the string you are using as argument). It is not practical or feasible.
The only option here is inserting as literals the actual values into the sql string passed to OPENROWSET.
Be careful with sql injection and quotes.
-- Gianluca Sartori
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply