Solved

sql 2005 email store procedure

Posted on 2009-07-01
5
180 Views
Last Modified: 2012-05-07
i am trying to create a store procedure. where the count is more than zero it should send an email with all records. can some one help me out with this.
declare @maxcount as varchar(10)
 
select @maxcount = count(id_number)from dbo.iqm_newemployees
 
select @maxcount
 
if @maxcount >= 3 
	Select @maxcount
 
DECLARE @ProductTotals TABLE 
(
[id_number] [int] NOT NULL,
	[employee_name] [varchar](51) NOT NULL,
	[account_disabled] [bit] NOT NULL,
	[startdate] [datetime] NULL,
	[defaultlocation] [int] NULL
 
)
insert into @ProductTotals(id_number,employee_name,account_disabled,startdate,defaultlocation)
select id_number,employee_name,account_disabled,startdate,defaultlocation from dbo.iqm_newemployees
where status = 0 
 
select * from @ProductTotals
 
 
 
 
	EXEC msdb.dbo.sp_send_dbmail
      @recipients='reddy@arjaywireless.com',
      @body= 'select * from @producttotals', 
      @subject = 'new record inserted',
      @profile_name = 'Arjayreddysemail'
 
 
else
 select 'test'

Open in new window

0
Comment
Question by:romeiovasu
  • 2
  • 2
5 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 24755754
just do
select * from @productTotals

if @@rowcount > 0
              exec . . .
else
select 'No Rows to Email' as Status
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24755786
insert into @ProductTotals(id_number,employee_name,account_disabled,startdate,defaultlocation)
select id_number,employee_name,account_disabled,startdate,defaultlocation from dbo.iqm_newemployees
where status = 0

IF @@ROWCOUNT > 0
 
      EXEC msdb.dbo.sp_send_dbmail
      @recipients='reddy@arjaywireless.com',
      @body= 'select * from @producttotals',
      @subject = 'new record inserted',
      @profile_name = 'Arjayreddysemail'
 
0
 

Author Comment

by:romeiovasu
ID: 24755899
but in my email i am gettign this one

select * from @producttotals

i am not getting the result.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24755922
   EXEC msdb.dbo.sp_send_dbmail
      @recipients='reddy@arjaywireless.com',
      @query = 'select * from @producttotals',
      @subject = 'new record inserted',
      @profile_name = 'Arjayreddysemail'

 
0
 

Author Comment

by:romeiovasu
ID: 24755948
this is the error i am getting now

(3 row(s) affected)
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478
Query execution failed: Msg 1087, Level 15, State 2, Server ARJAYDATA, Line 1
Must declare the table variable "@producttotals".
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question