email out error message

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.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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 FreelancerCommented:
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?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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ãoMSSQL Senior EngineerCommented:
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ãoMSSQL Senior EngineerCommented:
Good. Do you need something else for this question?
marrowyungSenior Technical architecture (Data)Author Commented:
just gave your mark.. you great

man.. relax.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.