Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Working with CATCH Block continuity Expand / Collapse
Author
Message
Posted Tuesday, March 2, 2010 2:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:36 AM
Points: 2,840, Visits: 3,970
USE [master]
GO
CREATE PROC [dbo].[ap_adm_OrphanUsersFix]
AS

SET NOCOUNT ON
DECLARE @cmd varchar(4000)

BEGIN TRY

Create table #Windows_Auth_Orphan_User
(
[Str] nvarchar(300)
)
DECLARE @DBCount INT,@MaxCount INT, @Qry nvarchar(4000), @DBName sysname
DECLARE @db_list table (dbname nvarchar(100),ID int identity)
SET @Qry = ''
SET @DBCount = 1

INSERT INTO @db_list(dbname )
SELECT name FROM sys.sysdatabases
WHERE dbid > 4

SELECT @MaxCount = MAX(ID) FROM @db_list
----Fixing Windows autheticated user-----------------
SET @DBCount = 1
WHILE(@DBCount < = @MaxCount )
BEGIN
SELECT @DBName = ''+ dbname + '' FROM @db_list WHERE id = @DBCount
SET @Qry = 'SELECT '' USE [' + @DBName + ' ];
ALTER USER ['' + NAME + ''] WITH LOGIN = [ '' + NAME + '']''
FROM sys.database_principals
WHERE ( type_desc = ''WINDOWS_GROUP'' OR type_desc = ''WINDOWS_USER'' )
AND name NOT like ''%dbo%'' AND name NOT LIKE ''%#%'''
INSERT INTO #Windows_Auth_Orphan_User EXEC (@Qry)
SET @DBCount = @DBCount + 1
END

--SELECT * FROM #Windows_Auth_Orphan_User
DECLARE MC CURSOR READ_ONLY FOR
SELECT [Str]FROM #Windows_Auth_Orphan_User
OPEN MC
FETCH NEXT FROM MC INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
print @cmd
Execute (@cmd)
END
FETCH NEXT FROM MC INTO @cmd
END
CLOSE MC
DEALLOCATE MC

DROP Table #Orphan_User_Tbl
Drop table #Windows_Auth_Orphan_User

END TRY

BEGIN CATCH
SELECT ERROR_NUMBER()ErrorNumber,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage
RETURN
END CATCH


Above SP, fix all the orphan windows_user or windows_group but if sometimes any user got failed, then it will be catched in catch block.
and it wil not resume further execution for remaining orphan users.
how can i make the code to work for remaining users, when it will be fell into catch block ( will "RETURN" work there?) ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #874952
Posted Tuesday, March 2, 2010 4:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 2,114, Visits: 5,502
First I have to admit that I didn’t check the code, so there are maybe other issues that I didn’t notice. As for your question, you can use nested catch try blocks. If you’ll put only the line Execute (@cmd) in side its own try catch block and the dynamic SQL will fail, the code will continu to run the rest of the loop.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #875024
Posted Tuesday, March 2, 2010 7:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:36 AM
Points: 2,840, Visits: 3,970
 DECLARE MC CURSOR READ_ONLY FOR 
SELECT [Str]FROM #Windows_Auth_Orphan_User
OPEN MC
FETCH NEXT FROM MC INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
print @cmd
Execute (@cmd)
END
FETCH NEXT FROM MC INTO @cmd
END
CLOSE MC
DEALLOCATE MC

Above code is taken from my first post window. let says we have 5 records for above cursor
while executed by cursor, it got error in 2nd records;
it should send to catch box and then throw message by
SELECT  ERROR_NUMBER()ErrorNumber,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage


(which is working fine)
and should go back to cursor and start working on remaining records( for 3, 4 and 5 records).

but my code got stopped after throwing error message.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #875162
Posted Tuesday, March 2, 2010 8:17 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
Adi already answered your question. Did you do what he said?

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #875203
Posted Tuesday, March 2, 2010 8:29 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
And you know, this would all be a lot easier and simpler if you did this in a set-based way, instead of using a CURSOR and WHILE loops.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #875211
Posted Tuesday, March 2, 2010 10:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:36 AM
Points: 2,840, Visits: 3,970
RBarryYoung (3/2/2010)[hrthis would all be a lot easier and simpler if you did this in a set-based way

I didnt get you here ? any example ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #875670
Posted Wednesday, March 3, 2010 8:16 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
RBarryYoung (3/2/2010)
And you know, this would all be a lot easier and simpler if you did this in a set-based way, instead of using a CURSOR and WHILE loops.

This is quite a tricky one to do set-based, and there's no performance advantage. Why bother?

I can write the dynamic SQL and cursor version in ten minutes - I'd have to think hard about a set-based version.

Just interested.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #876011
Posted Wednesday, March 3, 2010 11:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:36 AM
Points: 2,840, Visits: 3,970
DECLARE MC CURSOR READ_ONLY FOR 
SELECT [Str]FROM #Windows_Auth_Orphan_User
OPEN MC
FETCH NEXT FROM MC INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN TRY
PRINT @cmd
Execute (@cmd)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()ErrorNumber,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage
FETCH NEXT FROM MC INTO @cmd
CONTINUE;
END CATCH
FETCH NEXT FROM MC INTO @cmd
END
CLOSE MC
DEALLOCATE MC

I made it and it worked for me. but not sure wether it is optimal or not ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #876568
Posted Thursday, March 4, 2010 4:44 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:14 AM
Points: 9,294, Visits: 9,483
Paul White (3/3/2010)
RBarryYoung (3/2/2010)
And you know, this would all be a lot easier and simpler if you did this in a set-based way, instead of using a CURSOR and WHILE loops.

This is quite a tricky one to do set-based, and there's no performance advantage. Why bother?

I can write the dynamic SQL and cursor version in ten minutes - I'd have to think hard about a set-based version.

It's really not that hard with the right tools. Here's one that I've been using for a while:
/*
EXECute a string of T-SQL commands over the contents
of a set, replacing token strings with column values as
indicated. The set is defined by the @FROM parameter.
Optionally allows for printing the final execution string,
switching databases, and user-specified TRY-CATCH handling.
Also allows the caller to switch quote characters, to ease
quote-doubling problems.

RBarryYoung Proactive Performance Solutions, Inc.
*/
ALTER PROC
OVER_SET (
@command AS NVARCHAR(MAX), -- Template SQL command
@from AS NVARCHAR(MAX), -- FROM..WHERE clause string
@subs1 AS NVARCHAR(MAX) = N'', -- Substitution parameters, these are
@subs2 AS NVARCHAR(MAX) = N'', -- of the form "<find>=<repl>" where:
@subs3 AS NVARCHAR(MAX) = N'', -- <find> will be searched for in @command, and
-- <repl> will replace it, if it was found
-- (typically, <repl> should be a column name
-- returned by the FROM clause)
@print AS BIT = 1, -- 0 = suppress PRINT of the SQL before executing
@catch AS VARCHAR(12) = 'continue',
-- TRY/CATCH option parameters. Choices are:
-- 'continue' on an error, print a message & continue
-- 'ignore' attempt to suppress all errors
-- 'raise' try to re-raise the error
-- 'none' no TRY/CATCH blocks
@use_db AS NVARCHAR(255) = N'', -- DB to switch to befor execution of the SQL text
@quote AS NVARCHAR(8) = N'' -- search for this character & replace with (').
)
AS
--
DECLARE @qt AS NVARCHAR(1), @cr AS NVARCHAR(1);
SELECT @qt = N'''', @cr = N'
';
DECLARE @find1 AS NVARCHAR(MAX), @prfx1 AS NVARCHAR(MAX), @sufx1 AS NVARCHAR(MAX)
DECLARE @find2 AS NVARCHAR(MAX), @prfx2 AS NVARCHAR(MAX), @sufx2 AS NVARCHAR(MAX)
DECLARE @find3 AS NVARCHAR(MAX), @prfx3 AS NVARCHAR(MAX), @sufx3 AS NVARCHAR(MAX)
DECLARE @prtst AS NVARCHAR(MAX), @prfxC AS NVARCHAR(MAX), @sufxC AS NVARCHAR(MAX)
DECLARE @newdb AS NVARCHAR(MAX), @declr AS NVARCHAR(MAX)
DECLARE @NewCmd AS NVARCHAR(MAX), @GenCmd AS NVARCHAR(MAX)
;
SELECT
@find1 = CASE WHEN @subs1 = N'' THEN N'' ELSE LEFT(@subs1,CHARINDEX(N'=',@subs1)-1) END,
@prfx1 = CASE WHEN @subs1 = N'' THEN N'' ELSE N'REPLACE(' END,
@sufx1 = CASE WHEN @subs1 = N'' THEN N'' ELSE N',@find1,'+RIGHT(@subs1,LEN(@subs1)-CHARINDEX(N'=',@subs1))+N')' END,
@find2 = CASE WHEN @subs2 = N'' THEN N'' ELSE LEFT(@subs2,CHARINDEX(N'=',@subs2)-1) END,
@prfx2 = CASE WHEN @subs2 = N'' THEN N'' ELSE N'REPLACE(' END,
@sufx2 = CASE WHEN @subs2 = N'' THEN N'' ELSE N',@find2,'+RIGHT(@subs2,LEN(@subs2)-CHARINDEX(N'=',@subs2))+N')' END,
@find3 = CASE WHEN @subs3 = N'' THEN N'' ELSE LEFT(@subs3,CHARINDEX(N'=',@subs3)-1) END,
@prfx3 = CASE WHEN @subs3 = N'' THEN N'' ELSE N'REPLACE(' END,
@sufx3 = CASE WHEN @subs3 = N'' THEN N'' ELSE N',@find3,'+RIGHT(@subs3,LEN(@subs3)-CHARINDEX(N'=',@subs3))+N')' END,
@newdb = CASE WHEN @use_db= N'' THEN N'' ELSE N'USE [' + @use_db + N'];' + @cr END,
@declr = N'DECLARE @_Num AS INT, @_Lin AS INT, @_Err AS NVARCHAR(MAX), @_Msg AS NVARCHAR(MAX);'+@cr
;
;WITH
[base] AS (SELECT cmd = @command),
[quot] AS (SELECT cmd = CASE @quote WHEN N'' THEN cmd ELSE REPLACE(cmd, @quote, @qt) END FROM [base]),
[dble] AS (SELECT cmd = N'N'+@qt+REPLACE(cmd, @qt, @qt+@qt)+@qt FROM [quot]),
[prnt] AS (SELECT cmd = CASE @print WHEN 1 THEN N' PRINT '+cmd+';'+@cr ELSE N'' END
+ N' EXEC('+cmd+N');' FROM [dble]),
[ctch] AS (SELECT cmd =
CASE @catch WHEN N'none' THEN cmd
ELSE N'BEGIN TRY'+@cr+cmd+@cr+N'END TRY'+@cr+N'BEGIN CATCH'+@cr
+ N' SELECT @_Num=ERROR_NUMBER(), @_Lin=ERROR_LINE(), @_Err=ERROR_MESSAGE()'+@cr
+ CASE @catch
WHEN N'continue' THEN
N' SELECT @_msg=''Continuing after Error(''+CAST(@_Num AS NVARCHAR)+'') at Line ''+CAST(@_Lin AS NVARCHAR)+'''
+@cr+' ''+@_Err;'+@cr
+N' PRINT @_msg; '+@cr
+N' PRINT '' ''; '+@cr
WHEN N'ignore' THEN N' -- ignore = do nothing'+@cr
WHEN N'fail' THEN
N' SELECT @_msg=''Failing after Error(''+CAST(@_Num AS NVARCHAR)+'') at Line ''+CAST(@_Lin AS NVARCHAR)+'''
+@cr+' ''+@_Err;'+@cr
+N' RAISERROR(@_Num, 16, 1);'+@cr
+N' PRINT '' ''; '+@cr
ELSE N' --BAD else branch, shouldnt get here' END
+ N'END CATCH;' END FROM [prnt])
SELECT
@NewCmd = @prfx1+@prfx2+@prfx3+ N'@command' +@sufx1+@sufx2+@sufx3,
@command = cmd + @cr
FROM [ctch]
;
SELECT @GenCmd = '
DECLARE @sql AS NVARCHAR(MAX); SET @sql = '''+@newdb+ +@declr+ '''
;WITH
[-@from] AS ( SELECT * FROM ' +@from+ ' )
, [-@subs] AS ( SELECT [-NewCmd] = ' +@NewCmd+ ' FROM [-@from] )
, [-@print] AS ( SELECT [-NewCmd] = [-NewCmd] FROM [-@subs] )
SELECT
@sql = @sql + ''
'' + [-NewCmd]
FROM [-@subs]
;
EXEC sp_executesql @sql;
'
;
EXEC sp_executesql @GenCmd
, N'@command NVARCHAR(MAX), @from NVARCHAR(MAX), @find1 NVARCHAR(MAX), @find2 NVARCHAR(MAX), @find3 NVARCHAR(MAX)'
, @command, @from, @find1, @find2, @find3
;

And I think this is how Bhuvnesh's problem would be done in it:

EXEC OVER_SET '
EXEC OVER_SET "
ALTER USER [{name}] WITH LOGIN = [{name}]; ",
@from = "sys.database_principals
WHERE ( type_desc = ""WINDOWS_GROUP"" OR type_desc = ""WINDOWS_USER"" )
AND name NOT like ""%dbo%"" AND name NOT LIKE ""%#%"" ",
@use_db = "{db}",
@subs1 = "{name}=name",
@catch = "continue",
@print = 1,
@quote = "`";
',
@from = 'sys.sysdatabases
WHERE dbid > 4',
@subs1 = '{db}=name',
@catch = 'continue',
@print = 0,
@quote = '"';

I haven't compared it for performance, but I've not had any complaints myself.

As for why to do it this way? Well, I hope it's obvious, it's a heck of a lot easier to write, and even easier to read. IMHO, anyway.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #876708
Posted Thursday, March 4, 2010 5:48 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Gosh



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #876741
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse