Datetime query problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Datetime query problem

Author

Topic

DarkAngle
Starting Member

5 Posts

Posted - 2009-05-26 : 02:41:59

Im trying to retrieve some data from my table in database to a text file, but the problem is the HAPPENTIME always give me a blank in the text file. Below is my table script and my code.
Anyone have idea what going on?
Thanks.

Table Script
-------------------------------------
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 ,

[NETYPE] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ALARMTYPE] [tinyint] NULL ,
[HAPPENTIME] [datetime] NULL ,
[LASTUPDATE] [datetime] NULL ,
[CODE] [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 ,

[ENTITY] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

-----------------------------------------------
My code
-----------------------------------------------

SET NOCOUNT ON

DECLARE @rc int

CREATE TABLE ##Errors(
HAPPENTIME DateTime,
POSITION1 varchar(200),
CODE int
)

-- 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
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT *FROM ##Errors (nolock)" queryout c:\CSVOut.txt -c'

-- send an email with the file.
exec master..xp_sendmail
@recipients= N'mymail@gmail.com',
@subject = N'Scan Errors', -- email title
@type = N'text/html',
@query = N'SELECT * FROM ##Errors (nolock)'

DROP table ##Errors

END
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-26 : 02:46:02

You did not pass in any value to HAPPENTIME at all. The value will be NULL. You can specify default for it or pass in the value specifically in your INSERT INTO ##Errors statement.


CREATE TABLE ##Errors(
HAPPENTIME DateTime default getdate(),
POSITION1 varchar(200),
CODE int
)

KH
[spoiler]Time is always against us[/spoiler]

DarkAngle
Starting Member

5 Posts

Posted - 2009-05-26 : 04:22:54

thank you brother.. its work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources