July 14, 2009 at 9:08 am
I want to run a sql script through job, but I need a total count that of query by email
July 14, 2009 at 9:55 am
You can't really pass row counts back to the SQL Agent from a script. You may want to consider logging your row counts (and any other data) into a table and creating a second step that e-mails notifications out or using a Service Broker messaging system to queue up e-mail notifications.
July 14, 2009 at 10:02 am
If your "Step" is T-SQL and when you execute it returns your total row count, then you should be able to output that using the "Advanced" section for the specific step.
See attached.
I use this to output the results of an index rebuild
Then use db mail to send
July 14, 2009 at 10:19 am
Thanks, can you explain me let more how to do it,
I have following script and want to capture the counts from results
update cnsmr_accnt_tag set cnsmr_accnt_sft_delete_flg = 'Y',
upsrt_dttm = getdate(), upsrt_trnsctn_nmbr = 9000
from cnsmr_accnt_tag cat1
inner join
(select t2.cnsmr_accnt_tag_id
from (
select rank() over (PARTITION BY cat.cnsmr_accnt_id, cat.tag_id
order by cnsmr_accnt_tag_id) as rank_nmbr,cat.*
from cnsmr_accnt_tag cat
inner join (
select cnsmr_accnt_id, cat.tag_id, tag_shrt_nm, t.tag_typ_id, tag_typ_shrt_nm,
count(*) as tag_count
from cnsmr_Accnt_Tag cat
inner join tag t on t.tag_id = cat.tag_id
inner join tag_typ tt on tt.tag_typ_id = t.tag_typ_id
where cnsmr_accnt_sft_delete_flg = 'N'
group by cnsmr_accnt_id, cat.tag_id, tag_shrt_nm, t.tag_typ_id, tag_typ_shrt_nm
having count(*) > 1
) t1
on cat.cnsmr_accnt_id = t1.cnsmr_accnt_id and
cat.tag_id = t1.tag_id and
cat.cnsmr_accnt_sft_delete_flg = 'N'
--order by cat.cnsmr_accnt_id, cat.tag_id
) t2
where rank_nmbr > 1) cat2
on cat1.cnsmr_accnt_tag_id = cat2.cnsmr_accnt_tag_id;
July 14, 2009 at 10:24 am
Using SQL_Lock's advice, you'd need to configure your output to create a new output file for the step and create a second step to e-mail that file out as an attachment.
July 14, 2009 at 10:29 am
- Create Job
- Create Step 1 (your update statement) and in the Advanced section use an output file.
- Create step 2 (code below - replace with your variables).
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@domain.com',
@body = 'Testing123',
@subject = 'Mail using DB Mail' ,
@file_attachments = 'path_to_output_file';
GO
- Go back to the advanced options for step 1 and select step 2 within "On Success".
July 14, 2009 at 1:12 pm
Hello,
Thanks for the info, I try but still I can not get my counts by email, see my attached script, I want to get the number from TAGCOUNT's in my email. if you please create some kind of job with the reference of my script I will be very thanks full of you
use crs5_oltp
/*
CR Software inc.,
================================================================================================
============> Please back up your database before running this script 1
) t1
on cat.cnsmr_accnt_id = t1.cnsmr_accnt_id and
cat.tag_id = t1.tag_id and
cat.cnsmr_accnt_sft_delete_flg = 'N'
--order by cat.cnsmr_accnt_id, cat.tag_id
) t2
where rank_nmbr > 1) cat2
on cat1.cnsmr_accnt_tag_id = cat2.cnsmr_accnt_tag_id;
--=========================================================================
-- Delete duplicate consumer tags
--=========================================================================
update cnsmr_tag set cnsmr_tag_sft_delete_flg = 'Y', upsrt_dttm = getdate(), upsrt_trnsctn_nmbr = 9000
from cnsmr_tag ct1
inner join
(select t2.cnsmr_tag_id
from (
select rank() over (PARTITION BY ct.cnsmr_id, ct.tag_id order by cnsmr_tag_id) as rank_nmbr,
ct.*
from cnsmr_tag ct
inner join (
select cnsmr_id, ct.tag_id, tag_shrt_nm, t.tag_typ_id, tag_typ_shrt_nm, count(*) as tag_count
from cnsmr_Tag ct
inner join tag t on t.tag_id = ct.tag_id
inner join tag_typ tt on tt.tag_typ_id = t.tag_typ_id
where cnsmr_tag_sft_delete_flg = 'N'
group by cnsmr_id, ct.tag_id, tag_shrt_nm, t.tag_typ_id, tag_typ_shrt_nm
having count(*) > 1
) t1
on ct.cnsmr_id = t1.cnsmr_id and
ct.tag_id = t1.tag_id and
ct.cnsmr_tag_sft_delete_flg = 'N'
--order by ct.cnsmr_id, ct.tag_id
) t2
where rank_nmbr > 1) ct2
on ct1.cnsmr_tag_id = ct2.cnsmr_tag_id;
--=========================================================================
GO
IF @@ERROR0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'Script succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'Script failed'
GO
DROP TABLE #tmpErrors
GO
July 14, 2009 at 1:21 pm
OK, what is TAGCOUNT's? What have you tried? What problems/errors are you getting?
Have you even attempted SQL_Locks suggestions? If so, what part are you stuck on, what have you been able to complete?
July 14, 2009 at 1:31 pm
my script is working, I need the value from tagcount from first step and also tagcount from step2. and send me a email say tagcount= xx and tagcount = xx
July 14, 2009 at 1:40 pm
You can't get the SQL Agent to send you an e-mail that says tagcount=xx and tagcount=xx. You can, however, get SQL Agent to e-mail the script results as an attatchment as SQL_Lock has suggested. Will this work for you?
If not, you'll need to do as I said in my first post and create a solution that allows you go communicate your script results to an outside process that will handle the e-mails.
July 14, 2009 at 1:43 pm
The best way maybe to spilt the 2 updates out into 2 "Steps",
Also remove the "NOCOUNT" from the beginning of the script.
This should display the number of rows updated. To test run the update script in SSMS and see what appears in your results windows!! Ideally you need to see...
(10001 row(s) affected.....)
Script succeeded
July 14, 2009 at 1:44 pm
The best way maybe to spilt the 2 updates out into 2 "Steps",
Also remove the "NOCOUNT" from the beginning of the script.
This should display the number of rows updated. To test run the update script in SSMS and see what appears in your results windows!! Ideally you need to see...
(10001 row(s) affected.....)
Script succeeded
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply