November 21, 2003 at 7:46 am
I have a statment that I run against my linked servers to get the sysjob info for each server and database. The problem I am having is when the cursor runs through each sever and there is a logon or if a server is offline it breaks the program with no results returned. Is there a way to bypass the logon or server offline problems and continue to run through the remaining servers? Code below.
DECLARE @ServerName VARCHAR(100)
DECLARE @STR VARCHAR(2000)
DECLARE @failure INT
DECLARE ServerName_cursor CURSOR FOR
SELECT SERVERNAME FROM t_all_server
OPEN ServerName_cursor
FETCH NEXT FROM ServerName_cursor
INTO @ServerName
WHILE @@FETCH_STATUS = 0
BEGIN
select @STR = 'INSERT INTO temp_serverdata
SELECT ''' + @ServerName + ''' AS ''Host Server'',
sj.name AS ''Job Name'',
sjs.step_id AS Step#,
sjs.step_name,
''enabled'' =
CASE
WHEN sj.enabled = 1 THEN ''YES''
WHEN sj.enabled = 0 THEN ''NO''
END,
''scheduled'' =
CASE
WHEN sjss.enabled = 1 THEN ''YES''
WHEN sjss.enabled = 0 THEN ''NO''
END,
sj.description,
sc.name AS ''Variables(?)'',
''on_fail_action'' =
CASE
WHEN sjs.on_fail_action = 1 THEN ''Quit with Success''
WHEN sjs.on_fail_action = 2 THEN ''Quit the job reporting failure''
WHEN sjs.on_fail_action = 3 THEN ''Go to next step''
WHEN sjs.on_fail_action = 4 THEN ''Go to a specified step''
END,
(select email_address from msdb..sysoperators where id = sj.notify_email_operator_id) AS email_notification,
sjs.output_file_name
FROM OPENDATASOURCE(''SQLOLEDB'',''Data Source='+@ServerName+';User ID=USERNAME;Password=Password'').msdb.dbo.sysjobs sj,
OPENDATASOURCE(''SQLOLEDB'',''Data Source='+@ServerName+';User ID=USERNAME;Password=Password'').msdb.dbo.sysjobsteps sjs,
OPENDATASOURCE(''SQLOLEDB'',''Data Source='+@ServerName+';User ID=USERNAME;Password=Password'').msdb.dbo.sysjobschedules sjss,
OPENDATASOURCE(''SQLOLEDB'',''Data Source='+@ServerName+';User ID=USERNAME;Password=Password'').msdb.dbo.syscategories sc
WHERE sj.job_id = sjs.job_id
AND sjss.job_id = sj.job_id
AND sc.category_id = sj.category_id'
exec (@str)
FETCH NEXT FROM ServerName_cursor
INTO @ServerName
END
CLOSE ServerName_cursor
DEALLOCATE ServerName_cursor
GO
November 21, 2003 at 7:59 am
Not sure what you mean by a logon problem. If you can generate an error that lets you keep control of the batch/sp you could work around it.
such as, insert a seperate small execute inside the loop that runs before you actually run your server query, trap the error, and skip to the next server.
November 21, 2003 at 8:04 am
In my code I use an SQL logon. The problem is that there are some servers that do not share the same logon username/password or does not have the right to the specific tables.
The suggestion you gave, would that require me to setup maybe differnet if for else statements to test the logon and then run the code based upon where the IF statement landed?
November 21, 2003 at 8:04 am
In my code I use an SQL logon. The problem is that there are some servers that do not share the same logon username/password or does not have the right to the specific tables.
The suggestion you gave, would that require me to setup maybe differnet if for else statements to test the logon and then run the code based upon where the IF statement landed?
November 21, 2003 at 8:25 am
If I understand you right, yes. My understanding of severity levels on errors isn't what it should be- I don't know if they error you are getting actually fails the batch/sp. Using string executes I would think it wouldn't matter though. Give it a shot unless someone more knowledgable gives a better solution..
fetch...
begin
set @SQLStr = 'declare @x int select @x = id from '+@Server+'.master.dbo.sysocomments'
execute(@SQLStr)
if @@Error <> 0
begin
-- you got an error trying to access the server. handle your error here.
end
else
begin
-- do your query here
end
fetch
end
I stuck the variable assignment in there to prevent a rowset from being returned - probably a better way. sp_ExecuteSQL allows for passing of variables as output parms if you need it.
November 24, 2003 at 8:30 am
I had the same problem, when I was trying to retrieve the backup job history for all our servers. Apparently
when one of the remote servers is not available or login failed, it exits the batch. Here is the workaround.
Make it as a stored procedure on the central server and then make a VBscript file to execute the procedure against each
remote server with the remote server name passed as a parameter. The procedure would have to do dynamic execution to accept
the server name and build a query to execute against the server.
Here is the sample script (note the use of "On Error Resume Next " that is the key)..
Dim objDataConn1
Dim objDataConn2
Set objDataConn1 = wscript.CreateObject("ADODB.connection")
Set objDataConn2 = wscript.CreateObject("ADODB.connection")
objDataConn1.connectiontimeout = 500
objDataConn2.connectiontimeout = 500
objDataConn1.open "DSN=audit","sql_audit","*******"
objDataConn2.open "DSN=audit","sql_audit","*******"
sql="select srv_name FROM audit..cust_server WHERE linked_state = 'Y'"
Set rs = wscript.CreateObject("ADODB.RecordSet")
RS.Open sql, objDataConn1
Do While Not RS.EOF
wscript.echo "*** Server NAME :" & RS("srv_name").value
On Error Resume Next
wscript.echo " Retrieving backup data for Server:" + RS("srv_name").value
sql2 = "exec prc_backup_report_srv '"+ RS("srv_name").value +"', 'Y' "
' wscript.echo sql2
objDataConn2.execute sql2
IF (objDataConn2.errors.count ) > 0 then
' If we get a failure set the linked_state in the cust_database to 'I'
sql2 = "UPDATE cust_database SET linked_state = 'I' WHERE srv_name = '" + RS("srv_name").value +"'"
wscript.echo " Error Occured with Server: "+ RS("srv_name").value
'wscript.echo sql2
objDataConn2.execute sql2
end if
Err.Clear
wscript.echo " "
RS.MoveNext
Loop
Rs.close
objDataConn1.close
objDataConn2.close
Set RS = nothing
Set objDataConn1 = nothing
Set objDataConn2 = nothing
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply