email out error message

marrowyung
marrowyung used Ask the Experts™
on
hi all,

if I want to email out the SQL error message, like if I run this:

sp_change_users_login "update_one", "AdminProxy", "AdminProxy" 
sp_change_users_login "update_one", "CybernetUser", "CybernetUser" 

Open in new window


then if the user account doesn't not existing, and it gives error:

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 137
Terminating this procedure. The Login name 'AdminProxy' is absent or invalid.
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 137
Terminating this procedure. The Login name 'CybernetUser' is absent or invalid.

Open in new window


how can I have SQL server to email out the error message to me if I run all these in a SP, I don't want it to be an SQL job failure message as that statement only part of the whole logic. Not the whole logic of the total failure of a job.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
Use a TRY..CATCH block:
CREATE PROCEDURE usp_Name
AS
    BEGIN TRY
        EXEC sp_change_users_login "update_one", "AdminProxy", "AdminProxy" 
    END TRY

    BEGIN CATCH
        EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Profile Name',
        @recipients = 'user@domain.com',
        @body = ERROR_MESSAGE(),
        @subject = 'ERROR' 
    END CATCH
GO

Open in new window

Jason clarkDBA Freelancer

Commented:
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
marrowyungSenior Technical architecture (Data)

Author

Commented:
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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

marrowyungSenior Technical architecture (Data)

Author

Commented:
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()

Open 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

Open in new window


your vesrion will have error in the ()
marrowyungSenior Technical architecture (Data)

Author

Commented:
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

Open in new window


but it doesn't tell which line tells this.. any idea?
marrowyungSenior Technical architecture (Data)

Author

Commented:
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

Open 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';

Open in new window


any way to make the error of each of them combine into one error message and send in one email ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
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
  

Open 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".

Open in new window


any idea?
IT Engineer
Distinguished Expert 2017
Commented:
but it doesn't tell which line tells this.. any idea?
ERROR_LINE is what you want then:
SET @err_msg =  'Error on line ' + CAST(ERROR_LINE() AS varchar) + ' - ' + ERROR_MESSAGE()


I parse it and the error is:
BEGIN TRY should be after the USE Master and remove the GO and the BEGIN and END block:
DECLARE @err_msg AS NVARCHAR(MAX);

use master;
 
begin try
    exec sp_removedbreplication
end try
  
begin catch
   SET @err_msg =  'Error on line ' + CAST(ERROR_LINE() AS varchar) + ' - ' + ERROR_MESSAGE()
   EXEC msdb.dbo.sp_send_dbmail 
	  @profile_name = 'No Reply', 
	  @recipients = 'dddd@bbb.com.hk', -- 
	  @body = @err_msg ,
	  @subject = sdfdsfs '
end catch

Open in new window

marrowyungSenior Technical architecture (Data)

Author

Commented:
"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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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() 

Open in new window

marrowyungSenior Technical architecture (Data)

Author

Commented:
let me try tomorrow. tks
marrowyungSenior Technical architecture (Data)

Author

Commented:
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() 

Open in new window


this one works well. tks.
marrowyungSenior Technical architecture (Data)

Author

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


tks.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Good. Do you need something else for this question?
marrowyungSenior Technical architecture (Data)

Author

Commented:
just gave your mark.. you great

man.. relax.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial