Email results from trigger tables

I'm trying to e-mail the results of a trigger that fires on an employee
table. I'm trying to included the results of either the DELETED or INSERTED
tables, but nothing is returned. Can xp_sendmail include DELETED or INSERTED
table queries in the @.query = parameter?
I'm trying to send an e-mail to someone whenever a new employee is added or
deleted.
Thanks.Regardless that doesn´t work because the new session with XP_sendmail won´t
know about your deleted tables, the action you want to perform is not
preferable, because mail sending will be done synchronisly. So if your
mailserver is stuck in a problem and need 5 minutes for sending a mail (for
some reason) your transaction will hold on for that time, if the process
throws an error, your transaction might rollback. You don´t want that, erh ?
I would suggest (as this is not time critical) to write the data in a table
which is regulary checked for content to be sent.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Shawn Barrow" wrote:
> I'm trying to e-mail the results of a trigger that fires on an employee
> table. I'm trying to included the results of either the DELETED or INSERTED
> tables, but nothing is returned. Can xp_sendmail include DELETED or INSERTED
> table queries in the @.query = parameter?
> I'm trying to send an e-mail to someone whenever a new employee is added or
> deleted.
> Thanks.

Eliminating Duplicated

I have 3 tables Employee (empno,name,city) , Emp-Pl (empno,noofleaves) , and
Emp-Cl (empno,noofleaves). Now i require the report in the following format
Sample Data
employee
1 ravi kumar rajahmundry
2 ravindra kakinada
emp-pl emp-cl
empno noofleave empno noofleave
1 2 1 4
1 3 1 2
2 4 1 3
2 1 2 1
2 1
EMPNO NAME CL PL
1 ravikumar 4 2
2 3
3
2 ravindra 1 4
1
1
"venkataramarao" <venkataramarao@.discussions.microsoft.com> wrote in message
news:AC5F930E-5EE5-41B2-AB7B-C809082EF5C7@.microsoft.com...
>I have 3 tables Employee (empno,name,city) , Emp-Pl (empno,noofleaves) ,
>and
> Emp-Cl (empno,noofleaves). Now i require the report in the following
> format
> Sample Data
> employee
> 1 ravi kumar rajahmundry
> 2 ravindra kakinada
> emp-pl emp-cl
> empno noofleave empno noofleave
> 1 2 1 4
> 1 3 1 2
> 2 4 1 3
> 2 1 2 1
> 2 1
> EMPNO NAME CL PL
> 1 ravikumar 4 2
> 2 3
> 3
> 2 ravindra 1 4
> 1
> 1
I don't understand your requirements. Why does data from the CL table appear
in both the CL and PL columns? What determines this output?
Please post DDL and post sample data as INSERT statements rather than
sketches of tables.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Eliminating blank pages when printing

I have a report which prints an employee information sheet for multiple
employees - with each employee' sinformation on a separate page. When I view
and navigate through this online, I see one page per employee (which is
correct). However, when I print this report, I get a blank page (with the
page header but no employee data) inbetween each correct/full page. Is there
a way to suppress these blank pages?
FYI - the report data and section defined sizes are not big enough to cause
another page to spit out for each employee record.
Thank you in advance.On Nov 1, 1:42 pm, "Michael K. Walter" <mkwal...@.sqlcraft.com> wrote:
> I have a report which prints an employee information sheet for multiple
> employees - with each employee' sinformation on a separate page. When I view
> and navigate through this online, I see one page per employee (which is
> correct). However, when I print this report, I get a blank page (with the
> page header but no employee data) inbetween each correct/full page. Is there
> a way to suppress these blank pages?
> FYI - the report data and section defined sizes are not big enough to cause
> another page to spit out for each employee record.
> Thank you in advance.
If you are exporting to PDF format, this is pretty common. If so,
encapsulating a majority of your table/matrix controls (where
applicable) in a single rectangle control could be helpful. Also, you
might want to check your margins (via: Layout view >> Report drop-down
tab >> Report Properties... >> Layout) and possibly reduce them.
Another thing to check is how wide your report is in Layout view.
Normally, you will want to keep it at or below 6.5". Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Nov 1, 11:42 pm, "Michael K. Walter" <mkwal...@.sqlcraft.com> wrote:
> I have a report which prints an employee information sheet for multiple
> employees - with each employee' sinformation on a separate page. When I view
> and navigate through this online, I see one page per employee (which is
> correct). However, when I print this report, I get a blank page (with the
> page header but no employee data) inbetween each correct/full page. Is there
> a way to suppress these blank pages?
> FYI - the report data and section defined sizes are not big enough to cause
> another page to spit out for each employee record.
> Thank you in advance.
Hi Micheal
Check with your page width & height. B'coz of the width & height is
not fixing the one page that the reason Ur getting blank page while
taking printout..
Thanks
Sundaraguru S

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.