Hi Jason, Many thanks for your reply, it would be great if this will work on SQL 2000.
I tried testing this: ALTER TABLE testing ALTER COLUMN testing nvarchar(MAX); Server: Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near 'MAX'. I Googled a bit and found a couple of postings from people saying that using nvarchar(max) truncated their existing data. I'm a bit confused about what you say about: >> That lets SQL keep the first set of chars locally, and only points to >> the LOB when it needs to (only records that really have long values in >> myNText). Could you explain a little more, please? >> nvarchar(MAX) is the new ntext, and ntext is being >> deprecated, so it's a good move at any rate. Thanks for the warning on this. Jenny -----Original Message----- From: Jason Fisher [mailto:ja...@wanax.com] Sent: 24 April 2011 02:18 To: cf-talk Subject: Re: Problem Using QueryNew Change all your ntext to nvarchar(MAX), if you can. And run an UPDATE to free up space after the conversion, too ;) ALTER TABLE myTable ALTER COLUMN myNText nvarchar(MAX); UPDATE myTable SET myNText = myNText; That lets SQL keep the first set of chars locally, and only points to the LOB when it needs to (only records that really have long values in myNText). nvarchar(MAX) is the new ntext, and ntext is being deprecated, so it's a good move at any rate. On 4/23/2011 8:29 PM, Jenny Gavin-Wear wrote: > Hi Maureen, > > I found it is possible to create indexed views and add them to a fulltext > catalog, however, among a longgg list of constraints MS say that the views > cannot contain Ntext fields, which rules out that option for me. > > http://msdn.microsoft.com/en-us/library/aa933148%28v=sql.80%29.aspx > > -----Original Message----- > From: Maureen [mailto:mamamaur...@gmail.com] > Sent: 24 April 2011 00:26 > To: cf-talk > Subject: Re: Problem Using QueryNew > > > > Seems to me it would a lot more efficient to make a view in your > database that only returns the two fields you want to search on plus > any fields you need for search criteria. Or do a select with a join > that returns the fields. Once you do that, they are already in a > query, so you wouldn't need to make another query to hold them. > > On Sat, Apr 23, 2011 at 4:21 PM, Jenny Gavin-Wear > <jenn...@fasttrackonline.co.uk> wrote: >> Many thanks, Will. Obvious now I look at it again, duh me. >> >> Anyhoo, I had this (probably dumb) idea of combining sever fulltext search >> query results into one table, turning into a lot more work than I expected >> (+ learning curve). >> >> Maybe there is a better way of going about this: > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343940 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
