May 19, 2009 at 7:55 pm
Hi,
I'm trying to get some data from SQL 2000 database and send the data through email.
for example,when there is any update notification from table "CAF_ALARM" ,it will send all the data (alarm) to my email.
Anyone got the idea how to do it? I'm tried several method but it seem like cant work.
May 19, 2009 at 9:41 pm
You can do it in a trigger, but it's probably not a good idea. That's because in SQL 2000 you have to use SQL MAIL which is synchronous and also has a tendency to jam. Since triggers are also synchronous within a transaction, they will slow do access to your table terribly.
Upgrading to SQL 2005 would give you several different efficient ways to do it.
I would suggest writing a Job to just poll the table once a minute for new alerts and if found then send them.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 19, 2009 at 11:45 pm
RBarryYoung (5/19/2009)
You can do it in a trigger, but it's probably not a good idea. That's because in SQL 2000 you have to use SQL MAIL which is synchronous and also has a tendency to jam. Since triggers are also synchronous within a transaction, they will slow do access to your table terribly.Upgrading to SQL 2005 would give you several different efficient ways to do it.
I would suggest writing a Job to just poll the table once a minute for new alerts and if found then send them.
write a job to poll the table. yes, most of the people are recommend at that way but can give me a guide to do that?Is it use stored procedure to do it ?If possible give me some example of it. Im new in sql 🙁
thanks
May 19, 2009 at 11:57 pm
If you can provide us with the the table definition (CREATE TABLE ..., you can script this out from enterprise manager) and an example of what data change or action should initiate your emails, we would be happy to.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 20, 2009 at 1:54 am
RBarryYoung (5/19/2009)
If you can provide us with the the table definition (CREATE TABLE ..., you can script this out from enterprise manager) and an example of what data change or action should initiate your emails, we would be happy to.
below is the code, actually this is an alarm data, when there is any alarm in our equipment it will store in this table. I wish any new data update to the table will send an email to me with POSITION1,HAPPENTIME And CODE.
thanks a lot for your help 🙂
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CAF_ALARM]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CAF_ALARM]
GO
CREATE TABLE [dbo].[CAF_ALARM] (
[AID] [bigint] NOT NULL ,
[ID] [bigint] NOT NULL ,
[SYSTEMTYPE] [smallint] NOT NULL ,
[SERVERID] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POSITION1] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POSITION2] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POSITION3] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POSITION4] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POSITION5] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POSITION6] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POSITION7] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POSITION8] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POSITION9] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POSITION10] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NETYPE] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ALARMTYPE] [tinyint] NULL ,
[HAPPENTIME] [datetime] NULL ,
[LASTUPDATE] [datetime] NULL ,
[int] NULL ,
[REASON] [int] NULL ,
[SEVERITY] [tinyint] NULL ,
[CONFIRMTIME] [datetime] NULL ,
[CONFIRMER] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONFIRMSYSTEM] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONFIRMTYPE] [tinyint] NULL ,
[CONFIRMINFO] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FILTERSTATE] [tinyint] NULL ,
[INFO] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WORKSTATE] [tinyint] NULL ,
[SENDER] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROPAGATEADDR] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMMENTER] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMMENTSYSTEM] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMMENTTIME] [datetime] NULL ,
[COMMENTS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMATTR1] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMATTR2] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMATTR3] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMATTR4] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMATTR5] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMATTR6] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMATTR7] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMATTR8] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMATTR9] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMATTR10] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENTITY] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
May 20, 2009 at 2:25 am
Hi cl_see,
I'm in the process of developer a solution similar to your requirements.
I have a user on SS 2000, with no email client installed on the same server and a Novell network. I've used xp_smtp_sendmail, mentioned widely throughout SSC and available from http://www.sqldev.net/xp/xpsmtp.htm (the colours are a bit strange on this site - you may need to highlight the page to see the text!).
Via the scheduler I run a Stored Proc to scan a couple of tables of interest and write the results to a temporary table. Within this proc I then use xp_cmdshell to write this temporary table to a file, and attach this file to xp_smtp_sendmail (it is one of the parameters). Other paramters are the email address to send the message to and the I.P. address or name of the mail server.
BrainDonor.
May 20, 2009 at 2:51 am
BrainDonor (5/20/2009)
Hi cl_see,I'm in the process of developer a solution similar to your requirements.
I have a user on SS 2000, with no email client installed on the same server and a Novell network. I've used xp_smtp_sendmail, mentioned widely throughout SSC and available from http://www.sqldev.net/xp/xpsmtp.htm (the colours are a bit strange on this site - you may need to highlight the page to see the text!).
Via the scheduler I run a Stored Proc to scan a couple of tables of interest and write the results to a temporary table. Within this proc I then use xp_cmdshell to write this temporary table to a file, and attach this file to xp_smtp_sendmail (it is one of the parameters). Other paramters are the email address to send the message to and the I.P. address or name of the mail server.
BrainDonor.
hi there, can show me your code for run a stored proc to scan the table and write the result to a file?
thanks.
May 20, 2009 at 3:12 am
I've had to butcher it a little to remove any company-sensitive details:
CREATE PROC BrainDonor_Test
AS
SET NOCOUNT ON
DECLARE @rc int
DROP table ##Errors
CREATE TABLE ##Errors(
CC_IDInt,
ErrorVarChar(50)
)
-- Scan for suspect data
INSERT INTO ##Errors(CC_ID, Error)
SELECT DISTINCT PK_ID,
' Error type 1'
from dbo.Table1 (nolock)
WHERE PK_ID NOT IN (SELECT PK_ID FROM dbo.Table1 (nolock)
WHERE Table2 = 1)
UNION
SELECT g.PK_ID,
CASE
WHEN g.BT =1 THEN ' Error Type 2'
WHEN ISNULL(c.DT,0) 0 THEN ' Error Type 3'
ELSE ' Error Type 4'
END
FROM dbo.Table1 g(nolock)
INNER JOIN Table3 c (nolock) ON c.ID = g.ID
WHERE g.Table2 = 1
AND (g.BT =1 OR ISNULL(c.DT,0) 0 OR ISNULL(c.ID,0) = 100)
ORDER BY PK_ID
-- Any suspect records found?
IF EXISTS (SELECT * FROM ##Errors (nolock))
BEGIN
-- Write them to a csv file in vaguely legible format
DECLARE @Instruction VarChar(2000)
SET @Instruction = 'bcp "SELECT CAST (CC_ID AS VarChar) + Error FROM ##Errors (nolock)" queryout c:\CSVOut.csv -c'
EXEC master.dbo.xp_cmdshell @Instruction
-- send an email with the file.
EXEC @rc = master.dbo.xp_smtp_sendmail
@FROM= N'SQLServer@ScheduledJob', -- dummy address. A mandatory parameter but no address exists for the server
@TO= N'me@test_address.com',
@subject= N'Scan Errors',-- email title
@attachment= N'c:\CSVOut.csv',
@message= N'The attached file shows discrepencies within the tables.', -- text within the message
@type= N'text/html',
@server = N'BrainDonor.MailServer.com' -- email server address
END
GO
May 21, 2009 at 12:22 am
For my,can i write like this to retrieve POSITION1,HAPPENTIME And CODE?
Beside, is it will send the mail everytime my database update if use the code below??
The output i try to run :
Output:
Server: Msg 3701, Level 11, State 5, Line 5
Cannot drop the table '##Errors', because it does not exist in the system catalog.
Error: @attachment file "c:\CSVOut.csv" does not exist
can anyone tell me what wrong i did?how come the table and c:\CSVOut.csv will not exist?
Thanks
------------------------------------------------------------
CREATE PROC BrainDonor_Test
AS
SET NOCOUNT ON
DECLARE @rc int
DROP table ##Errors
CREATE TABLE ##Errors(
CODE int,
POSITION1 varchar(200),
)
-- Scan for suspect data
INSERT INTO ##Errors(CODE, POSITION1)
SELECT DISTINCT CODE,POSITION1
from dbo.CAF_ALARM (nolock)
-- Any suspect records found?
IF EXISTS (SELECT * FROM ##Errors (nolock))
BEGIN
-- Write them to a csv file in vaguely legible format
DECLARE @Instruction VarChar(2000)
SET @Instruction = 'bcp "SELECT Error FROM ##Errors (nolock)" queryout c:\CSVOut.csv -c'
EXEC master.dbo.xp_cmdshell @Instruction
-- send an email with the file.
EXEC @rc = master.dbo.xp_smtp_sendmail
@FROM = N'SQLServer@ScheduledJob', -- dummy address. A mandatory parameter but no address exists for the server
@TO = N'me@test_address.com',
@subject = N'Scan Errors', -- email title
@attachment = N'c:\CSVOut.csv',
@message = N'The attached file shows discrepencies within the tables.', -- text within the message
@type = N'text/html'
END
GO
May 21, 2009 at 8:50 am
When looking at the code I noticed that you have the Drop Table at the top of the Query. Move the drop table to the bottom of the query and this should correct the error message that you are receiving.
Thanks
Don
May 21, 2009 at 8:23 pm
-blank-
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply