RE: Problem Using QueryNew
You are using a simple cfloop from to, instead of a cfquery or a cfloop query. Because of this, when you call #stockTable.stockTitle#, it defaults to the first line of the query. Either change your loop to a cfloop query=stockTable Or change your data call to #stockTable.stockTitle[R]# William -Original Message- From: Jenny Gavin-Wear [mailto:jenn...@fasttrackonline.co.uk] Sent: Saturday, April 23, 2011 9:45 AM To: cf-talk Subject: Problem Using QueryNew My first time using created queries, I can't understand why this is putting the same value on every row. cfset stock = QueryNew(StockTitle, Varchar) cfoutput cfloop from=1 to=#stockTable.recordcount# index=R cfset newrow = QueryAddRow(stock,1) cfset temp = QuerySetCell(Stock, stockTitle, #stockTable.stockTitle#, R) /cfloop /cfoutput cfoutput query=stock #stock.stockTitle#br / /cfoutput No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.894 / Virus Database: 271.1.1/3591 - Release Date: 04/23/11 07:36:00 ~| 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:343910 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Problem Using QueryNew
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:- Scenario - Cart Structure Main Categories - Sub Categories = Products = Product Options I want to search in fields from two tables at a time. So it will output two groupings :- 1. List of product/options 2. Categories (main and sub) Currently, I've added all four tables to the FULLTEXT catalog. I'm running 4 queries on that from CF, each one looks like this: cfquery datasource=#application.dsn# name=stockTable SELECT TOP 100 PERCENT Search_Table.RANK, dbo.list_stock_shop.catMainID, dbo.list_stock_shop.catmainDisplay, dbo.list_stock_shop.openClosed, dbo.list_stock_shop.catMainDescr, dbo.list_stock_shop.catMainFullDescr, dbo.list_stock_shop.catMainPhoto, dbo.list_stock_shop.catSubID, dbo.list_stock_shop.catSubDisplay, dbo.list_stock_shop.grid, dbo.list_stock_shop.catSubDescr, dbo.list_stock_shop.catSubFullDescr, dbo.list_stock_shop.catSubPhoto, dbo.list_stock_shop.stockID, dbo.list_stock_shop.created, dbo.list_stock_shop.stockDisplay, dbo.list_stock_shop.stockTitle, dbo.list_stock_shop.taxRate, dbo.list_stock_shop.stockDescr, dbo.list_stock_shop.stockDescrShort, dbo.list_stock_shop.taxExempt, dbo.list_stock_shop.delivery, dbo.list_stock_shop.navAdmin, dbo.list_stock_shop.stockItemID, dbo.list_stock_shop.stockItemDisplay, dbo.list_stock_shop.cost, dbo.list_stock_shop.price, dbo.list_stock_shop.salePrice, dbo.list_stock_shop.itemTitle, dbo.list_stock_shop.itemDescr, dbo.list_stock_shop.stockItemPhoto, dbo.list_stock_shop.inStock, dbo.list_stock_shop.onOrder, dbo.list_stock_shop.allocated, dbo.list_stock_shop.soldQuan, dbo.list_stock_shop.minQuan, dbo.list_stock_shop.maxQuan, dbo.list_stock_shop.reorderQuan, dbo.list_stock_shop.saleFrom, dbo.list_stock_shop.saleTo, dbo.list_stock_shop.projected FROM FREETEXTTABLE(tbl_stock, *, '#myCleanSearch#', 1000) Search_Table INNER JOIN dbo.list_stock_shop ON Search_Table.[KEY] = dbo.list_stock_shop.stockID ORDER BY Search_Table.RANK DESC /cfquery Ignore the o/d on the field list, I'll cut to minimum when I'm done. My solution as it is will work, but it feels very ugly and process heavy. What I wonder is, can the search be run on more than one table into the catalog at one time? Am I going about this the right way? Jenny -Original Message- From: William Seiter [mailto:will...@seiter.com] Sent: 23 April 2011 17:51 To: cf-talk Subject: RE: Problem Using QueryNew You are using a simple cfloop from to, instead of a cfquery or a cfloop query. Because of this, when you call #stockTable.stockTitle#, it defaults to the first line of the query. Either change your loop to a cfloop query=stockTable Or change your data call to #stockTable.stockTitle[R]# William -Original Message- From: Jenny Gavin-Wear [mailto:jenn...@fasttrackonline.co.uk] Sent: Saturday, April 23, 2011 9:45 AM To: cf-talk Subject: Problem Using QueryNew My first time using created queries, I can't understand why this is putting the same value on every row. cfset stock = QueryNew(StockTitle, Varchar) cfoutput cfloop from=1 to=#stockTable.recordcount# index=R cfset newrow = QueryAddRow(stock,1) cfset temp = QuerySetCell(Stock, stockTitle, #stockTable.stockTitle#, R) /cfloop /cfoutput cfoutput query=stock #stock.stockTitle#br / /cfoutput No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.894 / Virus Database: 271.1.1/3591 - Release Date: 04/23/11 07:36:00 ~| 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:343918 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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:343919 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Problem Using QueryNew
Hi Maureen, Many thanks for the reply. Taking the first two queries. In Product I search on title, short description and long description. In Product Options I search on the option title and description. I tried some tests joining the tables:- cfquery dbtype=query name=product select stockID from stockTable left outer join stockItemsTable ON stockItemsTable.stockID = stockTable.stockID /cfquery I assume I need to use a left outer join. Although the stocktable and stockitemstable individually returned results, I can't get the combined tables to produce anything. As I understand it FREETEXTTABLE can only search on one table at a time? I can't use a database view in the fulltext catalogue? If I can it answers all my problems. Jenny -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:343922 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Problem Using QueryNew
I think what you want is an inner join. An inner join gives you ONLY the records in the Product Table that have matching ids in the Product Options table. An outer join will give you all the records in the Product Table but only those in Product Options that with matching ids. So if your tables look like this: Product ___ id type 1 boat 2 ship 3 plane 4 car 5 truck Product Option option_id option_product_id option 12 cargo 22 container 34 sedan 44 suv 6null convertible An inner join will return Product_id product_name option_id option_product_id option 2 ship1 2 cargo 2 ship2 2 container 4 car 3 4 sedan 4 car 4 4 suv An outer join will return Product_id product_name option_id option_product_id option 1 boatnull null null 2 ship 1 2 cargo 2 ship 2 2 container 3 plane null null null 4 car 3 4 sedan 4 car 4 4 suv 5 truck null null null On Sat, Apr 23, 2011 at 4:58 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Hi Maureen, Many thanks for the reply. Taking the first two queries. In Product I search on title, short description and long description. In Product Options I search on the option title and description. I tried some tests joining the tables:- cfquery dbtype=query name=product select stockID from stockTable left outer join stockItemsTable ON stockItemsTable.stockID = stockTable.stockID /cfquery I assume I need to use a left outer join. Although the stocktable and stockitemstable individually returned results, I can't get the combined tables to produce anything. As I understand it FREETEXTTABLE can only search on one table at a time? I can't use a database view in the fulltext catalogue? If I can it answers all my problem ~| 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:343928 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Problem Using QueryNew
On Sat, Apr 23, 2011 at 4:58 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: As I understand it FREETEXTTABLE can only search on one table at a time? I can't use a database view in the fulltext catalogue? If I can it answers all my problems. I've never used FREETEXTTABLE on a view, but I don't see why it wouldn't work, as the structure returned by a view looks just like a table ~| 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:343929 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Problem Using QueryNew
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:343930 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Problem Using QueryNew
Are you still running SQL Server 2000, and if not, is that still the case for the version of SQL you are running? On Sat, Apr 23, 2011 at 5:29 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk 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 ~| 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:343931 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Problem Using QueryNew
I'm still on SQL 2000. Sadly it can't be done in SQL 2008 either: The view cannot include text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement. http://msdn.microsoft.com/en-us/library/ms191432.aspx -Original Message- From: Maureen [mailto:mamamaur...@gmail.com] Sent: 24 April 2011 01:33 To: cf-talk Subject: Re: Problem Using QueryNew Are you still running SQL Server 2000, and if not, is that still the case for the version of SQL you are running? On Sat, Apr 23, 2011 at 5:29 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk 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 ~| 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:343932 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Problem Using QueryNew
Darn. That bites. Maybe a different searching mechanism if the simple query doesn't work. Or a temp table. On Sat, Apr 23, 2011 at 5:45 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: I'm still on SQL 2000. Sadly it can't be done in SQL 2008 either: The view cannot include text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement. http://msdn.microsoft.com/en-us/library/ms191432.aspx ~| 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:343934 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
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:343935 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Problem Using QueryNew
Hi Maureen, I really like your thinking, but I'm not sure that will achieve what I need, I'll definitely test it out tomorrow though, many thanks for your input, much appreciated. One niggle is that having to use two queries really messes up using the Ranking. Jenny -Original Message- From: Maureen [mailto:mamamaur...@gmail.com] Sent: 24 April 2011 01:28 To: cf-talk Subject: Re: Problem Using QueryNew No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.894 / Virus Database: 271.1.1/3592 - Release Date: 04/23/11 07:36:00 ~| 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:343939 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Problem Using QueryNew
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
Re: Problem Using QueryNew
Varchar(max) was added to mssql 2005 2000 has a limit of Varchar(8060) Sent from my iPhone ~| 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:343941 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Problem Using QueryNew
If you are using SQL Server 2000, to avoid truncation you can use TEXT or nText fields instead. These are BLOBS and are not contained by the max row length. The combined max row size for all other fields is 8060, anything above this will be truncated. Since SQL Server 2005 these were depreciated in favour of the new VarChar(max) data type. -- Russ Michaels www.bluethunderinternet.com : Business hosting services solutions www.cfmldeveloper.com: ColdFusion developer community www.michaels.me.uk : my blog www.cfsearch.com : ColdFusion search engine ** *skype me* : russmichaels ~| 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:343944 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Problem Using QueryNew
Ah, yeah, pretty sure nvarchar(MAX) was added in SQL 2005, unfortunately. So, to explain further, ntext holds only a pointer to the actual content elsewhere on the server, held in a Large Object (LOB). nvarchar(MAX) holds the first 8k of data in the table, so to speak, and then only pushes overflow data out via pointer. Since oftentimes the data in ntext fields isn't really that long (but the column has to account for those times it *is* long), by using MAX you actually save space and end up with an indexable column. The UPDATE basically pulls the first 8k of data into the table, replacing the old ntext pointer with the new MAX structure. Pete Freitag had an entry on it: http://www.petefreitag.com/item/734.cfm He further linked to another post about why to run the additional UPDATE as well: http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx On 4/23/2011 9:56 PM, Jenny Gavin-Wear wrote: 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:343945 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Problem Using QueryNew
Be sure to enable the blob/clob option(s) on the CF datasource if you go with those datatypes. That one bites me all the time. .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: Saturday, April 23, 2011 10:29 PM To: cf-talk Subject: Re: Problem Using QueryNew If you are using SQL Server 2000, to avoid truncation you can use TEXT or nText fields instead. These are BLOBS and are not contained by the max row length. The combined max row size for all other fields is 8060, anything above this will be truncated. Since SQL Server 2005 these were depreciated in favour of the new VarChar(max) data type. -- Russ Michaels www.bluethunderinternet.com : Business hosting services solutions www.cfmldeveloper.com: ColdFusion developer community www.michaels.me.uk : my blog www.cfsearch.com : ColdFusion search engine ** *skype me* : russmichaels ~| 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:343946 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Problem Using QueryNew
She's already using ntext. She is trying to figure a way to search two different ntext fields in two different related tables returned by the same query using FREETEXTTABLE On Sat, Apr 23, 2011 at 7:28 PM, Russ Michaels r...@michaels.me.uk wrote: If you are using SQL Server 2000, to avoid truncation you can use TEXT or nText fields instead. These are BLOBS and are not contained by the max row length. The combined max row size for all other fields is 8060, anything above this will be truncated. Since SQL Server 2005 these were depreciated in favour of the new VarChar(max) data type. ~| 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:343947 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Problem Using QueryNew
That's awesome, Jason, many thanks for explaining. I'm not using ntext fields extensively, so if I upgrade my SQL later it's not going to be a big head ache. -Original Message- From: Jason Fisher [mailto:ja...@wanax.com] Sent: 24 April 2011 03:30 To: cf-talk Subject: Re: Problem Using QueryNew Ah, yeah, pretty sure nvarchar(MAX) was added in SQL 2005, unfortunately. So, to explain further, ntext holds only a pointer to the actual content elsewhere on the server, held in a Large Object (LOB). nvarchar(MAX) holds the first 8k of data in the table, so to speak, and then only pushes overflow data out via pointer. Since oftentimes the data in ntext fields isn't really that long (but the column has to account for those times it *is* long), by using MAX you actually save space and end up with an indexable column. The UPDATE basically pulls the first 8k of data into the table, replacing the old ntext pointer with the new MAX structure. Pete Freitag had an entry on it: http://www.petefreitag.com/item/734.cfm He further linked to another post about why to run the additional UPDATE as well: http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarchar max-in-sql-2005.aspx On 4/23/2011 9:56 PM, Jenny Gavin-Wear wrote: 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:343949 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Problem Using QueryNew
Thanks Bobby, I did indeed miss that! -Original Message- From: Bobby Hartsfield [mailto:bo...@acoderslife.com] Sent: 24 April 2011 03:38 To: cf-talk Subject: RE: Problem Using QueryNew Be sure to enable the blob/clob option(s) on the CF datasource if you go with those datatypes. That one bites me all the time. .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.894 / Virus Database: 271.1.1/3592 - Release Date: 04/23/11 07:36:00 ~| 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:343950 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
