InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 19:54:30 GMT20http://www.sqlservercentral.com/Forums/Topic1428514-338-1.aspx[quote][b]Lynn Pettis (3/8/2013)[/b][hr]How the heck did I miss that![/quote]You saw a cursor and your eyes rolled back in your head...then you realized that this really might be one of those rare situations where a cursor actually does make sense...but by then you were so frazzled at trying to help somebody unravel a cursor you couldn't see straight anymore. I did feel a large disturbance about that time. :-PFri, 08 Mar 2013 10:08:51 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428514-338-1.aspx[quote][b]Sean Lange (3/8/2013)[/b][hr]That is because you concatenate values to the previous subject each pass through the loop.Change you loop to this.[code] --@emailSubject = @emailSubject + @sku EXEC msdb.dbo.sp_send_dbmail @importance = 'High', @recipients= @email , @subject = @emailSubject + @sku[/code]The subject portion stays the same 'Warning stock is below safety for : ', all you need is to add the sku to this.[/quote]How the heck did I miss that!Fri, 08 Mar 2013 10:02:29 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1428514-338-1.aspxthat's cracked it. thanks for the help.Fri, 08 Mar 2013 09:59:33 GMTspinhttp://www.sqlservercentral.com/Forums/Topic1428514-338-1.aspxYou don't need to have email as a constant in your select for your cursor, just use the value that is already there.The bigger question I would ask is do you really need a separate email for each sku or would a single email with all the skus be acceptable? If a single email with multiple skus would be ok you don't even need the cursor. And do you mean for these emails to be only a subject and not have a body?--EDIT--Meant to post the cleaned up version of your cursor.[code]DECLARE @emailTo VARCHAR(255), @email VARCHAR(max), @sku VARCHAR(30), @emailSubject VARCHAR(128),SET @emailTo = 'myemail@mycompany.co.uk'SET @emailSubject = 'Warning stock is below safety for : '-- SELECT RECORDS THAT NEED TO BE EMAILEDDECLARE c1 CURSOR FOR SELECT rtrim(StockCode) as StockCode FROM dbo.tbl_STOCK_safety_check WHERE email_status = 1-- LOOP THROUGH RECORDSET AND SEND EMAIL FOR EACH RECORDOPEN c1FETCH NEXT FROM c1 INTO @skuWHILE @@FETCH_STATUS <> -1BEGIN EXEC msdb.dbo.sp_send_dbmail @importance = 'High', @recipients= @email , @subject = @emailSubject + @sku FETCH NEXT FROM c1 INTO @email,@skuENDCLOSE c1DEALLOCATE c1[/code]Fri, 08 Mar 2013 09:58:34 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428514-338-1.aspxThat is because you concatenate values to the previous subject each pass through the loop.Change you loop to this.[code] --@emailSubject = @emailSubject + @sku EXEC msdb.dbo.sp_send_dbmail @importance = 'High', @recipients= @email , @subject = @emailSubject + @sku[/code]The subject portion stays the same 'Warning stock is below safety for : ', all you need is to add the sku to this.Fri, 08 Mar 2013 09:50:58 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428514-338-1.aspxNeed to see the code that builds this table: dbo.tbl_STOCK_safety_check Fri, 08 Mar 2013 09:29:38 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1428514-338-1.aspxthe problem is that the email subject should be this...email 1 subject : stockcode SKU0001 is below safetyemail 2 subject : stockcode SKU0002 is below safetyemail 3 subject : stockcode SKU0003 is below safety..not this...email 1 subject : stockcode SKU0001 is below safetyemail 2 subject : stockcode SKU0001SKU0002 is below safetyemail 3 subject : stockcode SKU0001SKU0002SKU0003 is below safetywhich is whats happening.Fri, 08 Mar 2013 08:50:14 GMTspinhttp://www.sqlservercentral.com/Forums/Topic1428514-338-1.aspxAnd what is the problem?Fri, 08 Mar 2013 08:32:55 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1428514-338-1.aspxhi alli'm writing a cursor to send emails when our stock level goes below a safety value. The cursor seems to work fine except i have a in my email subject i use a @sku variable which at the moment is concatenating all the sku's for each mail.i.e. the first email is fine: subject - SKU0001 IS BELOW SAFTEYthe second email reads : subject - SKU00014SKU0002 BELOW SAFETYthe third will read : subject - SKU00014SKU0002SKU0003 BELOW SAFETYhere's the code:[code="sql"]DECLARE@emailTo VARCHAR(255),@email VARCHAR(max),@sku VARCHAR(30),@emailSubject VARCHAR(128),SET @emailTo = 'myemail@mycompany.co.uk'SET @emailSubject = 'Warning stock is below safety for : '-- SELECT RECORDS THAT NEED TO BE EMAILED DECLARE c1 CURSOR FOR SELECT @emailTo as email_add, rtrim(StockCode) as StockCode FROM dbo.tbl_STOCK_safety_check WHERE email_status = 1-- LOOP THROUGH RECORDSET AND SEND EMAIL FOR EACH RECORD OPEN c1 FETCH NEXT FROM c1 INTO @email, @sku WHILE @@FETCH_STATUS <> -1 BEGIN SELECT @emailSubject = @emailSubject + @sku EXEC msdb.dbo.sp_send_dbmail @importance = 'High', @recipients= @email , @subject = @emailSubject FETCH NEXT FROM c1 INTO @email,@sku END CLOSE c1 DEALLOCATE c1[/code]Fri, 08 Mar 2013 04:44:30 GMTspin