Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
heygee
Starting Member
27 Posts |
 Posted - 2005-08-10 : 05:51:16
|
Hi all,I am working on a sproc which loops through a list of linked servers on my machine and gathers performance stats etc (code will be uploaded for you all to see once complete). I am trying to cover all avenues where problems could occur and have come across one which I am stuck with and need some advice. If once of the linked servers is not available, then the script bombs out without trying out the other servers. Here is part of the code:CREATE PROCEDURE sp_DBAStatsAS DECLARE @dbname nvarchar(20), @v_srvname nvarchar(30), @cmd nvarchar(1024), @my_error nvarchar(2) DECLARE srv_cursor CURSOR FOR SELECT srvname FROM master.dbo.sysservers WHERE srvname NOT IN ('DE00415', 'zzztest') SELECT @my_error = 0 BEGIN TRANSACTION Gather_Stats OPEN srv_cursor FETCH NEXT FROM srv_cursor INTO @v_srvname IF @@ERROR <> 0 SELECT @my_error = 1 WHILE @@FETCH_STATUS = 0 BEGIN CREATE TABLE #SqlPerfTemp (dbName varchar(30), LogSizeMB decimal(18,2), LogSpacePct decimal(18,2), Status int) SET @cmd = 'INSERT INTO #SqlPerfTemp EXECUTE (''' + @v_srvname + '.master.dbo.sp_executesql N' + char(39) + '''DBCC SQLPERF(LOGSPACE)' + char(39) + ''''')'-- Problem on next line if a server is down/doesn't exist EXECUTE sp_executesql @cmd IF @@ERROR <> 0 SELECT @my_error = 2 INSERT INTO DBAstats..SQLPERF ------The problem is at the line EXECUTE sp_executesql @cmd, is there anyway of looping back into the cursor to try the next value and return the server name which it failed on?TIA,Gee Gee"Happiness is a good health and a bad memory!" |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-08-10 : 10:37:57
|
Maybe you could try testing your connection first:declare @retcode intdeclare @server sysnamedeclare @cmd nvarchar(500)select @server = 'servernamexx'select @cmd = 'osql -S '+@server+' -E -l 3 -Q "select 1 "'exec @retcode = master.dbo.xp_cmdshell @cmd ,no_outputif @retcode = 0 begin print 'Server '+@server + ' is available' endelse begin print 'Server '+@server + ' is unavailable' end CODO ERGO SUM |
 |
|
|
heygee
Starting Member
27 Posts |
Posted - 2005-08-10 : 10:56:07
|
| Thanks for the reply Michael. Just the type of solution I am currently testing, although your code is better :)I was looking at actually adding a check loop as part of the dynamic sql so that the actual SET @cmd = 'INSERT INTO SqlPerfTemp... is preceeded by a check to see if the server is returning any data, if not then drop out of the loop and 'hopefully' go back to the beginning of the cursor and start with the next server. What you think?Gee"Happiness is a good health and a bad memory!" |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-08-10 : 11:30:08
|
| Yes, you can just run this right before you run the @SQL.The idea of the code I posted is that it will produce an error that can be trapped. The osql command will just cause xp_cmdshell to have a non-zero return code if it cannot connect.Of course, the permissions will be slightly different than going through the linked server, but I am assuming you are SYSADMIN, since you are running DBCC. In any case, it will produce an error if the remote server is down, unreachable, or there is a login failure. If you need more detail info about why, you could remove the no_output, insert the xp_cmdshell output into a table, and parse the output.Another alternative would be to run the code you are trying to execute inside xp_cmdshell with osql.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Looping back in a cursor when errors occur???