email out error message

If you run EXEC sp_change_users_login 'report' you get the list of orphaned users.
FIX the error using query given below. EXEC sp_change_users_login 'auto_fix', 'UserName'
I would first try to check whether a LOGIN "NAME" exists on the server (authentication).

If the login does not exist create the login and rerun the sp_change_users_login with 'auto_fix'
Follow this link may it help yours http://sqlserverdb.blogspot.in/2011/04/login-invalid-for-orphand-user.html

Jason clark,

"If you run EXEC sp_change_users_login 'report' you get the list of orphaned users.
FIX the error using query given below. EXEC sp_change_users_login 'auto_fix', 'UserName'
I would first try to check whether a LOGIN "NAME" exists on the server (authentication)."

I knew that but my point is, I will see some doesn't exist user and I want to email out the error message one by one.

Vitor,

so the "ERROR_MESSAGE(),"

is the SQL output (if it is an error) and email it out one email of each user when doing sp_change_users_login  ? and if only some of the user has problem the process is not going to stop and any of the these error can be send out?

Victor,

error message don't show out what exactly it is .

 i  am testing this:

DECLARE @err_msg AS NVARCHAR(MAX);

SET @err_msg = ERROR_MESSAGE()

Select allOpen in new window

 begin catch
	 EXEC msdb.dbo.sp_send_dbmail 
		  @profile_name = 'No Reply', 
		  @recipients = 'marrow.yung@cds.com.hk', -- 
		  @body = @err_msg ,
		  -- @body = @UserFail,
		  @subject = 'Error when running sp_change_users_login '
  end catch

Select allOpen in new window

your vesrion will have error in the ()

this works however:

 
DECLARE @err_msg AS NVARCHAR(MAX);

.
.
.
begin catch

 SET @err_msg =  ERROR_MESSAGE();
	 EXEC msdb.dbo.sp_send_dbmail 
		  @profile_name = 'No Reply', 
		  @recipients = 'marrow.yung@cds.com.hk', -- 
		  @body = @err_msg ,
		  @subject = 'Error when running sp_change_users_login '
  end catch

Select allOpen in new window

but it doesn't tell which line tells this.. any idea?

hi all,

I have a statement like this:

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'XXXXXX\aaaConsult' and TYPE = 'U')
    BEGIN
        CREATE USER [XXXXXX\aaaConsult] FOR LOGIN [XXXXXX\aaaConsult] WITH DEFAULT_SCHEMA=[dbo];
        
        EXECUTE sp_addrolemember 'Extol Developer', 'XXXXXX\aaaConsult';
        EXECUTE sp_addrolemember 'db_datareader', 'XXXXXX\aaaConsult';
    END;        
GO

GO
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'XXXXXX\extolconsultant2' and TYPE = 'U')
    BEGIN
        CREATE USER [XXXXXX\extolconsultant2] FOR LOGIN [XXXXXX\extolconsultant2] WITH DEFAULT_SCHEMA=[dbo];

        EXECUTE sp_addrolemember 'Extol Developer', 'XXXXXX\extolconsultant2';
        EXECUTE sp_addrolemember 'db_datareader', 'XXXXXX\extolconsultant2';

    END;
GO

Select allOpen in new window

but as you all can see, for each IF begin statement, we have 3 x statemetn:

    CREATE USER [XXXXXX\extolconsultant2] FOR LOGIN [XXXXXX\extolconsultant2] WITH DEFAULT_SCHEMA=[dbo];

        EXECUTE sp_addrolemember 'Extol Developer', 'XXXXXX\extolconsultant2';
        EXECUTE sp_addrolemember 'db_datareader', 'XXXXXX\extolconsultant2';

Select allOpen in new window

any way to make the error of each of them combine into one error message and send in one email ?

also when I am doing this:

DECLARE @err_msg AS NVARCHAR(MAX);
BEGIN
begin try

use master;
go
 
exec sp_removedbreplication

 end try
 begin catch

 SET @err_msg =  ERROR_MESSAGE();
	 EXEC msdb.dbo.sp_send_dbmail 
		  @profile_name = 'No Reply', 
		  @recipients = 'dddd@bbb.com.hk', -- 
		  @body = @err_msg ,
		  @subject = sdfdsfs '
  end catch
  
  
  
  END
  

Select allOpen in new window

I parse it and the error is:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'try'.
Msg 137, Level 15, State 1, Line 7
Must declare the scalar variable "@err_msg".
Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@err_msg".

Select allOpen in new window

any idea?

"ERROR_LINE is what you want then:
SET @err_msg =  'Error on line ' + CAST(ERROR_LINE() AS varchar) + ' - ' + ERROR_MESSAGE()
"

looks very execellent then... !

let me try.

but the normal error message is :

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 138

however, the one using yours:

Error on line 138 only.

You can get all those information from ERROR_* functions:

SET @err_msg = 'Msg ' + CAST(ERROR_NUMBER() AS varchar) + ', Level ' + CAST(ERROR_SEVERITY() AS varchar) + ', State ' + CAST(ERROR_STATE() AS varchar) + ', Procedure ' + ERROR_PROCEDURE() + + ', Line ' + CAST(ERROR_LINE() AS varchar) + CHAR(13) + CHAR(10) + ERROR_MESSAGE() 

Select allOpen in new window

let me try tomorrow. tks

Victor:

SET @err_msg = 'Msg ' + CAST(ERROR_NUMBER() AS varchar) + ', Level ' + CAST(ERROR_SEVERITY() AS varchar) + ', State ' + CAST(ERROR_STATE() AS varchar) + ', Procedure ' + ERROR_PROCEDURE() + + ', Line ' + CAST(ERROR_LINE() AS varchar) + CHAR(13) + CHAR(10) + ERROR_MESSAGE() 

Select allOpen in new window

this one works well. tks.

"BEGIN TRY should be after the USE Master and remove the GO and the BEGIN and END block:"


tks.

Good. Do you need something else for this question?

just gave your mark.. you great

man.. relax.