Varchar limitation on 8000 problem in SQL Server 2000

8 Dec 2005 7:03 PM

Henrik Skak Pedersen

Hi,

I have a problem with varchar(8000) in my trigger. It is a big problem for
me that the X and Y variables below are limited by 8000 characters, how can
I make them larger?

I can illustrate it like this:

Create database AuditTest
Create table Audit (
    AuditId int identity,
    deletedText text,
    insertedText text
)

create table Employee (
    Id int identity,
    Name varchar(100)
)

CREATE trigger EmployeeAudit on Employee
AFTER INSERT, UPDATE, DELETE
AS
    DECLARE @X VARCHAR(8000)
    select @x = (SELECT * from deleted for xml auto, elements) --,
xmlschema)
    DECLARE @Y VARCHAR(8000)
    select @Y = (SELECT * from inserted for xml auto, elements) --,
xmlschema)
    INSERT INTO Audit
    values(@x, @y)

Thanks Henrik

8 Dec 2005 7:44 PM

David Gugick

Henrik Skak Pedersen wrote:
Show quote

> Hi,
>
> I have a problem with varchar(8000) in my trigger. It is a big
> problem for me that the X and Y variables below are limited by 8000
> characters, how can I make them larger?
>
> I can illustrate it like this:
>
> Create database AuditTest
> Create table Audit (
>    AuditId int identity,
>    deletedText text,
>    insertedText text
> )
>
> create table Employee (
>    Id int identity,
>    Name varchar(100)
> )
>
> CREATE trigger EmployeeAudit on Employee
> AFTER INSERT, UPDATE, DELETE
> AS
>    DECLARE @X VARCHAR(8000)
>    select @x = (SELECT * from deleted for xml auto, elements) --,
> xmlschema)
>    DECLARE @Y VARCHAR(8000)
>    select @Y = (SELECT * from inserted for xml auto, elements) --,
> xmlschema)
>    INSERT INTO Audit
>    values(@x, @y)
>
> Thanks Henrik

A bigger problem is this:

a.. FOR XML is not valid in subselections, whether it is in UPDATE,
INSERT, or DELETE statements, a nested SELECT statement, or other
statements (SELECT INTO, assignment). For example, subselects as shown
in these examples are not supported:
Example A

SELECT *
FROM Table1
WHERE ......(SELECT * FROM Table2 FOR XML RAW)
Example B

DECLARE @doc nchar(3000)
SET @doc = (SELECT * FROM Customers WHERE CustomerID = 'ALFKI' FOR XML
RAW)See here for more
information:http://www.sqlxml.org/faqs.aspx?faq=104

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

8 Dec 2005 9:15 PM

Henrik Skak Pedersen

Yes, You are right, that is why I am trying to copy the xml into a variable
before inserting .

Show quote

"David Gugick" <david.gugick-nospam@quest.com> wrote in message
news:ORpWI%23C$FHA.516@TK2MSFTNGP15.phx.gbl...
> Henrik Skak Pedersen wrote:
>> Hi,
>>
>> I have a problem with varchar(8000) in my trigger. It is a big
>> problem for me that the X and Y variables below are limited by 8000
>> characters, how can I make them larger?
>>
>> I can illustrate it like this:
>>
>> Create database AuditTest
>> Create table Audit (
>>    AuditId int identity,
>>    deletedText text,
>>    insertedText text
>> )
>>
>> create table Employee (
>>    Id int identity,
>>    Name varchar(100)
>> )
>>
>> CREATE trigger EmployeeAudit on Employee
>> AFTER INSERT, UPDATE, DELETE
>> AS
>>    DECLARE @X VARCHAR(8000)
>>    select @x = (SELECT * from deleted for xml auto, elements) --,
>> xmlschema)
>>    DECLARE @Y VARCHAR(8000)
>>    select @Y = (SELECT * from inserted for xml auto, elements) --,
>> xmlschema)
>>    INSERT INTO Audit
>>    values(@x, @y)
>>
>> Thanks Henrik
>
> A bigger problem is this:
>
> a.. FOR XML is not valid in subselections, whether it is in UPDATE,
> INSERT, or DELETE statements, a nested SELECT statement, or other
> statements (SELECT INTO, assignment). For example, subselects as shown in
> these examples are not supported:
> Example A
>
> SELECT *
> FROM Table1
> WHERE ......(SELECT * FROM Table2 FOR XML RAW)
> Example B
>
> DECLARE @doc nchar(3000)
> SET @doc = (SELECT * FROM Customers WHERE CustomerID = 'ALFKI' FOR XML
> RAW)See here for more information:http://www.sqlxml.org/faqs.aspx?faq=104
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

8 Dec 2005 9:55 PM

JT

A VarChar will only store 8000 bytes maximum, but you could try whatever
with data type Text.

Perhaps XML is not the best way to store this type of object in the
database. In the past, I've implemented loging triggers similar to below.
The relational data is more compact and easier to query.

create table Employee
(
    AuditDate datetime,
    AuditType char(1),
    Id int identity,
    Name varchar(100)
)

CREATE trigger EmployeeAudit on Employee
AFTER INSERT, UPDATE, DELETE
AS
insert into EmployeeAudit select getdate(), 'D', * from deleted
insert into EmployeeAudit select getdate(), 'I', * from inserted

Show quote

"Henrik Skak Pedersen" <skak@community.nospam> wrote in message
news:e2d%23EoC$FHA.740@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I have a problem with varchar(8000) in my trigger. It is a big problem
> for me that the X and Y variables below are limited by 8000 characters,
> how can I make them larger?
>
> I can illustrate it like this:
>
> Create database AuditTest
> Create table Audit (
>    AuditId int identity,
>    deletedText text,
>    insertedText text
> )
>
> create table Employee (
>    Id int identity,
>    Name varchar(100)
> )
>
> CREATE trigger EmployeeAudit on Employee
> AFTER INSERT, UPDATE, DELETE
> AS
>    DECLARE @X VARCHAR(8000)
>    select @x = (SELECT * from deleted for xml auto, elements) --,
> xmlschema)
>    DECLARE @Y VARCHAR(8000)
>    select @Y = (SELECT * from inserted for xml auto, elements) --,
> xmlschema)
>    INSERT INTO Audit
>    values(@x, @y)
>
> Thanks Henrik
>

9 Dec 2005 7:14 AM

Henrik Skak Pedersen

Hi JT,

Thank you for you reply.

That was my backup plan :-)

I have to audit a lot of tables and send the result to a message query like
function. Therefore i thought that this would be better. But of couse if
there is a limitation on 8000 it will be impossible.

Show quote

"JT" <some***@microsoft.com> wrote in message
news:uJXGaKE$FHA.740@TK2MSFTNGP12.phx.gbl...
>A VarChar will only store 8000 bytes maximum, but you could try whatever
>with data type Text.
>
> Perhaps XML is not the best way to store this type of object in the
> database. In the past, I've implemented loging triggers similar to below.
> The relational data is more compact and easier to query.
>
> create table Employee
> (
>    AuditDate datetime,
>    AuditType char(1),
>    Id int identity,
>    Name varchar(100)
> )
>
> CREATE trigger EmployeeAudit on Employee
> AFTER INSERT, UPDATE, DELETE
> AS
> insert into EmployeeAudit select getdate(), 'D', * from deleted
> insert into EmployeeAudit select getdate(), 'I', * from inserted
>
>
> "Henrik Skak Pedersen" <skak@community.nospam> wrote in message
> news:e2d%23EoC$FHA.740@TK2MSFTNGP12.phx.gbl...
>> Hi,
>>
>> I have a problem with varchar(8000) in my trigger. It is a big problem
>> for me that the X and Y variables below are limited by 8000 characters,
>> how can I make them larger?
>>
>> I can illustrate it like this:
>>
>> Create database AuditTest
>> Create table Audit (
>>    AuditId int identity,
>>    deletedText text,
>>    insertedText text
>> )
>>
>> create table Employee (
>>    Id int identity,
>>    Name varchar(100)
>> )
>>
>> CREATE trigger EmployeeAudit on Employee
>> AFTER INSERT, UPDATE, DELETE
>> AS
>>    DECLARE @X VARCHAR(8000)
>>    select @x = (SELECT * from deleted for xml auto, elements) --,
>> xmlschema)
>>    DECLARE @Y VARCHAR(8000)
>>    select @Y = (SELECT * from inserted for xml auto, elements) --,
>> xmlschema)
>>    INSERT INTO Audit
>>    values(@x, @y)
>>
>> Thanks Henrik
>>
>
>