RE: Big Trouble in T-SQL land
Thanks for you help Paul. Actually, this doesn't fail in cf5. The SET NOCOUNT allows for multi sql statements (in MS SQL). Thanks for your suggestion on checking for objects, but as I am sure you can tell this is only a sample bit of code to explain our problem. This is not on our site. This type of query is used a lot on our site and in cf5 there was no problem. Moving to a stored proc does fix the problem; however, that is an impossibility with some of our queries - plus it would be a major re-write. -Original Message- From: Paul Hastings [mailto:[EMAIL PROTECTED]] Sent: Saturday, October 05, 2002 12:49 AM To: CF-Talk Subject: Re: Big Trouble in T-SQL land Now, we seem to be unable to create temporary tables. The following query returns 0 records in CFMX but 1 record in query analyzer. no, it looks like the CREATE TABLE in the same cfquery. fails with regular tables too. Anyone else? Suggestions? Glass of hemlock? move this into an SP. works there as expected. CREATE TABLE ##xyz (a varchar(10)) btw you should always check for objects prior to creating them just in case a process croaks leaves that table or whatever hanging about. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.394 / Virus Database: 224 - Release Date: 10/3/2002 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Big Trouble in T-SQL land
This move to MX has been quite the learning experience. Now, we seem to be unable to create temporary tables. The following query returns 0 records in CFMX but 1 record in query analyzer. Anyone else? Suggestions? Glass of hemlock? CFQUERY NAME=test DATASOURCE=foobardb DBTYPE=ODBC SET NOCOUNT ON CREATE TABLE ##xyz (a varchar(10)) INSERT INTO ##xyz ( a )VALUES ( 'foo' ) SELECT * FROM ##xyz DROP TABLE ##xyz SET NOCOUNT OFF /CFQUERY We are using MX with SQL 7. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Big Trouble in T-SQL land
Try turning the NOCUNT OFF right before the SELECT * FROM ##xyz then back on for the drop? Just a SWAG, perhaps it is similar to resultsets from stored procedures... Matthew P. Smith Web Developer, Object Oriented Naval Education Training Professional Development Technology Center (NETPDTC) (850)452-1001 ext. 1245 [EMAIL PROTECTED] -Original Message- From: Rob Rohan [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 12:21 PM To: CF-Talk Subject: Big Trouble in T-SQL land This move to MX has been quite the learning experience. Now, we seem to be unable to create temporary tables. The following query returns 0 records in CFMX but 1 record in query analyzer. Anyone else? Suggestions? Glass of hemlock? CFQUERY NAME=test DATASOURCE=foobardb DBTYPE=ODBC SET NOCOUNT ON CREATE TABLE ##xyz (a varchar(10)) INSERT INTO ##xyz ( a )VALUES ( 'foo' ) SELECT * FROM ##xyz DROP TABLE ##xyz SET NOCOUNT OFF /CFQUERY We are using MX with SQL 7. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Recall: Big Trouble in T-SQL land
Smith, Matthew P -CONT(DYN) would like to recall the message, Big Trouble in T-SQL land. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: Big Trouble in T-SQL land
Try turning the NOCOUNT OFF right before the SELECT * FROM ##xyz then back on for the drop? Just a SWAG, perhaps it is similar to resultsets from stored procedures... Matthew P. Smith Web Developer, Object Oriented Naval Education Training Professional Development Technology Center (NETPDTC) (850)452-1001 ext. 1245 [EMAIL PROTECTED] -Original Message- From: Rob Rohan [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 12:21 PM To: CF-Talk Subject: Big Trouble in T-SQL land This move to MX has been quite the learning experience. Now, we seem to be unable to create temporary tables. The following query returns 0 records in CFMX but 1 record in query analyzer. Anyone else? Suggestions? Glass of hemlock? CFQUERY NAME=test DATASOURCE=foobardb DBTYPE=ODBC SET NOCOUNT ON CREATE TABLE ##xyz (a varchar(10)) INSERT INTO ##xyz ( a )VALUES ( 'foo' ) SELECT * FROM ##xyz DROP TABLE ##xyz SET NOCOUNT OFF /CFQUERY We are using MX with SQL 7. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: Big Trouble in T-SQL land
Thanks for you help Paul. Actually, this doesn't fail in cf5. The SET NOCOUNT allows for multi sql statements (in MS SQL). Thanks for your i guess thats ODBC vs JDBC quirks. i use SET NOCOUNT w/mx but this is the only time i've seen it fail. Moving to a stored proc does fix the problem; however, that is an impossibility with some of our queries - plus it would be a major re-write. whatever but sp are most often a better way of doing these sorts of things in the 1st place. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.394 / Virus Database: 224 - Release Date: 3/10/2545 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Re: Big Trouble in T-SQL land
Now, we seem to be unable to create temporary tables. The following query returns 0 records in CFMX but 1 record in query analyzer. no, it looks like the CREATE TABLE in the same cfquery. fails with regular tables too. Anyone else? Suggestions? Glass of hemlock? move this into an SP. works there as expected. CREATE TABLE ##xyz (a varchar(10)) btw you should always check for objects prior to creating them just in case a process croaks leaves that table or whatever hanging about. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.394 / Virus Database: 224 - Release Date: 10/3/2002 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
Big Trouble in T-SQL land
This move to MX has been quite the learning experience. Now, we seem to be unable to create temporary tables. The following query returns 0 records in CFMX but 1 record in query analyzer. Anyone else? Suggestions? Glass of hemlock? CFQUERY NAME=test DATASOURCE=foobardb DBTYPE=ODBC SET NOCOUNT ON CREATE TABLE ##xyz (a varchar(10)) INSERT INTO ##xyz ( a )VALUES ( 'foo' ) SELECT * FROM ##xyz DROP TABLE ##xyz SET NOCOUNT OFF /CFQUERY We are using MX with SQL 7. Rob ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
