Problem with INSERT in a text field (SQLSERVER)

dev-sg1

unread,

Nov 6, 2009, 4:23:13 AM11/6/09

to transfer-dev

Hello,

I have a problem with an INSERT in a "text" field on SQLSERVER over
TRANSFER.

The value to INSERT is :

"24-Piece Cocktail (with 3 food stations)

Cold savory invitations (12 pieces per person)
Gourmand Pleasures
Foie gras spheres dusted with gold
Skewers of white radish and smoked swordfish
Vegetable “beggar’s purse” with eggs
Artichoke leaf with crab
Pins of rose salmon tartare
Frozen guacamole macaroon
Puff pastry morel squares
Creamy lamb skewers and a macadamia crisp
Fresh cheese and pesto Harlequin
Chicken and coconut bites
Cheese and walnut ‘ feuille à feuille’’
Scallops on the half-shell, vegetables with crunchy salt
Tomato goat cheese spheres
Mildly spicy chicken in a spoon

Cold savory food stations (please choose one)

Foie Gras
Fresh duck foie gras cooked with spices and served in a series of
variations on the cherry.
To enjoy with cherries in vinegar, a tomato and almond compote, slices
of rustic bread, Melba toast, country bread, coarse salt flavored with
Merlot and more.

Salmon
Smoked salmon back
Served with your choice of an emulsion of smoked tea or mint tea.
Salmon center flavored with dill cream
Served with tiny salmon eggs and blinis

Lobster
Lobster prepared in a series of variations on an emulsion of carrot
and orange, crisp vegetables, and more.
Served with coarse salt with saffron, slices of sweet potato, carrot
greens and flying fish eggs.
Minimum order: 100 guests

Soup and well-being
Watercress, parsnip, nasturtium and soup
Tiny diced vegetables, marinated cèpe mushrooms, herb sprouts,
breadsticks and croutons for ‘gourmand’ soup lovers.

‘Charcuterie’ from the mountains
Artisanal assortment of charcuterie from the Basque region: aromatic
chorizo, moist Jésus saucisson, dried sausage spiced with Espelette
pepper.
To savor on giant baguettes with mild spices, cherries in vinegar,
aubergine paste or OssauI-Iraty goat cheese.

‘Charcuterie’ from the mountains with Serrano
Artisanal assortment of charcuterie from the Basque region: aromatic
chorizo, moist Jésus saucisson, dried sausage spiced with Espelette
pepper.
To savor on giant baguettes with mild spices, cherries in vinegar,
aubergine paste or OssauI-Iraty goat cheese.
Minimum order: 200 guests

Golden delight
A jewel of haute gourmandise! Hot and cold, sea urchins and scallops,
foie gras
and Alba truffles. Sea urchins in a ‘chaud et froid’of rare curry,
scallops marinated with cèpe mushrooms and rocket for a slightly
bitter note. An egg, “toqué”with duck foie gras and coarse salt, and
Alba truffles in a warm ‘snow’: for sheer pleasure!

Espelette pepper and scallops
Scallops and Espelette pepper in perfect harmony.
Miniature warm spinach bread, waffled to your liking, served with
marinated scallops, Espelette jam, Manchego from the plains of la
Mancha and fruity
chorizo.

Hot savory invitations (5 pieces per person)
Lamb morsels with herbs
Duck shepherd’s pie with lime served in a spoon
Gruyère gougère puffs
Winter vegetable clafoutis

Hot savory food stations (please choose one)

Lamb
Rack of lamb served with a mix of niçois vegetables sprinkled with
your choice of pine nuts, riviera olives, basil sprouts, tomato
pearls, bell peppers and more.
Enhanced with a juice flavored with thyme or piquillos.
Minimum order: 100 guests

Raviole
Tasting of a selection of French Raviolefilled with ricotta and
rocket, to enjoy as you like with a mustard-licorice sauce, pine nuts,
sauce vierge, basil sprouts and more.
Minimum order: 100 guests

Lobster
Like a Capuccino… With the originality of roasted coffee beans…
A tasting of Lobster in ‘chaud et froid’for the sensation, a gold-
dusted Macadamia nut for the crunch and a touch of coffee for a
perfect alliance.

Irresistible Veal Rib
Veal, slow-cooked at low temperature, coarse salt with parsley from
Camargue, figs, violets, infused and marinated with Valencia almonds,
and fork-mashed potatoes with grilled pistachio oil.
A dash of imagination, a new approach, the touch of a master chef, the
best of the tradition with a hint of extravagance

Le ‘Pot au Feu’
Veal knuckle preserved in its juices, carrots, turnips, potatoes and
cooked leeks. Presented in a flavorful broth.
Croutons, coarse salt and a selection of mustards
Minimum order: 100 guests

Scallops with the essence of licorice
Scallops, cooked in a casserole, swimming in an aromatic licorice
broth.

Risotto
Emulsion of white truffles or squid ink. A rice cracker for a bit of
crunch. A hint of hazel nuts from the Piedmont region, squid and
Riviera olives.

Dessert invitations (5 pieces per person)
Medley of sweet things
Our raspberry ‘Turkish Delight’
Sparkling chocolate truffle
Pearls of white rum and lemon
Pear and chartreuse macaroon
Grilled chestnut macaroon
Mont-Blanc

Dessert food stations (please choose one)

Chocolate fondue
Hot chocolate fondue to savor with skewers of seasonal fruit or with
an assortment of miniature moelleux.

Chocolate fountain
Hot chocolate fountain to enjoy with skewers of seasonal fruits or
assorted miniature moelleux.
Minimum order: 300 guests

‘Lady Pineapple’
Intensely fruity… A magnificent basil sorbet is the perfect contrast.
Cinnamon, vanilla, almonds and sparkling sugar for a special
‘frisson’.

Chocolatier
A variety of pure cocoa butter chocolates:
Dark chocolate, fruit with a hint of acidity; milk
chocolate rich in cocoa with a touch of caramel; white
chocolate with a light vanilla flavor.
To savor with a passion or alto crémeux, with the crunchiness
of chopped nuts or the sweetness of candied ginger.
OR
Millefeuille (Napoleon pastry)
Layers of thin squares of caramelized puff pastry
separated by a richly delicious Bourbon vanilla custard
or a light hazel nut praline cream.
Sesame or sunflower seeds, speculoos cookies and vanilla… for
the greatest of pleasures.

Drinks consumption included:
one bottle of champagne for 3,
one bottle of wine* for 5,
one bottle of alcohols for 20,
softs drinks and fruit juices

*Selection of white wines (Bordeaux, Roussillon, Vallée du Rhône,
Bourgogne), rosé wine (Reuilly Pinot Gris) and red wines (Bordeaux,
Bourgogne, Beaujolais)"

The process stops at the line "Dark chocolate, fruit with a hint of
acidity; milk"

If you have an idea ?

Thank you

Dorioo

unread,

Nov 6, 2009, 8:15:53 AM11/6/09

to transf...@googlegroups.com

Does it work if you insert it using a SQL statement directly on the database?

- Gabriel

dev-sg1

unread,

Nov 6, 2009, 8:31:59 AM11/6/09

to transfer-dev

Hello Gabriel,

This is the request :

update va_menu_lng set mnul_html_content='24-Piece Cocktail (with 3 food
stations) Cold savory invitations (12 pieces per person) Gourmand Pleasures Foie gras spheres dusted with gold Skewers of white radish
and smoked swordfish Vegetable “beggar’s purse” with
eggs Artichoke leaf with crab Pins of rose salmon
tartare Frozen guacamole macaroon Puff pastry morel
squares Creamy lamb skewers and a macadamia crisp Fresh cheese
and pesto Harlequin Chicken and coconut bites Cheese and walnut
‘ feuille à feuille’’ Scallops on the half-shell, vegetables with
crunchy salt Tomato goat cheese spheres Mildly spicy chicken in
a spoon Cold savory food
stations (please choose one) Foie Gras Fresh duck foie gras cooked with spices and
served in a series of variations on the cherry. To enjoy with cherries in vinegar, a tomato and almond

compote, slices of rustic bread, Melba toast, country bread, coarse

salt flavored with Merlot and more. Salmon Smoked salmon back Served with your choice of an emulsion of
smoked tea or mint tea. Salmon center flavored with dill
cream Served with tiny salmon
eggs and blinis Lobster Lobster prepared in a series of variations on
an emulsion of carrot and orange, crisp vegetables, and more. Served with coarse salt with saffron,
slices of sweet potato, carrot greens and flying fish eggs. Minimum
order: 100 guests Soup
and well-being Watercress, parsnip, nasturtium and
soup Tiny diced vegetables,

marinated cèpe mushrooms, herb sprouts, breadsticks and croutons for

‘gourmand’ soup lovers. ‘Charcuterie’ from the mountains Artisanal assortment

of charcuterie from the Basque region: aromatic chorizo, moist Jésus

saucisson, dried sausage spiced with Espelette pepper. To savor on giant baguettes with mild

spices, cherries in vinegar, aubergine paste or OssauI-Iraty goat

cheese. ‘Charcuterie’
from the mountains with Serrano Artisanal assortment of

charcuterie from the Basque region: aromatic chorizo, moist Jésus

saucisson, dried sausage spiced with Espelette pepper. To savor on giant baguettes with mild

spices, cherries in vinegar, aubergine paste or OssauI-Iraty goat

cheese. Minimum order: 200 guests Golden delight A jewel of haute gourmandise! Hot and cold, sea urchins and
scallops, foie gras and Alba truffles. Sea urchins in a ‘chaud et

froid’of rare curry, scallops marinated with cèpe mushrooms and rocket
for a slightly bitter note. An egg, “toqué”with duck foie gras and
coarse salt, and Alba truffles in a warm ‘snow’: for sheer pleasure!

Espelette pepper and
scallops Scallops and Espelette pepper in perfect
harmony. Miniature warm spinach

bread, waffled to your liking, served with marinated scallops,

Espelette jam, Manchego from the plains of la Mancha and fruity chorizo. Hot
savory invitations (5 pieces per person) Lamb morsels with
herbs Duck shepherd’s pie with lime served in a spoon Gruyère
gougère puffs Winter vegetable clafoutis Hot savory food stations (please choose one) Lamb Rack of

lamb served with a mix of niçois vegetables sprinkled with your choice
of pine nuts, riviera olives, basil sprouts, tomato pearls, bell

peppers and more. Enhanced with a
juice flavored with thyme or piquillos. Minimum order: 100 guests Raviole Tasting of a selection of French Raviolefilled with ricotta

and rocket, to enjoy as you like with a mustard-licorice sauce, pine

nuts, sauce vierge, basil sprouts and more. Minimum order: 100 guests Lobster Like a Capuccino… With
the originality of roasted coffee beans… A tasting of Lobster in
‘chaud et froid’for the sensation, a gold-dusted Macadamia nut for the
crunch and a touch of coffee for a perfect alliance. Irresistible Veal Rib Veal, slow-

cooked at low temperature, coarse salt with parsley from Camargue,
figs, violets, infused and marinated with Valencia almonds, and fork-

mashed potatoes with grilled pistachio oil. A dash of imagination, a new approach, the touch of a
master chef, the best of the tradition with a hint of extravagance Le ‘Pot au Feu’ Veal knuckle preserved in its juices, carrots, turnips,
potatoes and cooked leeks. Presented in a flavorful broth. Croutons, coarse salt and a selection of
mustards Minimum order: 100 guests Scallops with the essence of licorice Scallops, cooked in a casserole, swimming in an aromatic
licorice broth. Risotto Emulsion of white truffles or squid ink. A rice cracker for a

bit of crunch. A hint of hazel nuts from the Piedmont region, squid

and Riviera olives. Dessert invitations (5 pieces per person) Medley of sweet
things Our raspberry ‘Turkish Delight’ Sparkling
chocolate truffle Pearls of white rum and lemon Pear and
chartreuse macaroon Grilled chestnut macaroon Mont-
Blanc Dessert food stations
(please choose one) Chocolate fondue Hot chocolate fondue to savor with

skewers of seasonal fruit or with an assortment of miniature

moelleux. Chocolate fountain Hot chocolate fountain to enjoy with skewers of seasonal
fruits or assorted miniature moelleux. Minimum order: 300 guests ‘Lady Pineapple’ Intensely fruity… A

magnificent basil sorbet is the perfect contrast. Cinnamon, vanilla,
almonds and sparkling sugar for a special

‘frisson’. Chocolatier A variety of pure cocoa butter
chocolates: Dark chocolate, fruit with a hint of acidity; milk 
chocolate rich in cocoa with a touch of caramel; white chocolate
with a light vanilla flavor. To savor with a passion or alto
crémeux, with the crunchiness of chopped nuts or the sweetness of
candied ginger. OR Millefeuille (Napoleon pastry) Layers
of thin squares of caramelized puff pastry separated by a richly
delicious Bourbon vanilla custard or a light hazel nut praline
cream. Sesame or sunflower seeds, speculoos cookies and vanilla…
for the greatest of pleasures. Drinks consumption included: one bottle of champagne
for 3, one bottle of wine* for 5, one bottle of alcohols for
20, softs drinks and fruit juices *Selection of white wines

(Bordeaux, Roussillon, Vallée du Rhône, Bourgogne), rosé wine (Reuilly

Pinot Gris) and red wines (Bordeaux, Bourgogne, Beaujolais)'
where mnul_mnu_id=371 and mnul_lng_id=2

Management SQL server : (1 row(s) affected)

So it work but thanks for your reactivity.

Dorioo

unread,

Nov 6, 2009, 9:08:04 AM11/6/09

to transf...@googlegroups.com

If a manual SQL statement works, I'd look at the SQL statement that Transfer is using, copy it, and try to run it manually against the database to see if/why it fails. If you have robust info on, coldfusion should show the queries used.

If that still doesn't work, you should add a little more info to the request to help diagnose the problem.
http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer

- Gabriel

Mark Mandel

unread,

Nov 6, 2009, 3:17:32 PM11/6/09

to transf...@googlegroups.com

How is the field configured?

Mark

--
E: mark....@gmail.com
T: http://www.twitter.com/neurotic
W: www.compoundtheory.com

dev-sg1

unread,

Nov 9, 2009, 4:33:24 AM11/9/09

to transfer-dev

Hello,

Dorioo :
I didn't get the query used because it's not so simple but I'll
pass... soon.

Mark :
On Transfer, the field is a property ( type "string" ) of my object.
On SQLServer, the field is a "text" field (not NULL).

Have a nice day.

Julien

Scott Brady

unread,

Nov 9, 2009, 7:01:02 AM11/9/09

to transf...@googlegroups.com

How is the DSN configured in ColdFusion? Do you have it configured to
allow CLOBs? If not, then CF will only insert a limited number of
characters into a field (even if the field in the database is a CLOB,
like 'text').

Scott

On Mon, Nov 9, 2009 at 2:33 AM, dev-sg1 <julien....@gmail.com> wrote:
>
> Mark :
> On Transfer, the field is a property ( type "string" ) of my object.
> On SQLServer, the field is a "text" field (not NULL).
>
> Have a nice day.

--
-----------------------------------------
Scott Brady
http://www.scottbrady.net/

Dorioo

unread,

Nov 9, 2009, 7:19:15 AM11/9/09

to transf...@googlegroups.com

Another thought. Are you using the jTDS driver? I recall problems with text fields when using that driver.

- Gabriel

dev-sg1

unread,

Nov 9, 2009, 8:20:48 AM11/9/09

to transfer-dev

Scott : My CLOB (and BLOB) parameters were disabled in coldfusion
administrator.
I enabled it but i have still the same error.

Gabriel : I'm using SQLServer Driver provided with Coldfusion MX 8.

I noticed, between 2 tests, if i put "<![CDATA[" + contentHTML + "]]>"
the request is ok....

Thank you.

dev-sg1

unread,

Nov 9, 2009, 8:27:35 AM11/9/09

to transfer-dev

"I noticed, between 2 tests, if i put "<![CDATA[" + contentHTML + "]]
>" the request is ok...."

Sorry, it's wrong... Forget it.

Mark Mandel

unread,

Nov 9, 2009, 3:29:45 PM11/9/09

to transf...@googlegroups.com

That is very strange... I've used 'string' parameters with n/text fields before and never had a problem.

Mark

dev-sg1

unread,

Nov 10, 2009, 8:15:52 AM11/10/09

to transfer-dev

Mark :

If you try to test with my query below, you will have the error :

"Type":"Database","SQLState":"HY000","queryError":"[Macromedia]
[SQLServer JDBC Driver][SQLServer]

Le processeur de requêtes ne peut pas fournir un plan de requête à
partir de l'optimiseur parce que une requête ne peut pas mettre à jour
en même temps une colonne text, ntext ou image et une clé de
clusterisation.

Translation in english :
The query processor can't provide a query plan from the optimizer
because a query can't update at the same time a text, ntext, or image
and a clustering key.

On 9 nov, 21:29, Mark Mandel <mark.man...@gmail.com> wrote:
> That is very strange... I've used 'string' parameters with n/text fields
> before and never had a problem.
>
> Mark
>

> On Tue, Nov 10, 2009 at 12:27 AM, dev-sg1 <julien.pass...@gmail.com> wrote:
>
> > "I noticed, between 2 tests, if i put "<![CDATA[" + contentHTML + "]]
> > >" the request is ok...."
>
> > Sorry, it's wrong... Forget it.
>
> --

> E: mark.man...@gmail.com

Mark Mandel

unread,

Nov 10, 2009, 5:08:19 PM11/10/09

to transf...@googlegroups.com

Which query is that? the original one?

Mark

--
E: mark....@gmail.com

dev-sg1

unread,

Nov 12, 2009, 4:35:46 AM11/12/09

to transfer-dev

Mark :

I tried to insert this value on SQLSERVER (text field) over TRANSFER
(property : string)

Do you want the query generated by TRANSFER ?

Julien

dev-sg1

unread,

Nov 16, 2009, 8:38:35 AM11/16/09

to transfer-dev

Hello All,

The sql query generated by Transfer which creates bug is :

SELECT NULL as mnul_title, va_menu_1.mnu_mfd_id, NULL as mnul_lng_id,
va_menu_1.mnu_timestamp, NULL as mnul_mnu_id, va_menu_1.mnu_id,
va_menu_1.mnu_libelle_bo, NULL as mnul_html_content, NULL as
mnul_timestamp, '' as transfer_compositeid,NULL as transfer_parentKey,
1 as transfer_orderIndex,'prestation.Menu' as transfer_className,'' as
transfer_parentClassName,'' as transfer_parentParentClassName,'' as
transfer_parentCompositeName,'false' as transfer_isArray,'' as
transfer_compositeName, 'false' as transfer_isProxied FROM va_menu
va_menu_1 WHERE va_menu_1.mnu_id = ? AND va_menu_1.mnu_id IS NOT
NULL UNION ALL SELECT va_menu_lng_2.mnul_title, NULL as mnu_mfd_id,
va_menu_lng_2.mnul_lng_id, NULL as mnu_timestamp,
va_menu_lng_2.mnul_mnu_id, NULL as mnu_id, NULL as mnu_libelle_bo,
va_menu_lng_2.mnul_html_content, va_menu_lng_2.mnul_timestamp, CAST
(va_menu_lng_2.mnul_lng_id as varchar(1000)) + '|' + CAST
(va_menu_lng_2.mnul_mnu_id as varchar(1000)) + '|' as
transfer_compositeid,CAST(va_menu_1.mnu_id as varchar(1000)) as
transfer_parentKey, 2 as transfer_orderIndex,'locale.Menu' as
transfer_className,'prestation.Menu' as transfer_parentClassName,'' as
transfer_parentParentClassName,'' as
transfer_parentCompositeName,'true' as transfer_isArray,'Locales' as
transfer_compositeName, 'false' as transfer_isProxied FROM va_menu
va_menu_1 INNER JOIN va_menu_lng va_menu_lng_2 ON va_menu_1.mnu_id =
va_menu_lng_2.mnul_mnu_id WHERE va_menu_1.mnu_id = ? ORDER BY
transfer_orderIndex ASC

If anyone has an idea.

Best Regards.

Julien

Dorioo

unread,

Nov 16, 2009, 9:12:19 AM11/16/09

to transf...@googlegroups.com

I still think there's value in taking that query, replacing the question marks with their respective values, and running it directly against the database. If it fails, you keep removing parts of the input data until it works. Once it works, you can add parts back until you zero in on the data that is causing it to fail.

Additionally, I would simplify the problem for testing by creating a new table with one column for the input you posted, hook it up with transfer, and see if it still fails. If it doesn't fail, I'd then work backwards by slowly adding properties and relationships between my test tables until they match real tables and relationships and it fails.

Debugging likes this will take time but it should lead you to the cause of the problem.

- Gabriel

dev-sg1

unread,

Nov 16, 2009, 9:15:31 AM11/16/09

to transfer-dev

Sorry, i'm stupid that's the query generated when i'm getting my
data....

On 16 nov, 15:12, Dorioo <dor...@gmail.com> wrote:
> I still think there's value in taking that query, replacing the question
> marks with their respective values, and running it directly against the
> database. If it fails, you keep removing parts of the input data until it
> works. Once it works, you can add parts back until you zero in on the data
> that is causing it to fail.
>
> Additionally, I would simplify the problem for testing by creating a new
> table with one column for the input you posted, hook it up with transfer,
> and see if it still fails. If it doesn't fail, I'd then work backwards by
> slowly adding properties and relationships between my test tables until they
> match real tables and relationships and it fails.
>
> Debugging likes this will take time but it should lead you to the cause of
> the problem.
>
> - Gabriel
>

dev-sg1

unread,

Nov 16, 2009, 9:20:22 AM11/16/09

to transfer-dev

Thanks a lot Gabriel.

I will follow your advices.

As soon as I will get good query.

Julien

dev-sg1

unread,

Nov 17, 2009, 8:20:23 AM11/17/09

to transfer-dev

Hello ALL,

I finally get the query generated by TRANSFER ! (filewrite in transfer/
com/sql/QueryExecution.cfc with "ExecuteQuery" method)

When i tried to query my database directly : SAME ERROR...

After my research :

On SQLSERVER 2005, when you try to update a "text" field over 8000
bytes (and not characters).

The query processor can't provide a query plan from the optimizer
because a query can't update at the same time a text, ntext, or image
and a clustering key.

The SOLUTION :

DECLARE @Pointer_Value varbinary(16)
SELECT @Pointer_Value = TEXTPTR(mnul_content)
FROM va_menu_lng
WHERE mnul_lng_id = 2.0
AND mnul_mnu_id = 346.0
WRITETEXT va_menu_lng.mnul_content @Pointer_Value 'valueover8000byes'

mnul_content = my text field.
mnul_menu_lng = my table.

----------------------------------------------------------------------

If your field contains over 8000 bytes you must use 'READTEXT' command
to retrieve your data.

Have a nice day.

Julien

dev-sg1

unread,

Nov 17, 2009, 8:32:54 AM11/17/09

to transfer-dev

By default, SQLSERVER creates primary keys as clustered.

Whe must set "clustered" to "OFF" to update over 8000 bytes in text
field.

Right click on your table
Select design mode
Highlight the primary key(s)
Right click on one of them
Select "index/key"
Set the parameter "create as clustered" to "off"
SAVE THE TABLE

Thank you All

Reply all

Reply to author

Forward