?
Solved

SQL Error - Need suggestions

Posted on 2009-03-31
5
Medium Priority
?
289 Views
Last Modified: 2012-05-06
I am getting the following error on my below mentioned coding. Any suggestions / changes ?

Warning: The table 'cusChangeLog' has been created but its maximum row size (8806) 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.

if not exists(select * from sysobjects where type='u' and name='cusChangeLog')
	create table cusChangeLog ( tbl varchar(255), Usr varchar(255),dte datetime,SPID int, Host varchar(255),Query varchar(8000))
go
--drop table cusChangeLog
 
if exists(select * from sysobjects where type='tr' and name='DoctorFacility_cusChangeLog')
	drop trigger DoctorFacility_cusChangeLog
go
if exists(select * from sysobjects where type='tr' and name='DFIds_cusChangeLog')
	drop trigger DFIds_cusChangeLog
go
 
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
 
 
CREATE TRIGGER DoctorFacility_cusChangeLog ON dbo.DoctorFacility
FOR INSERT,UPDATE,DELETE
AS 
BEGIN
set nocount on
CREATE TABLE #inputbuffer (eventtype nvarchar(30)  NULL,
                           params    int           NULL,
                           eventinfo varchar(8000) NULL)
DECLARE @host nvarchar(12), @process INT,@sql nvarchar(2048)
 
SELECT @sql = 'dbcc inputbuffer( ' + CAST( @@SPID as nvarchar(10) ) + ') WITH NO_INFOMSGS'
INSERT INTO #inputbuffer exec sp_executesql @sql
 
SELECT @host = convert(varchar(12), hostname), @process = hostprocess
FROM  master..sysprocesses spc, master..sysdatabases sdb
WHERE spc.dbid = sdb.dbid AND spc.spid = @@SPID
 
insert into cusChangeLog
select 'DoctorFacility',dbo.GetLogonId(),getdate(),@@SPID,@host,EventInfo from #inputbuffer
END
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
 
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
 
 
CREATE TRIGGER DFIds_cusChangeLog ON dbo.DFIds
FOR INSERT,UPDATE,DELETE
AS 
BEGIN
CREATE TABLE #inputbuffer (eventtype nvarchar(30)  NULL,
                           params    int           NULL,
                           eventinfo varchar(8000) NULL)
DECLARE @host nvarchar(12), @process INT,@sql nvarchar(2048)
 
SELECT @sql = 'dbcc inputbuffer( ' + CAST( @@SPID as nvarchar(10) ) + ') WITH NO_INFOMSGS'
INSERT INTO #inputbuffer exec sp_executesql @sql
 
SELECT @host = convert(varchar(12), hostname), @process = hostprocess
FROM  master..sysprocesses spc, master..sysdatabases sdb
WHERE spc.dbid = sdb.dbid AND spc.spid = @@SPID
 
insert into cusChangeLog
select 'DFIds',dbo.GetLogonId(),getdate(),@@SPID,@host,EventInfo from #inputbuffer
END
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
-- 
-- select * from cusChangeLog

Open in new window

0
Comment
Question by:Jeff S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24029587
This error message indicates that you have variable length columns in your table (such as nvarchar or varbinary) and that the total maximum length of all the columns adds up to more than 8060 bytes. You can still insert rows into the table provided that the total length of the data in each row does not exceed 8060 bytes. However, if the data does exceed 8060 bytes, the insertion fails with the following error message:

Server: Msg 511, Level 16, State 1, Line 5
Cannot create a row of size <rowlength> which is greater than the allowable maximum of 8060.
The statement has been terminated.

Does your eventinfo varchar(8000) needs to have 8000 characters. Recuce it to 7250 to avoid that error
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24029608
Otherwise if you need to have 8000 bytes or more, then change the datatype to text from varchar(8000).
This will definitely help you out.
0
 
LVL 7

Author Comment

by:Jeff S
ID: 24029635
rrjegan17 -
Would you recommend one over the other?
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 24029705
I am providing you two choices to resolve your issue:

1. Convert your varchar(8000) to varchar(7250)
OR
2. Convert your varchar(8000) to text datatype
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24031096
rrjegan:

2. Convert your varchar(8000) to text datatype

the TEXT datatype will be depricated and you should refer to using the appropriate (n)varchar(max) field.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question