[SOLVED] Table Join Error - 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

General SQL Server Forums

New to SQL Server Programming

[SOLVED] Table Join Error

Author

Topic

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2006-12-01 : 19:35:10

OK I have tried just about every combination over the past 3 days to get this working and have gotten close but just can't seem to get past this.

Truncate Table [SecurityEvents_Temp]

Insert Into SecurityEvents_Temp (
[DepartmentNumber],
[EventLog], 
[RecordNumber],
[TimeGenerated],
[TimeWritten],
[EventID],
[EventType],
[EventTypeName], 
[EventCategory],
[EventCategoryName],
[SourceName],
[Strings], 
[ComputerName],
[SID],
[Message],
[Data],
[UserName],
[DomainName]
)

Select 
[DepartmentNumber] = '001',
[UserName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 3),
[DomainName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 4),
[Events].*
from [Events]
join EventstoLog ON dbo.Events.EventID = dbo.EventsToLog.EventID

quote:
Msg 257, Level 16, State 3, Line 3
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

The Select with the Join at the bottom work great by itself.
The whole script without the Join works.
But add the Join to the Insert Into script and it fails.

Can someone look at this and point me in the right direction??

Also, below is most of the table strustures just in case.

USE [EventLogs]
GO
/****** Object:  Table [dbo].[Events]    Script Date: 12/01/2006 18:15:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Events](
	[EventLog] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[RecordNumber] [int] NULL,
	[TimeGenerated] [datetime] NULL,
	[TimeWritten] [datetime] NULL,
	[EventID] [int] NULL,
	[EventType] [int] NULL,
	[EventTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EventCategory] [int] NULL,
	[EventCategoryName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SourceName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Strings] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ComputerName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Message] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Data] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [EventLogs]
GO
/****** Object:  Table [dbo].[SecurityEvents_Temp]    Script Date: 12/01/2006 18:15:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SecurityEvents_Temp](
	[DepartmentNumber] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[UserName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DomainName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EventLog] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[RecordNumber] [int] NULL,
	[TimeGenerated] [datetime] NULL,
	[TimeWritten] [datetime] NULL,
	[EventID] [int] NULL,
	[EventType] [int] NULL,
	[EventTypeName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EventCategory] [int] NULL,
	[EventCategoryName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SourceName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Strings] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ComputerName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Message] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Data] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [EventLogs]
GO
/****** Object:  Table [dbo].[EventsToLog]    Script Date: 12/01/2006 18:16:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EventsToLog](
	[EventID] [int] NULL,
	[EventDescription] [char](120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Thank You,

John Fuhrman
http://www.titangs.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-12-01 : 21:58:22

You need to match up the columns you are inserting into with the columns that you are selecting for the insert. execute your select statement alone and compare the columns returned with the list of columns expressed under: "Insert Into SecurityEvents_Temp..." The error is that the first column of your events table eventLog varchar(255) but you are attempting to insert that value into a datetime column (TimeGenerated).

Be One with the Optimizer
TG

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2006-12-01 : 23:03:02

Thank you, Thank you, Thank you, Thank you, Thank you!!

That worked.

Thank You,

John Fuhrman
http://www.titangs.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources