Preventing a SP from halting on error

Question

Post reply

Preventing a SP from halting on error

cpack

Ten Centuries

Points: 1239
More actions
February 24, 2005 at 2:34 pm

#68374

I am cycling through my 45 plus linked servers testing for successful connection. Everything works fine until I can not connect to one. This causes the below code to halt. I need to make note of the lack of connection as detailed in the code, but continue to cycle through the remaining linked servers. Any help would be greatly appreciated.
declare
@sql varchar(2500),
@fqinstancename varchar(150),
@sqlid int
create table #temp(
[output] varchar(2500))
select @sqlid = (select top 1 A.SQL_ID from MASTER_SQL A, MASTER_SERVER B
  where A.SERVER_ID = B.SERVER_ID and B.MSDTC_FIX =1 and A.IS_ACTIVE = 1
  and A.SERVICE_CHECK = 1 and A.CONNECT_CHECK is null order by A.SQL_ID)
while @sqlid <= (select top 1 A.SQL_ID from MASTER_SQL A, MASTER_SERVER B
  where A.SERVER_ID = B.SERVER_ID and B.MSDTC_FIX =1 and A.IS_ACTIVE = 1
  and A.SERVICE_CHECK = 1 and A.CONNECT_CHECK is null order by A.SQL_ID desc)
begin
select @fqinstancename = (select FQ_INSTANCE_NAME from MASTER_SQL where SQL_ID = @sqlid)
select @sql = 'select [name] as [output] from [' + @fqinstancename + '].master.dbo.sysdatabases where [name] = ''master'''
print @fqinstancename
insert #temp
exec (@sql)
if (select [output] from #temp) = 'master'
begin
  update MASTER_SQL set CONNECT_CHECK = 1 where SQL_ID = @sqlid
end
else
begin
  insert MASTER_SQL_HISTORY (SQL_ID, LAST_MODIFIED_DATE, MODIFY_REASON)
  select @sqlid, getdate(), 'The SQL Instance ' + @fqinstancename + ' could not be queried. ' +
  'it has been excluded from the scan on ' + convert (varchar(50),getdate()) + '. The error was ' + (select [output] from #temp) +
  ' Please determine the reason for the failed connection. It will be checked again on the next scan.'
  update MASTER_SQL set CONNECT_CHECK = 0 where SQL_ID = @sqlid
end
truncate table #temp
select @sqlid = (select top 1 A.SQL_ID from MASTER_SQL A, MASTER_SERVER B
  where A.SERVER_ID = B.SERVER_ID and B.MSDTC_FIX =1 and A.IS_ACTIVE = 1
  and A.SERVICE_CHECK = 1 and A.CONNECT_CHECK is null order by A.SQL_ID)
end
drop table #temp
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

cpack Ten Centuries Points: 1239 More actions · Answer 1

I just tried this too, didn't work.

begin

select @fqinstancename = (select FQ_INSTANCE_NAME from MASTER_SQL where SQL_ID = @sqlid)

select @sql = 'select [name] as [output] from [' + @fqinstancename + '].master.dbo.sysdatabases where [name] = ''master'''

exec (@sql)

if @@error <> 0

begin

insert MASTER_SQL_HISTORY (SQL_ID, LAST_MODIFIED_DATE, MODIFY_REASON)

select @sqlid, getdate(), 'The SQL Instance ' + @fqinstancename + ' could not be queried. ' +

'it has been excluded from the scan on ' + convert (varchar(50),getdate()) +

'. Please determine the reason for the failed connection. It will be checked again on the next scan.'

update MASTER_SQL set CONNECT_CHECK = 0 where SQL_ID = @sqlid

end

else

begin

update MASTER_SQL set CONNECT_CHECK = 1 where SQL_ID = @sqlid

end

truncate table #temp

select @sqlid = (select top 1 A.SQL_ID from MASTER_SQL A, MASTER_SERVER B

where A.SERVER_ID = B.SERVER_ID and B.MSDTC_FIX =1 and A.IS_ACTIVE = 1

and A.SERVICE_CHECK = 1 and A.CONNECT_CHECK is null order by A.SQL_ID)

end

Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000

cpack Ten Centuries Points: 1239 More actions · Answer 2

push come to shove I just realized that I can call xp_cmdshell and open a osql connection and dump the selection results to a temp table. Is there a more streamlined way to do this?

Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000

cpack Ten Centuries Points: 1239 More actions · Answer 3

That doesnt work either. It still halts. I am running out of ideas. help

Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000

cpack Ten Centuries Points: 1239 More actions · Answer 4

DOH!!!! yes it does, it just took a little longer for osql to return a connection error that I tought it would. At least If ound a way to do what i wanted. excuse my ramblings.......

Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

Take a look at Erland Sommarskog's home page where he has 2 exhaustive article on error handling.

http://www.sommarskog.se/

SQL = Scarcely Qualifies as a Language

noeld SSC Guru Points: 96590 More actions · Answer 6

My approach to that is to use a Job but some others prefer DMO have alook at this thread for a DMO example

HTH

* Noel