Solved

SQL Error - Need suggestions

Posted on 2009-03-31
5
260 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
  • 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 500 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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now