InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 20:29:43 GMT20http://www.sqlservercentral.com/Forums/Topic1417349-1292-1.aspxI did get to work and I did remove the single quotes from my varibles, its my hotmail acount that needed verification.Anyway I have a differnt problem I am trying to send emails using SSIS I have seen where scripting is required to do so since the Send DB Mail task has limited capabilities.Do you know how this could be accomplished. Please see my other post athttp://www.sqlservercentral.com/Forums/Topic1417996-364-1.aspxMon, 11 Feb 2013 06:08:01 GMTkingdonshelhttp://www.sqlservercentral.com/Forums/Topic1417349-1292-1.aspxDid you resolve why you get the names of your variables and not the values? When you set @message_body, you shouldn't quote your variable names because they're treated as string literals. Here's the line I'm referring to with the quotes removed.[code="sql"]SET @MESSAGE_BODY = @CURRENTTIME + @CURRENTUSER + @SERVERNAME+ @ERROR_MESSAGE + @ERROR_SEVERITY + @ERROR_LINE + @ERROR_STATE[/code]Mon, 11 Feb 2013 05:55:22 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1417349-1292-1.aspxHey this happens sometime .Sun, 10 Feb 2013 23:53:04 GMTjeetsingh.cshttp://www.sqlservercentral.com/Forums/Topic1417349-1292-1.aspxI had just set up a hotmail account to send mails from after going to that mail box it said that I should log in to verify account in order guard against spam so hotmail was actually blocking the sending of the emails until I verified my address by logging in again so the code does work I can now see my error emails at various difernt emails that tat they were sent to Thanks anyway this took my a day and half to figure out, hope no one else have these problemsFri, 08 Feb 2013 06:16:54 GMTkingdonshelhttp://www.sqlservercentral.com/Forums/Topic1417349-1292-1.aspxI think that your the problem is with your table structureas error is for table and you can see the status of sysmail_mailtiemsit shouldn't be 1 as 1 is for sent .Fri, 08 Feb 2013 05:57:46 GMTjeetsingh.cshttp://www.sqlservercentral.com/Forums/Topic1417349-1292-1.aspxThe error I get states'Cannot insert explicit value for identity column in table 'TableName' when IDENTITY_INSERT is set to OFF.' the ERROR_ SEVERITY is 16 when I run the query select * from sysmail_allitems I can see the email message in the bogy column, but the email does not go through, Database Mail has been configured already and is working since test emails do go through but I thing something is wrong with my code.Please let me know what you thinkor suggest a proven way how to have a sp in the catch block that sends an error email, I would like to use this sp all the time in catch blocks to send error emails.Fri, 08 Feb 2013 03:18:24 GMTkingdonshelhttp://www.sqlservercentral.com/Forums/Topic1417349-1292-1.aspxCan You please specify the error message you receive .Fri, 08 Feb 2013 02:34:29 GMTjeetsingh.cshttp://www.sqlservercentral.com/Forums/Topic1417349-1292-1.aspxI am trying to test my solution for firing off an email after an error is encountered in SQL server.To do this I have created a stored procedure that has the msdb..sp_send_dbmail functionality. I then use the TRY CATCH T-SQL technique and in the CATCH BLOCK of the query I call my procedure for sending the email. If I highlight all the code in the CATCH block only I am able to get a blank email with subject and name of my error variables but not the values that I would like from the variables.However if I run the TRY section of the query no email is generated and the error messa is only displayed in SSMS, the catch block does not work.------------------------below is sp for sending the error messg.--------------------------ALTER PROCEDURE SendErrorMessage @CURRENTTIME VARCHAR(50),@CURRENTUSER VARCHAR(50),@SERVERNAME VARCHAR(50),@ERROR_NUMBER VARCHAR(50),@ERROR_MESSAGE VARCHAR(MAX),@ERROR_SEVERITY VARCHAR(50),@ERROR_LINE VARCHAR(50),@ERROR_STATE VARCHAR(10)--@ERROR_PROCEDURE VARCHAR(50) ASDECLARE @MESSAGE_BODY VARCHAR(MAX)SET @MESSAGE_BODY = '@CURRENTTIME' + '@CURRENTUSER' + '@SERVERNAME' + '@ERROR_MESSAGE'+ '@ERROR_SEVERITY'+ '@ERROR_LINE' + '@ERROR_STATE'EXEC msdb..sp_send_dbmail @profile_name = 'Admin',@recipients = '@yahoo.co.uk',@blind_copy_recipients = '@yahoo.com', @subject = 'FError',@body = @MESSAGE_BODY ; -----------Here is my Code for the Try Catch code---------------------BEGIN TRY INSERT LookupCar VALUES (21,NOWAY)--------there is no table called LookupCar so this does generate an errorEND TRYBEGIN CATCH DECLARE @ERROR_NUMBER VARCHAR(10) DECLARE @ERROR_SEVERITY VARCHAR(10) DECLARE @ERROR_STATE VARCHAR(10) --DECLARE @ERROR_PROCEDURE VARCHAR(1000) DECLARE @ERROR_LINE VARCHAR(10) DECLARE @ERROR_MESSAGE VARCHAR(1000) DECLARE @CURRENTTIME DATETIME DECLARE @SERVERNAME VARCHAR(100) DECLARE @CURRENTUSER VARCHAR(50) SET @ERROR_NUMBER = ERROR_NUMBER() SET @ERROR_SEVERITY = ERROR_SEVERITY() SET @ERROR_STATE = ERROR_STATE() --SET @ERROR_PROCEDURE = @ERROR_PROCEDURE() SET @ERROR_LINE = ERROR_LINE() SET @ERROR_MESSAGE = ERROR_MESSAGE() SET @CURRENTTIME = CURRENT_TIMESTAMP SET @SERVERNAME = @@SERVERNAME SET @CURRENTUSER = CURRENT_USER EXEC SendErrorMessage @CURRENTTIME = @CURRENTTIME , @CURRENTUSER = @CURRENTUSER, @SERVERNAME = @SERVERNAME , @ERROR_NUMBER = @ERROR_NUMBER, @ERROR_MESSAGE = @ERROR_MESSAGE, @ERROR_SEVERITY = @ERROR_SEVERITY, @ERROR_LINE = @ERROR_LINE, @ERROR_STATE = @ERROR_STATE END CATCH Could anyone spot why this code does not work, if I highlighr the code in the catch block I do get an email but if I run code from beginning the error only appears in SSMS.Thu, 07 Feb 2013 14:12:47 GMTkingdonshel