SQL Agent Job - Carry on Failure

Question

SQL Agent Job - Carry on Failure

Viewing 5 posts - 16 through 19 (of 19 total)

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

wak_no1 SSCrazy Points: 2400 More actions · Answer 1

Bijoy Chacko - Friday, March 9, 2018 5:25 AM
wak_no1 - Tuesday, March 6, 2018 11:46 AM
HiI have script that I run to fix orphaned user accounts on a instance - If I run the script in a standards SSMS window, it runs, hits errors with particular user accounts but crucially it carries on running and fixes the orphaned user accounts it can. Iâ€™ve put the exact TSQL in a SQL agent job, however when the job is ran, as soon as it hits an error, it stops executing and fails. Itâ€™s as if the SQL is parsed differently in a SQL Agent job?How do I get the TSQL to carry on regardless within a SQL Agent job , so that it runs as it does when itâ€™s executed in a SSMS Window?Thanks.
Have you tried with a Try Catch block

Nope, never used that before.

wak_no1 SSCrazy Points: 2400 More actions · Answer 2

in case anyone is interested, I solved this by using a try..catch block:

(also configured the agent job to write out to a log file, so we can capture the problematic accounts and deal with them separately)

BEGIN TRY

create table #logins (username nvarchar(max), UserSID nvarchar(max))

insert into #logins EXEC sp_change_users_login 'Report'

DECLARE @username nvarchar(max);

DECLARE users_Cursor CURSOR FOR SELECT username FROM #logins;

OPEN users_Cursor; FETCH NEXT FROM users_Cursor INTO @username; WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_change_users_login 'Update_One', @username, @username FETCH NEXT FROM users_Cursor INTO @username; END; CLOSE users_Cursor; DEALLOCATE users_Cursor;

drop table #logins

END TRY BEGIN CATCH -- Execute the error retrieval routine. SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; END CATCH

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Why not set it up so that the ones that are going to cause errors are excluded from your list?

DECLARE @SQL varchar(max); SET @SQL = ( SELECT 'ALTER USER ' + u.name + ' WITH LOGIN = ' + l.name + ';' + CHAR(13) FROM sys.database_principals u JOIN sys.server_principals l ON u.name = l.name AND u.sid <> l.sid WHERE u.type_desc = 'SQL_USER' FOR XML PATH ('') ); EXEC sys.sp_executesql @stmt = @SQL;

If you use an outer join, you can even return a list of orphaned users that couldn't be fixed.

John

wak_no1 SSCrazy Points: 2400 More actions · Answer 4

John Mitchell-245523 - Thursday, March 15, 2018 8:16 AM
Why not set it up so that the ones that are going to cause errors are excluded from your list?
DECLARE @SQL varchar(max); SET @SQL = ( SELECT 'ALTER USER ' + u.name + ' WITH LOGIN = ' + l.name + ';' + CHAR(13) FROM sys.database_principals u JOIN sys.server_principals l ON u.name = l.name AND u.sid <> l.sid WHERE u.type_desc = 'SQL_USER' FOR XML PATH ('') ); EXEC sys.sp_executesql @stmt = @SQL;
If you use an outer join, you can even return a list of orphaned users that couldn't be fixed.
John

I'll take that on-board, thanks.

Sue_H SSC Guru Points: 90860 More actions · Answer 5

I like the idea of the list of orphans like John is suggesting - seems like you'd want to know, want to address those since there are still orphans. And writing script to not have errors would be my preference. I can't think of the exception as to why you'd leave errors in code and then try to work around the errors instead of writing to address the logic and correct the errors. If the login needs to exist to correct the orphans in the script, it just seems to make sense to check for the logins.

It looks like when you hit an error executing sp_change_users_login in the modified script, it leaves behind the open cursor and temp table as they wouldn't be cleaned up.

Sue