Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
sql 2005 email store procedure
Posted on 2009-07-01
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'
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
5 Comments
Active today
just do
select * from @productTotals
if @@rowcount > 0
exec . . .
else
select 'No Rows to Email' as Status
select * from @productTotals
if @@rowcount > 0
exec . . .
else
select 'No Rows to Email' as Status
Active 7 days ago
insert into @ProductTotals(id_number,e
select id_number,employee_name,ac
where status = 0
IF @@ROWCOUNT > 0
EXEC msdb.dbo.sp_send_dbmail
@recipients='reddy@arjaywi
@body= 'select * from @producttotals',
@subject = 'new record inserted',
@profile_name = 'Arjayreddysemail'
select id_number,employee_name,ac
where status = 0
IF @@ROWCOUNT > 0
EXEC msdb.dbo.sp_send_dbmail
@recipients='reddy@arjaywi
@body= 'select * from @producttotals',
@subject = 'new record inserted',
@profile_name = 'Arjayreddysemail'
Active 7 days ago
EXEC msdb.dbo.sp_send_dbmail
@recipients='reddy@arjaywi
@query = 'select * from @producttotals',
@subject = 'new record inserted',
@profile_name = 'Arjayreddysemail'
@recipients='reddy@arjaywi
@query = 'select * from @producttotals',
@subject = 'new record inserted',
@profile_name = 'Arjayreddysemail'
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".
(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".
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month3 days, 17 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.