December 14, 2009 at 8:21 am
Long and short is i have an in house sql monitoring system that uses the commands shell to run variaous queries accross various sql server instances. The below is supposed to return various info related to replication. It works for in most instances however there are a few sql 2008 instances who are being problem children and i dont know whats going on
--If i log onto the box and run the sql manually...no prob
--THIS WORKS FINE EVEN THOUGH THERE IS NO distribution.dbo.MSpublications TABLE
if exists( select * from master.dbo.sysdatabases where name = 'distribution' )
begin
select distinct s.subscriber_id subscriberid, si.srvname SubscriberName, p.publication, p.publisher_db, a.article, a.source_object, a.destination_object, s.subscriber_db
from distribution.dbo.MSpublications p
join distribution.dbo.MSarticles a
on p.publication_id = a.publication_id
and p.publisher_db = a.publisher_db
join distribution.dbo.MSsubscriptions s
on a.publication_id = s.publication_id
and a.article_id = s.article_id
join master.dbo.sysservers si
on si.srvid = s.subscriber_id
order by p.publication, a.article
end
--THIS ONE ERRORS W THE BELOW ERROR EVEN THOUGH THE SQL IN THE OPENQUERY IS VERBATUM OF THE ABOVE
--Msg 208, Level 16, State 1, Line 1
--Invalid object name 'distribution.dbo.MSpublications'.
SELECT *
FROM OPENQUERY([C1APSQLD002\CLEAN], ' if exists( select * from master.dbo.sysdatabases where name = ''distribution'' ) begin select distinct s.subscriber_id subscriberid, si.srvname SubscriberName, p.publication, p.publisher_db, a.article, a.source_object, a.destination_object, s.subscriber_db from distribution.dbo.MSpublications p join distribution.dbo.MSarticles a on p.publication_id = a.publication_id and p.publisher_db = a.publisher_db join distribution.dbo.MSsubscriptions s on a.publication_id = s.publication_id and a.article_id = s.article_id join master.dbo.sysservers si on si.srvid = s.subscriber_id order by p.publication, a.article end ELSE SELECT 1')
anyone know why the openquery version is failing?
December 15, 2009 at 6:18 am
OPENQUERY must determine the result set to return. With a IF / ELSE statement the result set columns cannot be predetermined.
Regards
Gianluca
-- Gianluca Sartori
December 15, 2009 at 8:06 am
thanks...
December 17, 2009 at 12:42 pm
by the way the only way around this was to use the 'master.dbo.xp_cmdshell. The idea was i wanted to loop through all our active sql instances and do a check to see if the distribution db exists and if it did i would set the activation flag in another table used to determine which boxes to run the replication monitor process on.
SET NOCOUNT ON
declare @sql varchar(3000)
, @servName varchar(200)
, @err varchar(1000)
, @chvPingTempTableName varchar(1000)
, @chvSQL varchar(1000)
, @ncvCmdShellSQL nvarchar(3000)
, @chvLinkedServer varchar(200)
, @cnt int
, @resultCnt int
CREATE TABLE #tblLinkedServerPingOutput(servername varchar(200), PingResult TINYINT NOT NULL)
SET @chvPingTempTableName = '##tblLinkedServerPing'+ RTRIM(REPLACE(NEWID(),'-',''))
SET @chvSQL = 'SELECT 0 AS ''PingResult'' INTO ' + @chvPingTempTableName
EXEC(@chvSQL)
set @cnt = 0
SELECT distinct S.ServerName, SS.srvname AS 'LinkedServer', 0 AS 'CheckStatusComplete', 0 AS 'IsLocalServer'
INTO #tblServerList
FROM tblServer S (nolock)
JOIN master.dbo.SYSSERVERS SS (nolock)
ON S.ServerName = SS.datasource
JOIN tblServerJobMonitor SJM (nolock)
ON S.ServerName = SJM.ServerName
WHERE SJM.JobName = 'MonitorReplicationPublishersAndArticles'
--AND SJM.Active = 0
AND S.SQLMonitorActive = 1
order by S.ServerName, SS.srvname
WHILE EXISTS (SELECT ServerName FROM #tblServerList WHERE CheckStatusComplete = 0 and @cnt<100)
BEGIN
SET @chvSQL = ''
set @resultCnt= 0
-- Get Next Linked Server that has not been queried and not Local Server
SELECT TOP 1 @chvLinkedServer = LinkedServer
FROM #tblServerList
WHERE CheckStatusComplete = 0
SET @ncvCmdShellSQL = 'master.dbo.xp_cmdshell ''osql -E -S ' + @@SERVERNAME + ' -Q "IF EXISTS( SELECT * FROM [' + CAST(@chvLinkedServer AS VARCHAR) + '].master.dbo.sysdatabases WHERE name = ''''distribution'''') update '+@chvPingTempTableName+' set PingResult = 1 ELSE update '+@chvPingTempTableName+' set PingResult = 0"'', no_output'
EXEC sp_executesql @ncvCmdShellSQL
SET @chvSQL = 'INSERT INTO #tblLinkedServerPingOutput(servername, PingResult) SELECT '''+@chvLinkedServer+''',PingResult FROM ' + @chvPingTempTableName
EXEC(@chvSQL)
print cast(@cnt as varchar)
set @cnt = @cnt + 1
-- Update CheckStatusComplete... ends While Loop
UPDATE #tblServerList
SET CheckStatusComplete = 1
WHERE LinkedServer = @chvLinkedServer and CheckStatusComplete = 0
end
update sjm
set active = pingresult
, autoreenable = pingresult
from #tblLinkedServerPingOutput tt
JOIN tblServerJobMonitor SJM (nolock)
ON tt.ServerName = SJM.ServerName
WHERE SJM.JobName = 'MonitorReplicationPublishersAndArticles'
and sjm.active <> pingresult
drop table #tblLinkedServerPingOutput
set @chvSQL = 'drop table '+@chvPingTempTableName
EXEC(@chvSQL)
drop table #tblServerList
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply