Emailing Reports

Hi everyone I have a database that I am creating for another department. He emails applications to potential vendors. And I was wanting sql to do this for him. I was reading about xtended procedures XP_Sendmail, but this is the first time I have created an extended stored procedure. I need for it to email an attachment (the report)Read BOL on xp_sendmail. It's all there.sql

Email

Hi all me again I gotta question, the Sr executive asst has a number of reports that gets emailed to different department, only problem is those departments are on a totally different server. I am guessing that we need to start SQL AGENT Emailing MAPI to do this for us, of course I could be wrong. This is one of the stored procedures that creates one of the reports

CREATE PROCEDURE createSecurityListtobeEmailed
AS
SELECT SecurityListX."Last Name", SecurityListX."First Name", SecurityListX.MI, SecurityListX.Sex, SecurityListX.DOB, SecurityListX.IR#, SecurityListX."Issue Date", SecurityListX."Date served", SecurityListX.Duration, SecurityListX."Exp Date", SecurityListX."Reason for Exclusion", SecurityListX."Added Comment", SecurityListX.type INTO SecurityList
FROM SecurityListX
GROUP BY SecurityListX."Last Name", SecurityListX."First Name", SecurityListX.MI, SecurityListX.Sex, SecurityListX.DOB, SecurityListX.IR#, SecurityListX."Issue Date", SecurityListX."Date served", SecurityListX.Duration, SecurityListX."Exp Date", SecurityListX."Reason for Exclusion", SecurityListX."Added Comment", SecurityListX.type
HAVING (((SecurityListX."Last Name") Is Not Null))
GOIt appears that this just does a simple aggregation from SecurityListX into SecurityList. I can't see that it has any return set, or that it emails anything anywhere.

Are you sure that you aren't missing a few pieces to this puzzle ?

-PatP|||yes pat there are a few pieces of the puzzle missing, this will create a list which will be made into a report that a webpage on the casinos server, that the security department can view. Now I guess I have inherited this db and I need to include in the stored procedures using Xp_sendmail but I am unsure how to do it, and what I am doing is returning nothing but errors. So if you please sir....HELP???

Goodmorning Pat :)|||This is actually way too complex for me to even hazard a guess about what you really need to do, based on what I know so far. A lot depends on your mail configuration, security requirements, etc., so there isn'ta "one size fits most" kind of answer.

If all you want to do is run that statement, then send someone an email to tell them it ran (or failed), you can do that with just SQL Agent. If you want to extract the rows from the table and incorporate them into a mail message, then I'd suggest xp_sendmail.

At the moment I'm hampered by my rather limited understanding of what you want!

-PatP|||At the moment I'm hampered by my rather limited understanding of what you want!

And just when did that start to be a problem?

Sorry...I have Notus Lotes...(piece of garbage)|||And just when did that start to be a problem?

Sorry...I have Notus Lotes...(piece of garbage)I'm hampered by Bloats too. It does provide a great deal of casual entertainment as I'm trying to get some work done!

-PatP|||Nevermind Pat sorry to have bothered you|||Huh ?!?! Does that mean that you've solved the problem, or that you are going to look for another solution? Now you've given us enough to work on (via the snippets of VBA code) and you're going to take your ball and go home ?!?! Piddle! :rolleyes:

-PatP|||Misunderstood thought you were still unclear as was getting frustrated. I'm sure you have better things to do then to Mind read all day, since I couldnt get my point across. Had to retrieve more information was turning into a futile attempt...everyone was giving me bits and pieces.
XP_sendmail is new to me not sure how to do it|||That is actually one of the biggest hurdles to taking responsibility for a new (to you) system. You have to gather all of the stray bits of knowledge tucked into nooks and crannies throughout the systems affected, and if you have any pity on the next poor soul to come along you'll document the living bejesus out of what confused you.

If the next person has at least a few breadcrumbs to start from, they'll be a million miles further along than where you had to start from, and will hopefully utter prayers for you for the rest of their lives! Keep in mind that almost all systems are living things, they either grow or die, so the next person will have the chaos that you inherited plus everything that gets added during your tenure too. I've got a few systems that are exceptions that make great stories, but those are exceptions, not the rule by any means!

-PatP

Eliminate duplicate records

department table got duplicate data......

when i insert data from department to employee table, i want to eliminate duplicate and insert unique data.

department

emp_id emp_name emp_address

10 mary melville,ny

10 mary longisland,ny

11 linsy sugarland,tx

12 sam fairfax,va

12 sam dice,va

i want result in employee table....How can i get it with fastest query...i got tons of record to insert.

emp_id emp_name emp_address

10 mary melville,ny

11 linsy sugarland,tx

12 sam fairfax,va

insert into employee(emp_id,emp_name,emp_address)
select emp_id, max(emp_name), max(emp_address)
from department

where emp_id is not null

If you are using SQL Server 2005, give a look to the ROW_NUMBER() functon in books online. One technique is to create a "sequence number" for each grouping using the ROW_NUMBER() function and to select the records that have a generated "sequence number" of 1.

Also, something like this can be done:

Code Snippet

declare @.mockup table
( emp_id integer,
emp_name varchar(10),
emp_address varchar(25)
)
insert into @.mockup
select 10, 'mary', 'melville,ny' union all
select 10, 'mary', 'longisland,ny' union all
select 11, 'linsy', 'sugarland,tx' union all
select 12, 'sam', 'fairfax,va' union all
select 12, 'sam', 'dice,va'
--select * from @.mockup

select emp_id,
emp_name,
( select max(emp_address)
from @.mockup b
where a.emp_id = b.emp_id
) as emp_address
from @.mockup a
group by emp_id, emp_name

/*
emp_id emp_name emp_address
-- - -
10 mary melville,ny
11 linsy sugarland,tx
12 sam fairfax,va
*/

|||

For SS2005:

Code Snippet

create table #dept( emp_id int, emp_name varchar(50), emp_address varchar(50) )

insert into #dept

select 10, 'mary', 'melville,ny'

union all select 10, 'mary', 'longisland,ny'

union all select 11, 'linsy', 'sugarland,tx'

union all select 12, 'sam', 'fairfax,va'

union all select 12, 'sam', 'dice,va'

select emp_id, emp_name, emp_address

from

(

select *, row_number() over (partition by emp_id order by emp_id, emp_name desc, emp_address desc) as rn

from #dept

) emp

where rn = 1

For SS2000:

Code Snippet

select emp_id, max(emp_name) as emp_name, max(emp_address) as emp_address

from #dept

group by emp_id

|||

The trick here is to get a list of the records you want to keep. However, using MAX on character fields isn't the way to go (unless you don't care which record to keep). Hopefully you've got a column which gives some sort of order to the data (eg datecreated) so you can choose the most recent one, otherwise its a guessing game as to which one is "keepable"

So assuming you have one... ;-)

INSERT INTO employee (emp_id, name, emp_address)

SELECT d.emp_id, d.name, d.emp_address
FROM department d

INNER JOIN

(SELECT emp_id, MAX(datecreated) AS dt

FROM department

GROUP BY emp_id) AS dist

ON d.emp_id = dist.emp_id and d.datecreated = dist.dt

HTH!

|||

when i use max function....i get this error....

i get an error like this.....
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

|||When you try the insert? Check that your column definitions in the department and employee tables match. You may have a smaller field in the destination table...|||

The problem isn't with max(), it's a mismatch between column definitions in your source and your target.

|||

Getting this error...

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'datecreated'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'datecreated'.

|||

I guess you don't have a column matching that in your table ;-)

My example was based upon the presumption that you had a field such as that in your table. You won't be able to just copy and paste it and get results. It was based upon the following DDL

CREATE TABLE departments
(emp_id int, emp_name varchar(100), emp_address varchar(100), datecreated datetime)

Basically, if you do have a field (in my example datecreated) that tells you which record is the one you want to keep then you can use a variation on my example. If you don't have one and you're happy for the choice to be made based arbitarilly then go with Dales option. His can be used straight out of the box.

Sorry for any confusion.

|||

Is there any internal field of table record i can use?.

like in oracle, do we have system variable/ records created along with insert of data in a row in sql server?.

so can i use that date created field?.

|||

I'm afraid not. If you don't have an explicit date field you can't use my example.

If the ddl of your table is the same as in Dales example, you should go with one of his excellent suggestions.

|||

i used dale solution...max thing.

select emp_id, max(emp_name) as emp_name, max(emp_address) as emp_address

from #dept

group by emp_id

works fine today.