Working with CATCH Block continuity

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Working with CATCH Block continuity

Author

Topic

bhuvnesh.dogra
Starting Member

22 Posts

Posted - 2010-03-02 : 04:58:28

[code]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

[/code]

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?) ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-03 : 16:09:32

RETURN means to exit out of the stored procedure, so no that will not work. You should just grab the unorphan users script in the script library forum here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

bhuvnesh.dogra
Starting Member

22 Posts

Posted - 2010-03-04 : 02:05:34

[sql]DECLARE MC CURSOR
READ_ONLY
FOR

SELECT [Database_Name]+ '..sp_change_users_login ''UPDATE_ONE'' , ''' + Orphaned_User + ''' ,''' + Orphaned_User + ''';'
FROM #Orphan_User_Tbl
ORDER BY [Database_Name], [Orphaned_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 [/sq]

i just modified the cursor part with above code. do you see any thing bad in it ?

---Bhuvnesh-----
While 1=1(learning Sql...)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources