Retrieve data from sql 2000 database and send via email
-
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.Anybody can give me some example code how to write it?
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
-
you can send a mail from sql server 2000 with two ways (as of i know).
Sql Mail:-
Setup the outlook on sql server and configure and send a mail.
http://support.microsoft.com/kb/870675
SMTP :
if you know the SMTP ip you can use it to send with CDO :
http://support.microsoft.com/kb/312839
HTH.
Thanx.
Vinay
Thanx.
Vinayhttp://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay -
Hi there, i know how to send the mail but have no idea how to grad the data i wan every time when there is any update to the alarm.
Regards,
CL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply