Best Way to Trap SQL Error
What is the best way to gracefully trap this sort of error ... Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. The error occurred on line 3. TNX for any suggestions. Rick. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338940 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Best Way to Trap SQL Error
Have you tried cftry/cfcatch? -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 8 November 2010 23:07, Rick Colman rcol...@cox.net wrote: What is the best way to gracefully trap this sort of error ... Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. The error occurred on line 3. TNX for any suggestions. Rick. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338943 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Best Way to Trap SQL Error
The best way would be to validate the data before getting your database involved. On Mon, Nov 8, 2010 at 10:07 AM, Rick Colman rcol...@cox.net wrote: What is the best way to gracefully trap this sort of error ... Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. The error occurred on line 3. TNX for any suggestions. Rick. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338945 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Best Way to Trap SQL Error
A good site-wide error handler that sends alert e-mails would help. The cferror tag is one method that works well. -Mike Chabot On Mon, Nov 8, 2010 at 10:07 AM, Rick Colman rcol...@cox.net wrote: What is the best way to gracefully trap this sort of error ... Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. The error occurred on line 3. TNX for any suggestions. Rick. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338946 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Best Way to Trap SQL Error
This was a really good idea, and solved the problem. I validated for an integer, and the error went away. TNX. On 11/8/2010 7:49 AM, Michael Grant wrote: The best way would be to validate the data before getting your database involved. On Mon, Nov 8, 2010 at 10:07 AM, Rick Colmanrcol...@cox.net wrote: What is the best way to gracefully trap this sort of error ... Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. The error occurred on line 3. TNX for any suggestions. Rick. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338947 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: local sql error, not on live site.
I would post the whole query... sometimes the line number is not precisely correct :) -mk Mark A. Kruger, MCSE, CFG (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: Matthew Smith [mailto:chedders...@gmail.com] Sent: Wednesday, February 17, 2010 6:12 PM To: cf-talk Subject: local sql error, not on live site. I have a local install of sql server developer edition 2005. I have used the data base publishing wizard to copy the db from crystaltech to my local server. I am getting an error in a query that does not happen with crystaltech. Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver]Invalid precision value This is the line: cfif len(attributes.cfUserID) AND cfUserID = cfqueryparam value=#trim(attributes.cfUserID)# CF_SQL_VARCHAR /cfif I have also tried CF_SQL_LONGVARCHAR. The value of trim(attributes.cfUserID) is 1506_18149772. The data type of the column is nvarchar(100). ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330866 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: local sql error, not on live site.
On 2/18/10, Matthew Smith wrote: I am getting an error in a query that does not happen with crystaltech. Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver]Invalid precision value I am betting that a reputable hoster such as Crystaltech is not using ODBC for connections to MS SQL Server. Yet the error code indicates your local test environment does. Any particular reason for that? The data type of the column is nvarchar(100). ODBC and native charsets are a match made in hell. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330883 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: local sql error, not on live site.
That did it. Set up a regular sql server datasource and it worked. Thank you so much. Just out of curiosity, when should I use nvarchar as opposed to varchar? On Thu, Feb 18, 2010 at 12:20 PM, Jochem van Dieten joch...@gmail.comwrote: On 2/18/10, Matthew Smith wrote: I am getting an error in a query that does not happen with crystaltech. Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver]Invalid precision value I am betting that a reputable hoster such as Crystaltech is not using ODBC for connections to MS SQL Server. Yet the error code indicates your local test environment does. Any particular reason for that? The data type of the column is nvarchar(100). ODBC and native charsets are a match made in hell. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330884 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: local sql error, not on live site.
Just out of curiosity, when should I use nvarchar as opposed to varchar? nvarchar allows for an expanded set of characters, for example if you want your app to be able to handle double byte characters for non latin languages such as Chinese or Russian you would use nvarchar or ntext. However, nvarchar does require more space to store that data, so if you are 100% certain you will NEVER need to accommodate double byte characters, then varchar will work just fine. =] -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer Manager, Phoenix Cold Fusion User Group, AZCFUG.org ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330885 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: local sql error, not on live site.
I also believe (though I'm happy to be corrected if I'm wrong) that you can later safely switch from a varchar to an nvarchar. You'd be in danger of truncating data if you went from nvarchar to varchar, but since varchar is a subset of nvarchar, changing the data type at a later date should be safe. Judah On Thu, Feb 18, 2010 at 11:04 AM, Alan Rother alan.rot...@gmail.com wrote: Just out of curiosity, when should I use nvarchar as opposed to varchar? nvarchar allows for an expanded set of characters, for example if you want your app to be able to handle double byte characters for non latin languages such as Chinese or Russian you would use nvarchar or ntext. However, nvarchar does require more space to store that data, so if you are 100% certain you will NEVER need to accommodate double byte characters, then varchar will work just fine. =] -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer Manager, Phoenix Cold Fusion User Group, AZCFUG.org ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330886 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
local sql error, not on live site.
I have a local install of sql server developer edition 2005. I have used the data base publishing wizard to copy the db from crystaltech to my local server. I am getting an error in a query that does not happen with crystaltech. Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver]Invalid precision value This is the line: cfif len(attributes.cfUserID) AND cfUserID = cfqueryparam value=#trim(attributes.cfUserID)# CF_SQL_VARCHAR /cfif I have also tried CF_SQL_LONGVARCHAR. The value of trim(attributes.cfUserID) is 1506_18149772. The data type of the column is nvarchar(100). ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330856 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SQL Error
I'm using Oracle and CF7, doing a Query of Query. I'm attempting to research a
query to find any that the title doesn't have affiliate Faculty in it.
The query of query is giving me an error:
The system has attempted to use an undefined value, which usually indicates a
programming error, either in your code or some system code.
Null Pointers are another name for undefined values.
Here's the first query:
CFQUERY NAME=people_search DATASOURCE=dpch
SELECT
id,fname,mname,lname,type,title,dept,phone,location,email,email_alternate,summary
FROM PEOPLE
WHERE upper(dept) Like upper('%epidemiology%')
ORDER BY upper(lname) ASC, upper(fname) ASC
/CFQUERY
Here's the second query that gives the error:
cfquery dbtype=query name=faculty
select *
FROM people_search
WHERE upper(people_search.title) not like upper('%Affiliate Faculty,
Epidemiology Biostatistics%')
/cfquery
Oddly if I change out title for lname it works fine. Here's the
information on those two fields:
TITLE VARCHAR2200
LNAME VARCHAR2100
So they're largely similar, but daggummit, I need it to be title I use.
thank you.
daniel
~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323387
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
A non-SQL error occurred while requesting a connection from dataS
This is related to an earlier post today Coldfusion 8 Data Source Timeout. I am still getting a timeout when configuring a certain Data Source to a remote database. Using dbaccess from my CF8 machine works - no problem. I looked the cfserver.log and get: A non-SQL error occurred while requesting a connection from dataS. When I use a wrong login/password to same db - I get valide response - that the login/password are wrong. - We have another machine, running CF5, I configured the identical ODBC Source there, and it verified without a problem. Any idea what I could check next - thanks? If it is a non-SQL error - what kind of Error is it? ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319758 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Break from the SQL Injection thread.... weird SQL error
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 28: Incorrect syntax
near '@P10'.
I haven't the foggiest idea what's going on here.
here's the passed in params from the java trace:
(param 1) = [type='IN', class='java.lang.String', value='Elmer',
sqltype='cf_sql_varchar'] ,
(param 2) = [type='IN', class='java.lang.String', value='J',
sqltype='cf_sql_varchar'] ,
(param 3) = [type='IN', class='java.lang.String', value='Fudd',
sqltype='cf_sql_varchar'] ,
(param 4) = [type='IN', class='java.lang.String', value='0',
sqltype='cf_sql_varchar'] ,
(param 5) = [type='IN', class='java.lang.String', value='0',
sqltype='cf_sql_varchar'] ,
(param 6) = [type='IN', class='java.lang.String', value='0',
sqltype='cf_sql_varchar'] ,
(param 7) = [type='IN', class='java.lang.String',
value='[EMAIL PROTECTED]', sqltype='cf_sql_varchar'] ,
(param 8) = [type='IN', class='java.lang.String', value='703-555-1212',
sqltype='cf_sql_varchar'] ,
(param 9) = [type='IN', class='java.lang.String', value='3120',
sqltype='cf_sql_varchar'] ,
(param 10) = [type='IN', class='java.lang.String', value='Academic
Advising', sqltype='cf_sql_varchar'] ,
(param 11) = [type='IN', class='java.lang.Boolean', value='false',
sqltype='CF_SQL_BIT'] ,
(param 12) = [type='IN', class='java.lang.Boolean', value='false',
sqltype='CF_SQL_BIT'] ,
(param 13) = [type='IN', class='java.lang.String', value='Web',
sqltype='cf_sql_varchar'] ,
(param 14) = [type='IN', class='java.lang.Boolean', value='true',
sqltype='CF_SQL_BIT'] ,
(param 15) = [type='IN', value='null', sqltype='CF_SQL_DATE'] ,
(param 16) = [type='IN', class='java.lang.String', value='{d
'2008-08-08'}', sqltype='CF_SQL_DATE']
It's just a simple insert, and everything lines up (I think)
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:310567
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Break from the SQL Injection thread.... weird SQL error
Scott:
That error usually indicates an error with the query itself, not necessarily
an error with the value passed in. For example, you may be missing a comma
after the 10th parameter (i.e., the 10th cfqueryparam). Send the SQL code
from the debug info and let's see what we can do.
--
Mosh Teitelbaum
evoch, LLC
Tel: (301) 942-5378
Fax: (301) 933-3651
WWW: http://www.evoch.com/
-Original Message-
From: Scott Stewart [mailto:[EMAIL PROTECTED]
Sent: Friday, August 08, 2008 4:04 PM
To: CF-Talk
Subject: Break from the SQL Injection thread weird SQL error
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 28: Incorrect syntax
near '@P10'.
I haven't the foggiest idea what's going on here.
here's the passed in params from the java trace:
(param 1) = [type='IN', class='java.lang.String', value='Elmer',
sqltype='cf_sql_varchar'] ,
(param 2) = [type='IN', class='java.lang.String', value='J',
sqltype='cf_sql_varchar'] ,
(param 3) = [type='IN', class='java.lang.String', value='Fudd',
sqltype='cf_sql_varchar'] ,
(param 4) = [type='IN', class='java.lang.String', value='0',
sqltype='cf_sql_varchar'] ,
(param 5) = [type='IN', class='java.lang.String', value='0',
sqltype='cf_sql_varchar'] ,
(param 6) = [type='IN', class='java.lang.String', value='0',
sqltype='cf_sql_varchar'] ,
(param 7) = [type='IN', class='java.lang.String',
value='[EMAIL PROTECTED]', sqltype='cf_sql_varchar'] ,
(param 8) = [type='IN', class='java.lang.String', value='703-555-1212',
sqltype='cf_sql_varchar'] ,
(param 9) = [type='IN', class='java.lang.String', value='3120',
sqltype='cf_sql_varchar'] ,
(param 10) = [type='IN', class='java.lang.String', value='Academic
Advising', sqltype='cf_sql_varchar'] ,
(param 11) = [type='IN', class='java.lang.Boolean', value='false',
sqltype='CF_SQL_BIT'] ,
(param 12) = [type='IN', class='java.lang.Boolean', value='false',
sqltype='CF_SQL_BIT'] ,
(param 13) = [type='IN', class='java.lang.String', value='Web',
sqltype='cf_sql_varchar'] ,
(param 14) = [type='IN', class='java.lang.Boolean', value='true',
sqltype='CF_SQL_BIT'] ,
(param 15) = [type='IN', value='null', sqltype='CF_SQL_DATE'] ,
(param 16) = [type='IN', class='java.lang.String', value='{d
'2008-08-08'}', sqltype='CF_SQL_DATE']
It's just a simple insert, and everything lines up (I think)
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:310574
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Sql error during insert
-Original Message- From: Doug Brown [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: 11/14/06 7:17 PM Subject: Sql error during insert I do not recall seeing this before. Could someone tell me what I am doing wrong? I get the following error while executing this query. Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '@P1'. cfset mylist=Antiques Art Baby Books Business Industrial Cameras Photo Cars, Boats, Vehicles Parts Cell Phones PDAs Clothing, Shoes Accessories Coins Paper Money Collectibles Computers Networking Consumer Elec ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260473 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Sql error during insert
I do not recall seeing this before. Could someone tell me what I am doing wrong? I get the following error while executing this query. Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '@P1'. cfset mylist=Antiques Art Baby Books Business Industrial Cameras Photo Cars, Boats, Vehicles Parts Cell Phones PDAs Clothing, Shoes Accessories Coins Paper Money Collectibles Computers Networking Consumer Electronics Crafts Dolls Bears DVDs Movies Entertainment Memorabilia Gift Certificates Health Beauty Home Garden Jewelry Watches Music Musical Instruments Pottery Glass Real Estate Specialty Services Sporting Goods Sports Mem, Cards Fan Shop Stamps Tickets Toys Hobbies Travel Video Games Everything Else !---Replace all new line characters with pipe symbol for delimeter--- cfset myNewList = #ReReplaceNoCase(myList, \n , |, ALL)# !---loop through our new list and insert into database--- cfloop list=#myNewList# index=listItem cfquery datasource=nexbid INSERT INTO categories (category) VALUES (cfqueryparam cfsqltype=cf_sql_varchar value=#lTrim(listItem)# /cfquery /cfloop ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260448 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Sql error during insert
If this is a true copy and paste, you're missing your ending ')' in the VALUES statement. On 11/14/06, Doug Brown [EMAIL PROTECTED] wrote: I do not recall seeing this before. Could someone tell me what I am doing wrong? I get the following error while executing this query. Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '@P1'. cfset mylist=Antiques Art Baby Books Business Industrial Cameras Photo Cars, Boats, Vehicles Parts Cell Phones PDAs Clothing, Shoes Accessories Coins Paper Money Collectibles Computers Networking Consumer Electronics Crafts Dolls Bears DVDs Movies Entertainment Memorabilia Gift Certificates Health Beauty Home Garden Jewelry Watches Music Musical Instruments Pottery Glass Real Estate Specialty Services Sporting Goods Sports Mem, Cards Fan Shop Stamps Tickets Toys Hobbies Travel Video Games Everything Else !---Replace all new line characters with pipe symbol for delimeter--- cfset myNewList = #ReReplaceNoCase(myList, \n , |, ALL)# !---loop through our new list and insert into database--- cfloop list=#myNewList# index=listItem cfquery datasource=nexbid INSERT INTO categories (category) VALUES (cfqueryparam cfsqltype=cf_sql_varchar value=#lTrim(listItem)# /cfquery /cfloop ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260449 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Sql error during insert
You are missing a ) after the queryparam ..:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 14, 2006 10:15 PM To: CF-Talk Subject: Sql error during insert I do not recall seeing this before. Could someone tell me what I am doing wrong? I get the following error while executing this query. Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '@P1'. cfset mylist=Antiques Art Baby Books Business Industrial Cameras Photo Cars, Boats, Vehicles Parts Cell Phones PDAs Clothing, Shoes Accessories Coins Paper Money Collectibles Computers Networking Consumer Electronics Crafts Dolls Bears DVDs Movies Entertainment Memorabilia Gift Certificates Health Beauty Home Garden Jewelry Watches Music Musical Instruments Pottery Glass Real Estate Specialty Services Sporting Goods Sports Mem, Cards Fan Shop Stamps Tickets Toys Hobbies Travel Video Games Everything Else !---Replace all new line characters with pipe symbol for delimeter--- cfset myNewList = #ReReplaceNoCase(myList, \n , |, ALL)# !---loop through our new list and insert into database--- cfloop list=#myNewList# index=listItem cfquery datasource=nexbid INSERT INTO categories (category) VALUES (cfqueryparam cfsqltype=cf_sql_varchar value=#lTrim(listItem)# /cfquery /cfloop ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260450 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Sql error during insert
You also need to specify the delimiter in your loop as a | ..:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Crow T. Robot [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 14, 2006 10:22 PM To: CF-Talk Subject: Re: Sql error during insert If this is a true copy and paste, you're missing your ending ')' in the VALUES statement. On 11/14/06, Doug Brown [EMAIL PROTECTED] wrote: I do not recall seeing this before. Could someone tell me what I am doing wrong? I get the following error while executing this query. Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '@P1'. cfset mylist=Antiques Art Baby Books Business Industrial Cameras Photo Cars, Boats, Vehicles Parts Cell Phones PDAs Clothing, Shoes Accessories Coins Paper Money Collectibles Computers Networking Consumer Electronics Crafts Dolls Bears DVDs Movies Entertainment Memorabilia Gift Certificates Health Beauty Home Garden Jewelry Watches Music Musical Instruments Pottery Glass Real Estate Specialty Services Sporting Goods Sports Mem, Cards Fan Shop Stamps Tickets Toys Hobbies Travel Video Games Everything Else !---Replace all new line characters with pipe symbol for delimeter--- cfset myNewList = #ReReplaceNoCase(myList, \n , |, ALL)# !---loop through our new list and insert into database--- cfloop list=#myNewList# index=listItem cfquery datasource=nexbid INSERT INTO categories (category) VALUES (cfqueryparam cfsqltype=cf_sql_varchar value=#lTrim(listItem)# /cfquery /cfloop ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260451 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Sql error during insert
Never mind...I missed a closing ) on my query. Geez looked at it forever. D.B - Original Message - From: Doug Brown [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Tuesday, November 14, 2006 8:15 PM Subject: Sql error during insert I do not recall seeing this before. Could someone tell me what I am doing wrong? I get the following error while executing this query. Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '@P1'. cfset mylist=Antiques Art Baby Books Business Industrial Cameras Photo Cars, Boats, Vehicles Parts Cell Phones PDAs Clothing, Shoes Accessories Coins Paper Money Collectibles Computers Networking Consumer Electronics Crafts Dolls Bears DVDs Movies Entertainment Memorabilia Gift Certificates Health Beauty Home Garden Jewelry Watches Music Musical Instruments Pottery Glass Real Estate Specialty Services Sporting Goods Sports Mem, Cards Fan Shop Stamps Tickets Toys Hobbies Travel Video Games Everything Else !---Replace all new line characters with pipe symbol for delimeter--- cfset myNewList = #ReReplaceNoCase(myList, \n , |, ALL)# !---loop through our new list and insert into database--- cfloop list=#myNewList# index=listItem cfquery datasource=nexbid INSERT INTO categories (category) VALUES (cfqueryparam cfsqltype=cf_sql_varchar value=#lTrim(listItem)# /cfquery /cfloop ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260454 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Sql error during insert
Thanks. I hate it when the little stuff haunts you -) Doug - Original Message - From: Bobby Hartsfield [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Tuesday, November 14, 2006 8:21 PM Subject: RE: Sql error during insert You are missing a ) after the queryparam ..:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Doug Brown [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 14, 2006 10:15 PM To: CF-Talk Subject: Sql error during insert I do not recall seeing this before. Could someone tell me what I am doing wrong? I get the following error while executing this query. Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Line 2: Incorrect syntax near '@P1'. cfset mylist=Antiques Art Baby Books Business Industrial Cameras Photo Cars, Boats, Vehicles Parts Cell Phones PDAs Clothing, Shoes Accessories Coins Paper Money Collectibles Computers Networking Consumer Electronics Crafts Dolls Bears DVDs Movies Entertainment Memorabilia Gift Certificates Health Beauty Home Garden Jewelry Watches Music Musical Instruments Pottery Glass Real Estate Specialty Services Sporting Goods Sports Mem, Cards Fan Shop Stamps Tickets Toys Hobbies Travel Video Games Everything Else !---Replace all new line characters with pipe symbol for delimeter--- cfset myNewList = #ReReplaceNoCase(myList, \n , |, ALL)# !---loop through our new list and insert into database--- cfloop list=#myNewList# index=listItem cfquery datasource=nexbid INSERT INTO categories (category) VALUES (cfqueryparam cfsqltype=cf_sql_varchar value=#lTrim(listItem)# /cfquery /cfloop ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:260456 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
OT: SQL Error 1064
Hi everyone first post and unfortunately it had to be OT... Here's the scoop... Real new to SQL, currently reading through CF7 Web application construction kit and the install script for the MySQL data for this book is throwing me this error: You have an error (1064) in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; USE ows; # # Create the tables # #' at line 4 Running MySQL server ver. 4.1.12a-nt via TCP/IP I get this error when using the MySQL Query Browser, also tried installing the script via command line and it throws a few warnings as well. Is there something simple I'm not seeing here, or can someone point me in the right direction? Thanks people. The script is pasted bellow (I cut off the population portion), sorry for the length. Jay _ ### # Create and use database # ### CREATE DATABASE ows; USE ows; # # Create the tables # # # Create Actors table CREATE TABLE Actors ( ActorID int NOT NULL auto_increment, NameFirst char(50) NOT NULL, NameLast char(50) NOT NULL, Age smallint(6) NOT NULL, NameFirstReal char(50) NOT NULL, NameLastReal char(50) NOT NULL, AgeReal smallint(6) NOT NULL, IsEgomaniac bool NOT NULL, IsTotalBabe bool NOT NULL, Gender char(1) NOT NULL, PRIMARY KEY (ActorID) ) TYPE=MyISAM; # Create Contacts table CREATE TABLE Contacts ( ContactID int NOT NULL auto_increment, FirstName char(50) NOT NULL, LastName char(50) NOT NULL, Address char(100) NULL, City char(50) NULL, State char(5) NULL, Zip char(10) NULL, Country char(50) NULL, Email char(100) NULL, Phone char(50) NULL, UserLogin char(50) NULL, UserPassword char(50) NULL, MailingList bool NOT NULL DEFAULT 0, UserRoleID bigint NULL, PRIMARY KEY (ContactID) ) TYPE=MyISAM; # Create Directors table CREATE TABLE Directors ( DirectorID int NOT NULL auto_increment, FirstName char(50) NOT NULL, LastName char(50) NOT NULL, PRIMARY KEY (DirectorID) ) TYPE=MyISAM; # Create Expenses table CREATE TABLE Expenses ( ExpenseID int NOT NULL auto_increment, FilmID int NOT NULL, ExpenseAmount real NOT NULL, Description char(100) NOT NULL, ExpenseDate date NOT NULL, PRIMARY KEY (ExpenseID) ) TYPE=MyISAM; # Create Films table CREATE TABLE Films ( FilmID int NOT NULL auto_increment, MovieTitle char(255) NOT NULL, PitchText char(100) NOT NULL, AmountBudgeted real NULL, RatingID int NULL, Summary text NULL, ImageName char(50) NULL, DateInTheaters date NULL, PRIMARY KEY (FilmID) ) TYPE=MyISAM; # Create FilmsActors table CREATE TABLE FilmsActors ( FARecID int NOT NULL auto_increment, FilmID int NOT NULL, ActorID int NOT NULL, IsStarringRole bool NOT NULL, Salary real NOT NULL, PRIMARY KEY (FARecID) ) TYPE=MyISAM; # Create FilmsDirectors table CREATE TABLE FilmsDirectors ( FDRecID int NOT NULL auto_increment, FilmID int NOT NULL, DirectorID int NOT NULL, Salary real NOT NULL, PRIMARY KEY (FDRecID) ) TYPE=MyISAM; # Create FilmsRatings table CREATE TABLE Filmsratings ( RatingID int NOT NULL auto_increment, Rating char(50) NOT NULL, PRIMARY KEY (RatingID) ) TYPE=MyIsam; # Create Merchandise table CREATE TABLE Merchandise ( MerchID int NOT NULL auto_increment, FilmID int NOT NULL, MerchName char(50) NOT NULL, MerchDescription char(100) NULL, MerchPrice real NULL, ImageNameSmall char(50) NULL, ImageNameLarge char(50) NULL, PRIMARY KEY (MerchID) ) TYPE=MyISAM; # Create MerchandiseOrders table CREATE TABLE MerchandiseOrders ( OrderID int NOT NULL auto_increment, ContactID int NOT NULL, OrderDate date NOT NULL, ShipAddress char(100) NULL, ShipCity char(50) NULL, ShipState char(5) NULL, ShipZip char(10) NULL, ShipCountry char(50) NULL, ShipDate date NULL, PRIMARY KEY (OrderID) ) TYPE=MyISAM; # Create MerchandiseOrdersItems table CREATE TABLE MerchandiseOrdersItems ( OrderItemID int NOT NULL auto_increment, OrderID int NOT NULL, ItemID int NOT NULL, OrderQty int NOT NULL, ItemPrice real NOT NULL, PRIMARY KEY (OrderItemID) ) TYPE=MyISAM; # Create UserRoles table CREATE TABLE UserRoles ( UserRoleID int NOT NULL auto_increment, UserRoleName char(20) NULL, UserRoleFunction char(75) NULL, PRIMARY KEY (UserRoleID) ) TYPE=MyISAM; --SNIP-- ~| 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:215329 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
Weird SQL error
Anyone ever seen this? Error Occurred While Processing Request 7 = 7 the query is a simple select query... SELECT * FROM GALLERY_PHOTOS where blog_id=cfqueryparam cfsqltype=CF_SQL_INTEGER value=#blog_id# and gallery_id=cfqueryparam cfsqltype=CF_SQL_INTEGER value=#GALLERY_ID# (I don't know what database is involved, the person who reported this bug to me didn't specify... she also said it was sporadic) Rick ~| 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:207773 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: Weird SQL error
Rick Root wrote: Anyone ever seen this? Error Occurred While Processing Request 7 = 7 the query is a simple select query... SELECT * FROM GALLERY_PHOTOS where blog_id=cfqueryparam cfsqltype=CF_SQL_INTEGER value=#blog_id# and gallery_id=cfqueryparam cfsqltype=CF_SQL_INTEGER value=#GALLERY_ID# (I don't know what database is involved, the person who reported this bug to me didn't specify... she also said it was sporadic) This is occuring in SQL Server 2000. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207787 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
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
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: cfquery Your SQL WHERE something = cfqueryparam... /cfquery To this: pre cfoutput cfquery Your SQL WHERE something = lt;cfqueryparam... /cfquery /cfoutput /pre 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
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: 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! I'm no Postgres expert - maybe Jochem will weigh in - but my guess is that the backslashes are metacharacters. You may have to escape them in some way. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 ~| 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:184924 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: funny SQL error - running Postgres
Try using the CFQUERYPARAM tag __ Pete Freitag http://www.cfdev.com/ Author of the CFMX Developers Cookbook http://www.petefreitag.com/bookshelf/ ~| 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:184930 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: funny SQL error - running Postgres
Yep '\ is reserved in Postgres, and CF doesn't escape it like it auto-escapes apostrophe. I think \\ is the escape for it, so turning 'H:\UpLoadImages\CmosPic.jpg' into 'H:\\UpLoadImages\\CmosPic.jpg' may work. Also, cfqueryparam may clear it up completely. Someone here is bound to know more than me. -- For Tabs, Trees, and more, use the jComponents: http://clearsoftware.net/client/jComponents.cfm ~| 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:184932 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: funny SQL error - running Postgres
YES ! Thank you... was using it for the INSERT but not for the SELECT... rumor has it that it should be used for EVERYTHING... but alas I find it hard to buckle down and upgrade all the queries. Thanks Pete, Very Much Appreciated! Gonz On Fri, 19 Nov 2004 16:12:55 -0500, Pete Freitag [EMAIL PROTECTED] wrote: Try using the CFQUERYPARAM tag __ Pete Freitag http://www.cfdev.com/ Author of the CFMX Developers Cookbook http://www.petefreitag.com/bookshelf/ ~| 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:184934 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
SOT: SQL Error converting data type
Why would I get this SQL error : Error converting data type varchar to numeric on one filed and not another when I do a submit from a form even though they are setup the same. Both are numeric in the SQL table. I'm entering them this way: CFIF getponumber.recordcount EQ 0 OR VAL(getponumber.ponumber) EQ 0 CFSET session.ponumber=1001 CFSET session.cnumber=1001 CFELSE CFSET session.cnumber = getcnumber.cnumber + 1 CFSET session.ponumber = getponumber.ponumber + 1 /CFIF CFQUERY name=addPO datasource=purchaseorders INSERT INTO purchaseorders ( ponumber, cnumber ) VALUES ( #session.ponumber#, #session.cnumber# ') /cfquery Robert O. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
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]
Encrypt() putting single quote in string, causes SQL error when trying to SELECT
Hi, Have an interesting problem. I'm using Encrypt() to store sensitive user data in SQL Server. On some strings, the function is encrypting with a single quote as one of the chars. This is causing a SQL error when I'm trying to either enter or retrieve the data with cfquery. Has anyone run across this problem? Can anyone offer a workaround? Thanks, James ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=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: Encrypt() putting single quote in string, causes SQL error when trying to SELECT
U should use in every query when u try to retrieve or update or insert data into a field of type String(I mean varchar, nvarchar, etc...) the function replace(Strvalue, ','') Regards, PD: This developer mistake let to the attacker one simple SQL data injection in u Data Base. -- M. Sc. Hassan Arteaga Rodríguez Microsoft Certified System Engineer WEB Programmer. Network Admin [EMAIL PROTECTED] http://www.enmicuba.com -Original Message- From: James Johnson [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2003 11:53 AM To: CF-Talk Subject: Encrypt() putting single quote in string, causes SQL error when trying to SELECT Hi, Have an interesting problem. I'm using Encrypt() to store sensitive user data in SQL Server. On some strings, the function is encrypting with a single quote as one of the chars. This is causing a SQL error when I'm trying to either enter or retrieve the data with cfquery. Has anyone run across this problem? Can anyone offer a workaround? Thanks, James ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=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
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=subscribeforumid=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=subscribeforumid=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=subscribeforumid=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=subscribeforumid=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 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
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=subscribeforumid=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
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 = apos;30apos;) Is just what is outputted in the error, it is
actually ('30')
!--- Dynamic Query ---
SELECT zone#getzone.service# AS ship_value
FROM #variables.service_table#
WHERE weight = '#attributes.weight#'
ORDER BY weight desc
!--- Above output ---
SELECT zone202 AS ship_value
FROM 2da
WHERE weight = apos;30apos;
ORDER BY weight desc
!--- PRODUCES This Error ---
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 : /cfquery
184 : cfset variables.process = Yes
185 : cfset variables.ups_rate = getrate.ship_value
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=subscribeforumid=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: 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 = apos;30apos;) Is just what is outputted in the
error, it is actually ('30')
!--- Dynamic Query ---
SELECT zone#getzone.service# AS ship_value
FROM #variables.service_table#
WHERE weight = '#attributes.weight#'
ORDER BY weight desc
!--- Above output ---
SELECT zone202 AS ship_value
FROM 2da
WHERE weight = apos;30apos;
ORDER BY weight desc
!--- PRODUCES This Error ---
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 : /cfquery
184 : cfset variables.process = Yes
185 : cfset variables.ups_rate = getrate.ship_value
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=subscribeforumid=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: 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 = apos;30apos;) Is just what is outputted in the
error, it is actually ('30')
!--- Dynamic Query ---
SELECT zone#getzone.service# AS ship_value
FROM #variables.service_table#
WHERE weight = '#attributes.weight#'
ORDER BY weight desc
!--- Above output ---
SELECT zone202 AS ship_value
FROM 2da
WHERE weight = apos;30apos;
ORDER BY weight desc
!--- PRODUCES This Error ---
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 : /cfquery
184 : cfset variables.process = Yes
185 : cfset variables.ups_rate = getrate.ship_value
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=subscribeforumid=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: 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 = apos;30apos;) Is just what is outputted in the
error, it is actually ('30')
!--- Dynamic Query ---
SELECT zone#getzone.service# AS ship_value
FROM #variables.service_table#
WHERE weight = '#attributes.weight#'
ORDER BY weight desc
!--- Above output ---
SELECT zone202 AS ship_value
FROM 2da
WHERE weight = apos;30apos;
ORDER BY weight desc
!--- PRODUCES This Error ---
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 : /cfquery
184 : cfset variables.process = Yes
185 : cfset variables.ups_rate = getrate.ship_value
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=subscribeforumid=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=subscribeforumid=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
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 /cfquery tag on second query -- - CFMX / SQL Code - cfquery name=summaryInfo datasource=#request.dsn# 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 /cfquery cfquery name=userList dbtype=query SELECT DISTINCT userID, userName, team, approval, comments FROM summaryInfo WHERE team = 'marketing' /cfquery ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=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.
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
=
CFQUERY datasource=#application.ds#
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#'
/CFQUERY
=
HELP?
TIA
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
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
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
=
CFQUERY datasource=#application.ds#
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#'
/CFQUERY
=
HELP?
TIA
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: !--- Int Fields with default --- cfset FieldNameValue= cfset ColumnNames= cfset FieldNameValues= cfparam name=Form.ID default=0 !--- Get Column Names, Data Types from Form Field TableName and DSN(should be an hidden form fields --- cfquery name=GetTableInfodatasource=#Form.DSN# sp_columns #Form.TableName# /cfquery !--- Loop through each column in table --- cfloop query=GetTableInfo cfif ListFindNoCase(Form.FIELDNAMES,Column_Name) NEQ 0 AND Trim(Evaluate(ListGetAt(Form.FieldNames,ListFindNoCase(Form.FIELDNAMES,Column_Name NEQ cfset ElePos = ListFindNoCase(Form.FIELDNAMES,Column_Name) !--- Check to see what data type each column is and build insert based on value --- CFSWITCH EXPRESSION=#GetTableInfo.Type_Name# cfcase value=varchar cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase cfcase value=datetime cfset FieldNameValues = FieldNameValues , Column_Name = CreateODBCDateTime(Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos /cfcase cfcase value=text cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase cfcase value=timestamp cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase cfcase value=smalldatetime cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase CFDEFAULTCASE cfset FieldNameValues = FieldNameValues , Column_Name = Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) /CFDEFAULTCASE /CFSWITCH /cfif /cfloop !--- Check to see if there is a ,(comma) at the START of the string --- CFIF Left(FieldNameValues,1) EQ ,cfset FieldNameValues = Right(FieldNameValues,Len(FieldNameValues)-1)/CFIF CFIF Left(ColumnNames,1) EQ ,cfset ColumnNames = Right(ColumnNames,Len(ColumnNames)-1)/CFIF !--- Sample Insert statement --- cfquery name=Update datasource=#Form.DSN# Update #Form.Tablename# SET #FieldNameValues# WHERE ID = #Form.ID# /cfquery 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 /PLAINTEXTPRE/PRE/Q/S/SAMP/SCRIPT/SELECT/SMALL/STRIKE/STRONG/SUB/SUP/TABLE/TD/TEXTAREA/TH/TITLE/TR/TT/U UL/UL/VAR/WBRXMP/XMP HR H3Error Occurred While Processing Request/H3 P TABLE border=1 TBODY TR TD H4Error Diagnostic Information/H4 PODBC Error Code = 37000 (Syntax error or access violation) P P[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'Hosp'. P PSQL = 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 PData Source = INTERNET_PHA P PThe 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./P P PDate/Time: 06/06/02 09:55:33BRBrowser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461)BRRemote Address: 204.116.185.37BRHTTP Referrer: http://intertest/preregistration/preop2.cfm?PreRegType=Hospamp;Drugs=Noamp;ID=5/P __ 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: strange SQL error
do this: cfquery name=Update datasource=#Form.DSN# Update #preserveSingleQuotes(Form.Tablename)# SET #preserveSingleQuotes(FieldNameValues)# WHERE ID = #preserveSingleQuotes(Form.ID)# /cfquery +---+ 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: !--- Int Fields with default --- cfset FieldNameValue= cfset ColumnNames= cfset FieldNameValues= cfparam name=Form.ID default=0 !--- Get Column Names, Data Types from Form Field TableName and DSN(should be an hidden form fields --- cfquery name=GetTableInfodatasource=#Form.DSN# sp_columns #Form.TableName# /cfquery !--- Loop through each column in table --- cfloop query=GetTableInfo cfif ListFindNoCase(Form.FIELDNAMES,Column_Name) NEQ 0 AND Trim(Evaluate(ListGetAt(Form.FieldNames,ListFindNoCase(Form.FIELDNAMES,Colum n_Name NEQ cfset ElePos = ListFindNoCase(Form.FIELDNAMES,Column_Name) !--- Check to see what data type each column is and build insert based on value --- CFSWITCH EXPRESSION=#GetTableInfo.Type_Name# cfcase value=varchar cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase cfcase value=datetime cfset FieldNameValues = FieldNameValues , Column_Name = CreateODBCDateTime(Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos /cfcase cfcase value=text cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase cfcase value=timestamp cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase cfcase value=smalldatetime cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase CFDEFAULTCASE cfset FieldNameValues = FieldNameValues , Column_Name = Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) /CFDEFAULTCASE /CFSWITCH /cfif /cfloop !--- Check to see if there is a ,(comma) at the START of the string --- CFIF Left(FieldNameValues,1) EQ ,cfset FieldNameValues = Right(FieldNameValues,Len(FieldNameValues)-1)/CFIF CFIF Left(ColumnNames,1) EQ ,cfset ColumnNames = Right(ColumnNames,Len(ColumnNames)-1)/CFIF !--- Sample Insert statement --- cfquery name=Update datasource=#Form.DSN# Update #Form.Tablename# SET #FieldNameValues# WHERE ID = #Form.ID# /cfquery 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 /PLAINTEXTPRE/PRE/Q/S/SAMP/SCRIPT/SELECT/SMALL/STRIKE/S TRONG/SUB/SUP/TABLE/TD/TEXTAREA/TH/TITLE/TR/TT/U UL/UL/VAR/WBRXMP/XMP HR H3Error Occurred While Processing Request/H3 P TABLE border=1 TBODY TR TD H4Error Diagnostic Information/H4 PODBC Error Code = 37000 (Syntax error or access violation) P P[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'Hosp'. P PSQL = 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 PData Source = INTERNET_PHA P PThe 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./P P PDate/Time: 06/06/02 09:55:33BRBrowser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1
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: 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: 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: 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: 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 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. __ 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?
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
cfif listlen(selectedItems)
cfloop list=#selectedItems# index=thisSelectedItem
cfquery name=insertItem database=someDatabase
insert into someTable (col1,col2)
values (#thisUser#,#thisSelectedItem#)
/cfquery
/cfloop
/cfif
-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: 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 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
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
cfif listlen(selectedItems)
cfloop list=#selectedItems# index=thisSelectedItem
cfquery name=insertItem database=someDatabase
insert into someTable (col1,col2)
values (#thisUser#,#thisSelectedItem#)
/cfquery
/cfloop
/cfif
-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
cfif listlen(selectedItems)
cfloop list=#selectedItems# index=thisSelectedItem
cfquery name=insertItem database=someDatabase
insert into someTable (col1,col2)
values (#thisUser#,#thisSelectedItem#)
/cfquery
/cfloop
/cfif
-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
cfif listlen(selectedItems)
cfloop list=#selectedItems# index=thisSelectedItem
cfquery name=insertItem database=someDatabase
insert into someTable (col1,col2)
values (#thisUser#,#thisSelectedItem#)
/cfquery
/cfloop
/cfif
-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
RE: What is this SQL Error?
But isn't it what's happening with the hotspot problem? - the query is done, but the database still has the page locked. BTW, Thanks bunches Dave, it's great to have a geekguru available like this ;) -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 1:07 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: 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
RE: What is this SQL Error?
But isn't it what's happening with the hotspot problem? - the query is done, but the database still has the page locked. I seriously doubt that your problem is occurring because of one individual instance of your page running. However, if you have more than one person adding records at the same time, you could easily run into this. BTW, Thanks bunches Dave, it's great to have a geekguru available like this ;) You're welcome! Can I get a free Lexis-Nexis account out of this? Just kidding. 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
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
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
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?
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
CF/SQL error after sql re-start
After re-starting sql server2000 sp2, I have noticed the following error in the CF log files. Its happening sporiacally for the users. Then if they hit refresh once it happens, the page will execute successfully. Can't find anything on Microsoft's site about it or in the actual sql server log files. I'll re-start the server tonight, and maybe that will take care of it,but then again, maybe it won't. Am wondering if anyone here has seen the same thing ? ODBC Error Code = 01000 (General warning) [Microsoft][ODBC SQL Server Driver] [DBNETLIB]ConnectionWrite (WrapperWrite()). -- Visit The Most Powerful Tool on the Farm at http://www.ifarm.com Get the latest on Ag News, Market Reports, FREE email, and much more. __ 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, 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: 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
