SQL Update with Cast as varchar(max)

[jeltzroth]

I want to update the longnotes field in the History table, and many of the notes are quite long.  I wanted to issue an Execute SQL command, since there are some remotes that also need this update.  Therefore, I believe I need to cast the field as varchar(max) within the replace function.  I know that varchar(max) is not compatible with the SQL 2000 compatibility level, and have updated the level on the database to 90 (or 100).  But still result in a "cannot parse SQL" error.  Does anyone know of the SalesLogix OLEDB provider is limited to the compatibility level of 80 (SQL 2000)?  I may have to take a different approach.

[tagersea]

What is the SQL you are trying to execute?

[RJSamp]

Probably.....there are many SQL statements that aren't executeable through the SLX OLE DB Provider....my favorite being TRUNCATE TABLE ACCOUNT

[MikeSpragg]

Maximum char field is 4000 chars - so Convert(varchar(4000), notes) would work. Obviously, leads to truncation if bigger than this.

[pjames95003]

Thanks Mike! Once again found what I needed with your name attached to it. :-)