sql error
This one is driving me nuts. I keep getting a 'Base table not found error'. Invalid object name 'Products'. This is my query: SELECT Industries.name as industryName, Products.name as productName, Products.version, Products.prefix, Products.ID as prodID FROMProducts, Industries WHERE Products.name = '#COOKIE.prodName#' AND Products.version = '#COOKIE.prodVersion#' AND Products.industryID = Industries.ID order by Industries.name, Products.name Does anyone know what may be causing this if you look at my code? thanks __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
SQL Error
Has anyone seen this error here before? What is the cause and the fix? ODBC Error Code = 40001 (Serialization failure) [Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction (process ID #17) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction. TIA, Duane ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
SQL error
Help? I'm sure this is a silly insert error but I just don't see the problem. The answer filed is set up for 2000 Char. Do I need to use a special tag to insert HTML into the field? Running CF 4.5 and SQL7. ODBC Error Code = 22001 (String data right truncation) [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. SQL = "insert into dbo.faq (question,answer) values ('What do I need to play at IWONITLIVE.com? ','Below are the requirements to plat at IWONITLIVE.com: Requirement Minimum Recommended Internet Connection 56K Dial-Up DSL/Cable or higher Windows Media Player Version 5 Version 7 Speakers Any Any Microphone Any Any ')" Jeff Davis ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
SQL Error
Never mind. I figured it out. My tempdb was out of room. I just had to create a new device on the SQL Server and then alter the tempdb to use the new device that I created. Man, I cannot wait until we upgrade to SQL 2K (Currently are on SQL 6.5) ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
SQL Error
This parameter value: (param 15) = [type='IN', class='java.lang.Double', value='44.5', sqltype='CF_SQL_MONEY'] is creating the following error: Error Executing Database Query. Invalid data for CFSQLTYPE CF_SQL_DOUBLE. This is storing a value to a MS Access DB field that has data type of currency. Can anyone tell me what is wrong? Andy [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
SQL Error
Does anyone know why this would occur? Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Invalid precision value I am using MS-Access and using CFQUERYPARAM tags. The tags are specified properly such as Integer or VARCHAR types. As well I re-verified that the field types match the type of data coming in. One field is a MEMO type field and wondering if a particular character might be throwing it off. I have converted all ' and " marks to the ASCII character value. I was thinking that might be it but not the case. TIA! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201220 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
CFMX/SQL Error
I am getting a very non-descript error in CFMX when doing a query on query. Has anybody seen this error before? I have posted the CFMX/SQL code below as well. Thanks - Error Message - Error casting an object of type java.lang.boolean to an incompatible type. This usually indicates a programming error in Java, although it could also mean you have tried to use a foreign object in a different way that it was designed. -- Error line number indicates closing tag on second query -- - CFMX / SQL Code - SELECT systems.systemName, users.userID, users.name AS userName, users.team, users.approval, users.comments FROM systems INNER JOIN users ON systems.ownerID = users.userID WHERE users.reviewCurrent = true GROUP BY users.team, users.userID, users.name, systems.systemName, users.approval, users.comments ORDER BY users.team, users.name, systems.systemName SELECT DISTINCT userID, userName, team, approval, comments FROM summaryInfo WHERE team = 'marketing' ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 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.
Unusual SQL error
I never have received this error before. [Macromedia][SQLServer JDBC Driver][SQLServer]Intra-query parallelism caused your server command (process ID #52) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1). Can anyone give some insite? -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 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 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
weird sql error
I keep getting a really strange sql error and I don't know what the heck it's coming from. === DETAIL: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 33: Incorrect syntax near '@P11'. SQL = "SET NOCOUNT ON INSERT INTO images ( archiveFlag, approvalTypeID, deletedFlag, imageExtensionID, contentTypeID, addedBy, modifiedBy, dateAdded, dateModified, mediaFilesize, mediaHeight, mediaWidth, filename, title, alt, caption) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ?, ?, ?, ?, ?, ?) SET NOCOUNT OFF SELECT @@IDENTITY AS newID" Query Parameter Value(s) - Parameter #1 = 0 Parameter #2 = 10 Parameter #3 = 0 Parameter #4 = 0 Parameter #5 = 112 Parameter #6 = 100 Parameter #7 = 100 Parameter #8 = {ts '2002-03-14 10:51:32'} Parameter #9 = {ts '2002-03-14 10:51:32'} Parameter #10 = 153654 Parameter #11 = 0 Parameter #12 = 0 Parameter #13 = 9906-feeding_031402_105132.bmp Parameter #14 = test Parameter #15 = Parameter #16 = === I've done a cf_dump of all the variable scopes and I can't figure out where this @P11 is coming from. Anyone run across something like this before? Jen Perkins Macromedia Certified ColdFusion Developer Carol/Trevelyan Strategy Group http://www.ctsg.com/ Kumbaya, Dammit! http://www.kumbayadammit.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.336 / Virus Database: 188 - Release Date: 3/11/2002 __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: sql error
Um, wild guess . . . no Products table? Are you referencing the wrong database? Regards, Matthew Walker /* Cabbage Tree Creative Ltd Christchurch - New Zealand http://www.matthewwalker.net.nz/ http://www.cabbagetree.co.nz/ */ > -Original Message- > From: S R [mailto:[EMAIL PROTECTED]] > Sent: Thursday, 16 May 2002 10:13 a.m. > To: CF-Talk > Subject: sql error > > > This one is driving me nuts. I keep getting a 'Base table not > found error'. > Invalid object name 'Products'. > > This is my query: > > SELECT Industries.name as industryName, > Products.name as productName, > Products.version, Products.prefix, Products.ID as prodID > FROMProducts, Industries > WHERE Products.name = '#COOKIE.prodName#' AND > Products.version = > '#COOKIE.prodVersion#' AND Products.industryID = Industries.ID > order by Industries.name, Products.name > > Does anyone know what may be causing this if you look at my code? > > thanks > > > __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: sql error
Did you creat the table in one database and upload it to your SQL using an account? look at the DB owner. example: Create a table in SQL has the name dbo.tablename Create table in local SQL or Access and import/Export it you get username.tablename Rick -Original Message- From: S R [mailto:[EMAIL PROTECTED]] Sent: Wed 5/15/2002 5:12 PM To: CF-Talk Cc: Subject: sql error __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: sql error
That's why this is driving me crazy because I already checked out that possibility and I'm definitely referencing the correct database. There is a products table. The strange thing is that sometimes it does work and sometimes it doesnt. It just doesn't make sense. >From: "Matthew Walker" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: CF-Talk <[EMAIL PROTECTED]> >Subject: RE: sql error >Date: Thu, 16 May 2002 10:18:41 +1200 > >Um, wild guess . . . no Products table? Are you referencing the wrong >database? > > >Regards, >Matthew Walker >/* > Cabbage Tree Creative Ltd > Christchurch - New Zealand > > http://www.matthewwalker.net.nz/ > http://www.cabbagetree.co.nz/ >*/ > > > > -Original Message- > > From: S R [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, 16 May 2002 10:13 a.m. > > To: CF-Talk > > Subject: sql error > > > > > > This one is driving me nuts. I keep getting a 'Base table not > > found error'. > > Invalid object name 'Products'. > > > > This is my query: > > > > SELECT Industries.name as industryName, > > Products.name as productName, > > Products.version, Products.prefix, Products.ID as prodID > > FROMProducts, Industries > > WHERE Products.name = '#COOKIE.prodName#' AND > > Products.version = > > '#COOKIE.prodVersion#' AND Products.industryID = Industries.ID > > order by Industries.name, Products.name > > > > Does anyone know what may be causing this if you look at my code? > > > > thanks > > > > > > > __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: sql error
I think I figured it out. I have a web farm and the database name was not specified on one of the server's CF Admin. thx >From: "S R" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: CF-Talk <[EMAIL PROTECTED]> >Subject: RE: sql error >Date: Wed, 15 May 2002 15:24:56 -0700 > >That's why this is driving me crazy because I already checked out that >possibility and I'm definitely referencing the correct database. There is a >products table. > >The strange thing is that sometimes it does work and sometimes it doesnt. >It >just doesn't make sense. > > > >From: "Matthew Walker" <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: CF-Talk <[EMAIL PROTECTED]> > >Subject: RE: sql error > >Date: Thu, 16 May 2002 10:18:41 +1200 > > > >Um, wild guess . . . no Products table? Are you referencing the wrong > >database? > > > > > >Regards, > >Matthew Walker > >/* > > Cabbage Tree Creative Ltd > > Christchurch - New Zealand > > > > http://www.matthewwalker.net.nz/ > > http://www.cabbagetree.co.nz/ > >*/ > > > > > > > -Original Message- > > > From: S R [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, 16 May 2002 10:13 a.m. > > > To: CF-Talk > > > Subject: sql error > > > > > > > > > This one is driving me nuts. I keep getting a 'Base table not > > > found error'. > > > Invalid object name 'Products'. > > > > > > This is my query: > > > > > > SELECT Industries.name as industryName, > > > Products.name as productName, > > > Products.version, Products.prefix, Products.ID as prodID > > > FROMProducts, Industries > > > WHERE Products.name = '#COOKIE.prodName#' AND > > > Products.version = > > > '#COOKIE.prodVersion#' AND Products.industryID = Industries.ID > > > order by Industries.name, Products.name > > > > > > Does anyone know what may be causing this if you look at my code? > > > > > > thanks > > > > > > > > > > > > __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: sql error
that's not it. That would give you a DATASOURCE NOT FOUND error. Chances are you just mapped to the wrong DB somewhere or your permissions are screwed up somehow. +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: S R [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 3:31 PM To: CF-Talk Subject: RE: sql error I think I figured it out. I have a web farm and the database name was not specified on one of the server's CF Admin. thx >From: "S R" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: CF-Talk <[EMAIL PROTECTED]> >Subject: RE: sql error >Date: Wed, 15 May 2002 15:24:56 -0700 > >That's why this is driving me crazy because I already checked out that >possibility and I'm definitely referencing the correct database. There is a >products table. > >The strange thing is that sometimes it does work and sometimes it doesnt. >It >just doesn't make sense. > > > >From: "Matthew Walker" <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: CF-Talk <[EMAIL PROTECTED]> > >Subject: RE: sql error > >Date: Thu, 16 May 2002 10:18:41 +1200 > > > >Um, wild guess . . . no Products table? Are you referencing the wrong > >database? > > > > > >Regards, > >Matthew Walker > >/* > > Cabbage Tree Creative Ltd > > Christchurch - New Zealand > > > > http://www.matthewwalker.net.nz/ > > http://www.cabbagetree.co.nz/ > >*/ > > > > > > > -Original Message- > > > From: S R [mailto:[EMAIL PROTECTED]] > > > Sent: Thursday, 16 May 2002 10:13 a.m. > > > To: CF-Talk > > > Subject: sql error > > > > > > > > > This one is driving me nuts. I keep getting a 'Base table not > > > found error'. > > > Invalid object name 'Products'. > > > > > > This is my query: > > > > > > SELECT Industries.name as industryName, > > > Products.name as productName, > > > Products.version, Products.prefix, Products.ID as prodID > > > FROMProducts, Industries > > > WHERE Products.name = '#COOKIE.prodName#' AND > > > Products.version = > > > '#COOKIE.prodVersion#' AND Products.industryID = Industries.ID > > > order by Industries.name, Products.name > > > > > > Does anyone know what may be causing this if you look at my code? > > > > > > thanks > > > > > > > > > > > > __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: sql error
> > I think I figured it out. I have a web farm and the database > > name was not specified on one of the server's CF Admin. > > that's not it. That would give you a DATASOURCE NOT FOUND > error. Chances are you just mapped to the wrong DB somewhere > or your permissions are screwed up somehow. No, that would do it - the datasource would exist, but it just wouldn't point to a specific database, and with say SQL Server, you'd end up querying the default database, which unless you change that for the specific db user account would be "master". Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: sql error
That's exactly what happened. The datasource was there but with no database specified on one of the servers. >From: Dave Watts <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: CF-Talk <[EMAIL PROTECTED]> >Subject: RE: sql error >Date: Wed, 15 May 2002 18:51:11 -0400 > > > > I think I figured it out. I have a web farm and the database > > > name was not specified on one of the server's CF Admin. > > > > that's not it. That would give you a DATASOURCE NOT FOUND > > error. Chances are you just mapped to the wrong DB somewhere > > or your permissions are screwed up somehow. > >No, that would do it - the datasource would exist, but it just wouldn't >point to a specific database, and with say SQL Server, you'd end up >querying >the default database, which unless you change that for the specific db user >account would be "master". > >Dave Watts, CTO, Fig Leaf Software >http://www.figleaf.com/ >voice: (202) 797-5496 >fax: (202) 797-5444 > __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Strange SQL error
In all my time developing SQL I have never come across sommat like this - please someone tell me I'm being stoopid as it's a Fri afternoon. I have the following SP which refuses to release - it says it cannot find parameter @JuryTitle. If I comment out @JuryTitle and the corresponding field insert it works. If I change the name of the variable it doesn'tsomeone slap me with a kipper so I can see where I've gone wrong... CREATE PROCEDURE JuryInsert -- Any parameters here @CountryID int , @JuryName varchar(100) , @JuryTitle varchar(50) , @JuryCompanyvarchar(50) , @JuryCity varchar(50) , @JuryTypeID int AS BEGIN INSERT INTO tblJury ( CountryID , JuryName , JuryTitle , JuryCompany , JuryCity , JuryTypeID ) VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID ) IF @@error <> 0 BEGIN GOTO ERROR_EXIT END RETURN @@error ERROR_EXIT: RETURN @@error END GO -- Andy Ewings Director Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.net Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
strange SQL error
I have a action page that accepts any form page and its variables then based on the stored proc "sp_columns" builds sql statment for update or insert. When I try to use it I get an Datefield overflow error (SQL 2000). But if I output the sql statment and then copy and past it into my sql enterprise manager the statement will work fine. If I remove the form date field I then get an syntax error, but I can copy and past it into SQL enterprise manager and it works. Here is the page that builds the sql statment: sp_columns #Form.TableName# Update #Form.Tablename# SET #FieldNameValues# WHERE ID = #Form.ID# Here is the output of the sql statment it builds and then error i get. Update tbl_PreOp SET id=5, PreRegType='Hosp', Allergies='Tape,Iodine', LatexDiagnosis='Yes', EpipenAnaphylaxis='Yes', RubberReaction='Yes', TypeReation='test1', AvacadoBannaETC='Yes', TypeReactionToAvacadoBananaETC='test2', MedCondition='test3', UnexplainedAllergyType='test4' WHERE ID = 5 Queries GetTableInfo (Records=57, Time=63ms) SQL = sp_columns tbl_PreOp Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'Hosp'. SQL = "Update tbl_PreOp SET id=5, PreRegType=''Hosp'', Allergies=''Tape,Iodine'', LatexDiagnosis=''Yes'', EpipenAnaphylaxis=''Yes'', RubberReaction=''Yes'', TypeReation=''test1'', AvacadoBannaETC=''Yes'', TypeReactionToAvacadoBananaETC=''test2'', MedCondition=''test3'', UnexplainedAllergyType=''test4'' WHERE ID = 5" Data Source = "INTERNET_PHA" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (55:1) to (55:47) in the template file I:\INETPUB\WWWROOT\PALMETTOHEALTH\PREREGISTRATION\..\INCLUDE\CFM\INC_AUTOFORMUPDATE.CFM. Date/Time: 06/06/02 09:55:33Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461)Remote Address: 204.116.185.37HTTP Referrer: http://intertest/preregistration/preop2.cfm?PreRegType=Hosp&Drugs=No&ID=5 __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Weird SQL Error.
I get the following intermittent error from my SQL 2000 Server: Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 52 generated fatal exception c01d EXCEPTION_ILLEGAL_INSTRUCTION. SQL Server is terminating this process. Any suggestions? Thanks, Neil ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Strange SQL Error...
Has anyone seen this? There are no "subqueries" going on in this SQL statement.. But yet we get this error. == ODBC Error Code = 21000 () [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. SQL = "UPDATE tbl2inspectorformgeneration SET inspected = '5/5/1'" == Clues anyone? TTAIA! Lee ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Error
It's impossible to completely get rid of this error however you can minimize the chances of it happening. Look at the SQL books on line for advice on how to do this Justin >-Original Message- >From: Duane Boudreau [mailto:[EMAIL PROTECTED]] >Sent: Monday, January 29, 2001 3:27 PM >To: CF-Talk >Subject: SQL Error > > >Has anyone seen this error here before? What is the cause and the fix? > >ODBC Error Code = 40001 (Serialization failure) > >[Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction (process ID >#17) was deadlocked with another process and has been chosen as >the deadlock >victim. Rerun your transaction. > >TIA, >Duane > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Error
You get a deadlock when process A has got a lock on resource 1 and wants to get a lock on resource 2, and process B has got a lock on resource 2 and wants to get a lock on resource 1. The DBMS has no choice but to step in and abort one of the transactions. You should be able to CFCATCH the error and resubmit the transaction. If possible, change the design so that it can't happen. Nick -Original Message- From: Duane Boudreau [mailto:[EMAIL PROTECTED]] Sent: Monday, January 29, 2001 3:27 PM To: CF-Talk Subject: SQL Error Has anyone seen this error here before? What is the cause and the fix? ODBC Error Code = 40001 (Serialization failure) [Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction (process ID #17) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction. TIA, Duane ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Error
Thanks. At this point I haven't seen the db design. I'll have to have a look to see if there is a design flaw. Duane -Original Message- From: DeVoil, Nick [mailto:[EMAIL PROTECTED]] Sent: Monday, January 29, 2001 10:54 AM To: CF-Talk Subject: RE: SQL Error You get a deadlock when process A has got a lock on resource 1 and wants to get a lock on resource 2, and process B has got a lock on resource 2 and wants to get a lock on resource 1. The DBMS has no choice but to step in and abort one of the transactions. You should be able to CFCATCH the error and resubmit the transaction. If possible, change the design so that it can't happen. Nick -Original Message- From: Duane Boudreau [mailto:[EMAIL PROTECTED]] Sent: Monday, January 29, 2001 3:27 PM To: CF-Talk Subject: SQL Error Has anyone seen this error here before? What is the cause and the fix? ODBC Error Code = 40001 (Serialization failure) [Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction (process ID #17) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction. TIA, Duane ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL error
Is that # hash mark in the color value causing you trouble? - Original Message - From: "Jeff Davis" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Saturday, February 17, 2001 10:16 AM Subject: SQL error > Help? > I'm sure this is a silly insert error but I just don't see the problem. The answer filed is set up for 2000 Char. Do I need to use a special tag to insert HTML into the field? Running CF 4.5 and SQL7. > > ODBC Error Code = 22001 (String data right truncation) [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. > SQL = "insert into dbo.faq (question,answer) values ('What do I need to play at IWONITLIVE.com? ','Below are the requirements to plat at IWONITLIVE.com: > > > Requirement > Minimum > Recommended > > > Internet > Connection > 56K > Dial-Up > DSL/Cable > or higher > > > Windows > Media Player > Version > 5 > Version > 7 > > > Speakers > Any > Any > > > Microphone > Any > Any > > ')" > > > Jeff Davis > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL error
You may want to use a text field, not a char field. I imagine the quotes in the insert may be cause of the problems. There are a handful of statements that have an effect on the quotes. Look into htmleditformat() first. I don't see any single quotes in your insert, so preservesinglequotes() probably won't have an effect. Why do you want to store so much HTML in the database? Jeff Davis wrote: > Help? > I'm sure this is a silly insert error but I just don't see the problem. The answer >filed is set up for 2000 Char. Do I need to use a special tag to insert HTML into the >field? Running CF 4.5 and SQL7. > > ODBC Error Code = 22001 (String data right truncation) [Microsoft][ODBC SQL >Server Driver][SQL Server]String or binary data would be truncated. > SQL = "insert into dbo.faq (question,answer) values ('What do I need to play at >IWONITLIVE.com? ','Below are the requirements to plat at IWONITLIVE.com: > > > Requirement > Minimum > Recommended > > > size="2">Internet > Connection > size="2">56K > Dial-Up > size="2">DSL/Cable > or higher > > > size="2">Windows > Media Player > size="2">Version > 5 > size="2">Version > 7 > > > size="2">Speakers > size="2">Any > size="2">Any > > > size="2">Microphone > size="2">Any > size="2">Any > > ')" > > Jeff Davis > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL error
I have a backend page set up to insert new FAQ's to the site so teh CEO can add them as they come up. This is really the only one that has this much HTML for the table layout of requirements. It was teh # sign causing the problem, I removed all the tags and it inserted just fine. Thanks Guys. Jeff Davis - Original Message - From: "Jeffry Houser" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Saturday, February 17, 2001 2:31 PM Subject: Re: SQL error > > You may want to use a text field, not a char field. > >I imagine the quotes in the insert may be cause of the problems. There are a handful of statements that have an effect on the quotes. Look into htmleditformat() first. I don't see any single quotes > in your insert, so preservesinglequotes() probably won't have an effect. > >Why do you want to store so much HTML in the database? > > > Jeff Davis wrote: > > > Help? > > I'm sure this is a silly insert error but I just don't see the problem. The answer filed is set up for 2000 Char. Do I need to use a special tag to insert HTML into the field? Running CF 4.5 and SQL7. > > > > ODBC Error Code = 22001 (String data right truncation) [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. > > SQL = "insert into dbo.faq (question,answer) values ('What do I need to play at IWONITLIVE.com? ','Below are the requirements to plat at IWONITLIVE.com: > > > > > > Requirement > > Minimum > > Recommended > > > > > > Internet > > Connection > > 56K > > Dial-Up > > DSL/Cable > > or higher > > > > > > Windows > > Media Player > > Version > > 5 > > Version > > 7 > > > > > > Speakers > > Any > > Any > > > > > > Microphone > > Any > > Any > > > > ')" > > > > Jeff Davis > > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL error
Jeff It's not a silly error, but exactly what it says... Your second value is 2330 characters long... a 2,000 char field fills up quickly, doesn't it! HTH Dick At 11:16 AM -0500 2/17/01, Jeff Davis wrote: >Help? >I'm sure this is a silly insert error but I just don't see the >problem. The answer filed is set up for 2000 Char. Do I need to use >a special tag to insert HTML into the field? Running CF 4.5 and SQL7. > >ODBC Error Code = 22001 (String data right truncation) >[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data >would be truncated. >SQL = "insert into dbo.faq (question,answer) values ('What do I need >to play at IWONITLIVE.com? ','Below are the requirements to plat at >IWONITLIVE.com: > > > color="#FF">Requirement > color="#FF">Minimum > color="#FF">Recommended > > > Internet > Connection > 56K > Dial-Up > DSL/Cable > or higher > > > Windows > Media Player > Version > 5 > Version > 7 > > > Speakers > Any > Any > > > Microphone > Any > Any > > ')" > > >Jeff Davis > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Error
I'm not too hot with Access but if that were SQL Server my first thought would be to look at decimal datatypes. Ade -Original Message- From: Adkins, Randy [mailto:[EMAIL PROTECTED] Sent: 01 April 2005 23:27 To: CF-Talk Subject: SQL Error Does anyone know why this would occur? Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Invalid precision value I am using MS-Access and using CFQUERYPARAM tags. The tags are specified properly such as Integer or VARCHAR types. As well I re-verified that the field types match the type of data coming in. One field is a MEMO type field and wondering if a particular character might be throwing it off. I have converted all ' and " marks to the ASCII character value. I was thinking that might be it but not the case. TIA! -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.0 - Release Date: 31/03/2005 ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201223 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL Error
But there is no decimals in this. Adrian Lynch wrote: >I'm not too hot with Access but if that were SQL Server my first thought >would be to look at decimal datatypes. > >Ade > >-Original Message- >From: Adkins, Randy [mailto:[EMAIL PROTECTED] >Sent: 01 April 2005 23:27 >To: CF-Talk >Subject: SQL Error > > >Does anyone know why this would occur? > > Error Executing Database Query. [Macromedia][SequeLink JDBC >Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Invalid >precision value > >I am using MS-Access and using CFQUERYPARAM tags. The tags are specified >properly such >as Integer or VARCHAR types. As well I re-verified that the field types >match the type >of data coming in. > >One field is a MEMO type field and wondering if a particular character >might be >throwing it off. > >I have converted all ' and " marks to the ASCII character value. >I was thinking that might be it but not the case. > >TIA! > >-- >No virus found in this outgoing message. >Checked by AVG Anti-Virus. >Version: 7.0.308 / Virus Database: 266.9.0 - Release Date: 31/03/2005 > > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201240 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Error
Change this: Your SQL WHERE something = To this: Your SQL WHERE something = <cfqueryparam...> And take a look at what's actually being passed into the DB. Ade -Original Message- From: ColdFusion Developer [mailto:[EMAIL PROTECTED] Sent: 02 April 2005 00:50 To: CF-Talk Subject: Re: SQL Error But there is no decimals in this. Adrian Lynch wrote: >I'm not too hot with Access but if that were SQL Server my first thought >would be to look at decimal datatypes. > >Ade > >-Original Message- >From: Adkins, Randy [mailto:[EMAIL PROTECTED] >Sent: 01 April 2005 23:27 >To: CF-Talk >Subject: SQL Error > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.0 - Release Date: 31/03/2005 ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201241 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Error
I recently had this same issue with a site that was switched from CF5 to MX61. Client storage DB was choking on the CFDATA table's data field which is also a memo field. Solved it by creating a new data source in CFadmin using the Access driver with unicode support driver type. HTH, Doug -Original Message- From: Adkins, Randy [mailto:[EMAIL PROTECTED] Sent: April 1, 2005 2:27 PM To: CF-Talk Subject: SQL Error Does anyone know why this would occur? Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Invalid precision value I am using MS-Access and using CFQUERYPARAM tags. The tags are specified properly such as Integer or VARCHAR types. As well I re-verified that the field types match the type of data coming in. One field is a MEMO type field and wondering if a particular character might be throwing it off. I have converted all ' and " marks to the ASCII character value. I was thinking that might be it but not the case. TIA! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201243 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: SQL Error
Oops, forgot to say that I also created a new database, then used Cfadmin to create the Client Storage tables, and then copied the data from the old DB, and then created a new unicode supported data source... DF -Original Message- From: Doug Fentiman [mailto:[EMAIL PROTECTED] Sent: April 1, 2005 4:31 PM To: CF-Talk Subject: RE: SQL Error I recently had this same issue with a site that was switched from CF5 to MX61. Client storage DB was choking on the CFDATA table's data field which is also a memo field. Solved it by creating a new data source in CFadmin using the Access driver with unicode support driver type. HTH, Doug -Original Message- From: Adkins, Randy [mailto:[EMAIL PROTECTED] Sent: April 1, 2005 2:27 PM To: CF-Talk Subject: SQL Error Does anyone know why this would occur? Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Invalid precision value I am using MS-Access and using CFQUERYPARAM tags. The tags are specified properly such as Integer or VARCHAR types. As well I re-verified that the field types match the type of data coming in. One field is a MEMO type field and wondering if a particular character might be throwing it off. I have converted all ' and " marks to the ASCII character value. I was thinking that might be it but not the case. TIA! ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201244 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
SQL error - Arithmetic overflow...?
This ran just fine using Microsoft Access. Now I get an overflow? Can you spot what's wrong? The values in the fields aren't very big - the largest is like 1952 to which I'm adding 20 or 30 or so... = ODBC Error Code = 22003 (Numeric value out of range) [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting numeric to data type numeric. SQL = "Update MEMBERS Set status='Gold', current_point_ratio=1.25, total_purchases=total_purchases+22, purchases_this_year=purchases_this_year+22, purchases_quarter_1=purchases_quarter_1+22, total_points_earned=total_points_earned+27.5, points_earned_this_year=points_earned_this_year+27.5, points_earned_quarter_1=points_earned_quarter_1+27.5, point_balance=point_balance+27.5, last_tran_posted_on={d '2002-10-02'}, last_tran_posted_at={t '21:16:33'} Where uuid='23A99597-9F04-11D6-AEAA00A0CC617FCD'" I guess it's too much to ask that they tell me WHAT value is out of range - or, since I am now using a "real" SQL database instead of MS Access, do I have to do all the calculations outside of the Update? Thanks in Advance, Ed Gordon = HERE'S THE CF CODE = Update MEMBERS Set status='#STATUSCHANGE#', current_point_ratio=#RATIOCHANGE#, total_purchases=total_purchases+#numAmount#, purchases_this_year=purchases_this_year+#numAmount#, purchases_quarter_1=purchases_quarter_1+#numAmount#, total_points_earned=total_points_earned+#numPoints#, points_earned_this_year=points_earned_this_year+#numPoints#, points_earned_quarter_1=points_earned_quarter_1+#numPoints#, point_balance=point_balance+#numPoints#, last_tran_posted_on=#CREATEODBCDATE(tdazedate)#, last_tran_posted_at=#CREATEODBCTIME(theodbctime)# Where uuid='#getmember.uuid#' = HELP? TIA
RE: Unusual SQL error
Sounds to me like when SQL created it's execution plan it is running several parts of the query simultaneously. For some reason when this happens it causes a conflict within your query. What SQL is asking you to do is give it a hint about how to process the query to avoid this problem. __ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX:301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299WWW:http://www.euservices.com __ > -Original Message- > From: Creese, Eric [mailto:[EMAIL PROTECTED] > Sent: Friday, July 04, 2003 1:17 PM > To: CF-Talk > Subject: Unusual SQL error > > > I never have received this error before. > > [Macromedia][SQLServer JDBC Driver][SQLServer]Intra-query > parallelism caused > your server command (process ID #52) to deadlock. Rerun the > query without > intra-query parallelism by using the query hint option (maxdop 1). > Can anyone give some insite? > > > -- > > This e-mail is intended for the use of the addressee(s) only > and may contain > privileged, confidential, or proprietary information that is > exempt from > disclosure under law. If you have received this message in > error, please > inform us promptly by reply e-mail, then delete the e-mail > and destroy any > printed copy. Thank you. > == > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 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 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Unusual SQL error
Thanks, I added the option (maxdop 1) to the end of the query to set the max degree of parallism to 1 -Original Message- From: Bill Grover [mailto:[EMAIL PROTECTED] Sent: Monday, July 07, 2003 9:30 AM To: CF-Talk Subject: RE: Unusual SQL error Sounds to me like when SQL created it's execution plan it is running several parts of the query simultaneously. For some reason when this happens it causes a conflict within your query. What SQL is asking you to do is give it a hint about how to process the query to avoid this problem. __ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX:301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299WWW:http://www.euservices.com __ > -Original Message- > From: Creese, Eric [mailto:[EMAIL PROTECTED] > Sent: Friday, July 04, 2003 1:17 PM > To: CF-Talk > Subject: Unusual SQL error > > > I never have received this error before. > > [Macromedia][SQLServer JDBC Driver][SQLServer]Intra-query > parallelism caused > your server command (process ID #52) to deadlock. Rerun the > query without > intra-query parallelism by using the query hint option (maxdop 1). > Can anyone give some insite? > > > -- > > This e-mail is intended for the use of the addressee(s) only > and may contain > privileged, confidential, or proprietary information that is > exempt from > disclosure under law. If you have received this message in > error, please > inform us promptly by reply e-mail, then delete the e-mail > and destroy any > printed copy. Thank you. > == > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 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. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Unusual SQL error
That should fix it. __ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX:301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299WWW:http://www.euservices.com __ > -Original Message- > From: Eric Creese [mailto:[EMAIL PROTECTED] > Sent: Monday, July 07, 2003 10:34 AM > To: CF-Talk > Subject: RE: Unusual SQL error > > > Thanks, > > I added the option (maxdop 1) to the end of the query to set > the max degree of parallism to 1 > > -Original Message- > From: Bill Grover [mailto:[EMAIL PROTECTED] > Sent: Monday, July 07, 2003 9:30 AM > To: CF-Talk > Subject: RE: Unusual SQL error > > > Sounds to me like when SQL created it's execution plan it is > running several parts of the query simultaneously. For some > reason when this happens it causes a conflict within your > query. What SQL is asking you to do is give it a hint about > how to process the query to avoid this problem. > > __ > > Bill Grover > Supervisor MIS Phone: 301.424.3300 x3324 > EU Services, Inc. FAX:301.424.3696 > 649 North Horners Lane E-Mail: [EMAIL PROTECTED] > Rockville, MD 20850-1299WWW:http://www.euservices.com > __ > > > > > -Original Message- > > From: Creese, Eric [mailto:[EMAIL PROTECTED] > > Sent: Friday, July 04, 2003 1:17 PM > > To: CF-Talk > > Subject: Unusual SQL error > > > > > > I never have received this error before. > > > > [Macromedia][SQLServer JDBC Driver][SQLServer]Intra-query > > parallelism caused > > your server command (process ID #52) to deadlock. Rerun the > > query without > > intra-query parallelism by using the query hint option (maxdop 1). > > Can anyone give some insite? > > > > > > -- > > > > This e-mail is intended for the use of the addressee(s) only > > and may contain > > privileged, confidential, or proprietary information that is > > exempt from > > disclosure under law. If you have received this message in > > error, please > > inform us promptly by reply e-mail, then delete the e-mail > > and destroy any > > printed copy. Thank you. > > == > > > > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Unusual SQL error
Bill, This just got me thinking about a problem I had a while back. I had a sql7 query like this: select * from myTable where isDate(myDateField) = 1 and month(myDateField) > 2 Everything worked fine until I moved to SQL 2000. Then it would return an error because myDateField could not be converted to datetime. The myDateField column can have a date, "NR", "INC". (Not my choice, doing this was dictated to us by another group in our company). The explanation I got from another DBA said that SQL server 7 processed each item of the where sequentially, but SQL 2000 processes all of the where items at the same time. If that is true, do you think that setting the maxdop 1 would prevent the error? No rush for an answer, we already changed all of our code, this is more just for my own piece of mind. Steve -Original Message- From: Bill Grover [mailto:[EMAIL PROTECTED] Sent: Monday, July 07, 2003 10:36 AM To: CF-Talk Subject: RE: Unusual SQL error That should fix it. __ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX:301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299WWW:http://www.euservices.com __ > -Original Message- > From: Eric Creese [mailto:[EMAIL PROTECTED] > Sent: Monday, July 07, 2003 10:34 AM > To: CF-Talk > Subject: RE: Unusual SQL error > > > Thanks, > > I added the option (maxdop 1) to the end of the query to set > the max degree of parallism to 1 > > -Original Message- > From: Bill Grover [mailto:[EMAIL PROTECTED] > Sent: Monday, July 07, 2003 9:30 AM > To: CF-Talk > Subject: RE: Unusual SQL error > > > Sounds to me like when SQL created it's execution plan it is > running several parts of the query simultaneously. For some > reason when this happens it causes a conflict within your > query. What SQL is asking you to do is give it a hint about > how to process the query to avoid this problem. > > __ > > Bill Grover > Supervisor MIS Phone: 301.424.3300 x3324 > EU Services, Inc. FAX:301.424.3696 > 649 North Horners Lane E-Mail: [EMAIL PROTECTED] > Rockville, MD 20850-1299WWW:http://www.euservices.com > __ > > > > > -Original Message- > > From: Creese, Eric [mailto:[EMAIL PROTECTED] > > Sent: Friday, July 04, 2003 1:17 PM > > To: CF-Talk > > Subject: Unusual SQL error > > > > > > I never have received this error before. > > > > [Macromedia][SQLServer JDBC Driver][SQLServer]Intra-query > > parallelism caused > > your server command (process ID #52) to deadlock. Rerun the > > query without > > intra-query parallelism by using the query hint option (maxdop 1). > > Can anyone give some insite? > > > > > > -- > > > > This e-mail is intended for the use of the addressee(s) only > > and may contain > > privileged, confidential, or proprietary information that is > > exempt from > > disclosure under law. If you have received this message in > > error, please > > inform us promptly by reply e-mail, then delete the e-mail > > and destroy any > > printed copy. Thank you. > > == > > > > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 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. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Unusual SQL error
Maybe but not necessarily. It is possible that SQL thinks the most efficient way to retrieve the data is to process the 2nd part of the where before the 1st part. If you have data in your fields that are not dates then your select will fail because of this data. MAXDOP goes beyond just the where clauses. If you are joining tables together SQL will actually do selects on each individual table simultaneously then link the results together before doing the final filtering. The MAXDOP setting controls how many of these selects will take place simultaneously. I hope that answered your question. __ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX:301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299WWW:http://www.euservices.com __ > -Original Message- > From: DURETTE, STEVEN J (AIT) [mailto:[EMAIL PROTECTED] > Sent: Monday, July 07, 2003 10:47 AM > To: CF-Talk > Subject: RE: Unusual SQL error > > > Bill, > > This just got me thinking about a problem I had a while back. > > I had a sql7 query like this: > > select * > from myTable > where isDate(myDateField) = 1 > and month(myDateField) > 2 > > Everything worked fine until I moved to SQL 2000. Then it > would return an > error because myDateField could not be converted to datetime. > > The myDateField column can have a date, "NR", "INC". (Not my > choice, doing > this was dictated to us by another group in our company). > The explanation I > got from another DBA said that SQL server 7 processed each > item of the where > sequentially, but SQL 2000 processes all of the where items > at the same > time. > > If that is true, do you think that setting the maxdop 1 would > prevent the > error? > > No rush for an answer, we already changed all of our code, > this is more just > for my own piece of mind. > > Steve > > > -----Original Message- > From: Bill Grover [mailto:[EMAIL PROTECTED] > Sent: Monday, July 07, 2003 10:36 AM > To: CF-Talk > Subject: RE: Unusual SQL error > > > That should fix it. > > __ > > Bill Grover > Supervisor MIS Phone: 301.424.3300 x3324 > EU Services, Inc. FAX:301.424.3696 > 649 North Horners Lane E-Mail: [EMAIL PROTECTED] > Rockville, MD 20850-1299WWW:http://www.euservices.com > __________ > > > > > -Original Message- > > From: Eric Creese [mailto:[EMAIL PROTECTED] > > Sent: Monday, July 07, 2003 10:34 AM > > To: CF-Talk > > Subject: RE: Unusual SQL error > > > > > > Thanks, > > > > I added the option (maxdop 1) to the end of the query to set > > the max degree of parallism to 1 > > > > -Original Message- > > From: Bill Grover [mailto:[EMAIL PROTECTED] > > Sent: Monday, July 07, 2003 9:30 AM > > To: CF-Talk > > Subject: RE: Unusual SQL error > > > > > > Sounds to me like when SQL created it's execution plan it is > > running several parts of the query simultaneously. For some > > reason when this happens it causes a conflict within your > > query. What SQL is asking you to do is give it a hint about > > how to process the query to avoid this problem. > > > > __ > > > > Bill Grover > > Supervisor MIS Phone: 301.424.3300 x3324 > > EU Services, Inc. FAX: 301.424.3696 > > 649 North Horners Lane E-Mail: [EMAIL PROTECTED] > > Rockville, MD 20850-1299WWW:http://www.euservices.com > > __ > > > > > > > > > -Original Message- > > > From: Creese, Eric [mailto:[EMAIL PROTECTED] > > > Sent: Friday, July 04, 2003 1:17 PM > > > To: CF-Talk > > > Subject: Unusual SQL error > > > > > > > > > I never have received this error before. > > > > > > [Macromedia][SQLServer JDBC Driver][SQLServer]Intra-query > > > parallelism caused > > > your server command (process ID #52) to deadlock. Rerun the > > > query without > > > intra-query parallelism by using th
RE: weird sql error
How about the missing comma in the Values part of your query... looks like a temp stored procedure is being created and choking between parameters 10 and 11. Justin > -Original Message- > From: Jen Perkins [mailto:[EMAIL PROTECTED]] > Sent: Thursday, March 14, 2002 2:38 PM > To: CF-Talk > Subject: weird sql error > > I keep getting a really strange sql error and I don't know > what the heck > it's coming from. > > === > DETAIL: [Microsoft][ODBC SQL Server Driver][SQL Server]Line > 33: Incorrect > syntax near '@P11'. > > SQL = "SET NOCOUNT ON INSERT INTO images ( archiveFlag, > approvalTypeID, > deletedFlag, imageExtensionID, contentTypeID, addedBy, modifiedBy, > dateAdded, dateModified, mediaFilesize, mediaHeight, > mediaWidth, filename, > title, alt, caption) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ?, > ?, ?, ?, ?, ?) > SET NOCOUNT OFF SELECT @@IDENTITY AS newID" > > Query Parameter Value(s) - > Parameter #1 = 0 > Parameter #2 = 10 > Parameter #3 = 0 > Parameter #4 = 0 > Parameter #5 = 112 > Parameter #6 = 100 > Parameter #7 = 100 > Parameter #8 = {ts '2002-03-14 10:51:32'} > Parameter #9 = {ts '2002-03-14 10:51:32'} > Parameter #10 = 153654 > Parameter #11 = 0 > Parameter #12 = 0 > Parameter #13 = 9906-feeding_031402_105132.bmp > Parameter #14 = test > Parameter #15 = > Parameter #16 = > === > > I've done a cf_dump of all the variable scopes and I can't > figure out where > this @P11 is coming from. Anyone run across something like > this before? > > > > Jen Perkins > Macromedia Certified ColdFusion Developer > Carol/Trevelyan Strategy Group > http://www.ctsg.com/ > > Kumbaya, Dammit! > http://www.kumbayadammit.com/ > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.336 / Virus Database: 188 - Release Date: 3/11/2002 > > __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: weird sql error
That might be it. Good Catch!! -Original Message- From: Justin Greene [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 14, 2002 4:45 PM To: CF-Talk Subject: RE: weird sql error How about the missing comma in the Values part of your query... looks like a temp stored procedure is being created and choking between parameters 10 and 11. Justin > -Original Message- > From: Jen Perkins [mailto:[EMAIL PROTECTED]] > Sent: Thursday, March 14, 2002 2:38 PM > To: CF-Talk > Subject: weird sql error > > I keep getting a really strange sql error and I don't know > what the heck > it's coming from. > > === > DETAIL: [Microsoft][ODBC SQL Server Driver][SQL Server]Line > 33: Incorrect > syntax near '@P11'. > > SQL = "SET NOCOUNT ON INSERT INTO images ( archiveFlag, > approvalTypeID, > deletedFlag, imageExtensionID, contentTypeID, addedBy, modifiedBy, > dateAdded, dateModified, mediaFilesize, mediaHeight, > mediaWidth, filename, > title, alt, caption) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ?, > ?, ?, ?, ?, ?) > SET NOCOUNT OFF SELECT @@IDENTITY AS newID" > > Query Parameter Value(s) - > Parameter #1 = 0 > Parameter #2 = 10 > Parameter #3 = 0 > Parameter #4 = 0 > Parameter #5 = 112 > Parameter #6 = 100 > Parameter #7 = 100 > Parameter #8 = {ts '2002-03-14 10:51:32'} > Parameter #9 = {ts '2002-03-14 10:51:32'} > Parameter #10 = 153654 > Parameter #11 = 0 > Parameter #12 = 0 > Parameter #13 = 9906-feeding_031402_105132.bmp > Parameter #14 = test > Parameter #15 = > Parameter #16 = > === > > I've done a cf_dump of all the variable scopes and I can't > figure out where > this @P11 is coming from. Anyone run across something like > this before? > > > > Jen Perkins > Macromedia Certified ColdFusion Developer > Carol/Trevelyan Strategy Group > http://www.ctsg.com/ > > Kumbaya, Dammit! > http://www.kumbayadammit.com/ > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.336 / Virus Database: 188 - Release Date: 3/11/2002 > > __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
SQL Error, I know
I know this is suppose to be for CF stuff, but I know someone can help with this. I'm under the gun here. I'm getting this SQL 2000 error message when I try to add a new column to an existing table that's filled with data. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object 'Tmp_samp_request' in database 'hititte4_db' because the 'PRIMARY' filegroup is full. Thank you in advance. Please help. D- __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Strange SQL error
I assume in this bit: VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID you're actually using VALUES ( @CountryID , @JuryName , @JuryTitle , @JuryCompany , @JuryCity , @JuryTypeID ? > -Original Message- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: 17 May 2002 16:56 > To: CF-Talk > Subject: Strange SQL error > > > In all my time developing SQL I have never come across sommat > like this - > please someone tell me I'm being stoopid as it's a Fri > afternoon. > I have the following SP which refuses to release - it says it > cannot find > parameter @JuryTitle. If I comment out @JuryTitle and the > corresponding > field insert it works. If I change the name of the variable it > doesn'tsomeone slap me with a kipper so I can see > where I've > gone wrong... > > CREATE PROCEDURE JuryInsert > > -- Any parameters here > @CountryID int > , @JuryName varchar(100) > , @JuryTitle varchar(50) > , @JuryCompanyvarchar(50) > , @JuryCity varchar(50) > , @JuryTypeID int > > AS > > BEGIN > > INSERT INTO tblJury ( > CountryID > , JuryName > , JuryTitle > , JuryCompany > , JuryCity > , JuryTypeID > ) > VALUES ( > @CountryID > , @JuryName > , @test > , @JuryCompany > , @JuryCity > , @JuryTypeID > ) > > IF @@error <> 0 > BEGIN > GOTO ERROR_EXIT > END > > RETURN @@error > > ERROR_EXIT: > RETURN @@error > > > END > GO > -- > Andy Ewings > Director > Thoughtbubble Ltd > http://www.thoughtbubble.net > -- > United Kingdom > http://www.thoughtbubble.net > Tel: +44 (0) 20 7387 8890 > -- > New Zealand > http://www.thoughtbubble.co.nz/ > Tel: +64 (0) 9 419 4235 > -- > The information in this email and in any attachments is > confidential and > intended solely for the attention and use of the named > addressee(s). Any > views or opinions presented are solely those of the author and do not > necessarily represent those of Thoughtbubble. This information may be > subject to legal, professional or other privilege and further > distribution > of it is strictly prohibited without our authority. If you are not the > intended recipient, you are not authorised to disclose, copy, > distribute, or > retain this message. Please notify us on +44 (0)207 387 8890. > > > __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Strange SQL error
ah - yes sorry - forgot to change it back -Original Message- From: Rich Wild [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 16:50 To: CF-Talk Subject: RE: Strange SQL error I assume in this bit: VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID you're actually using VALUES ( @CountryID , @JuryName , @JuryTitle , @JuryCompany , @JuryCity , @JuryTypeID ? > -Original Message- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: 17 May 2002 16:56 > To: CF-Talk > Subject: Strange SQL error > > > In all my time developing SQL I have never come across sommat > like this - > please someone tell me I'm being stoopid as it's a Fri > afternoon. > I have the following SP which refuses to release - it says it > cannot find > parameter @JuryTitle. If I comment out @JuryTitle and the > corresponding > field insert it works. If I change the name of the variable it > doesn'tsomeone slap me with a kipper so I can see > where I've > gone wrong... > > CREATE PROCEDURE JuryInsert > > -- Any parameters here > @CountryID int > , @JuryName varchar(100) > , @JuryTitle varchar(50) > , @JuryCompanyvarchar(50) > , @JuryCity varchar(50) > , @JuryTypeID int > > AS > > BEGIN > > INSERT INTO tblJury ( > CountryID > , JuryName > , JuryTitle > , JuryCompany > , JuryCity > , JuryTypeID > ) > VALUES ( > @CountryID > , @JuryName > , @test > , @JuryCompany > , @JuryCity > , @JuryTypeID > ) > > IF @@error <> 0 > BEGIN > GOTO ERROR_EXIT > END > > RETURN @@error > > ERROR_EXIT: > RETURN @@error > > > END > GO > -- > Andy Ewings > Director > Thoughtbubble Ltd > http://www.thoughtbubble.net > -- > United Kingdom > http://www.thoughtbubble.net > Tel: +44 (0) 20 7387 8890 > -- > New Zealand > http://www.thoughtbubble.co.nz/ > Tel: +64 (0) 9 419 4235 > -- > The information in this email and in any attachments is > confidential and > intended solely for the attention and use of the named > addressee(s). Any > views or opinions presented are solely those of the author and do not > necessarily represent those of Thoughtbubble. This information may be > subject to legal, professional or other privilege and further > distribution > of it is strictly prohibited without our authority. If you are not the > intended recipient, you are not authorised to disclose, copy, > distribute, or > retain this message. Please notify us on +44 (0)207 387 8890. > > > __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Strange SQL error
Right fixed it - all we did was to move the declaration of the JuryTitle param to the bottom of the list and itr works! SQL schmeequel -Original Message- From: Rich Wild [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 16:50 To: CF-Talk Subject: RE: Strange SQL error I assume in this bit: VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID you're actually using VALUES ( @CountryID , @JuryName , @JuryTitle , @JuryCompany , @JuryCity , @JuryTypeID ? > -Original Message- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: 17 May 2002 16:56 > To: CF-Talk > Subject: Strange SQL error > > > In all my time developing SQL I have never come across sommat > like this - > please someone tell me I'm being stoopid as it's a Fri > afternoon. > I have the following SP which refuses to release - it says it > cannot find > parameter @JuryTitle. If I comment out @JuryTitle and the > corresponding > field insert it works. If I change the name of the variable it > doesn'tsomeone slap me with a kipper so I can see > where I've > gone wrong... > > CREATE PROCEDURE JuryInsert > > -- Any parameters here > @CountryID int > , @JuryName varchar(100) > , @JuryTitle varchar(50) > , @JuryCompanyvarchar(50) > , @JuryCity varchar(50) > , @JuryTypeID int > > AS > > BEGIN > > INSERT INTO tblJury ( > CountryID > , JuryName > , JuryTitle > , JuryCompany > , JuryCity > , JuryTypeID > ) > VALUES ( > @CountryID > , @JuryName > , @test > , @JuryCompany > , @JuryCity > , @JuryTypeID > ) > > IF @@error <> 0 > BEGIN > GOTO ERROR_EXIT > END > > RETURN @@error > > ERROR_EXIT: > RETURN @@error > > > END > GO > -- > Andy Ewings > Director > Thoughtbubble Ltd > http://www.thoughtbubble.net > -- > United Kingdom > http://www.thoughtbubble.net > Tel: +44 (0) 20 7387 8890 > -- > New Zealand > http://www.thoughtbubble.co.nz/ > Tel: +64 (0) 9 419 4235 > -- > The information in this email and in any attachments is > confidential and > intended solely for the attention and use of the named > addressee(s). Any > views or opinions presented are solely those of the author and do not > necessarily represent those of Thoughtbubble. This information may be > subject to legal, professional or other privilege and further > distribution > of it is strictly prohibited without our authority. If you are not the > intended recipient, you are not authorised to disclose, copy, > distribute, or > retain this message. Please notify us on +44 (0)207 387 8890. > > > __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Strange SQL error
uh? > -Original Message- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: 17 May 2002 17:18 > To: CF-Talk > Subject: RE: Strange SQL error > > > Right fixed it - all we did was to move the declaration of > the JuryTitle > param to the bottom of the list and itr works! SQL schmeequel > > -Original Message- > From: Rich Wild [mailto:[EMAIL PROTECTED]] > Sent: 17 May 2002 16:50 > To: CF-Talk > Subject: RE: Strange SQL error > > > I assume in this bit: > > VALUES ( > @CountryID > , @JuryName > , @test > , @JuryCompany > , @JuryCity > , @JuryTypeID > > > you're actually using > > VALUES ( > @CountryID > , @JuryName > , @JuryTitle > , @JuryCompany > , @JuryCity > , @JuryTypeID > > > ? > > > -Original Message- > > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > > Sent: 17 May 2002 16:56 > > To: CF-Talk > > Subject: Strange SQL error > > > > > > In all my time developing SQL I have never come across sommat > > like this - > > please someone tell me I'm being stoopid as it's a Fri > > afternoon. > > I have the following SP which refuses to release - it says it > > cannot find > > parameter @JuryTitle. If I comment out @JuryTitle and the > > corresponding > > field insert it works. If I change the name of the variable it > > doesn'tsomeone slap me with a kipper so I can see > > where I've > > gone wrong... > > > > CREATE PROCEDURE JuryInsert > > > > -- Any parameters here > > @CountryID int > > , @JuryName varchar(100) > > , @JuryTitle varchar(50) > > , @JuryCompanyvarchar(50) > > , @JuryCity varchar(50) > > , @JuryTypeID int > > > > AS > > > > BEGIN > > > > INSERT INTO tblJury ( > > CountryID > > , JuryName > > , JuryTitle > > , JuryCompany > > , JuryCity > > , JuryTypeID > > ) > > VALUES ( > > @CountryID > > , @JuryName > > , @test > > , @JuryCompany > > , @JuryCity > > , @JuryTypeID > > ) > > > > IF @@error <> 0 > > BEGIN > > GOTO ERROR_EXIT > > END > > > > RETURN @@error > > > > ERROR_EXIT: > > RETURN @@error > > > > > > END > > GO > > -- > > Andy Ewings > > Director > > Thoughtbubble Ltd > > http://www.thoughtbubble.net > > -- > > United Kingdom > > http://www.thoughtbubble.net > > Tel: +44 (0) 20 7387 8890 > > -- > > New Zealand > > http://www.thoughtbubble.co.nz/ > > Tel: +64 (0) 9 419 4235 > > -- > > The information in this email and in any attachments is > > confidential and > > intended solely for the attention and use of the named > > addressee(s). Any > > views or opinions presented are solely those of the author > and do not > > necessarily represent those of Thoughtbubble. This > information may be > > subject to legal, professional or other privilege and further > > distribution > > of it is strictly prohibited without our authority. If you > are not the > > intended recipient, you are not authorised to disclose, copy, > > distribute, or > > retain this message. Please notify us on +44 (0)207 387 8890. > > > > > > > > __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Strange SQL error
What Andy meant was that he moved the @JuryTitle param to the bottom of the list like so: -- Any parameters here @CountryID int , @JuryName varchar(100) , @JuryCompanyvarchar(50) , @JuryCity varchar(50) , @JuryTypeID int , @JuryTitle varchar(50) @JuryTitle was originally declared after the second param @JuryName. For some reason the SP then released perfectly. Bizarrely I then moved the @JuryTitle param back to where it was and then the SP relases again. For the last 2 hours the SP would not release and we are at a loss as to why it didn't work. Any Ideas SQL gurus what may have happened? -Original Message- From: Rich Wild [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 17:10 To: CF-Talk Subject: RE: Strange SQL error uh? > -Original Message- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: 17 May 2002 17:18 > To: CF-Talk > Subject: RE: Strange SQL error > > > Right fixed it - all we did was to move the declaration of > the JuryTitle > param to the bottom of the list and itr works! SQL schmeequel > > -Original Message- > From: Rich Wild [mailto:[EMAIL PROTECTED]] > Sent: 17 May 2002 16:50 > To: CF-Talk > Subject: RE: Strange SQL error > > > I assume in this bit: > > VALUES ( > @CountryID > , @JuryName > , @test > , @JuryCompany > , @JuryCity > , @JuryTypeID > > > you're actually using > > VALUES ( > @CountryID > , @JuryName > , @JuryTitle > , @JuryCompany > , @JuryCity > , @JuryTypeID > > > ? > > > -----Original Message- > > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > > Sent: 17 May 2002 16:56 > > To: CF-Talk > > Subject: Strange SQL error > > > > > > In all my time developing SQL I have never come across sommat > > like this - > > please someone tell me I'm being stoopid as it's a Fri > > afternoon. > > I have the following SP which refuses to release - it says it > > cannot find > > parameter @JuryTitle. If I comment out @JuryTitle and the > > corresponding > > field insert it works. If I change the name of the variable it > > doesn'tsomeone slap me with a kipper so I can see > > where I've > > gone wrong... > > > > CREATE PROCEDURE JuryInsert > > > > -- Any parameters here > > @CountryID int > > , @JuryName varchar(100) > > , @JuryTitle varchar(50) > > , @JuryCompanyvarchar(50) > > , @JuryCity varchar(50) > > , @JuryTypeID int > > > > AS > > > > BEGIN > > > > INSERT INTO tblJury ( > > CountryID > > , JuryName > > , JuryTitle > > , JuryCompany > > , JuryCity > > , JuryTypeID > > ) > > VALUES ( > > @CountryID > > , @JuryName > > , @test > > , @JuryCompany > > , @JuryCity > > , @JuryTypeID > > ) > > > > IF @@error <> 0 > > BEGIN > > GOTO ERROR_EXIT > > END > > > > RETURN @@error > > > > ERROR_EXIT: > > RETURN @@error > > > > > > END > > GO > > -- > > Andy Ewings > > Director > > Thoughtbubble Ltd > > http://www.thoughtbubble.net > > -- > > United Kingdom > > http://www.thoughtbubble.net > > Tel: +44 (0) 20 7387 8890 > > -- > > New Zealand > > http://www.thoughtbubble.co.nz/ > > Tel: +64 (0) 9 419 4235 > > -- > > The information in this email and in any attachments is > > confidential and > > intended solely for the attention and use of the named > > addressee(s). Any > > views or opinions presented are solely those of the author > and do not
RE: Strange SQL error
Something that's happened to me before is that, depending on what you used to write the code originally, a hidden illegal character may have been introduced. When you moved it, once again depending on what you were using, the cut and paste would eliminate any illegal characters. I usually pass code through notepad if I've touched anything like Word or Wordpad for example because of the illegal characters. Margaret -Original Message- From: Stephen Galligan [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 9:25 AM To: CF-Talk Subject: RE: Strange SQL error What Andy meant was that he moved the @JuryTitle param to the bottom of the list like so: -- Any parameters here @CountryID int , @JuryName varchar(100) , @JuryCompanyvarchar(50) , @JuryCity varchar(50) , @JuryTypeID int , @JuryTitle varchar(50) @JuryTitle was originally declared after the second param @JuryName. For some reason the SP then released perfectly. Bizarrely I then moved the @JuryTitle param back to where it was and then the SP relases again. For the last 2 hours the SP would not release and we are at a loss as to why it didn't work. Any Ideas SQL gurus what may have happened? -Original Message- From: Rich Wild [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 17:10 To: CF-Talk Subject: RE: Strange SQL error uh? > -Original Message- > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > Sent: 17 May 2002 17:18 > To: CF-Talk > Subject: RE: Strange SQL error > > > Right fixed it - all we did was to move the declaration of > the JuryTitle > param to the bottom of the list and itr works! SQL schmeequel > > -Original Message- > From: Rich Wild [mailto:[EMAIL PROTECTED]] > Sent: 17 May 2002 16:50 > To: CF-Talk > Subject: RE: Strange SQL error > > > I assume in this bit: > > VALUES ( > @CountryID > , @JuryName > , @test > , @JuryCompany > , @JuryCity > , @JuryTypeID > > > you're actually using > > VALUES ( > @CountryID > , @JuryName > , @JuryTitle > , @JuryCompany > , @JuryCity > , @JuryTypeID > > > ? > > > -Original Message- > > From: Andy Ewings [mailto:[EMAIL PROTECTED]] > > Sent: 17 May 2002 16:56 > > To: CF-Talk > > Subject: Strange SQL error > > > > > > In all my time developing SQL I have never come across sommat > > like this - > > please someone tell me I'm being stoopid as it's a Fri > > afternoon. > > I have the following SP which refuses to release - it says it > > cannot find > > parameter @JuryTitle. If I comment out @JuryTitle and the > > corresponding > > field insert it works. If I change the name of the variable it > > doesn'tsomeone slap me with a kipper so I can see > > where I've > > gone wrong... > > > > CREATE PROCEDURE JuryInsert > > > > -- Any parameters here > > @CountryID int > > , @JuryName varchar(100) > > , @JuryTitle varchar(50) > > , @JuryCompanyvarchar(50) > > , @JuryCity varchar(50) > > , @JuryTypeID int > > > > AS > > > > BEGIN > > > > INSERT INTO tblJury ( > > CountryID > > , JuryName > > , JuryTitle > > , JuryCompany > > , JuryCity > > , JuryTypeID > > ) > > VALUES ( > > @CountryID > > , @JuryName > > , @test > > , @JuryCompany > > , @JuryCity > > , @JuryTypeID > > ) > > > > IF @@error <> 0 > > BEGIN > > GOTO ERROR_EXIT > > END > > > > RETURN @@error > > > > ERROR_EXIT: > > RETURN @@error > > > > > > END > > GO > > -- > > Andy Ewings > > Director > > Thoughtbubble Ltd > > http://www.thoughtbubble.net > > -- > > United Kingdom > > h
RE: strange SQL error
do this: Update #preserveSingleQuotes(Form.Tablename)# SET #preserveSingleQuotes(FieldNameValues)# WHERE ID = #preserveSingleQuotes(Form.ID)# +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ "...'If there must be trouble, let it be in my day, that my child may have peace'..." - Thomas Paine, The American Crisis -Original Message- From: David Brown [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 11:26 AM To: CF-Talk Subject: strange SQL error I have a action page that accepts any form page and its variables then based on the stored proc "sp_columns" builds sql statment for update or insert. When I try to use it I get an Datefield overflow error (SQL 2000). But if I output the sql statment and then copy and past it into my sql enterprise manager the statement will work fine. If I remove the form date field I then get an syntax error, but I can copy and past it into SQL enterprise manager and it works. Here is the page that builds the sql statment: sp_columns #Form.TableName# Update #Form.Tablename# SET #FieldNameValues# WHERE ID = #Form.ID# Here is the output of the sql statment it builds and then error i get. Update tbl_PreOp SET id=5, PreRegType='Hosp', Allergies='Tape,Iodine', LatexDiagnosis='Yes', EpipenAnaphylaxis='Yes', RubberReaction='Yes', TypeReation='test1', AvacadoBannaETC='Yes', TypeReactionToAvacadoBananaETC='test2', MedCondition='test3', UnexplainedAllergyType='test4' WHERE ID = 5 Queries GetTableInfo (Records=57, Time=63ms) SQL = sp_columns tbl_PreOp Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'Hosp'. SQL = "Update tbl_PreOp SET id=5, PreRegType=''Hosp'', Allergies=''Tape,Iodine'', LatexDiagnosis=''Yes'', EpipenAnaphylaxis=''Yes'', RubberReaction=''Yes'', TypeReation=''test1'', AvacadoBannaETC=''Yes'', TypeReactionToAvacadoBananaETC=''test2'', MedCondition=''test3'', UnexplainedAllergyType=''test4'' WHERE ID = 5" Data Source = "INTERNET_PHA" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (55:1) to (55:47) in the template file I:\INETPUB\WWWROOT\PALMETTOHEALTH\PREREGISTRATION\..\INCLUDE\CFM\INC_AUTOFOR MUPDATE.CFM. Date/Time: 06/06/02 09:55:33Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461)Remote Address: 204.116.185.37HTTP Referrer: http://intertest/preregistration/preop2.cfm?PreRegType=Hosp&Drugs=No& ;ID=5 __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
SQL Error: Inconsistent Data
This is a multi-part message in MIME format. --349BCA69A7CAD89DC18E181D Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I think I understand the error, but how comes it only happens on my production server and not on my local development server. It works fine on development server. Local development is Win2k, production server is Solaris. [IBM][CLI Driver][AS] SQL7008N REXX variable "ASSIGNED " contains inconsistent data. SQLSTATE=55019 Data Source = "OIMDBADM" SQL = "insert into oimdbadm.assigned (id, date, time, agency, reqtype, assigned, address, requester, req_email, phone, account_code, est_start_date, est_comp_date, est_man_hrs, act_start_date, act_comp_date, act_man_hrs, due_date, title, descrip, comments, completed) values ('1009', '2000-10-25', '03:20:14', 'Occupational & Professional Licensing', 'Web Support -- Intranet', 'Greg Mamayek', '[EMAIL PROTECTED]', 'Mickey Mouse', '[EMAIL PROTECTED]', '408 555-1212', 'General Administration -- 61626', NULL, NULL, '0', NULL, NULL, '0', '2000-11-01', 'Testing Demo', 'This is a test of the OIM Work Order System. This is only a test. BEP.', '', 'Incomplete')" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (69:1) to (69:45) in the template file /inq2/netscape/suitespot/intranet-docs/dllr/secured-docs/oim_new_test/oim_assign_action.cfm --349BCA69A7CAD89DC18E181D Content-Type: text/x-vcard; charset=us-ascii; name="jtaavon.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for James Taavon Content-Disposition: attachment; filename="jtaavon.vcf" begin:vcard n:Taavon;James tel;cell:(443) 506-2117 tel;fax:(410) 333-5203 tel;work:(410) 767-3415 x-mozilla-html:FALSE org:Department of Labor, Licensing and Regulation;Office of Information and Technology version:2.1 email;internet:[EMAIL PROTECTED] title:Senior Web Developer adr;quoted-printable:;;1100 N. Eutaw Street,=0D=0ARoom 203;Baltimore;MD;21201; fn:James Taavon end:vcard --349BCA69A7CAD89DC18E181D-- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
strange SQL error code
Anyone ever seen this one? ODBC Error Code = 01004 (Data truncated) [Microsoft][ODBC SQL Server Driver]Fractional truncation On that note, does anyone have a list of Microsoft SQL Error Codes? Please copy me with your reply. Thanks. -- Mark Warrick Phone: (714) 547-5386 Efax.com Fax: (801) 730-7289 Personal Email: [EMAIL PROTECTED] Personal URL: http://www.warrick.net Business Email: [EMAIL PROTECTED] Business URL: http://www.fusioneers.com ICQ: 346566 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebarRsts or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Strange SQL Error...
Try: SQL = 'UPDATE tbl2inspectorformgeneration SET inspected = ''5/5/1''' Single quotes make strings, not double quotes. Two single quotes together makes for a single quote inside of a string. -Original Message- From: Lee Fuller [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 04, 2001 12:33 PM To: CF-Talk Subject: Strange SQL Error... Has anyone seen this? There are no "subqueries" going on in this SQL statement.. But yet we get this error. == ODBC Error Code = 21000 () [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. SQL = "UPDATE tbl2inspectorformgeneration SET inspected = '5/5/1'" == Clues anyone? TTAIA! Lee ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Strange SQL Error...
Do you have an update trigger on the table? -Original Message- From: Lee Fuller [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 04, 2001 2:33 PM To: CF-Talk Subject: Strange SQL Error... Has anyone seen this? There are no "subqueries" going on in this SQL statement.. But yet we get this error. == ODBC Error Code = 21000 () [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. SQL = "UPDATE tbl2inspectorformgeneration SET inspected = '5/5/1'" == Clues anyone? TTAIA! Lee ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Help with SQL error
I have a template that strips out HTML code from a string. It then takes the first 255 chars to create a blurb that is used as a summary in a verity collection. When I try to insert the blurb in the DB, I get the following SQL error: ODBC Error Code = 37000 (Syntax error or access violation) [MERANT][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string 'It is important that community policies and procedures be enforced uniformly and consistently.'. SQL = "INSERT INTO sections (secNumber, secName, chapID, blurb) VALUES (620, 'Enforcing Community Policies', 6, 'It is important that community policies and procedures be enforced uniformly and consistently. Residents will then have a clear understanding of their responsibilities, as well as those of Management. The Resident Handbook, which all residents ar')" Why do I get this error. The SQL looks OK to me, the "blurb" looks ok in the error message, and I cannot find the unclosed quotation marks anywhere. I have tried wrapping the blurb variable in PreserveSingleQuotes(), to no avail. Any ideas. Here is the insert code: INSERT INTO sections (secNumber, secName, chapID, blurb) VALUES (#attributes.secNumber#, '#attributes.secName#', #attributes.chapID#, '#attributes.blurb#') Marwan Saidi Webmaster CED - Concord IS [EMAIL PROTECTED] 407.741.8645 ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error 408
This is a multi-part message in MIME format. --294808483026881139F5AB7E Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Ok, I understand what the error is saying, but I can't figure why it is saying it. All my columns are set to the proper data types. I am really getting tired of getting all these errors. I am sorry for filing up the messages with all of them. Thanks in advance for the help. DB2 Error Code = -408 [IBM][CLI Driver][AS] SQL0408N An UPDATE, INSERT, or SET transition-variable value is not compatible with the data type of its target column "EST_START_DATE". SQLSTATE=42821 SQL = "update oimdbadm.assigned set est_start_date = 10/31/2000, est_comp_date = 10/31/2000, est_man_hrs = '1', act_start_date = 10/31/2000, act_comp_date = 10/31/2000, act_man_hrs = '1', staff_comments = 'Test' where id = '1006'" Data Source = "OIMDBADM" SQL = "update oimdbadm.assigned set est_start_date = 10/31/2000, est_comp_date = 10/31/2000, est_man_hrs = '1', act_start_date = 10/31/2000, act_comp_date = 10/31/2000, act_man_hrs = '1', staff_comments = 'Test' where id = '1006'" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (33:1) to (33:46) in the template file oim_edit_action.cfm UPDATE FILE (oim_edit_action.cfm) update oimdbadm.assigned set est_start_date = #form.est_start_date#, est_comp_date = #form.est_comp_date#, est_man_hrs = '#form.est_man_hrs#', act_start_date = #form.act_start_date#, act_comp_date = #form.act_comp_date#, act_man_hrs = '#form.act_man_hrs#', staff_comments = '#form.staff_comments#' where id = '#form.id#' --294808483026881139F5AB7E Content-Type: text/x-vcard; charset=us-ascii; name="jtaavon.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for James Taavon Content-Disposition: attachment; filename="jtaavon.vcf" begin:vcard n:Taavon;James tel;cell:(443) 506-2117 tel;fax:(410) 333-5203 tel;work:(410) 767-3415 x-mozilla-html:FALSE org:Department of Labor, Licensing and Regulation;Office of Information and Technology version:2.1 email;internet:[EMAIL PROTECTED] title:Senior Web Developer adr;quoted-printable:;;1100 N. Eutaw Street,=0D=0ARoom 203;Baltimore;MD;21201; fn:James Taavon end:vcard --294808483026881139F5AB7E-- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error 408
do you have any sort of visual query builder available to you, for debugging, etc.? I'd try that if you're getting a lot of errors...just to get a good example or two if nothing else. ~Simon -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 26, 2000 3:12 PM To: CF-Talk Subject: NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error 408 This is a multi-part message in MIME format. --294808483026881139F5AB7E Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Ok, I understand what the error is saying, but I can't figure why it is saying it. All my columns are set to the proper data types. I am really getting tired of getting all these errors. I am sorry for filing up the messages with all of them. Thanks in advance for the help. DB2 Error Code = -408 [IBM][CLI Driver][AS] SQL0408N An UPDATE, INSERT, or SET transition-variable value is not compatible with the data type of its target column "EST_START_DATE". SQLSTATE=42821 SQL = "update oimdbadm.assigned set est_start_date = 10/31/2000, est_comp_date = 10/31/2000, est_man_hrs = '1', act_start_date = 10/31/2000, act_comp_date = 10/31/2000, act_man_hrs = '1', staff_comments = 'Test' where id = '1006'" Data Source = "OIMDBADM" SQL = "update oimdbadm.assigned set est_start_date = 10/31/2000, est_comp_date = 10/31/2000, est_man_hrs = '1', act_start_date = 10/31/2000, act_comp_date = 10/31/2000, act_man_hrs = '1', staff_comments = 'Test' where id = '1006'" The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (33:1) to (33:46) in the template file oim_edit_action.cfm UPDATE FILE (oim_edit_action.cfm) update oimdbadm.assigned set est_start_date = #form.est_start_date#, est_comp_date = #form.est_comp_date#, est_man_hrs = '#form.est_man_hrs#', act_start_date = #form.act_start_date#, act_comp_date = #form.act_comp_date#, act_man_hrs = '#form.act_man_hrs#', staff_comments = '#form.staff_comments#' where id = '#form.id#' --294808483026881139F5AB7E Content-Type: text/x-vcard; charset=us-ascii; name="jtaavon.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for James Taavon Content-Disposition: attachment; filename="jtaavon.vcf" begin:vcard n:Taavon;James tel;cell:(443) 506-2117 tel;fax:(410) 333-5203 tel;work:(410) 767-3415 x-mozilla-html:FALSE org:Department of Labor, Licensing and Regulation;Office of Information and Technology version:2.1 email;internet:[EMAIL PROTECTED] title:Senior Web Developer adr;quoted-printable:;;1100 N. Eutaw Street,=0D=0ARoom 203;Baltimore;MD;21201; fn:James Taavon end:vcard --294808483026881139F5AB7E-- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error 408
Put ' around the value (ie the date) and see if that works Bill Wheatley Director of Development AEPS INC Allaire ColdFusion Consulting Partner http://www.aeps.com ICQ: 417645 http://www.aeps2000.com 954-472-6684 X303 - Original Message - From: "James Taavon" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, October 26, 2000 3:12 PM Subject: NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error 408 > This is a multi-part message in MIME format. > --294808483026881139F5AB7E > Content-Type: text/plain; charset=us-ascii > Content-Transfer-Encoding: 7bit > > Ok, I understand what the error is saying, but I can't figure why it is > saying it. All my columns are set to the proper data types. I am really > getting tired of getting all these errors. I am sorry for filing up the > messages with all of them. > > Thanks in advance for the help. > > DB2 Error Code = -408 > > [IBM][CLI Driver][AS] SQL0408N An UPDATE, INSERT, or SET > transition-variable value is not compatible with the data type of its > target column "EST_START_DATE". SQLSTATE=42821 > > SQL = "update oimdbadm.assigned set est_start_date = 10/31/2000, > est_comp_date = 10/31/2000, est_man_hrs = '1', act_start_date = > 10/31/2000, act_comp_date = 10/31/2000, act_man_hrs = '1', > staff_comments = 'Test' where id = '1006'" > > Data Source = "OIMDBADM" > > SQL = "update oimdbadm.assigned set est_start_date = 10/31/2000, > est_comp_date = 10/31/2000, est_man_hrs = '1', act_start_date = > 10/31/2000, act_comp_date = 10/31/2000, act_man_hrs = '1', > staff_comments = 'Test' where id = '1006'" > > The error occurred while processing an element with a general identifier > of (CFQUERY), occupying document position (33:1) to (33:46) in the > template file oim_edit_action.cfm > > UPDATE FILE (oim_edit_action.cfm) > > > update oimdbadm.assigned > set est_start_date = #form.est_start_date#, > est_comp_date = #form.est_comp_date#, > est_man_hrs = '#form.est_man_hrs#', > act_start_date = #form.act_start_date#, > act_comp_date = #form.act_comp_date#, > act_man_hrs = '#form.act_man_hrs#', > staff_comments = '#form.staff_comments#' > where id = '#form.id#' > > --294808483026881139F5AB7E > Content-Type: text/x-vcard; charset=us-ascii; > name="jtaavon.vcf" > Content-Transfer-Encoding: 7bit > Content-Description: Card for James Taavon > Content-Disposition: attachment; > filename="jtaavon.vcf" > > begin:vcard > n:Taavon;James > tel;cell:(443) 506-2117 > tel;fax:(410) 333-5203 > tel;work:(410) 767-3415 > x-mozilla-html:FALSE > org:Department of Labor, Licensing and Regulation;Office of Information and Technology > version:2.1 > email;internet:[EMAIL PROTECTED] > title:Senior Web Developer > adr;quoted-printable:;;1100 N. Eutaw Street,=0D=0ARoom 203;Baltimore;MD;21201; > fn:James Taavon > end:vcard > > --294808483026881139F5AB7E-- > > -- -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error 408
This is a multi-part message in MIME format. --4FB7B0DD43D4F9E2FDA1BF6B Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit You mean like using SQL Query Builder in Studio? The problem there is that I am unable to setup a connection with the remote database. At least I think I cant. I will try. Simon Horwith wrote: > > do you have any sort of visual query builder available to you, for > debugging, etc.? I'd try that if you're getting a lot of errors...just to > get a good example or two if nothing else. > > ~Simon > > -Original Message- > From: James Taavon [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 26, 2000 3:12 PM > To: CF-Talk > Subject: NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error > 408 > > This is a multi-part message in MIME format. > --294808483026881139F5AB7E > Content-Type: text/plain; charset=us-ascii > Content-Transfer-Encoding: 7bit > > Ok, I understand what the error is saying, but I can't figure why it is > saying it. All my columns are set to the proper data types. I am really > getting tired of getting all these errors. I am sorry for filing up the > messages with all of them. > > Thanks in advance for the help. > > DB2 Error Code = -408 > > [IBM][CLI Driver][AS] SQL0408N An UPDATE, INSERT, or SET > transition-variable value is not compatible with the data type of its > target column "EST_START_DATE". SQLSTATE=42821 > > SQL = "update oimdbadm.assigned set est_start_date = 10/31/2000, > est_comp_date = 10/31/2000, est_man_hrs = '1', act_start_date = > 10/31/2000, act_comp_date = 10/31/2000, act_man_hrs = '1', > staff_comments = 'Test' where id = '1006'" > > Data Source = "OIMDBADM" > > SQL = "update oimdbadm.assigned set est_start_date = 10/31/2000, > est_comp_date = 10/31/2000, est_man_hrs = '1', act_start_date = > 10/31/2000, act_comp_date = 10/31/2000, act_man_hrs = '1', > staff_comments = 'Test' where id = '1006'" > > The error occurred while processing an element with a general identifier > of (CFQUERY), occupying document position (33:1) to (33:46) in the > template file oim_edit_action.cfm > > UPDATE FILE (oim_edit_action.cfm) > > > update oimdbadm.assigned > set est_start_date = #form.est_start_date#, > est_comp_date = #form.est_comp_date#, > est_man_hrs = '#form.est_man_hrs#', > act_start_date = #form.act_start_date#, > act_comp_date = #form.act_comp_date#, > act_man_hrs = '#form.act_man_hrs#', > staff_comments = '#form.staff_comments#' > where id = '#form.id#' > > --294808483026881139F5AB7E > Content-Type: text/x-vcard; charset=us-ascii; > name="jtaavon.vcf" > Content-Transfer-Encoding: 7bit > Content-Description: Card for James Taavon > Content-Disposition: attachment; > filename="jtaavon.vcf" > > begin:vcard > n:Taavon;James > tel;cell:(443) 506-2117 > tel;fax:(410) 333-5203 > tel;work:(410) 767-3415 > x-mozilla-html:FALSE > org:Department of Labor, Licensing and Regulation;Office of Information and > Technology > version:2.1 > email;internet:[EMAIL PROTECTED] > title:Senior Web Developer > adr;quoted-printable:;;1100 N. Eutaw Street,=0D=0ARoom > 203;Baltimore;MD;21201; > fn:James Taavon > end:vcard > > --294808483026881139F5AB7E-- > > > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a > message with 'unsubscribe' in the body to [EMAIL PROTECTED] > > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message >with 'unsubscribe' in the body to [EMAIL PROTECTED] --4FB7B0DD43D4F9E2FDA1BF6B Content-Type: text/x-vcard; charset=us-ascii; name="jtaavon.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for James Taavon Content-Disposition: attachment; filename="jtaavon.vcf" begin:vcard n:Taavon;James tel;cell:(443) 506-2117 tel;fax:(410) 333-5203 tel;work:(410) 767-3415 x-mozilla-html:FALSE org:Department of Labor, Licensing and Regulation;Office of Information and Technology version:2.1 email;internet:[EMAIL PROTECTED] title:Senior Web Developer adr;quoted-printable:;;1100 N. Eutaw Street,=0D=0ARoom 203;Baltimore;MD;21201; fn:James Taavon end:vcard --4FB7B0DD43D4F9E2FDA1BF6B-- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error 408
what sort of data feilds are the rows you are putting this stuff into? datetime, char, varchar, etc... -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 26, 2000 2:27 PM To: CF-Talk Subject: Re: NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error 408 This is a multi-part message in MIME format. --4FB7B0DD43D4F9E2FDA1BF6B Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit You mean like using SQL Query Builder in Studio? The problem there is that I am unable to setup a connection with the remote database. At least I think I cant. I will try. Simon Horwith wrote: > > do you have any sort of visual query builder available to you, for > debugging, etc.? I'd try that if you're getting a lot of errors...just to > get a good example or two if nothing else. > > ~Simon > > -Original Message- > From: James Taavon [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 26, 2000 3:12 PM > To: CF-Talk > Subject: NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error > 408 > > This is a multi-part message in MIME format. > --294808483026881139F5AB7E > Content-Type: text/plain; charset=us-ascii > Content-Transfer-Encoding: 7bit > > Ok, I understand what the error is saying, but I can't figure why it is > saying it. All my columns are set to the proper data types. I am really > getting tired of getting all these errors. I am sorry for filing up the > messages with all of them. > > Thanks in advance for the help. > > DB2 Error Code = -408 > > [IBM][CLI Driver][AS] SQL0408N An UPDATE, INSERT, or SET > transition-variable value is not compatible with the data type of its > target column "EST_START_DATE". SQLSTATE=42821 > > SQL = "update oimdbadm.assigned set est_start_date = 10/31/2000, > est_comp_date = 10/31/2000, est_man_hrs = '1', act_start_date = > 10/31/2000, act_comp_date = 10/31/2000, act_man_hrs = '1', > staff_comments = 'Test' where id = '1006'" > > Data Source = "OIMDBADM" > > SQL = "update oimdbadm.assigned set est_start_date = 10/31/2000, > est_comp_date = 10/31/2000, est_man_hrs = '1', act_start_date = > 10/31/2000, act_comp_date = 10/31/2000, act_man_hrs = '1', > staff_comments = 'Test' where id = '1006'" > > The error occurred while processing an element with a general identifier > of (CFQUERY), occupying document position (33:1) to (33:46) in the > template file oim_edit_action.cfm > > UPDATE FILE (oim_edit_action.cfm) > > > update oimdbadm.assigned > set est_start_date = #form.est_start_date#, > est_comp_date = #form.est_comp_date#, > est_man_hrs = '#form.est_man_hrs#', > act_start_date = #form.act_start_date#, > act_comp_date = #form.act_comp_date#, > act_man_hrs = '#form.act_man_hrs#', > staff_comments = '#form.staff_comments#' > where id = '#form.id#' > > --294808483026881139F5AB7E > Content-Type: text/x-vcard; charset=us-ascii; > name="jtaavon.vcf" > Content-Transfer-Encoding: 7bit > Content-Description: Card for James Taavon > Content-Disposition: attachment; > filename="jtaavon.vcf" > > begin:vcard > n:Taavon;James > tel;cell:(443) 506-2117 > tel;fax:(410) 333-5203 > tel;work:(410) 767-3415 > x-mozilla-html:FALSE > org:Department of Labor, Licensing and Regulation;Office of Information and > Technology > version:2.1 > email;internet:[EMAIL PROTECTED] > title:Senior Web Developer > adr;quoted-printable:;;1100 N. Eutaw Street,=0D=0ARoom > 203;Baltimore;MD;21201; > fn:James Taavon > end:vcard > > --294808483026881139F5AB7E-- > > > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a > message with 'unsubscribe' in the body to [EMAIL PROTECTED] > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] --4FB7B0DD43D4F9E2FDA1BF6B Content-Type: text/x-vcard; charset=us-ascii; name="jtaavon.vcf" Content-Transfer-Encoding: 7bit Content-Descri
Re: NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error 408
This is a multi-part message in MIME format. --46E6CC4E93010FE7E8BA65C5 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit William, ok, that worked... But now I get my other error back SQL error 7008, inconsistent data? How do I know when or when not to use songle quotes? [IBM][CLI Driver][AS] SQL7008N REXX variable "ASSIGNED " contains inconsistent data. SQLSTATE=55019 SQL = "update oimdbadm.assigned set est_start_date = '10/31/2000', est_comp_date = '10/31/2000', est_man_hrs = '1', act_start_date = '10/31/2000', act_comp_date = '10/31/2000', act_man_hrs = '1', assigned = 'James Taavon', staff_comments = ' ' where id = '1006'" Data Source = "OIMDBADM" SQL = "update oimdbadm.assigned set est_start_date = '10/31/2000', est_comp_date = '10/31/2000', est_man_hrs = '1', act_start_date = '10/31/2000', act_comp_date = '10/31/2000', act_man_hrs = '1', assigned = 'James Taavon', staff_comments = ' ' where id = '1006'" William J Wheatley wrote: > > Put ' around the value (ie the date) and see if that works > > Bill Wheatley > Director of Development > AEPS INC > Allaire ColdFusion Consulting Partner > http://www.aeps.com > ICQ: 417645 > http://www.aeps2000.com > 954-472-6684 X303 > > - Original Message - > From: "James Taavon" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, October 26, 2000 3:12 PM > Subject: NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error 408 > > > This is a multi-part message in MIME format. > > --294808483026881139F5AB7E > > Content-Type: text/plain; charset=us-ascii > > Content-Transfer-Encoding: 7bit > > > > Ok, I understand what the error is saying, but I can't figure why it is > > saying it. All my columns are set to the proper data types. I am really > > getting tired of getting all these errors. I am sorry for filing up the > > messages with all of them. > > > > Thanks in advance for the help. > > > > DB2 Error Code = -408 > > > > [IBM][CLI Driver][AS] SQL0408N An UPDATE, INSERT, or SET > > transition-variable value is not compatible with the data type of its > > target column "EST_START_DATE". SQLSTATE=42821 > > > > SQL = "update oimdbadm.assigned set est_start_date = 10/31/2000, > > est_comp_date = 10/31/2000, est_man_hrs = '1', act_start_date = > > 10/31/2000, act_comp_date = 10/31/2000, act_man_hrs = '1', > > staff_comments = 'Test' where id = '1006'" > > > > Data Source = "OIMDBADM" > > > > SQL = "update oimdbadm.assigned set est_start_date = 10/31/2000, > > est_comp_date = 10/31/2000, est_man_hrs = '1', act_start_date = > > 10/31/2000, act_comp_date = 10/31/2000, act_man_hrs = '1', > > staff_comments = 'Test' where id = '1006'" > > > > The error occurred while processing an element with a general identifier > > of (CFQUERY), occupying document position (33:1) to (33:46) in the > > template file oim_edit_action.cfm > > > > UPDATE FILE (oim_edit_action.cfm) > > > > > > update oimdbadm.assigned > > set est_start_date = #form.est_start_date#, > > est_comp_date = #form.est_comp_date#, > > est_man_hrs = '#form.est_man_hrs#', > > act_start_date = #form.act_start_date#, > > act_comp_date = #form.act_comp_date#, > > act_man_hrs = '#form.act_man_hrs#', > > staff_comments = '#form.staff_comments#' > > where id = '#form.id#' > > > > --294808483026881139F5AB7E > > Content-Type: text/x-vcard; charset=us-ascii; > > name="jtaavon.vcf" > > Content-Transfer-Encoding: 7bit > > Content-Description: Card for James Taavon > > Content-Disposition: attachment; > > filename="jtaavon.vcf" > > > > begin:vcard > > n:Taavon;James > > tel;cell:(443) 506-2117 > > tel;fax:(410) 333-5203 > > tel;work:(410) 767-3415 > > x-mozilla-html:FALSE > > org:Department of Labor, Licensing and Regulation;Office of Information > and Technology > > version:2.1 > > email;internet:[EMAIL PROTECTED] > > title:Senior Web Developer > > adr;quoted-printable:;;1100 N. Eutaw Street,=0D=0ARoom > 203;Baltimore;MD;21201; > > fn:James Taavon > > end:vcard > > > > --294808483026881139F5
RE: NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error 408
yeah, something like that. any visual query builder that can access the database you are querying. I belive you said it is an AS400... do you have access to the PF, LF, or RPG files on that server (or to the people who wrote them)? PF or LF Type Files would give you a very clear description of how the AS400 database is set up (a PF File is a physical file...which basically describes the data itself, and an LF File is a logical file...it shows table joins). RPG Code might give you insite, if you can find a good example of an insert or update (an RPG File is similar to a stored procedure for purpose of this discussion). If you can access the AS400 menu, but don't know where to start, try finding subfiles and trace the file execution path from there (a subfile is just a data-display or data entry screen). Hopefully, it won't come to any of that...you can try a visual builder or talk with one of the RPG Programmers there. If nothing else, anyone reading this who knows absolutely nothing about programming and databases on the AS400, does now. ~Simon -Original Message- From: James Taavon [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 26, 2000 3:27 PM To: CF-Talk Subject: Re: NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error 408 This is a multi-part message in MIME format. --4FB7B0DD43D4F9E2FDA1BF6B Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit You mean like using SQL Query Builder in Studio? The problem there is that I am unable to setup a connection with the remote database. At least I think I cant. I will try. Simon Horwith wrote: > > do you have any sort of visual query builder available to you, for > debugging, etc.? I'd try that if you're getting a lot of errors...just to > get a good example or two if nothing else. > > ~Simon > > -Original Message- > From: James Taavon [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 26, 2000 3:12 PM > To: CF-Talk > Subject: NEED HELP!! Ignore Previously SQL Error I posted. New SQL Error > 408 > > This is a multi-part message in MIME format. > --294808483026881139F5AB7E > Content-Type: text/plain; charset=us-ascii > Content-Transfer-Encoding: 7bit > > Ok, I understand what the error is saying, but I can't figure why it is > saying it. All my columns are set to the proper data types. I am really > getting tired of getting all these errors. I am sorry for filing up the > messages with all of them. > > Thanks in advance for the help. > > DB2 Error Code = -408 > > [IBM][CLI Driver][AS] SQL0408N An UPDATE, INSERT, or SET > transition-variable value is not compatible with the data type of its > target column "EST_START_DATE". SQLSTATE=42821 > > SQL = "update oimdbadm.assigned set est_start_date = 10/31/2000, > est_comp_date = 10/31/2000, est_man_hrs = '1', act_start_date = > 10/31/2000, act_comp_date = 10/31/2000, act_man_hrs = '1', > staff_comments = 'Test' where id = '1006'" > > Data Source = "OIMDBADM" > > SQL = "update oimdbadm.assigned set est_start_date = 10/31/2000, > est_comp_date = 10/31/2000, est_man_hrs = '1', act_start_date = > 10/31/2000, act_comp_date = 10/31/2000, act_man_hrs = '1', > staff_comments = 'Test' where id = '1006'" > > The error occurred while processing an element with a general identifier > of (CFQUERY), occupying document position (33:1) to (33:46) in the > template file oim_edit_action.cfm > > UPDATE FILE (oim_edit_action.cfm) > > > update oimdbadm.assigned > set est_start_date = #form.est_start_date#, > est_comp_date = #form.est_comp_date#, > est_man_hrs = '#form.est_man_hrs#', > act_start_date = #form.act_start_date#, > act_comp_date = #form.act_comp_date#, > act_man_hrs = '#form.act_man_hrs#', > staff_comments = '#form.staff_comments#' > where id = '#form.id#' > > --294808483026881139F5AB7E > Content-Type: text/x-vcard; charset=us-ascii; > name="jtaavon.vcf" > Content-Transfer-Encoding: 7bit > Content-Description: Card for James Taavon > Content-Disposition: attachment; > filename="jtaavon.vcf" > > begin:vcard > n:Taavon;James > tel;cell:(443) 506-2117 > tel;fax:(410) 333-5203 > tel;work:(410) 767-3415 > x-mozilla-html:FALSE > org:Department of Labor, Licensing and Regulation;Office of Information and > Technology > version:2.1 > email;internet:[EMAIL PROTECTED] > title:Senior Web Developer > adr;quoted-printabl
RE: SQL error - Arithmetic overflow...?
Don't know for sure, but could it be that the fields where you're adding 27.5 are set up as integer fields? This might not have been a problem in Access where there's just a 'number' datatype, but could make a difference in sql with 'int', 'float', 'decimal' etc If you've auto-upsized your access db to SQL and all the values in an access 'number' field were integers, the upsizer would probably create a SQL field with type 'int'. HTH Alex > -Original Message- > From: Ed Gordon [mailto:[EMAIL PROTECTED]] > Sent: 03 October 2002 03:30 > To: CF-Talk > Subject: SQL error - Arithmetic overflow...? > > > This ran just fine using Microsoft Access. Now I get an overflow? > > Can you spot what's wrong? The values in the fields aren't > very big - the > largest is like 1952 to which I'm adding 20 or 30 or so... > > = > ODBC Error Code = 22003 (Numeric value out of range) > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic > overflow error > converting numeric to data type numeric. > > > SQL = "Update MEMBERS Set status='Gold', current_point_ratio=1.25, > total_purchases=total_purchases+22, > purchases_this_year=purchases_this_year+22, > purchases_quarter_1=purchases_quarter_1+22, > total_points_earned=total_points_earned+27.5, > points_earned_this_year=points_earned_this_year+27.5, > points_earned_quarter_1=points_earned_quarter_1+27.5, > point_balance=point_balance+27.5, last_tran_posted_on={d > '2002-10-02'}, > last_tran_posted_at={t '21:16:33'} Where > uuid='23A99597-9F04-11D6-AEAA00A0CC617FCD'" > > > I guess it's too much to ask that they tell me WHAT value is > out of range - > or, since I am now using a "real" SQL database instead of MS > Access, do I > have to do all the calculations outside of the Update? > > Thanks in Advance, > > Ed Gordon > > > > = > HERE'S THE CF CODE > = > > Update MEMBERS > Set status='#STATUSCHANGE#', >current_point_ratio=#RATIOCHANGE#, >total_purchases=total_purchases+#numAmount#, >purchases_this_year=purchases_this_year+#numAmount#, >purchases_quarter_1=purchases_quarter_1+#numAmount#, >total_points_earned=total_points_earned+#numPoints#, >points_earned_this_year=points_earned_this_year+#numPoints#, >points_earned_quarter_1=points_earned_quarter_1+#numPoints#, >point_balance=point_balance+#numPoints#, >last_tran_posted_on=#CREATEODBCDATE(tdazedate)#, >last_tran_posted_at=#CREATEODBCTIME(theodbctime)# > > Where uuid='#getmember.uuid#' > > > = > HELP? > > TIA > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
OT: SQL Error with Syntax
OK, I cannot see where this SYNTAX error is can anyone else see the possible problem? Below are two queries, both are the same, one is PRE-CF (variables) one is POST CF after CF processes the variables that makes it a query. > (WHERE weight <= '30') Is just what is outputted in the error, it is actually ('30') SELECT zone#getzone.service# AS ship_value FROM #variables.service_table# WHERE weight <= '#attributes.weight#' ORDER BY weight desc SELECT zone202 AS ship_value FROM 2da WHERE weight <= '30' ORDER BY weight desc Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '2'. 181 : WHERE weight <= '#attributes.weight#' 182 : ORDER BY weight desc 183 : 184 : 185 : VENDORERRORCODE 170 SQLSTATE HY000 Paul Giesenhagen QuillDesign ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 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. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Error with Syntax
I'm not sure if SQL Server has issues with having an integer as the first character of a table name, but I suspect that is where the problem is. It's generally a good idea to not start any variable, table or column names with an integer, lest you run into weird issues. I would try wrapping the table name in brackets such as [2da] and see if that works. -Justin Scott - Original Message - From: "Paul Giesenhagen" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, March 10, 2003 9:58 AM Subject: OT: SQL Error with Syntax > OK, I cannot see where this SYNTAX error is can anyone else see the possible problem? Below are two queries, both are the same, one is PRE-CF (variables) one is POST CF after CF processes the variables that makes it a query. > > > (WHERE weight <= '30') Is just what is outputted in the error, it is actually ('30') > > > > SELECT zone#getzone.service# AS ship_value > FROM #variables.service_table# > WHERE weight <= '#attributes.weight#' > ORDER BY weight desc > > > SELECT zone202 AS ship_value > FROM 2da > WHERE weight <= '30' > ORDER BY weight desc > > > Error Executing Database Query. > [Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '2'. > > 181 : WHERE weight <= '#attributes.weight#' > 182 : ORDER BY weight desc > 183 : > 184 : > 185 : > > VENDORERRORCODE 170 > SQLSTATE HY000 > > Paul Giesenhagen > QuillDesign > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Error, I know
Your database is full. Allocate more space for it in Enterprise Manager. Dan Phillips CFXHosting.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 19, 2002 10:42 AM To: CF-Talk Subject: SQL Error, I know I know this is suppose to be for CF stuff, but I know someone can help with this. I'm under the gun here. I'm getting this SQL 2000 error message when I try to add a new column to an existing table that's filled with data. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object 'Tmp_samp_request' in database 'hititte4_db' because the 'PRIMARY' filegroup is full. Thank you in advance. Please help. D- __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL Error, I know
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL > Server]Could not allocate space for > object 'Tmp_samp_request' in database 'hititte4_db' > because the 'PRIMARY' filegroup is full. is that db set to grow? if so, your disk is probably filled. if not, allow it to grow... __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Error, I know
nice one - seen this before. It aint your disk that is full it's your filegroup (SQL groups data/objects into filegroups). By any chance are you running SQL on SBS (Small business Server)? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 19 March 2002 15:48 To: CF-Talk Subject: Re: SQL Error, I know > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL > Server]Could not allocate space for > object 'Tmp_samp_request' in database 'hititte4_db' > because the 'PRIMARY' filegroup is full. is that db set to grow? if so, your disk is probably filled. if not, allow it to grow... __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Error, I know
Not quite sure. Our ISP is hosting our CF Web site along with SQL 2000. The ISP's Adminsitrator is trying to tell me it's a Professional Services request and wants to charge me $200/hour to fix the problem. D- > nice one - seen this before. It aint your disk that is full it's your > filegroup (SQL groups data/objects into filegroups). By any chance are you > running SQL on SBS (Small business Server)? > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: 19 March 2002 15:48 > To: CF-Talk > Subject: Re: SQL Error, I know > > > > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL > > Server]Could not allocate space for > > object 'Tmp_samp_request' in database 'hititte4_db' > > because the 'PRIMARY' filegroup is full. > > is that db set to grow? if so, your disk is probably filled. if > not, allow it to grow... > > > __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Error, I know
If you are running SQL on SBS there is a maximum size to your filegroups (which you can easily reach). It sucks believe me -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 19 March 2002 16:02 To: CF-Talk Subject: RE: SQL Error, I know Not quite sure. Our ISP is hosting our CF Web site along with SQL 2000. The ISP's Adminsitrator is trying to tell me it's a Professional Services request and wants to charge me $200/hour to fix the problem. D- > nice one - seen this before. It aint your disk that is full it's your > filegroup (SQL groups data/objects into filegroups). By any chance are you > running SQL on SBS (Small business Server)? > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: 19 March 2002 15:48 > To: CF-Talk > Subject: Re: SQL Error, I know > > > > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL > > Server]Could not allocate space for > > object 'Tmp_samp_request' in database 'hititte4_db' > > because the 'PRIMARY' filegroup is full. > > is that db set to grow? if so, your disk is probably filled. if > not, allow it to grow... > > > __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Error, I know
Is there any solution? > If you are running SQL on SBS there is a maximum size to your filegroups > (which you can easily reach). It sucks believe me > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: 19 March 2002 16:02 > To: CF-Talk > Subject: RE: SQL Error, I know > > > Not quite sure. Our ISP is hosting our CF Web site > along with SQL 2000. The ISP's Adminsitrator is trying > to tell me it's a Professional Services request and > wants to charge me $200/hour to fix the problem. > > D- > > nice one - seen this before. It aint your disk that is full it's your > > filegroup (SQL groups data/objects into filegroups). By any chance are > you > > running SQL on SBS (Small business Server)? > > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Sent: 19 March 2002 15:48 > > To: CF-Talk > > Subject: Re: SQL Error, I know > > > > > > > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL > > > Server]Could not allocate space for > > > object 'Tmp_samp_request' in database 'hititte4_db' > > > because the 'PRIMARY' filegroup is full. > > > > is that db set to grow? if so, your disk is probably filled. if > > not, allow it to grow... > > > > > > > > __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: SQL Error, I know
Ouch!! I would first find out how much space you are suposed to have for your database, then right click on your database (in enterprise manager) and view properties (it will tell you how much space is being used). If it is full (which is probably is).. and the numbers are correct (ie you are using as much as they said that they would give you). Email the administrator and ask him for more space .. if they want to charge you $200.00 to do that ... look for a new provider ..!! Best of Luck! Paul Giesenhagen QuillDesign http://www.quilldesign.com SiteDirector v2.0 - Commerce Builder - Original Message - From: <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, March 19, 2002 10:01 AM Subject: RE: SQL Error, I know > Not quite sure. Our ISP is hosting our CF Web site > along with SQL 2000. The ISP's Adminsitrator is trying > to tell me it's a Professional Services request and > wants to charge me $200/hour to fix the problem. > > D- > > nice one - seen this before. It aint your disk that is full it's your > > filegroup (SQL groups data/objects into filegroups). By any chance are you > > running SQL on SBS (Small business Server)? > > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Sent: 19 March 2002 15:48 > > To: CF-Talk > > Subject: Re: SQL Error, I know > > > > > > > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL > > > Server]Could not allocate space for > > > object 'Tmp_samp_request' in database 'hititte4_db' > > > because the 'PRIMARY' filegroup is full. > > > > is that db set to grow? if so, your disk is probably filled. if > > not, allow it to grow... > > > > > > > __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Error, I know
How's this for customer relations ... e-mail from hosting company's DBA ... Your database is not configured to grow automatically. You pay for a set size monthly and that is all that is available. You can request more disk space if you need it, but that's up to you. This is definitely NOT a support issue. The server your database is on and functioning normally and all is working fine. That is our concern from a support side. The problem with inserting columns falls strictly under Professional Services. Like I said, I can fix the problem and ensure that this works, but it is up to you if you want me to go ahead and do this. Please let me know. > If you are running SQL on SBS there is a maximum size to your filegroups > (which you can easily reach). It sucks believe me > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: 19 March 2002 16:02 > To: CF-Talk > Subject: RE: SQL Error, I know > > > Not quite sure. Our ISP is hosting our CF Web site > along with SQL 2000. The ISP's Adminsitrator is trying > to tell me it's a Professional Services request and > wants to charge me $200/hour to fix the problem. > > D- > > nice one - seen this before. It aint your disk that is full it's your > > filegroup (SQL groups data/objects into filegroups). By any chance are > you > > running SQL on SBS (Small business Server)? > > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Sent: 19 March 2002 15:48 > > To: CF-Talk > > Subject: Re: SQL Error, I know > > > > > > > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL > > > Server]Could not allocate space for > > > object 'Tmp_samp_request' in database 'hititte4_db' > > > because the 'PRIMARY' filegroup is full. > > > > is that db set to grow? if so, your disk is probably filled. if > > not, allow it to grow... > > > > > > > > __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Error, I know
You would have to have admin rights to the SQL server to increase the space. Have your ISP check to see how much free space you have left on the SQL server. Obviously if it's under a couple Megs or has no space left, more space needs to be added. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 19, 2002 11:06 AM To: CF-Talk Subject: RE: SQL Error, I know Is there any solution? > If you are running SQL on SBS there is a maximum size to your filegroups > (which you can easily reach). It sucks believe me > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: 19 March 2002 16:02 > To: CF-Talk > Subject: RE: SQL Error, I know > > > Not quite sure. Our ISP is hosting our CF Web site > along with SQL 2000. The ISP's Adminsitrator is trying > to tell me it's a Professional Services request and > wants to charge me $200/hour to fix the problem. > > D- > > nice one - seen this before. It aint your disk that is full it's your > > filegroup (SQL groups data/objects into filegroups). By any chance are > you > > running SQL on SBS (Small business Server)? > > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Sent: 19 March 2002 15:48 > > To: CF-Talk > > Subject: Re: SQL Error, I know > > > > > > > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL > > > Server]Could not allocate space for > > > object 'Tmp_samp_request' in database 'hititte4_db' > > > because the 'PRIMARY' filegroup is full. > > > > is that db set to grow? if so, your disk is probably filled. if > > not, allow it to grow... > > > > > > > > __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Error, I know
Thanks everyone! You guys are the best! D- > You would have to have admin rights to the SQL server to increase the space. > Have your ISP check to see how much free space you have left on the SQL > server. Obviously if it's under a couple Megs or has no space left, more > space needs to be added. > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, March 19, 2002 11:06 AM > To: CF-Talk > Subject: RE: SQL Error, I know > > > Is there any solution? > > If you are running SQL on SBS there is a maximum size to your filegroups > > (which you can easily reach). It sucks believe me > > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Sent: 19 March 2002 16:02 > > To: CF-Talk > > Subject: RE: SQL Error, I know > > > > > > Not quite sure. Our ISP is hosting our CF Web site > > along with SQL 2000. The ISP's Adminsitrator is trying > > to tell me it's a Professional Services request and > > wants to charge me $200/hour to fix the problem. > > > > D- > > > nice one - seen this before. It aint your disk that is full it's your > > > filegroup (SQL groups data/objects into filegroups). By any chance are > > you > > > running SQL on SBS (Small business Server)? > > > > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > > Sent: 19 March 2002 15:48 > > > To: CF-Talk > > > Subject: Re: SQL Error, I know > > > > > > > > > > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL > > > > Server]Could not allocate space for > > > > object 'Tmp_samp_request' in database 'hititte4_db' > > > > because the 'PRIMARY' filegroup is full. > > > > > > is that db set to grow? if so, your disk is probably filled. if > > > not, allow it to grow... > > > > > > > > > > > > > > > __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Error, I know
They also may be including your log files in the space that you use - If they are not backing up properly you might find that your log files are massive and that providing the segments in the logfiles are ordered properly backin g them up should free up a tonne of space -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 19 March 2002 16:16 To: CF-Talk Subject: RE: SQL Error, I know Thanks everyone! You guys are the best! D- > You would have to have admin rights to the SQL server to increase the space. > Have your ISP check to see how much free space you have left on the SQL > server. Obviously if it's under a couple Megs or has no space left, more > space needs to be added. > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, March 19, 2002 11:06 AM > To: CF-Talk > Subject: RE: SQL Error, I know > > > Is there any solution? > > If you are running SQL on SBS there is a maximum size to your filegroups > > (which you can easily reach). It sucks believe me > > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Sent: 19 March 2002 16:02 > > To: CF-Talk > > Subject: RE: SQL Error, I know > > > > > > Not quite sure. Our ISP is hosting our CF Web site > > along with SQL 2000. The ISP's Adminsitrator is trying > > to tell me it's a Professional Services request and > > wants to charge me $200/hour to fix the problem. > > > > D- > > > nice one - seen this before. It aint your disk that is full it's your > > > filegroup (SQL groups data/objects into filegroups). By any chance are > > you > > > running SQL on SBS (Small business Server)? > > > > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > > Sent: 19 March 2002 15:48 > > > To: CF-Talk > > > Subject: Re: SQL Error, I know > > > > > > > > > > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL > > > > Server]Could not allocate space for > > > > object 'Tmp_samp_request' in database 'hititte4_db' > > > > because the 'PRIMARY' filegroup is full. > > > > > > is that db set to grow? if so, your disk is probably filled. if > > > not, allow it to grow... > > > > > > > > > > > > > > > __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
What is this SQL Error?
First time I've seen this one...what does it mean? Diagnostics: ODBC Error Code = 40001 (Serialization failure) [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 60) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction. __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: strange SQL error code
What version ODBC driver are you using? http://support.microsoft.com/support/kb/articles/q234/9/87.asp INFO: SQL Server Driver Version 3.70 May Return Fractional Truncation Message When Processing DateTime Parameter Joe Hoffman mailto:[EMAIL PROTECTED] National Institutes of Health Center for Information Technology Division of Computer System Services -Original Message- From: Warrick, Mark [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 07, 2000 2:12 PM To: CF-Talk Subject: strange SQL error code Anyone ever seen this one? ODBC Error Code = 01004 (Data truncated) [Microsoft][ODBC SQL Server Driver]Fractional truncation On that note, does anyone have a list of Microsoft SQL Error Codes? Please copy me with your reply. Thanks. -- Mark Warrick Phone: (714) 547-5386 Efax.com Fax: (801) 730-7289 Personal Email: [EMAIL PROTECTED] Personal URL: http://www.warrick.net Business Email: [EMAIL PROTECTED] Business URL: http://www.fusioneers.com ICQ: 346566 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: strange SQL error code
>On that note, does anyone have a list of Microsoft SQL Error Codes? They are listed in the online books online-reference. If you like I'll post you a text file offlist. Regards, Anthony Geoghegan. Lead Developer, What's On Where (WOW!) http://www.wow.ie mailto:[EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
SQL error (minus the mess)
That was a really bad start - please accept my apologies for the mess. Let's try again. The page I have done up is a very simple test page with code: SELECT * FROM input I am using a Access 97 database, if that helps. Remote, I am getting this error: SQL operation unauthorized. The SQL operation SELECT is unauthorized for the data source 'HCint' I'm sorry if this seems a trivial question, but I'm very green but keen to learn Cold Fusion - and this has stumped me for 4 days. Any help would be greatly appreciated. Cheers Kylie Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Invalid SQL error - need Help
I am running CF 5 and accessing an Oracle 7 database using Oracle SQL When I run this Query in a Query: select stvcnty_desc county,count(*) fcount from STUDENTS group by stvcnty_desc ORDER BY county I get this error: "Query Manipulation Error Code = 0 Invalid SQL" If I comment out this query then the rest of the code runs fine What is my problem ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
OT: SQL error code 08S01
Has anyone ever had this problem: ODBC Error Code = 08S01 (Communication link failure) We started getting this error message at the bottom of all our pages out of nowhere, and I can't figure out why or how to get rid of it. I'm using CF 4 to connect to Microsoft SQL 7. jim __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
funny SQL error - running Postgres
after an insert... the inserted record can not be found... it's there, I can see it but this query returns with zero records found. The Details: First: INSERT INTO DocsList (DocsList_ID,filename) VALUES (746,'H:\UpLoadImages\CmosPic.jpg') then sometime later we Check4Duplicate with: SELECT * FROM DocsList WHERE FileName = 'H:\UpLoadImages\CmosPic.jpg' This Retuns ZERO Records found. but I can see the record plain as day in the database and in reports etc... when I do it with simpler things like 'CmosPic.jpg' there are no problems ??? any clues will be very appreciated! Gonz ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184923 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: SQL Error with Syntax
If the weight field is number then it should be weight = 30 and not weight = '30' >>> [EMAIL PROTECTED] 03/10/03 09:58AM >>> OK, I cannot see where this SYNTAX error is can anyone else see the possible problem? Below are two queries, both are the same, one is PRE-CF (variables) one is POST CF after CF processes the variables that makes it a query. > (WHERE weight <= '30') Is just what is outputted in the error, it is actually ('30') SELECT zone#getzone.service# AS ship_value FROM #variables.service_table# WHERE weight <= '#attributes.weight#' ORDER BY weight desc SELECT zone202 AS ship_value FROM 2da WHERE weight <= '30' ORDER BY weight desc Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '2'. 181 : WHERE weight <= '#attributes.weight#' 182 : ORDER BY weight desc 183 : 184 : 185 : VENDORERRORCODE 170 SQLSTATE HY000 Paul Giesenhagen QuillDesign ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: OT: SQL Error with Syntax
Quoting integers in SQL Server is valid, and can help protect against some hack attempts. -Justin Scott - Original Message - From: "Randell B Adkins" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, March 10, 2003 10:00 AM Subject: Re: OT: SQL Error with Syntax > If the weight field is number then it should be weight = 30 and not > weight = '30' > > > >>> [EMAIL PROTECTED] 03/10/03 09:58AM >>> > OK, I cannot see where this SYNTAX error is can anyone else see the > possible problem? Below are two queries, both are the same, one is > PRE-CF (variables) one is POST CF after CF processes the variables that > makes it a query. > > > (WHERE weight <= '30') Is just what is outputted in the > error, it is actually ('30') > > > > SELECT zone#getzone.service# AS ship_value > FROM #variables.service_table# > WHERE weight <= '#attributes.weight#' > ORDER BY weight desc > > > SELECT zone202 AS ship_value > FROM 2da > WHERE weight <= '30' > ORDER BY weight desc > > > Error Executing Database Query. > [Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax > near '2'. > > 181 : WHERE weight <= '#attributes.weight#' > 182 : ORDER BY weight desc > 183 : > 184 : > 185 : > > VENDORERRORCODE 170 > SQLSTATE HY000 > > Paul Giesenhagen > QuillDesign > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 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 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SOT: SQL error returned to coldfusion
I think I must be the king of strange errors and events, but I do have another one to ask about. Today, all of our applications on one server which all have been performing for over a year with client variables in a SQL database started showing up with the following error. An error occurred while evaluating the expression: request.urltoken=client.urltoken Error near line 12, column 7. Error resolving parameter CLIENT.URLTOKEN The client variable URLTOKEN does not exist. The cause of this error is very likely one of the following things:The name of the client variable has been misspelled.The client variable has not yet been created or has timed out. The code for that has not been changed in over six months and this is affecting client variables across all sites, so that leads me to believe that there is an ODBC problem that is not creating the client variable. A little further into the application log, I come across the following error: [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context It seems that the SSPI error only occurs on templates with a cflocation in it. Otherwise, the first error was occurring. Of course, rebooting the server quick fixed the problem, but I am trying to find more information about the SSPI error. As far as I can determine it has to do with authentication to the SQL server, but I'm not sure. The only other event that occurred prior to these bout of errors was a couple of searches that failed open the verity collections. We are definitely having a problem with the verity collections getting corrupted. I'm trying to determine if one issue lead to the next or if there is a bigger issue with connectivity to the SQL server (which has been fine and unchanged for over a year). Config: W2K, IIS, CF 5 Pro - W2K, SQL 7.0 Anyone else experience a similar issue? Thanks, John ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: What is this SQL Error?
We've run into this from time to time, it occurs when two or more users thry to access the data at exactly the same time even though SQL is suppose to handle it. Just refreshing it seems to clear up the deadlock. I haven't heard any other solutions for resolving the problem though. Let me know if you do. *** Bob Imperial Software Development Team Schoollink, Inc. 910-223-2116 ext. 108 *** "Linking Schools to The World .. One Classroom at a Time" -Original Message- From: Houk, Gary [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 16, 2002 3:06 PM To: CF-Talk Subject: What is this SQL Error? First time I've seen this one...what does it mean? Diagnostics: ODBC Error Code = 40001 (Serialization failure) [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 60) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction. __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
Thanks Bob...I'll let you know if I find a workaround. - Gary -Original Message- From: Bob Imperial [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 16, 2002 4:11 PM To: CF-Talk Subject: RE: What is this SQL Error? We've run into this from time to time, it occurs when two or more users thry to access the data at exactly the same time even though SQL is suppose to handle it. Just refreshing it seems to clear up the deadlock. I haven't heard any other solutions for resolving the problem though. Let me know if you do. *** Bob Imperial Software Development Team Schoollink, Inc. 910-223-2116 ext. 108 *** "Linking Schools to The World .. One Classroom at a Time" -Original Message- From: Houk, Gary [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 16, 2002 3:06 PM To: CF-Talk Subject: What is this SQL Error? First time I've seen this one...what does it mean? Diagnostics: ODBC Error Code = 40001 (Serialization failure) [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 60) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction. __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
are you sure you are using transaction statements. Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -Original Message- From: Bob Imperial [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 16, 2002 4:11 PM To: CF-Talk Subject: RE: What is this SQL Error? We've run into this from time to time, it occurs when two or more users thry to access the data at exactly the same time even though SQL is suppose to handle it. Just refreshing it seems to clear up the deadlock. I haven't heard any other solutions for resolving the problem though. Let me know if you do. *** Bob Imperial Software Development Team Schoollink, Inc. 910-223-2116 ext. 108 *** "Linking Schools to The World .. One Classroom at a Time" -Original Message- From: Houk, Gary [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 16, 2002 3:06 PM To: CF-Talk Subject: What is this SQL Error? First time I've seen this one...what does it mean? Diagnostics: ODBC Error Code = 40001 (Serialization failure) [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 60) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction. __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
I'm trying to figure out if ColdFusion locks the whole table when running a cfquery. I'm getting those deadlock errors in production, where too many people access the same table at the same time. It never happens in the development environment or in the testing environment. But the thing is, I get deadlocks with insert queries! Something simple as "insert into sometable (col1, col2, col3) values ('val1','val2','val3')" This tells me that the lock is not done on a row level, but on the table level. I'm using MSSQL 7, and the DBA assures me that it would lock at row level, so I'm guessing ColdFusion is requesting a lock at table level somewhere. Is there a CFAdmin setting for this? -Original Message- From: Houk, Gary [<mailto:[EMAIL PROTECTED]>] Sent: Tuesday, April 16, 2002 3:06 PM To: CF-Talk Subject: What is this SQL Error? First time I've seen this one...what does it mean? Diagnostics: ODBC Error Code = 40001 (Serialization failure) [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 60) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction. __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
If you're getting a deadlock error, chances are that it has to do with the order that your queries are going. If you have one page with a transaction that starts with an insert to Table A and then updates all the rows in Table B while you have another page with a transaction that inserts into Table B and then updates all the rows in Table A you can have a deadlock. Example: Page 1 BEGIN TRANSACTION INSERT INTO TableA (field1,field2) VALUES ('value1', 'value2') UPDATE TableB SET field1 = 5 COMMIT TRANSACTION Page 2 BEGIN TRANSACTION INSERT INTO TableB (field1,field2) VALUES ('value1', 'value2') UPDATE TableA SET field1 = 5 COMMIT TRANSACTION If both pages run at the same time, page 1 will lock TableA and page 2 will lock TableB. Neither can let go of the lock until the transaction is complete, but they can't get to other table they need until the other page releases its lock. The idea is to put all your tables into the same order so if page 1 works on TableA and then TableB, then page 2 should work on TableA and then TableB as well -- not in reverse order. Does that help? Ben Johnson Hostworks, Inc. __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
> I'm trying to figure out if ColdFusion locks the whole table > when running a cfquery. This is entirely dependent on the database. CF is just a regular database client, like any other database client. > I'm getting those deadlock errors in production, where too > many people access the same table at the same time. It never > happens in the development environment or in the testing > environment. This makes sense, since you're less likely to have collisions with fewer users. > But the thing is, I get deadlocks with insert queries! > Something simple as "insert into sometable (col1, col2, > col3) values ('val1','val2','val3')" This tells me that the > lock is not done on a row level, but on the table level. > I'm using MSSQL 7, and the DBA assures me that it would > lock at row level, so I'm guessing ColdFusion is requesting > a lock at table level somewhere. Is there a CFAdmin setting > for this? No, as mentioned above, this isn't specific to CF; there's no CF Admin setting for this. However, I wouldn't be so sure that locking is being done on a row level. If I recall correctly, that's not the default behavior for SQL Server 7. I think that SQL Server 7 locks pages (an "internal" storage unit) rather than locking individual rows or locking the whole table. Now, here's a problem. You may have several rows within a single page. Also, if you're using an identity column as a primary key, and you've defined a clustered index on that column, each insert will be physically located right after the previous insert. So, if you have one page with one row being inserted, and that page gets locked, and the database wants to write the next row to the same page due to the clustered index (which by definition matches the physical sort order of the column) that second write may be delayed, and if the database is busy enough, that may cause problems. I've heard that problem called a "hotspot" before. For a solution to this problem, I'm not sure what route would be best to take, in your situation. However, I'm not certain that this, by itself, is the problem you're having; typically, I think that this problem simply causes things to get significantly slower. A deadlock, on the other hand, implies that you have two transactions occurring, and neither can complete until the other has finished. So, the question for you is, are you doing anything else besides the insert here? Are you using CFTRANSACTION or transactional logic within your query/stored procedure also? On the other hand, for all I know, if the hotspot issue gets bad enough, maybe that will return a deadlock error! Your DBA should be able to help determine what's going on at the database level, I hope. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
Thanks, I'll discuss that with our DBA. I'm not using any CFTRANSACTION, though. What might have an impact here is that this is a code that loops up to four times and does an insert every time. The user selects up to four items, and then the code loops through them and inserts a record in the table in each iteration. It's simple as insert into someTable (col1,col2) values (#thisUser#,#thisSelectedItem#) -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:19 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: What is this SQL Error? > I'm trying to figure out if ColdFusion locks the whole table > when running a cfquery. This is entirely dependent on the database. CF is just a regular database client, like any other database client. > I'm getting those deadlock errors in production, where too > many people access the same table at the same time. It never > happens in the development environment or in the testing > environment. This makes sense, since you're less likely to have collisions with fewer users. > But the thing is, I get deadlocks with insert queries! > Something simple as "insert into sometable (col1, col2, > col3) values ('val1','val2','val3')" This tells me that the > lock is not done on a row level, but on the table level. > I'm using MSSQL 7, and the DBA assures me that it would > lock at row level, so I'm guessing ColdFusion is requesting > a lock at table level somewhere. Is there a CFAdmin setting > for this? No, as mentioned above, this isn't specific to CF; there's no CF Admin setting for this. However, I wouldn't be so sure that locking is being done on a row level. If I recall correctly, that's not the default behavior for SQL Server 7. I think that SQL Server 7 locks pages (an "internal" storage unit) rather than locking individual rows or locking the whole table. Now, here's a problem. You may have several rows within a single page. Also, if you're using an identity column as a primary key, and you've defined a clustered index on that column, each insert will be physically located right after the previous insert. So, if you have one page with one row being inserted, and that page gets locked, and the database wants to write the next row to the same page due to the clustered index (which by definition matches the physical sort order of the column) that second write may be delayed, and if the database is busy enough, that may cause problems. I've heard that problem called a "hotspot" before. For a solution to this problem, I'm not sure what route would be best to take, in your situation. However, I'm not certain that this, by itself, is the problem you're having; typically, I think that this problem simply causes things to get significantly slower. A deadlock, on the other hand, implies that you have two transactions occurring, and neither can complete until the other has finished. So, the question for you is, are you doing anything else besides the insert here? Are you using CFTRANSACTION or transactional logic within your query/stored procedure also? On the other hand, for all I know, if the hotspot issue gets bad enough, maybe that will return a deadlock error! Your DBA should be able to help determine what's going on at the database level, I hope. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
The DBA wants me to call a stored procedure, "wait" until the stored procedure is done, then continue with the loop to call it again... Is there a way to make sure the store procedure is "done" before the code continues? -Original Message- From: Hoag, Claudia (LNG) [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:27 PM To: CF-Talk Subject: RE: What is this SQL Error? Thanks, I'll discuss that with our DBA. I'm not using any CFTRANSACTION, though. What might have an impact here is that this is a code that loops up to four times and does an insert every time. The user selects up to four items, and then the code loops through them and inserts a record in the table in each iteration. It's simple as insert into someTable (col1,col2) values (#thisUser#,#thisSelectedItem#) -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:19 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: What is this SQL Error? > I'm trying to figure out if ColdFusion locks the whole table > when running a cfquery. This is entirely dependent on the database. CF is just a regular database client, like any other database client. > I'm getting those deadlock errors in production, where too > many people access the same table at the same time. It never > happens in the development environment or in the testing > environment. This makes sense, since you're less likely to have collisions with fewer users. > But the thing is, I get deadlocks with insert queries! > Something simple as "insert into sometable (col1, col2, > col3) values ('val1','val2','val3')" This tells me that the > lock is not done on a row level, but on the table level. > I'm using MSSQL 7, and the DBA assures me that it would > lock at row level, so I'm guessing ColdFusion is requesting > a lock at table level somewhere. Is there a CFAdmin setting > for this? No, as mentioned above, this isn't specific to CF; there's no CF Admin setting for this. However, I wouldn't be so sure that locking is being done on a row level. If I recall correctly, that's not the default behavior for SQL Server 7. I think that SQL Server 7 locks pages (an "internal" storage unit) rather than locking individual rows or locking the whole table. Now, here's a problem. You may have several rows within a single page. Also, if you're using an identity column as a primary key, and you've defined a clustered index on that column, each insert will be physically located right after the previous insert. So, if you have one page with one row being inserted, and that page gets locked, and the database wants to write the next row to the same page due to the clustered index (which by definition matches the physical sort order of the column) that second write may be delayed, and if the database is busy enough, that may cause problems. I've heard that problem called a "hotspot" before. For a solution to this problem, I'm not sure what route would be best to take, in your situation. However, I'm not certain that this, by itself, is the problem you're having; typically, I think that this problem simply causes things to get significantly slower. A deadlock, on the other hand, implies that you have two transactions occurring, and neither can complete until the other has finished. So, the question for you is, are you doing anything else besides the insert here? Are you using CFTRANSACTION or transactional logic within your query/stored procedure also? On the other hand, for all I know, if the hotspot issue gets bad enough, maybe that will return a deadlock error! Your DBA should be able to help determine what's going on at the database level, I hope. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
> I'm not using any CFTRANSACTION, though. What might have > an impact here is that this is a code that loops up to four > times and does an insert every time. The user selects up > to four items, and then the code loops through them and > inserts a record in the table in each iteration. Well, then, see if you're using a clustered index on your identity column, and find out from your DBA what your lock level really is for inserts, and as a stopgap preventative measure, you may want to do the following: 1. Use CFTRANSACTION around your insert block, if for no other reason than if the thing fails, you won't have some of the records added but not others. Alternatively, keep track of which records have been entered successfully on each loop iteration; this will be a tiny bit of extra coding, but won't have the overhead of CFTRANSACTION if you really don't need it otherwise. 2. Use CFTRY/CFCATCH to catch your deadlock errors, and retry the operation in the CFCATCH block. You may even want to nest another CFTRY within there, so that if the second attempt fails, you can provide an alternative, like prompting the user to try again. Then, it's time to actually fix the problem (note that the above measures are just stopgaps). You may need a faster, or more powerful, database server, you may need to improve the speed of your database connection, you may need to port this logic to stored procedures (along with the rest of your db logic), and so on. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
I guess you could wait for a return code from the SP and test for it in your code. Used to do this all the time with VB but never done it with CF -Original Message- From: Hoag, Claudia (LNG) [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 17:37 To: CF-Talk Subject: RE: What is this SQL Error? The DBA wants me to call a stored procedure, "wait" until the stored procedure is done, then continue with the loop to call it again... Is there a way to make sure the store procedure is "done" before the code continues? -Original Message- From: Hoag, Claudia (LNG) [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:27 PM To: CF-Talk Subject: RE: What is this SQL Error? Thanks, I'll discuss that with our DBA. I'm not using any CFTRANSACTION, though. What might have an impact here is that this is a code that loops up to four times and does an insert every time. The user selects up to four items, and then the code loops through them and inserts a record in the table in each iteration. It's simple as insert into someTable (col1,col2) values (#thisUser#,#thisSelectedItem#) -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:19 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: What is this SQL Error? > I'm trying to figure out if ColdFusion locks the whole table > when running a cfquery. This is entirely dependent on the database. CF is just a regular database client, like any other database client. > I'm getting those deadlock errors in production, where too > many people access the same table at the same time. It never > happens in the development environment or in the testing > environment. This makes sense, since you're less likely to have collisions with fewer users. > But the thing is, I get deadlocks with insert queries! > Something simple as "insert into sometable (col1, col2, > col3) values ('val1','val2','val3')" This tells me that the > lock is not done on a row level, but on the table level. > I'm using MSSQL 7, and the DBA assures me that it would > lock at row level, so I'm guessing ColdFusion is requesting > a lock at table level somewhere. Is there a CFAdmin setting > for this? No, as mentioned above, this isn't specific to CF; there's no CF Admin setting for this. However, I wouldn't be so sure that locking is being done on a row level. If I recall correctly, that's not the default behavior for SQL Server 7. I think that SQL Server 7 locks pages (an "internal" storage unit) rather than locking individual rows or locking the whole table. Now, here's a problem. You may have several rows within a single page. Also, if you're using an identity column as a primary key, and you've defined a clustered index on that column, each insert will be physically located right after the previous insert. So, if you have one page with one row being inserted, and that page gets locked, and the database wants to write the next row to the same page due to the clustered index (which by definition matches the physical sort order of the column) that second write may be delayed, and if the database is busy enough, that may cause problems. I've heard that problem called a "hotspot" before. For a solution to this problem, I'm not sure what route would be best to take, in your situation. However, I'm not certain that this, by itself, is the problem you're having; typically, I think that this problem simply causes things to get significantly slower. A deadlock, on the other hand, implies that you have two transactions occurring, and neither can complete until the other has finished. So, the question for you is, are you doing anything else besides the insert here? Are you using CFTRANSACTION or transactional logic within your query/stored procedure also? On the other hand, for all I know, if the hotspot issue gets bad enough, maybe that will return a deadlock error! Your DBA should be able to help determine what's going on at the database level, I hope. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
> The DBA wants me to call a stored procedure, "wait" until the > stored procedure is done, then continue with the loop to call > it again... Is there a way to make sure the store procedure > is "done" before the code continues? I'm pretty sure that CF will handle this for you; CF won't call the next iteration of the loop until the first one has completed. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
:) yes, I guess I can do that! Thanks -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:40 PM To: CF-Talk Subject: RE: What is this SQL Error? I guess you could wait for a return code from the SP and test for it in your code. Used to do this all the time with VB but never done it with CF -Original Message- From: Hoag, Claudia (LNG) [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 17:37 To: CF-Talk Subject: RE: What is this SQL Error? The DBA wants me to call a stored procedure, "wait" until the stored procedure is done, then continue with the loop to call it again... Is there a way to make sure the store procedure is "done" before the code continues? -Original Message- From: Hoag, Claudia (LNG) [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:27 PM To: CF-Talk Subject: RE: What is this SQL Error? Thanks, I'll discuss that with our DBA. I'm not using any CFTRANSACTION, though. What might have an impact here is that this is a code that loops up to four times and does an insert every time. The user selects up to four items, and then the code loops through them and inserts a record in the table in each iteration. It's simple as insert into someTable (col1,col2) values (#thisUser#,#thisSelectedItem#) -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:19 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: What is this SQL Error? > I'm trying to figure out if ColdFusion locks the whole table > when running a cfquery. This is entirely dependent on the database. CF is just a regular database client, like any other database client. > I'm getting those deadlock errors in production, where too > many people access the same table at the same time. It never > happens in the development environment or in the testing > environment. This makes sense, since you're less likely to have collisions with fewer users. > But the thing is, I get deadlocks with insert queries! > Something simple as "insert into sometable (col1, col2, > col3) values ('val1','val2','val3')" This tells me that the > lock is not done on a row level, but on the table level. > I'm using MSSQL 7, and the DBA assures me that it would > lock at row level, so I'm guessing ColdFusion is requesting > a lock at table level somewhere. Is there a CFAdmin setting > for this? No, as mentioned above, this isn't specific to CF; there's no CF Admin setting for this. However, I wouldn't be so sure that locking is being done on a row level. If I recall correctly, that's not the default behavior for SQL Server 7. I think that SQL Server 7 locks pages (an "internal" storage unit) rather than locking individual rows or locking the whole table. Now, here's a problem. You may have several rows within a single page. Also, if you're using an identity column as a primary key, and you've defined a clustered index on that column, each insert will be physically located right after the previous insert. So, if you have one page with one row being inserted, and that page gets locked, and the database wants to write the next row to the same page due to the clustered index (which by definition matches the physical sort order of the column) that second write may be delayed, and if the database is busy enough, that may cause problems. I've heard that problem called a "hotspot" before. For a solution to this problem, I'm not sure what route would be best to take, in your situation. However, I'm not certain that this, by itself, is the problem you're having; typically, I think that this problem simply causes things to get significantly slower. A deadlock, on the other hand, implies that you have two transactions occurring, and neither can complete until the other has finished. So, the question for you is, are you doing anything else besides the insert here? Are you using CFTRANSACTION or transactional logic within your query/stored procedure also? On the other hand, for all I know, if the hotspot issue gets bad enough, maybe that will return a deadlock error! Your DBA should be able to help determine what's going on at the database level, I hope. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
Yes, but if it calls the database and doesn't wait for anything back, just goes ahead and calls it again... wouldn't it happen the same as when I call the queries - and the loop is faster than the database? -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:46 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: What is this SQL Error? > The DBA wants me to call a stored procedure, "wait" until the > stored procedure is done, then continue with the loop to call > it again... Is there a way to make sure the store procedure > is "done" before the code continues? I'm pretty sure that CF will handle this for you; CF won't call the next iteration of the loop until the first one has completed. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
> Yes, but if it calls the database and doesn't wait for > anything back, just goes ahead and calls it again... > wouldn't it happen the same as when I call the queries > - and the loop is faster than the database? It has to wait until the database server returns a message, before continuing. That message needn't be a recordset, but it does signal completion of the SQL batch. In most situations CF doesn't support asynchronous processing - starting something but not waiting around for it to finish. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
I thought about using cftry/cfcatch, but what if my second attempt is still done before the table is again available... I was wondering if ColdFusion had a "timer" function - such as, "wait 300ms before continuing"? -Original Message- From: Hoag, Claudia (LNG) [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:50 PM To: CF-Talk Subject: RE: What is this SQL Error? Yes, but if it calls the database and doesn't wait for anything back, just goes ahead and calls it again... wouldn't it happen the same as when I call the queries - and the loop is faster than the database? -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:46 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: What is this SQL Error? > The DBA wants me to call a stored procedure, "wait" until the > stored procedure is done, then continue with the loop to call > it again... Is there a way to make sure the store procedure > is "done" before the code continues? I'm pretty sure that CF will handle this for you; CF won't call the next iteration of the loop until the first one has completed. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: What is this SQL Error?
> I thought about using cftry/cfcatch, but what if my second > attempt is still done before the table is again available... > I was wondering if ColdFusion had a "timer" function - such > as, "wait 300ms before continuing"? No, it doesn't, but I don't think that matters, since CF's processing of queries isn't asynchronous, to the best of my knowledge. That is, it won't continue processing after a CFQUERY tag until the database says "I'm all done". Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists