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
3 3460
Am I missing something?
What error message did you get?
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
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: James |
last post by:
I am currently working on a PHP based website that needs to be able to draw
from Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. I
took a lot of time and care creating a...
|
by: Jason |
last post by:
I have a field in my databse that stores comments submitted from a web
form. For some reason the data is being truncated when re-displayed. I
have the settings as follows:
varchar(255) is this...
|
by: Zack Sessions |
last post by:
If I declare a local variable in a cursors query and append values to
it with successiuve concatenations, how come SQL7 does not report an
error if I concatenate a value to it that will overflow...
|
by: John |
last post by:
Hi Everyone,
I have a question about dynamically changing the length of a varchar(n)
field, in case the value I'm trying to insert is too big and will give
a "truncated" error, but before the...
|
by: Chris Moore |
last post by:
Hello,
I am attempting to write a stored procedure that builds and executes a
dynamic SQL statement which can be up to 8000 characters long.
Therefore, I have declared a variable of type...
|
by: sbcaco |
last post by:
Hello again...
can someone tell me how to work around this error:
String or binary data would be truncated.
I am doing this:
update table1
set =
|
by: manoj339 |
last post by:
I have varchar variable declared .When I print this var it shows me correct value before a C function.
Inside a c function this variable value is getting truncated .
The c function does not pass...
|
by: Krishna Ladwa |
last post by:
In Sql Server 2000 Version, I found that no Notification message box appears when converting text column to varchar but the data gets truncated to the given size for the varchar. Whereas it appears...
|
by: maheshmohta |
last post by:
Background
Often while remodeling legacy application, one of the important tasks for the architects is to have an optimum usage of storage capabilities of database. Most of the legacy applications...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |