Transfer Error when using ntext or text and a ManyToMany Relationship

Posted At : February 17, 2009 10:43 AM | Posted By : sean
Related Categories: Transfer, ColdFusion

When you set Transfer to use a table that has an ntext or text column on SQL Server 2000 (and perhaps later versions) and also a many to many relationship you may receive the following error when you attempt to retreive the collection for the many to many:

[Macromedia][SQLServer JDBC Driver][SQLServer]Only
text pointers are
allowed in work tables, never text, ntext, or image
columns. The query
processor produced a query plan that required a
text, ntext, or image
column in a work table.

For example I have the following tables set up

Table Name:
Table1
id        char(35)    primary key
title
varchar(255)
description    ntext

Table Name: Table2
id
char(35)    primary key
description
varchar(255)

Table Name: Table1_Table2
table1_id    char(35)
composite key
table2_id    char(35)    composite
key

And the following transfer configuration:

So when you call getRelationArray() on a Table1 object. You will receive the error. To correct this I took the following steps: Create a for table 1 with the ntext column casted to varchar: select id,title,cast(description as varchar(8000)) as description from table1 Save this as vwTable1. Change your transfer config to point to the view instead of table one so

becomes

Now, you wont be able to run it yet because the view is not updatable since you used the casted field. To overcome this we are going to use an INSTEAD OF trigger. You need to create 2 triggers. One will be called whenever UPDATE is called on the vwTable1 and the other will be called when INSERT INTO is called on vwTable1 You can create the triggers as follows, obviously change it to suit your table's needs:

CREATE trigger io_trigger_insert_vwTable1 on
vwTable1
instead of insert
as
begin
    set nocount
on
    if (not exists (select t.[id] from table1 t, inserted i where
t.[id] = i.[id]))
        insert into table1

select
            [id],

[title],
            [description]
        from

            inserted
end

create
trigger io_trigger_update_vwAd on vwAd
instead of update
as

begin
set nocount on
if (exists (select t.[id] from table1 t,
inserted i where t.[id] = i.[id]))
    update
        table1

    set
        title = i.title,

[description] = i.[description]
    from
        table1
t, inserted i
    where
        t.[id] =
i.[id]
end

Once you have created these triggers you should be able to use Transfer as you normally would. Using the casted column in the view will allow you to call the getRelationArray(). The SQL that Transfer creates will no longer be invalid. You may run into a couple of other items though. First, your ntext field is now limited to 8000 characters since you are casting it to varchar. You may be able to increase this, but I haven't tried. Your mileage may vary, but be sure before the value is passed to transfer that it is validated as less than 8000 characters (perhaps in your decorator). Next, if you have any non-null, defaulted columns in your base table (a created date perhaps) you HAVE to provide a value when you call insert into vwTable1. Since Transfer is creating this SQL you have to ensure that Transfer includes that column. This means that you cannot use the ignore-insert="true" option in the transfer configuration. SQL Server will still respect your default value, as long as you provide that column in the sql statement pointed at the view. You can find more information about INSTEAD OF INSERT and INSTEAD OF UPDATE triggers on the MSDN site. I have only used this in a simple application with two base tables and one relationship table, so if your setup is more complex it may not work out for you.