Hello,
I kindly request you to help me on how to create a simple package for the following senior, I'm new in this area and i did a workaround inside Business Intelligence Development Studio, it seems to me it is easy so far but after specifying for me which flows to use.
1-Open a connection to MS SQL Server R2
2-Choose a Table (In my senior it is one table)
3-Write a query
3.1- if the query retrieve records then do the following
Send a Mail
Write a DML like updating another table
Else
do nothing
3.2 This will be scheduled in the database to be run every minute (don't worry the DB Administrator will take care after i send him the package file)
Now:
what flows to use and what about the query i'm using :
Four Main Columns:
Request NO
status ------ Status has values like (Open =1, Closed=2, Canceled=3)
et ---------- End Time
sdate ------ Date
Select Count(*)
From Table
Where status = 1
and
Server Time > et
and
Server Date = sdate
--------------------------
Questions:
1- What is the right type of the server time and date, i'm using datetime2(7) for field End Time and Date for field sdate.
2- After i get a record with a status of Open, i need to change the record to a status of Closed to prevent the job from sending a message every minute, so where shall i write this DML statement:
UPDATE table
SET status = 2
WHERE Server Time > et
and
Server Date = sdate
and
status = 1
3- When sending the message how can i define or pass the request number field of that particular record in the message body.
Thanks..
declare @report_file_name varchar(30),
@sql varchar(max),
@emailbody varchar(4000),
@subjecttext varchar(1000),
@parameter sysname
select @parameter = request_number from your_table_name;
set @subjecttext = 'This is a TEST email'+@parameter
set @sql = N'SET NOCOUNT ON
SELECT whatever FROM whatever_table WHERE whatever'
SET @report_file_name = 'file_list_at_' + convert(varchar(10),getdat
SET @emailbody = 'Here is the list you wanted as today(yyyy.mm.dd): '+ convert(varchar(10),getdat
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail Profile', -- mail profile here
@recipients='user@mail.com
@subject = @subjecttext,
@body = @emailbody,
@body_format = 'TEXT',
@query = @sql,
@attach_query_result_as_fi
@execute_query_database = 'YourDB', -- your database name here
@query_attachment_filename
@query_result_header = 1,
@query_result_no_padding = 1,
@query_result_separator = ' ',
@query_result_width = 1000;