VarChar being truncated.

[SparkByte]

Now I know this has been asked before, but I am not getting the same error as mentioned in the earlier posts I could find.

Server is SQL 2000 Ent.

As you can see the table is for parsing Security event logs. This information is then used to create reports that are sent out to clients.

Problem:
The Strings and message values from the EventLogs gets truncated when pulled from the servers. (68 of them)

When I try to set the columns to their respect maximums I get an an error.

Could someone enlighten me as to why. I am VERY new to SQL programming, as in, no formal training. So this is a bit puzzling.

Thanks..

Code and output below.

USE [LogParser]
GO
/****** Object:  Table [dbo].[SecurityEvents_Temp]    Script Date: 03/27/2007 15:57:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SecurityEvents_Temp](
    [BankNumber] [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](8000) 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](8000) 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

OutPut

Warning: The table 'SecurityEvents _Temp' has been created but its maximum row size (18377) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

SparkByte
www.titangs.com

[iburyak]

Am I missing something?
What error message did you get?

[tezza98]

This from SQL Server Books Online

SQL Server can have as many as two billion tables per database and 1,024 columns per table. The number of rows and total size of the table are limited only by the available storage. The maximum number of bytes per row is 8,060. If you create tables with varchar, nvarchar, or varbinary columns in which the total defined width exceeds 8,060 bytes, the table is created, but a warning message appears. Trying to insert more than 8,060 bytes into such a row or to update a row so that its total row size exceeds 8,060 produces an error message and the statement fails.

So you can only have a maximum of 8060 bytes, so you will have to make the varchars smaller to accomodate this

[iburyak]

You found a right comment from Books on line.

If you need to store larger amount then this you have following options for consideration:
1. Use Text datatype instead of varchar which can store as much data as your computer can handle. This requires some programming skills.
2. You can brake event log message into smaller pieces and store it as consecutive records with additional sequence number column.
3. You can store messages in files and just have full path to that file in your table in case you need to retrieve context and display it in some front end.


Good Luck.