RE: SQL Server and Nulls
Dbfield= Steve -Original Message- From: Robert Harrison [mailto:rharri...@aimg.com] Sent: Friday, April 17, 2015 3:58 PM To: cf-talk Subject: SQL Server and Nulls In an update query, to a tinyint field which allows nulls, I have the update dbfield=#mydatefield# If mydatefield has no value, sql is throwing an error. The field allows nulls. I've never had to say if "" then NULL before. What the heck? Robert Harrison Full Stack Developer AIMG rharri...@aimg.com Main Office: 704-321-1234 ext.118 Direct Line: 516-302-4345 www.aimg.com ~| 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:360470 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Server and Nulls
dbfield = On Fri, Apr 17, 2015 at 3:58 PM, Robert Harrison wrote: > > In an update query, to a tinyint field which allows nulls, I have the > update > > dbfield=#mydatefield# > > If mydatefield has no value, sql is throwing an error. The field allows > nulls. I've never had to say if "" then NULL before. What the heck? > > > Robert Harrison > Full Stack Developer > AIMG > rharri...@aimg.com > Main Office: 704-321-1234 ext.118 > Direct Line: 516-302-4345 > www.aimg.com > > > > ~| 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:360471 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
SQL Server and Nulls
In an update query, to a tinyint field which allows nulls, I have the update dbfield=#mydatefield# If mydatefield has no value, sql is throwing an error. The field allows nulls. I've never had to say if "" then NULL before. What the heck? Robert Harrison Full Stack Developer AIMG rharri...@aimg.com Main Office: 704-321-1234 ext.118 Direct Line: 516-302-4345 www.aimg.com ~| 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:360469 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
MS2008 SQL Express Driver versions changes?
All our currently troubles began with the upgrade to CF11. Under CF9 our application ran perfectly. All kinds of strange errors "GC out memory" / "java.sql.SQLNonTransientConnectionException" Random down times. No pattern or common error that I have yet detected. I have even considered the driver as being a potential cause. Any ideas about driver versions between CF9 and CF11 ? How would one go about getting more info on this? Cheers ~| 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:359838 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: protection from sql attacks with regex++
> Doing that on everything. If you're parametrizing everything on the queries then what is the concern? -Justin ~| 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:359122 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: protection from sql attacks with regex++
Doing that on everything. -Original Message- From: Robert Harrison [mailto:rob...@austin-williams.com] Sent: Friday, August 15, 2014 1:54 PM To: cf-talk Subject: RE: protection from sql attacks with regex++ Uhm... cfqueryparam Robert Harrison Director of Interactive Services Austin & Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_williams -Original Message- From: Stephens, Larry V [mailto:steph...@iu.edu] Sent: Friday, August 15, 2014 1:51 PM To: cf-talk Subject: protection from sql attacks with regex++ Using information from a Ben Nadel atricle, jsStringFormat( htmlEditFormat()) seems to be catching insertions like and escaping them. However, I have tried a number of regex routines from http://www.symantec.com/connect/articles/detection-sql-injection-and-cross-site-scripting-attacks plus another from a CF article that I can't place at the moment, to catch statements like "select * from tblX" inserted into a text field. None of them seem to work. The number of articles and pages making recommendations and giving examples is overwhelming. Can someone provide a suggestion for protecting a site in addition to what I got from Nadel and using ScriptProtect? ~| 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:359121 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: protection from sql attacks with regex++
Unless you were using evaluate (column) name inside another query somewhere I am not aware of how that could be used for an injection On Aug 15, 2014 1:51 PM, "Stephens, Larry V" wrote: > > Using information from a Ben Nadel atricle, jsStringFormat( > htmlEditFormat()) seems to be catching insertions like and escaping > them. > > However, I have tried a number of regex routines from > http://www.symantec.com/connect/articles/detection-sql-injection-and-cross-site-scripting-attacks > plus another from a CF article that I can't place at the moment, to catch > statements like "select * from tblX" inserted into a text field. None of > them seem to work. > > The number of articles and pages making recommendations and giving > examples is overwhelming. Can someone provide a suggestion for protecting a > site in addition to what I got from Nadel and using ScriptProtect? > > > > > ~| 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:359120 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: protection from sql attacks with regex++
Uhm... cfqueryparam Robert Harrison Director of Interactive Services Austin & Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_williams -Original Message- From: Stephens, Larry V [mailto:steph...@iu.edu] Sent: Friday, August 15, 2014 1:51 PM To: cf-talk Subject: protection from sql attacks with regex++ Using information from a Ben Nadel atricle, jsStringFormat( htmlEditFormat()) seems to be catching insertions like and escaping them. However, I have tried a number of regex routines from http://www.symantec.com/connect/articles/detection-sql-injection-and-cross-site-scripting-attacks plus another from a CF article that I can't place at the moment, to catch statements like "select * from tblX" inserted into a text field. None of them seem to work. The number of articles and pages making recommendations and giving examples is overwhelming. Can someone provide a suggestion for protecting a site in addition to what I got from Nadel and using ScriptProtect? ~| 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:359119 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
protection from sql attacks with regex++
Using information from a Ben Nadel atricle, jsStringFormat( htmlEditFormat()) seems to be catching insertions like and escaping them. However, I have tried a number of regex routines from http://www.symantec.com/connect/articles/detection-sql-injection-and-cross-site-scripting-attacks plus another from a CF article that I can't place at the moment, to catch statements like "select * from tblX" inserted into a text field. None of them seem to work. The number of articles and pages making recommendations and giving examples is overwhelming. Can someone provide a suggestion for protecting a site in addition to what I got from Nadel and using ScriptProtect? ~| 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:359118 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Unable To display Arabic text fetched SQL Server 2008 database into my CFM page
On 8/7/2014 1:34 PM, Sathyanarayanan Ramanathan wrote: > Yes. I was using ODBC socket datasource to connect CF with SQL server well you can stop now. > Driver]Error establishing socket to host and port: 127.0.0.1:1433. use real IP or server name instead of 127.0.0.1. if you're using an instance, make sure you add that for the server name, "bangkok\sqlexpress" for example. also double check username/password (won't see that authentication error until server name issue is resolved). ~| 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:359086 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Issue Solved -> Unable To display Arabic text fetched SQL Server 2008 database into my CFM page
Dear Friends, Finally the is solved. Thanks Bobby & Paul. While adding datasource in CF administrator , in server field I was wrongly trying as localhost, now I changed to "MICRO\SQLEXPRESS" as server and db name in database field along with check box checked for Enable High ASCII characters and Unicode for data sources configured for non-Latin characters. This solved the issue. Now am able to successfully connect CF9 with SQL server 2008 and Arabic text is displaying as well. Your valuable time & help is really appreciated. Thanks, Sathya.R On Thu, Aug 7, 2014 at 10:34 AM, Sathyanarayanan Ramanathan < sathya0...@gmail.com> wrote: > Dear, > > Thanks for your replies. But still am facing issue. > > @Paul, > > Yes. I was using ODBC socket datasource to connect CF with SQL server > database. In that the connection was successful but Arabic didn't display > properly in CFM page.(In db the datatype is correctly used nvarchar and I > can see Arabic data as well.) > > @Bobby, > > As per your suggestion, I tried to create datasource with Microsoft SQL > server driver. But am getting the below error. > " > >- Connection verification failed for data source: MCONSULT >java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer >JDBC Driver]Error establishing socket to host and port: 127.0.0.1:1433. >Reason: Connection refused: connect >The root cause was that: java.sql.SQLNonTransientConnectionException: >[Macromedia][SQLServer JDBC Driver]Error establishing socket to host and >port: 127.0.0.1:1433. Reason: Connection refused: connect > > " > I found few suggestions from net & tried below things but still issue > exists. > 1. In SQL server configuration manager I enabled TCP/IP protocol. > 2. In Windows Firewall I added inbound rule for TCP 1433. > > Both my CF9 & SQL server 2008 are installed in same machine. > > Please suggest. > > Sathya.R > > > On Thu, Aug 7, 2014 at 5:19 AM, Paul Hastings > wrote: > >> >> On 8/7/2014 6:16 AM, Bobby wrote: >> > >> > He said the arabic text was actually in the database but only ??? when >> > retrieving/displaying with CF. Wouldnt it be garbage IN the db if it >> wasnt >> > an nchar/nvarchar data type? >> >> could be already garbaged as far as cf & unicode go. in the bad old days >> (pre >> cf/java unicode) that was actually the only way to work w/some encodings. >> >> doesn't matter to cf either way. the encodings match back-to-front but >> the text >> data is already garbaged or the encodings are mismatched & getting >> garbaged on >> the way to cf. >> >> >> just to be thorough, i suppose its probably prudent to ask if cf is using >> the >> JDBC driver to talk to the db and not some ODBC thing? >> >> >> >> >> >> ~| 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:359085 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Unable To display Arabic text fetched SQL Server 2008 database into my CFM page
Dear, Thanks for your replies. But still am facing issue. @Paul, Yes. I was using ODBC socket datasource to connect CF with SQL server database. In that the connection was successful but Arabic didn't display properly in CFM page.(In db the datatype is correctly used nvarchar and I can see Arabic data as well.) @Bobby, As per your suggestion, I tried to create datasource with Microsoft SQL server driver. But am getting the below error. " - Connection verification failed for data source: MCONSULT java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: 127.0.0.1:1433. Reason: Connection refused: connect The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: 127.0.0.1:1433. Reason: Connection refused: connect " I found few suggestions from net & tried below things but still issue exists. 1. In SQL server configuration manager I enabled TCP/IP protocol. 2. In Windows Firewall I added inbound rule for TCP 1433. Both my CF9 & SQL server 2008 are installed in same machine. Please suggest. Sathya.R On Thu, Aug 7, 2014 at 5:19 AM, Paul Hastings wrote: > > On 8/7/2014 6:16 AM, Bobby wrote: > > > > He said the arabic text was actually in the database but only ??? when > > retrieving/displaying with CF. Wouldnt it be garbage IN the db if it > wasnt > > an nchar/nvarchar data type? > > could be already garbaged as far as cf & unicode go. in the bad old days > (pre > cf/java unicode) that was actually the only way to work w/some encodings. > > doesn't matter to cf either way. the encodings match back-to-front but the > text > data is already garbaged or the encodings are mismatched & getting > garbaged on > the way to cf. > > > just to be thorough, i suppose its probably prudent to ask if cf is using > the > JDBC driver to talk to the db and not some ODBC thing? > > > > > > ~| 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:359084 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Unable To display Arabic text fetched SQL Server 2008 database into my CFM page
On 8/7/2014 6:16 AM, Bobby wrote: > > He said the arabic text was actually in the database but only ??? when > retrieving/displaying with CF. Wouldnt it be garbage IN the db if it wasnt > an nchar/nvarchar data type? could be already garbaged as far as cf & unicode go. in the bad old days (pre cf/java unicode) that was actually the only way to work w/some encodings. doesn't matter to cf either way. the encodings match back-to-front but the text data is already garbaged or the encodings are mismatched & getting garbaged on the way to cf. just to be thorough, i suppose its probably prudent to ask if cf is using the JDBC driver to talk to the db and not some ODBC thing? ~| 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:359081 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Unable To display Arabic text fetched SQL Server 2008 database into my CFM page
He said the arabic text was actually in the database but only ??? when retrieving/displaying with CF. Wouldnt it be garbage IN the db if it wasnt an nchar/nvarchar data type? On 8/6/14, 12:21 PM, "Paul Hastings" wrote: > >On 8/6/2014 8:17 PM, Sathyanarayanan Ramanathan wrote: >> *Issue 1: *Now I have existing SQL server 2008 db with Arabic text data >>in >> some table columns. But when I try to fetch & display using CF even >>after >> adding charset in meta tag & cfprocessingdirective as UTF-8 all Arabic >>text >> appeared as . > >"??" means your text data is garbaged from the db. double check that it's >stored >in an "N" datatype (nchar, nvarchar, etc.). then double check if the data >is in >fact encoded as unicode. > > > ~| 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:359068 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Unable To display Arabic text fetched SQL Server 2008 database into my CFM page
On 8/6/2014 8:28 PM, Bobby wrote: > > Last time, my suggestion was to make sure the "Enable High ASCII > characters and Unicode for data sources configured for non-Latin > characters� setting was enabled on your CF datasource but that was before > I realized it was an oracle datasource. If you are using a SQL Server > datasource this time, that setting should apply so make sure the setting > is enabled and try it again. will only have effect on cfqueryparam. ~| 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:359067 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Unable To display Arabic text fetched SQL Server 2008 database into my CFM page
On 8/6/2014 8:17 PM, Sathyanarayanan Ramanathan wrote: > *Issue 1: *Now I have existing SQL server 2008 db with Arabic text data in > some table columns. But when I try to fetch & display using CF even after > adding charset in meta tag & cfprocessingdirective as UTF-8 all Arabic text > appeared as . "??" means your text data is garbaged from the db. double check that it's stored in an "N" datatype (nchar, nvarchar, etc.). then double check if the data is in fact encoded as unicode. ~| 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:359066 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Unable To display Arabic text fetched SQL Server 2008 database into my CFM page
Last time, my suggestion was to make sure the "Enable High ASCII characters and Unicode for data sources configured for non-Latin characters² setting was enabled on your CF datasource but that was before I realized it was an oracle datasource. If you are using a SQL Server datasource this time, that setting should apply so make sure the setting is enabled and try it again. On 8/6/14, 9:17 AM, "Sathyanarayanan Ramanathan" wrote: > >Dear Friends, > >Few days before I was trying to insert Arabic text into my ColdFusion >application with oracle database. And finally it worked for me as I >mentioned in previous mail chain below. > >*Steps taken:* > >*Oracle side:* I created the new database with NLS_CHARACTERSET as >AL32UTF8 >and imported my old exported database having different characterset. > >*CF side:* >I added the below two lines at the top of the cfm template. > > > >*Current Issue:* >But now am facing similar issue with respect to insert Arabic text into my >ColdFusion application with SQL Server 2008 database. > >*Issue 1: *Now I have existing SQL server 2008 db with Arabic text data in >some table columns. But when I try to fetch & display using CF even after >adding charset in meta tag & cfprocessingdirective as UTF-8 all Arabic >text >appeared as . > >But if I use charset as Windows-1256 static text in the cfm page such as >column headers are coming as Arabic. But the fetched data from sql db >still >appear as ??? > >Please help. > >Sathya.R > > > ~| 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:359065 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Unable To display Arabic text fetched SQL Server 2008 database into my CFM page
Dear Friends, Few days before I was trying to insert Arabic text into my ColdFusion application with oracle database. And finally it worked for me as I mentioned in previous mail chain below. *Steps taken:* *Oracle side:* I created the new database with NLS_CHARACTERSET as AL32UTF8 and imported my old exported database having different characterset. *CF side:* I added the below two lines at the top of the cfm template. *Current Issue:* But now am facing similar issue with respect to insert Arabic text into my ColdFusion application with SQL Server 2008 database. *Issue 1: *Now I have existing SQL server 2008 db with Arabic text data in some table columns. But when I try to fetch & display using CF even after adding charset in meta tag & cfprocessingdirective as UTF-8 all Arabic text appeared as . But if I use charset as Windows-1256 static text in the cfm page such as column headers are coming as Arabic. But the fetched data from sql db still appear as ??? Please help. Sathya.R ~| 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:359064 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Global String Replace
> you could just used a stored procedure which will save it directly to the > database, and then execute it from CF That never even crossed my mind. Good idea. Thanks. Robert Harrison Director of Interactive Services Austin & Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_ ~| 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:357867 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Global String Replace
you could just used a stored procedure which will save it directly to the database, and then execute it from CF On Thu, Mar 6, 2014 at 9:05 PM, Robert Harrison wrote: > > Thanks everyone for the suggestions. I've tested the one at this link: > http://www.mssqltips.com/sqlservertip/1555/sql-server-find-and-replace-values-in-all-tables-and-all-text-columns/and > it works perfectly. > > I mentioned previously that I was hoping to run it in CF, and that was > partially because some of the hosts don't like to give direct access to the > data bases on their servers. Regardless, after testing I can see it's a > heavy load and have to agree with Ben Forta that it really should be run as > a query in Studio. I'll deal with getting the access I need to run directly. > > Thanks, > Robert > > Robert Harrison > Director of Interactive Services > > Austin & Williams > Advertising I Branding I Digital I Direct > 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 > T 631.231.6600 X 119 F 631.434.7022 > http://www.austin-williams.com > > Blog: http://www.austin-williams.com/blog > Twitter: http://www.twitter.com/austi > > ~| 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:357866 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Global String Replace
Thanks everyone for the suggestions. I've tested the one at this link: http://www.mssqltips.com/sqlservertip/1555/sql-server-find-and-replace-values-in-all-tables-and-all-text-columns/ and it works perfectly. I mentioned previously that I was hoping to run it in CF, and that was partially because some of the hosts don't like to give direct access to the data bases on their servers. Regardless, after testing I can see it's a heavy load and have to agree with Ben Forta that it really should be run as a query in Studio. I'll deal with getting the access I need to run directly. Thanks, Robert Robert Harrison Director of Interactive Services Austin & Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austi ~| 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:357865 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Global String Replace
I agree with Ben...this is something that you should be running in SQL Management Studio. ColdFusion is not meant to run this kind of stuff (unless you set the timeout on your templates to 0). The code attached below will loop over all of the user tables, and then loop over all of the text type columns for that table while writing out an update statement. Send your results to Text in the query window. Then you can cut and paste the text results into a new query window and run it. Make sure you change the text values to be changed and what they are going to be changed to. Also, if your tables follow a naming convention, you can add that to the first cursor declaration as an additional where clause... DECLARE @tableName NVARCHAR(255), @objectID BIGINT, @columnName NVARCHAR(255), @TextToReplace NVARCHAR(1000), @ReplaceTextWith NVARCHAR(1000) SET @TextToReplace = 'www.mysite.com' SET @replaceTextWith = 'www.mynewsite.com' DECLARE userTables CURSOR FOR SELECT name, object_id FROM sys.tables WHERE [type] = 'U' OPEN userTables FETCH NEXT FROM userTables INTO @tableName, @objectID WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'UPDATE ' + @tableName + ' SET ' /* Select the columns from the table where the data type is a text type column Text- 35 sql_variant - 98 ntext - 99 varchar - 167 char- 175 nvarchar- 231 nchar - 239 */ DECLARE userColumns CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @objectID AND system_type_id IN (35,98,99,167,175,231,239) OPEN userColumns FETCH NEXT FROM userColumns INTO @columnName WHILE @@FETCH_STATUS = 0 BEGIN PRINT '[' + @columnName + '] = REPLACE([' + @columnname + '], ''' + @TextToReplace + ''', ''' + @ReplaceTextWith + '''),' FETCH NEXT FROM userColumns INTO @columnName END CLOSE userColumns DEALLOCATE userColumns PRINT '1=1' PRINT 'GO' PRINT ' ' FETCH NEXT FROM userTables INTO @tableName, @columnName END CLOSE userTables DEALLOCATE userTables Original Message > From: "Robert Harrison" > Sent: Tuesday, March 04, 2014 7:19 AM > To: "cf-talk" > Subject: RE: SQL Global String Replace > > Actually, that's the kind of operation that you'd not want to perform in CF (or PHP or any other database client). Unless you truly need all that data within a CF page for some other reason, you shouldn't be sending it all back and forth between DBMS and CF. > > ... I'm going to do this locally, but it seems to be done in PHP as a matter of course. We use such a thing on our WordPress sites when to change the URLs we move from a staging URL to a live URL, and there are lots of PHP programs prewritten to do just that. What I'm doing here is the same thing... changing URLs embedded into the CMS pages. > > - Robert > > > > ~| 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:357847 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Global String Replace
Actually, that's the kind of operation that you'd not want to perform in CF (or PHP or any other database client). Unless you truly need all that data within a CF page for some other reason, you shouldn't be sending it all back and forth between DBMS and CF. ... I'm going to do this locally, but it seems to be done in PHP as a matter of course. We use such a thing on our WordPress sites when to change the URLs we move from a staging URL to a live URL, and there are lots of PHP programs prewritten to do just that. What I'm doing here is the same thing... changing URLs embedded into the CMS pages. - Robert ~| 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:357837 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Global String Replace
Actually, that's the kind of operation that you'd not want to perform in CF (or PHP or any other database client). Unless you truly need all that data within a CF page for some other reason, you shouldn't be sending it all back and forth between DBMS and CF. --- Ben (Sent from my newest Android device) On Mar 4, 2014 6:08 AM, "Robert Harrison" wrote: > > Yes, I do mean like that, but I was really hoping someone had it already > written up in CF with a tested procedure they would be willing to share. > > I was able to find several downloads for PHP, but nothing for CF. > > Thanks > > Robert Harrison > Director of Interactive Services > > Austin & Williams > Advertising I Branding I Digital I Direct > 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 > T 631.231.6600 X 119 F 631.434.7022 > http://www.austin-williams.com > > Blog: http://www.austin-williams.com/blog > Twitter: http://www.twitter.com/austin_ > > ~| 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:357836 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Global String Replace
Yes, I do mean like that, but I was really hoping someone had it already written up in CF with a tested procedure they would be willing to share. I was able to find several downloads for PHP, but nothing for CF. Thanks Robert Harrison Director of Interactive Services Austin & Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_ ~| 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:357835 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Global String Replace
Take a look at the sys.tables and sys.columns tables in your SQL database. You should be able to write a couple of cursors to loop over each and just print out the SQL to run separately (or you can get fancy and generate the SQL statement and run it via EXEC sp_executeSQL functions). I don't have my SQL server handy but can take a look later. -- Jeff -Original Message- From: Robert Harrison [mailto:rob...@austin-williams.com] Sent: Tuesday, March 04, 2014 6:56 AM To: cf-talk Subject: SQL Global String Replace Does anyone have an update program that can update a text string in all tables/rows/columns of an MS SQL data base? Need to do a global text string replace on several sites. Any help appreciated. Thanks, Robert Harrison Director of Interactive Services Austin & Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_wi ~| 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:357834 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Global String Replace
you mean like this http://www.mssqltips.com/sqlservertip/1555/sql-server-find-and-replace-values-in-all-tables-and-all-text-columns/ On Tue, Mar 4, 2014 at 1:55 PM, Robert Harrison wrote: > > Does anyone have an update program that can update a text string in all > tables/rows/columns of an MS SQL data base? > > Need to do a global text string replace on several sites. Any help > appreciated. > > Thanks, > > Robert Harrison > Director of Interactive Services > > Austin & Williams > Advertising I Branding I Digital I Direct > 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 > T 631.231.6600 X 119 F 631.434.7022 > http://www.austin-williams.com > > Blog: http://www.austin-williams.com/blog > Twitter: http://www.twitter.com/austin_wi > > ~| 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:357833 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
SQL Global String Replace
Does anyone have an update program that can update a text string in all tables/rows/columns of an MS SQL data base? Need to do a global text string replace on several sites. Any help appreciated. Thanks, Robert Harrison Director of Interactive Services Austin & Williams Advertising I Branding I Digital I Direct 125 Kennedy Drive, Suite 100 I Hauppauge, NY 11788 T 631.231.6600 X 119 F 631.434.7022 http://www.austin-williams.com Blog: http://www.austin-williams.com/blog Twitter: http://www.twitter.com/austin_wi ~| 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:357831 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CF10 / SQL Server Windows Authentication
Thanks I'm going to go down with a SQL user and not use the windows authentication. It wasn't my choice to use more this is what I was given. Thanks for your help Sent from my iPhone > On 11 Feb 2014, at 22:50, Russ Michaels wrote: > > > I would also point out that if you are currently running CF as system, then > you obviously have not locked it down either, so you should consider doing > that. > You should also remember that if you run CF as a domain user who has access > to network resources and all the databases on your sql server, then any > code and any person with access to upload code on your cf server also has > this level of access as well. > > >> On Tue, Feb 11, 2014 at 9:03 PM, Dave Watts wrote: >> >> >>> Im having difficulty in creating a datasource to SQL server that has >> windows authentication setup. >>> When i create the datasource using the SA account it creates >> successfully, but when i try and use the windows >>> account i get an error: >>> 'Login failed for user'\domain\username' >>> >>> i have checked the security options for the server and the user is setup >> as db_owner and when i try and login using >>> the SQL management studio with the windows details i can login >> successfully. >>> >>> it just wont work when creating the datasource. >>> >>> I have tried using different combinations in the username field: >>> domain\username >>> username >>> username.domain.local >>> usern...@domain.com >>> uppercase and lowercase >>> >>> still i get the same error - 'Login failed for user' >> >> I'll second Russ' recommendation to use mixed mode authentication >> instead - it's generally just a lot easier. But if you absolutely, >> positively have to use Windows authentication, follow the steps >> described here: >> >> http://www.cfuser.com/windows-authentication-sql-server-and-coldfusion/ >> >> Dave Watts, CTO, Fig Leaf Software >> 1-202-527-9569 >> http://www.figleaf.com/ >> http://training.figleaf.com/ >> >> Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on >> GSA Schedule, and provides the highest caliber vendor-authorized >> instruction at our training centers, online, or onsite. > > ~| 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:357667 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CF10 / SQL Server Windows Authentication
I would also point out that if you are currently running CF as system, then you obviously have not locked it down either, so you should consider doing that. You should also remember that if you run CF as a domain user who has access to network resources and all the databases on your sql server, then any code and any person with access to upload code on your cf server also has this level of access as well. On Tue, Feb 11, 2014 at 9:03 PM, Dave Watts wrote: > > > Im having difficulty in creating a datasource to SQL server that has > windows authentication setup. > > When i create the datasource using the SA account it creates > successfully, but when i try and use the windows > > account i get an error: > > 'Login failed for user'\domain\username' > > > > i have checked the security options for the server and the user is setup > as db_owner and when i try and login using > > the SQL management studio with the windows details i can login > successfully. > > > > it just wont work when creating the datasource. > > > > I have tried using different combinations in the username field: > > domain\username > > username > > username.domain.local > > usern...@domain.com > > uppercase and lowercase > > > > still i get the same error - 'Login failed for user' > > I'll second Russ' recommendation to use mixed mode authentication > instead - it's generally just a lot easier. But if you absolutely, > positively have to use Windows authentication, follow the steps > described here: > > http://www.cfuser.com/windows-authentication-sql-server-and-coldfusion/ > > Dave Watts, CTO, Fig Leaf Software > 1-202-527-9569 > http://www.figleaf.com/ > http://training.figleaf.com/ > > Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on > GSA Schedule, and provides the highest caliber vendor-authorized > instruction at our training centers, online, or onsite. > > ~| 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:357661 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CF10 / SQL Server Windows Authentication
> Im having difficulty in creating a datasource to SQL server that has windows > authentication setup. > When i create the datasource using the SA account it creates successfully, > but when i try and use the windows > account i get an error: > 'Login failed for user'\domain\username' > > i have checked the security options for the server and the user is setup as > db_owner and when i try and login using > the SQL management studio with the windows details i can login successfully. > > it just wont work when creating the datasource. > > I have tried using different combinations in the username field: > domain\username > username > username.domain.local > usern...@domain.com > uppercase and lowercase > > still i get the same error - 'Login failed for user' I'll second Russ' recommendation to use mixed mode authentication instead - it's generally just a lot easier. But if you absolutely, positively have to use Windows authentication, follow the steps described here: http://www.cfuser.com/windows-authentication-sql-server-and-coldfusion/ Dave Watts, CTO, Fig Leaf Software 1-202-527-9569 http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:357658 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CF10 / SQL Server Windows Authentication
best method is to use mixed mode and use an sql login from coldfusion dsn. If you want to use a windows authentication then you need to run coldfusion under a windows user with authentication on the sql server. On Tue, Feb 11, 2014 at 8:43 PM, Anthony Doherty wrote: > > Hi, > Im having difficulty in creating a datasource to SQL server that has > windows authentication setup. > When i create the datasource using the SA account it creates successfully, > but when i try and use the windows account i get an error: > 'Login failed for user'\domain\username' > > i have checked the security options for the server and the user is setup > as db_owner and when i try and login using the SQL management studio with > the windows details i can login successfully. > > it just wont work when creating the datasource. > > I have tried using different combinations in the username field: > domain\username > username > username.domain.local > usern...@domain.com > uppercase and lowercase > > still i get the same error - 'Login failed for user' > > any help would be greatly appreciated! > > Thanks > Anthony > > ~| 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:357657 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CF10 / SQL Server Windows Authentication
Would you happen to be using MS SQL Express locally? You may need to adjust your TCP/IP connection settings for SQL, as well as adjust the systems firewall rules for access. The following post may assist some: http://www.fusioncube.net/index.php/coldfusion-sql-server-express Steve 'Cutter' Blades Adobe Community Professional Adobe Certified Expert Advanced Macromedia ColdFusion MX 7 Developer http://cutterscrossing.com Co-Author "Learning Ext JS 3.2" Packt Publishing 2010 https://www.packtpub.com/learning-ext-js-3-2-for-building-dynamic-desktop-style-user-interfaces/book "The best way to predict the future is to help create it" On 2/11/2014 2:43 PM, Anthony Doherty wrote: > Hi, > Im having difficulty in creating a datasource to SQL server that has windows > authentication setup. > When i create the datasource using the SA account it creates successfully, > but when i try and use the windows account i get an error: > 'Login failed for user'\domain\username' > > i have checked the security options for the server and the user is setup as > db_owner and when i try and login using the SQL management studio with the > windows details i can login successfully. > > it just wont work when creating the datasource. > > I have tried using different combinations in the username field: > domain\username > username > username.domain.local > usern...@domain.com > uppercase and lowercase > > still i get the same error - 'Login failed for user' > > any help would be greatly appreciated! > > Thanks > Anthony > > ~| 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:357656 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CF10 / SQL Server Windows Authentication
Hi, Im having difficulty in creating a datasource to SQL server that has windows authentication setup. When i create the datasource using the SA account it creates successfully, but when i try and use the windows account i get an error: 'Login failed for user'\domain\username' i have checked the security options for the server and the user is setup as db_owner and when i try and login using the SQL management studio with the windows details i can login successfully. it just wont work when creating the datasource. I have tried using different combinations in the username field: domain\username username username.domain.local usern...@domain.com uppercase and lowercase still i get the same error - 'Login failed for user' any help would be greatly appreciated! Thanks Anthony ~| 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:357655 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Issue implementing SSL authentication to use SQL force encryption
> I am having an issue when I add the suggested SSL connection string via > adobe\Cold Fusion website>>>>>>> > (EncryptionMethod=SSL; TrustStore=path to keystore; > TrustStorePassword=trustStorePassword; > ValidateServerCertificate=true|false; HostNameInCertificate) and when I > restart the Cold Fusion services or reboot > the server the neo-databasesource file loses it's structure causing no access > to the database unless I remove the > connection string and cut of SQL force encryption. Anyone with any insight on > this type of issue. Please advise? It's not clear to me whether you're having the problem only after a restart, or whether you were never able to connect to the database via TLS/SSL at all. If it's the latter, have you added the SQL Server's certificate chain to the CF keystore? Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:357309 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Issue implementing SSL authentication to use SQL force encryption
All, I am having an issue when I add the suggested SSL connection string via adobe\Cold Fusion website>>>>>>> (EncryptionMethod=SSL; TrustStore=path to keystore; TrustStorePassword=trustStorePassword; ValidateServerCertificate=true|false; HostNameInCertificate) and when I restart the Cold Fusion services or reboot the server the neo-databasesource file loses it's structure causing no access to the database unless I remove the connection string and cut of SQL force encryption. Anyone with any insight on this type of issue. Please advise? ~| 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:357308 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Simple SQL Query sometimes really Slow?
Thank you everyone for all your suggestions. Gonna start testing them out. Byron: I have this issue when running the query via management studio and via cfquery so not sure if that is relevant.. Jon: I'm still running 2005 (if it ain't broke...), but was also thinking about trying READ UNCOMMITTED (maybe that would have the same effect as your suggestion?) Mark: The select is only returning a couple of columns (date/int) and no text.. I'll check the activity monitor.. the execution plan shows the index usage and doesn't appear to account for the delays... Jeff: Thanks for the Queries, I'll try them! Whohoo! Cftalk is alive!! Brook -Original Message- From: Byron Mann [mailto:byronos...@gmail.com] Sent: December-05-13 10:22 AM To: cf-talk Subject: Re: Simple SQL Query sometimes really Slow? Could never figure this out, but we had a similar issue on 2005 with a date time column. I remember we changed from a cfquery to a stored procedure and it was resolved. Byron Mann Lead Engineer & Architect HostMySite.com On Dec 5, 2013 12:27 PM, "Brook Davies" wrote: > > This may not be the right place to post this (man, CF-TALK has changed > a lot in the last 5 or so years ;)). > > > > I have a simple SQL query that is showing up as running slow. When I > run it via the Management Studio it is sometimes fast 0.1 seconds and > sometimes, seemingly randomly slow 1.5 minutes!). Other queries on > other tables are executing normally. This table only has 50k records > and even a simple query is sometimes really slow. > > > > The query that runs slow is as simple as > > > > select commitDate,id from databaseChangeLog > > where usr_id = 62622 and form_id = 312468 > > and commitDate > '2013-12-04 11:00:05.0' > > > > But is just as slow without the date part. The table has a clustered > index on the primary key (id) and a non-clustered index on > usr_id,form_id and commitDate. The index doesn't seem to make any difference. > > > > My guess is the table is locked. My question is: > > > > How can I determine if it is locked? What would be locking it. I > checked all my code and there are no CFTRANSACTIONS or ISOLATED READS > or anything like that. There are some inserts and the table has 2 TEXT > columns which are being updated at times with fairly large values. But > the only queries reported as slow are these simple SELECTS. The query > execution plan uses the non-clustered index on (usr_id,form_id and > commitDate). > > > > I'm just at a loss as to why this specific query is sometimes so slow.. > where to look? > > > > Brook > > > > > ~| 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:357295 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Simple SQL Query sometimes really Slow?
Could never figure this out, but we had a similar issue on 2005 with a date time column. I remember we changed from a cfquery to a stored procedure and it was resolved. Byron Mann Lead Engineer & Architect HostMySite.com On Dec 5, 2013 12:27 PM, "Brook Davies" wrote: > > This may not be the right place to post this (man, CF-TALK has changed a > lot > in the last 5 or so years ;)). > > > > I have a simple SQL query that is showing up as running slow. When I run it > via the Management Studio it is sometimes fast 0.1 seconds and sometimes, > seemingly randomly slow 1.5 minutes!). Other queries on other tables are > executing normally. This table only has 50k records and even a simple query > is sometimes really slow. > > > > The query that runs slow is as simple as > > > > select commitDate,id from databaseChangeLog > > where usr_id = 62622 and form_id = 312468 > > and commitDate > '2013-12-04 11:00:05.0' > > > > But is just as slow without the date part. The table has a clustered index > on the primary key (id) and a non-clustered index on usr_id,form_id and > commitDate. The index doesn't seem to make any difference. > > > > My guess is the table is locked. My question is: > > > > How can I determine if it is locked? What would be locking it. I checked > all > my code and there are no CFTRANSACTIONS or ISOLATED READS or anything like > that. There are some inserts and the table has 2 TEXT columns which are > being updated at times with fairly large values. But the only queries > reported as slow are these simple SELECTS. The query execution plan uses > the > non-clustered index on (usr_id,form_id and commitDate). > > > > I'm just at a loss as to why this specific query is sometimes so slow.. > where to look? > > > > Brook > > > > > ~| 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:357294 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Simple SQL Query sometimes really Slow?
for analyzing the execution plan, check out SQL Sentry Plan Explorer. They have a free version, and it's a much better tool for execution plan analysis than Management Studio On Thu, Dec 5, 2013 at 10:03 AM, Mark A Kruger wrote: > > Brooke, > > Couple of points of inquiry. > > 1) 50k records can be a little OR a lot. How much actual data is returned. > Is a lot of textual? Management studio might look really fast but the > problem could be a lot of character data buffering to the web server. > > 2) have you looked at the activity monitor? Filter by your connection and > watch for blocks or waits - taking note of the process blocking. > > 3) Indexing might be ok but maybe not. Take a look at the "execution plan" > in Management Studio - it can tell you what the most expensive operations > of > the query are. > > 4) Double check parallelism on the server. This can bite you under certain > conditions and will result in what look like "randomly slow" queries with > no > seeming blocks. See my blog post about it: > http://www.coldfusionmuse.com/index.cfm/2011/11/18/cf.mssql.parallelism > > Hope this helps a little. Good luck! > > -mark > > > > > -Original Message- > From: Brook Davies [mailto:cft...@logiforms.com] > Sent: Thursday, December 05, 2013 11:26 AM > To: cf-talk > Subject: Simple SQL Query sometimes really Slow? > > > This may not be the right place to post this (man, CF-TALK has changed a > lot > in the last 5 or so years ;)). > > > > I have a simple SQL query that is showing up as running slow. When I run it > via the Management Studio it is sometimes fast 0.1 seconds and sometimes, > seemingly randomly slow 1.5 minutes!). Other queries on other tables are > executing normally. This table only has 50k records and even a simple query > is sometimes really slow. > > > > The query that runs slow is as simple as > > > > select commitDate,id from databaseChangeLog > > where usr_id = 62622 and form_id = 312468 > > and commitDate > '2013-12-04 11:00:05.0' > > > > But is just as slow without the date part. The table has a clustered index > on the primary key (id) and a non-clustered index on usr_id,form_id and > commitDate. The index doesn't seem to make any difference. > > > > My guess is the table is locked. My question is: > > > > How can I determine if it is locked? What would be locking it. I checked > all > my code and there are no CFTRANSACTIONS or ISOLATED READS or anything like > that. There are some inserts and the table has 2 TEXT columns which are > being updated at times with fairly large values. But the only queries > reported as slow are these simple SELECTS. The query execution plan uses > the > non-clustered index on (usr_id,form_id and commitDate). > > > > I'm just at a loss as to why this specific query is sometimes so slow.. > where to look? > > > > Brook > > > > > > > ~| 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:357293 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
re: Simple SQL Query sometimes really Slow?
There are a couple of queries that you can run while your query is
executing to see if there is anything else running that is blocking the
execution. The first will show all running activities and identify if any
are blocking one another. The second is just a handy script to see what's
running currently.
/*** Find blocking SPIDS
***/ SELECT s.spid, BlockingSPID =
s.blocked, DatabaseName = DB_NAME(s.dbid), s.program_name,
s.loginame, ObjectName = OBJECT_NAME(objectid, s.dbid), Definition =
CAST(text AS VARCHAR(MAX)) FROM sys.sysprocesses sCROSS APPLY
sys.dm_exec_sql_text (sql_handle) WHERE s.spid > 50
/*** Find all running queries
***/ SELECT sqltext.TEXT,
req.session_id, req.status, req.command, req.cpu_time,
req.total_elapsed_time FROM sys.dm_exec_requests req CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS sqltext
If these don't show anything blocking your query, you may want to look at
using query hints to force the use of a particular index. SQLServer will
somtimes choose a poor execution plan. You can give it hints on which
index to use at the table level using something like the following: SELECT
* FROM tablename WITH (INDEX({indexname})) WHERE . A good primer on
using index hints can be found here:
http://blog.sqlauthority.com/2009/02/08/sql-server-introduction-to-force-ind
ex-query-hints-index-hint-part2/
Hope this helps,
-- Jeff
Original Message
> From: "Brook Davies"
> Sent: Thursday, December 05, 2013 10:27 AM
> To: "cf-talk"
> Subject: Simple SQL Query sometimes really Slow?
>
> This may not be the right place to post this (man, CF-TALK has changed a
lot
> in the last 5 or so years ;)).
>
>
>
> I have a simple SQL query that is showing up as running slow. When I run
it
> via the Management Studio it is sometimes fast 0.1 seconds and
sometimes,
> seemingly randomly slow 1.5 minutes!). Other queries on other tables are
> executing normally. This table only has 50k records and even a simple
query
> is sometimes really slow.
>
>
>
> The query that runs slow is as simple as
>
>
>
> select commitDate,id from databaseChangeLog
>
> where usr_id = 62622 and form_id = 312468
>
> and commitDate > '2013-12-04 11:00:05.0'
>
>
>
> But is just as slow without the date part. The table has a clustered
index
> on the primary key (id) and a non-clustered index on usr_id,form_id and
> commitDate. The index doesn't seem to make any difference.
>
>
>
> My guess is the table is locked. My question is:
>
>
>
> How can I determine if it is locked? What would be locking it. I checked
all
> my code and there are no CFTRANSACTIONS or ISOLATED READS or anything
like
> that. There are some inserts and the table has 2 TEXT columns which are
> being updated at times with fairly large values. But the only queries
> reported as slow are these simple SELECTS. The query execution plan uses
the
> non-clustered index on (usr_id,form_id and commitDate).
>
>
>
> I'm just at a loss as to why this specific query is sometimes so slow..
> where to look?
>
>
>
> Brook
>
>
>
>
>
~|
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:357292
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Simple SQL Query sometimes really Slow?
Just for clarification, do you have a unique index with the three non-primary columns, or are those indexed individually? If individually, I would suggest a combined index, at least of the usr_id and form_id columns, as those are numeric values and then a separate index of the date column. Ive found mixing datatypes within an index usually doesnt gain much in query performance. Since 2008, SQL Server has the default Lock Escalation setting as Table, which means that the processing of large updates will lock to the table. You can see the specifics of whats happening under-the-hood with each of the escalation settings here: http://msdn.microsoft.com/en-us/library/ms190273.aspx You might try setting Lock Escallation to DISABLE and see if that resolves the issue. You can also turn on Snapshot Isolation to allow your reads to proceed, even when large updates are happening: http://msdn.microsoft.com/en-us/library/tcbchxcb%28VS.80%29.aspx HTH, Jon On Dec 5, 2013, at 12:26 PM, Brook Davies wrote: > > This may not be the right place to post this (man, CF-TALK has changed a lot > in the last 5 or so years ;)). > > > > I have a simple SQL query that is showing up as running slow. When I run it > via the Management Studio it is sometimes fast 0.1 seconds and sometimes, > seemingly randomly slow 1.5 minutes!). Other queries on other tables are > executing normally. This table only has 50k records and even a simple query > is sometimes really slow. > > > > The query that runs slow is as simple as > > > > select commitDate,id from databaseChangeLog > > where usr_id = 62622 and form_id = 312468 > > and commitDate > '2013-12-04 11:00:05.0' > > > > But is just as slow without the date part. The table has a clustered index > on the primary key (id) and a non-clustered index on usr_id,form_id and > commitDate. The index doesn't seem to make any difference. > > > > My guess is the table is locked. My question is: > > > > How can I determine if it is locked? What would be locking it. I checked all > my code and there are no CFTRANSACTIONS or ISOLATED READS or anything like > that. There are some inserts and the table has 2 TEXT columns which are > being updated at times with fairly large values. But the only queries > reported as slow are these simple SELECTS. The query execution plan uses the > non-clustered index on (usr_id,form_id and commitDate). > > > > I'm just at a loss as to why this specific query is sometimes so slow.. > where to look? > > > > Brook > > > > > ~| 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:357291 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Simple SQL Query sometimes really Slow?
Brooke, Couple of points of inquiry. 1) 50k records can be a little OR a lot. How much actual data is returned. Is a lot of textual? Management studio might look really fast but the problem could be a lot of character data buffering to the web server. 2) have you looked at the activity monitor? Filter by your connection and watch for blocks or waits - taking note of the process blocking. 3) Indexing might be ok but maybe not. Take a look at the "execution plan" in Management Studio - it can tell you what the most expensive operations of the query are. 4) Double check parallelism on the server. This can bite you under certain conditions and will result in what look like "randomly slow" queries with no seeming blocks. See my blog post about it: http://www.coldfusionmuse.com/index.cfm/2011/11/18/cf.mssql.parallelism Hope this helps a little. Good luck! -mark -Original Message- From: Brook Davies [mailto:cft...@logiforms.com] Sent: Thursday, December 05, 2013 11:26 AM To: cf-talk Subject: Simple SQL Query sometimes really Slow? This may not be the right place to post this (man, CF-TALK has changed a lot in the last 5 or so years ;)). I have a simple SQL query that is showing up as running slow. When I run it via the Management Studio it is sometimes fast 0.1 seconds and sometimes, seemingly randomly slow 1.5 minutes!). Other queries on other tables are executing normally. This table only has 50k records and even a simple query is sometimes really slow. The query that runs slow is as simple as select commitDate,id from databaseChangeLog where usr_id = 62622 and form_id = 312468 and commitDate > '2013-12-04 11:00:05.0' But is just as slow without the date part. The table has a clustered index on the primary key (id) and a non-clustered index on usr_id,form_id and commitDate. The index doesn't seem to make any difference. My guess is the table is locked. My question is: How can I determine if it is locked? What would be locking it. I checked all my code and there are no CFTRANSACTIONS or ISOLATED READS or anything like that. There are some inserts and the table has 2 TEXT columns which are being updated at times with fairly large values. But the only queries reported as slow are these simple SELECTS. The query execution plan uses the non-clustered index on (usr_id,form_id and commitDate). I'm just at a loss as to why this specific query is sometimes so slow.. where to look? Brook ~| 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:357290 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Simple SQL Query sometimes really Slow?
This may not be the right place to post this (man, CF-TALK has changed a lot in the last 5 or so years ;)). I have a simple SQL query that is showing up as running slow. When I run it via the Management Studio it is sometimes fast 0.1 seconds and sometimes, seemingly randomly slow 1.5 minutes!). Other queries on other tables are executing normally. This table only has 50k records and even a simple query is sometimes really slow. The query that runs slow is as simple as select commitDate,id from databaseChangeLog where usr_id = 62622 and form_id = 312468 and commitDate > '2013-12-04 11:00:05.0' But is just as slow without the date part. The table has a clustered index on the primary key (id) and a non-clustered index on usr_id,form_id and commitDate. The index doesn't seem to make any difference. My guess is the table is locked. My question is: How can I determine if it is locked? What would be locking it. I checked all my code and there are no CFTRANSACTIONS or ISOLATED READS or anything like that. There are some inserts and the table has 2 TEXT columns which are being updated at times with fairly large values. But the only queries reported as slow are these simple SELECTS. The query execution plan uses the non-clustered index on (usr_id,form_id and commitDate). I'm just at a loss as to why this specific query is sometimes so slow.. where to look? Brook ~| 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:357289 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Outputting SQL aliases as table headers
You can use array notation to reference columns with spaces. So, instead of myQuery.column with space use myQuery["column with space"][myQuery.currentRow] On Thu, Aug 29, 2013 at 10:09 AM, DURETTE, STEVEN J wrote: > > Try looking at query_name.columnList it should have a list of the columns > returned from a cfquery. > > Of course replace query_name with the name of your actual query. > > -Original Message- > From: Monique Boea [mailto:moniqueb...@gmail.com] > Sent: Thursday, August 29, 2013 11:08 AM > To: cf-talk > Subject: Re: Outputting SQL aliases as table headers > > > They are dynamic with spaces. > > > > > > ~| 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:356655 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Outputting SQL aliases as table headers
Try looking at query_name.columnList it should have a list of the columns returned from a cfquery. Of course replace query_name with the name of your actual query. -Original Message- From: Monique Boea [mailto:moniqueb...@gmail.com] Sent: Thursday, August 29, 2013 11:08 AM To: cf-talk Subject: Re: Outputting SQL aliases as table headers They are dynamic with spaces. ~| 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:356648 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Outputting SQL aliases as table headers
are you trying to say that you are generating the table dynamically and have no idea in advance what column names you will be using ? On Thu, Aug 29, 2013 at 4:07 PM, Monique Boea wrote: > > They are dynamic with spaces. > > > > > On Thu, Aug 29, 2013 at 11:04 AM, Russ Michaels > wrote: > > > > > I still do not see what your problem is, it is just text in a table > header, > > it can be anything you like, including spaces > > > > > > On Thu, Aug 29, 2013 at 4:01 PM, Monique Boea > > wrote: > > > > > > > > but the sql column name is what I need to have as the table column name > > and > > > there are spaces in the aliases. > > > > > > > > > On Thu, Aug 29, 2013 at 10:46 AM, Russ Michaels > > > wrote: > > > > > > > > > > > in the same way that you would output any column name in a table. > > > > You just output it. > > > > > > > > Alias Name > > > > > > > > > > > > > > > > > > > > > > > > > > > > On Thu, Aug 29, 2013 at 1:29 PM, Monique Boea > > > > > wrote: > > > > > > > > > > > > > > Hello all > > > > > > > > > > How can I output sql aliases as headers in a table? > > > > > > > > > > See attached. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ~| 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:356647 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Outputting SQL aliases as table headers
They are dynamic with spaces. On Thu, Aug 29, 2013 at 11:04 AM, Russ Michaels wrote: > > I still do not see what your problem is, it is just text in a table header, > it can be anything you like, including spaces > > > On Thu, Aug 29, 2013 at 4:01 PM, Monique Boea > wrote: > > > > > but the sql column name is what I need to have as the table column name > and > > there are spaces in the aliases. > > > > > > On Thu, Aug 29, 2013 at 10:46 AM, Russ Michaels > > wrote: > > > > > > > > in the same way that you would output any column name in a table. > > > You just output it. > > > > > > Alias Name > > > > > > > > > > > > > > > > > > > > > On Thu, Aug 29, 2013 at 1:29 PM, Monique Boea > > > wrote: > > > > > > > > > > > Hello all > > > > > > > > How can I output sql aliases as headers in a table? > > > > > > > > See attached. > > > > > > > > > > > > > > > > > > > > > > > > ~| 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:356646 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Outputting SQL aliases as table headers
I still do not see what your problem is, it is just text in a table header, it can be anything you like, including spaces On Thu, Aug 29, 2013 at 4:01 PM, Monique Boea wrote: > > but the sql column name is what I need to have as the table column name and > there are spaces in the aliases. > > > On Thu, Aug 29, 2013 at 10:46 AM, Russ Michaels > wrote: > > > > > in the same way that you would output any column name in a table. > > You just output it. > > > > Alias Name > > > > > > > > > > > > > > On Thu, Aug 29, 2013 at 1:29 PM, Monique Boea > > wrote: > > > > > > > > Hello all > > > > > > How can I output sql aliases as headers in a table? > > > > > > See attached. > > > > > > > > > > > > > > > ~| 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:356645 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Outputting SQL aliases as table headers
but the sql column name is what I need to have as the table column name and there are spaces in the aliases. On Thu, Aug 29, 2013 at 10:46 AM, Russ Michaels wrote: > > in the same way that you would output any column name in a table. > You just output it. > > Alias Name > > > > > > > On Thu, Aug 29, 2013 at 1:29 PM, Monique Boea > wrote: > > > > > Hello all > > > > How can I output sql aliases as headers in a table? > > > > See attached. > > > > > > > > ~| 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:356644 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Outputting SQL aliases as table headers
in the same way that you would output any column name in a table. You just output it. Alias Name On Thu, Aug 29, 2013 at 1:29 PM, Monique Boea wrote: > > Hello all > > How can I output sql aliases as headers in a table? > > See attached. > > > ~| 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:356643 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Outputting SQL aliases as table headers
Hello all How can I output sql aliases as headers in a table? See attached. ~| 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:356635 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Optimising SQL Statement
On Fri, Apr 26, 2013 at 12:56 PM, Richard White wrote: > I am sure there must be a way to restructure this query to bring the time > down > I am afraid we can't really help because most of the information we need for that is missing. Schema, cardinalities etc. > SELECT * FROM ( > > SELECT primarys.primaryid , q_1 AS `subjectID` , q_2 AS `studyNumbers` > > FROM primarys LEFT OUTER JOIN questions_1_100 ON primarys.primaryid = > > questions_1_100.primaryid WHERE 0 = 0 AND q_1 IS NOT NULL GROUP BY > > primaryid) AS maintable_1 > > GROUP BY `subjectID` > In addition there is a serious issue with this part of the query: it is not deterministic. You are selecting the columns primaryid, subjectID and studyNumbers from the inner select and then grouping by subjectID, without telling the DB what to do for the other columns. So if your inner query produces: 1, 2, 3 3, 2, 1 The result could be either of: 1,2,3 3,2,1 I am presuming this query produces the results you are expecting, but that is either an accident (and as soon as an optimisation changes the execution plan you get different results), or because there is a lot of correlation between the columns of your tables, of which you haven't told us anything. Jochem ~| 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:355619 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Optimising SQL Statement
You could start by replacing SELECT * with SELECT column1, column2, column3, etc. On 4/26/13 6:56 AM, "Richard White" wrote: > >Hi, I am sure there must be a way to restructure this query to bring the >time down but i cannot see it. Any pointers at all would be greatly >appreciated. > >> Hi, >> >> We have a problem with one of our MySQL statements and wondering if >> you guys can help point us in the right direction. >> >> Basically the following statement is taking 5 seconds to run. We have >> diagnosed it is down to the join of two select statement. When the >> select statements are run individually they take only 0.2 seconds but >> when combined with the JOIN it takes 5 seconds. >> >> WE have been told then when MySQL performs a join it creates temporary >> tables in the background. Is this correct? >> >> Is there anything you can see that we are doing wrong or can you see a >> better way? >> >> code start -- >> >> SELECT temp_4.primaryid, temp_1.`subjectID` , temp_4.`testOccasionID` , >> `studyNumbers` ,`testDate` >> >> FROM ( > >> SELECT * FROM ( > >> SELECT primarys.primaryid , q_1 AS `subjectID` , q_2 AS `studyNumbers` >> FROM primarys LEFT OUTER JOIN questions_1_100 ON primarys.primaryid = >> questions_1_100.primaryid WHERE 0 = 0 AND q_1 IS NOT NULL GROUP BY >> primaryid) AS maintable_1 >> GROUP BY `subjectID` ) AS temp_1 >> >> JOIN >> >> (SELECT * FROM >> (SELECT primarys.primaryid , q_1 AS `subjectID` , q_4 AS >> `testOccasionID` , DATE_FORMAT(q_5, '%m/%d/%Y') AS `testDate` FROM >> primarys LEFT OUTER JOIN questions_1_100 ON primarys.primaryid = >> questions_1_100.primaryid WHERE 0 = 0 AND q_1 IS NOT NULL AND q_4 IS >> NOT NULL GROUP BY primaryid) AS maintable_4 >> GROUP BY `subjectID` ,`testOccasionID` ) AS temp_4 >> >> ON temp_1.`subjectID` = temp_4.`subjectID` >> >> code end -- >> >> Many thanks >> Richard > > ~| 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:355606 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Optimising SQL Statement
Hi, I am sure there must be a way to restructure this query to bring the time down but i cannot see it. Any pointers at all would be greatly appreciated. > Hi, > > We have a problem with one of our MySQL statements and wondering if > you guys can help point us in the right direction. > > Basically the following statement is taking 5 seconds to run. We have > diagnosed it is down to the join of two select statement. When the > select statements are run individually they take only 0.2 seconds but > when combined with the JOIN it takes 5 seconds. > > WE have been told then when MySQL performs a join it creates temporary > tables in the background. Is this correct? > > Is there anything you can see that we are doing wrong or can you see a > better way? > > code start -- > > SELECT temp_4.primaryid, temp_1.`subjectID` , temp_4.`testOccasionID` , > `studyNumbers` ,`testDate` > > FROM ( > SELECT * FROM ( > SELECT primarys.primaryid , q_1 AS `subjectID` , q_2 AS `studyNumbers` > FROM primarys LEFT OUTER JOIN questions_1_100 ON primarys.primaryid = > questions_1_100.primaryid WHERE 0 = 0 AND q_1 IS NOT NULL GROUP BY > primaryid) AS maintable_1 > GROUP BY `subjectID` ) AS temp_1 > > JOIN > > (SELECT * FROM > (SELECT primarys.primaryid , q_1 AS `subjectID` , q_4 AS > `testOccasionID` , DATE_FORMAT(q_5, '%m/%d/%Y') AS `testDate` FROM > primarys LEFT OUTER JOIN questions_1_100 ON primarys.primaryid = > questions_1_100.primaryid WHERE 0 = 0 AND q_1 IS NOT NULL AND q_4 IS > NOT NULL GROUP BY primaryid) AS maintable_4 > GROUP BY `subjectID` ,`testOccasionID` ) AS temp_4 > > ON temp_1.`subjectID` = temp_4.`subjectID` > > code end -- > > Many thanks > Richard ~| 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:355601 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL bulk inserts, ftps, and unicode special characters
On 4/13/2013 1:55 AM, Rick Root wrote: > It occurs to me that even If I put the data in correctly, I still have to > "deal" with it because I can't really output a utf-16 character to a web > page (or can I?).. I dunno maybe it just works. no, it will show up fine (your db driver & cf will see to that). internally sql server stores the data as UCS2, that's going to get transformed to UTF-8 (or whatever you request). just make sure the columns holding the unicode text data are "N" dataype (nvarchar, etc.) & you should be good to go. ~| 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:355397 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL bulk inserts, ftps, and unicode special characters
Thanks Paul. It occurs to me that even If I put the data in correctly, I still have to "deal" with it because I can't really output a utf-16 character to a web page (or can I?).. I dunno maybe it just works. time to play... ~| 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:355395 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL bulk inserts, ftps, and unicode special characters
On 4/11/2013 11:53 PM, Rick Root wrote: > No, it doesn't. Not really. > > http://msdn.microsoft.com/en-us/library/bb330962(v=sql.90).aspx actually that page and a decade of my experience says it does. UTF-8 will get transformed (its designed for that) to UCS2 by the db driver. for all practical purposes, sql server does "support" utf-8 encoded text. BULK INSERT is another matter. UTF-8 was dropped from that particular tool for some reason or another (digging around it looks like maybe a bug that was supposed to be fixed in 2 months, 5 years ago). ms uses the term "Unicode Character Format" for what BULK INSERT supports but they're the only one that does. not exactly sure what's meant by it but reading thru this page: http://msdn.microsoft.com/en-us/library/ms188289.aspx the BOM mentioned there seems to indicate its UTF-16. so i think you're on the right track w/trying to get UTF-16 out of your source & using the wide char option. because its ms, i'd try to get little endian UTF-16 to be on the safe side. ~| 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:355372 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL bulk inserts, ftps, and unicode special characters
I'm gonna try to get the SAP people to send me a UTF-16 file instead (essentially the same as the link except I wouldn't have to convert it :) ) On Thu, Apr 11, 2013 at 1:03 PM, Russ Michaels wrote: > > you could try this work around. > > > http://stackoverflow.com/questions/5498033/how-to-write-utf-8-characters-using-bulk-insert-in-sql-server > > > On Thu, Apr 11, 2013 at 5:53 PM, Rick Root wrote: > > > > > On Tue, Mar 26, 2013 at 11:10 PM, Paul Hastings > >wrote: > > > > > > > > > SQL Server 2005 does not support UTF-8 apparently. > > > > > > sure it does. > > > > > > > No, it doesn't. Not really. > > > > http://msdn.microsoft.com/en-us/library/bb330962(v=sql.90).aspx > > > > I'm loading this data from UTF-8 encoded files coming from an SAP system, > > and I'm loading them using the BULK INSERT command. > > > > Even SQL Server 2012 doesn't support UTF-8. Look at this page ( > > http://msdn.microsoft.com/en-us/library/ms188365.aspx) and find "UTF-8") > > > > Ultimately, my "workaround" here is that I found the specific garbage > > strings after loading the data and replacing them with a sql udf I wrote > > that basically does this: > > > > set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(163),'"') > > set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(165),'"') > > set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(214),'''') > > set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(244),'-') > > set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(201),'-') > > > > 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:355363 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL bulk inserts, ftps, and unicode special characters
you could try this work around. http://stackoverflow.com/questions/5498033/how-to-write-utf-8-characters-using-bulk-insert-in-sql-server On Thu, Apr 11, 2013 at 5:53 PM, Rick Root wrote: > > On Tue, Mar 26, 2013 at 11:10 PM, Paul Hastings >wrote: > > > > > > SQL Server 2005 does not support UTF-8 apparently. > > > > sure it does. > > > > No, it doesn't. Not really. > > http://msdn.microsoft.com/en-us/library/bb330962(v=sql.90).aspx > > I'm loading this data from UTF-8 encoded files coming from an SAP system, > and I'm loading them using the BULK INSERT command. > > Even SQL Server 2012 doesn't support UTF-8. Look at this page ( > http://msdn.microsoft.com/en-us/library/ms188365.aspx) and find "UTF-8") > > Ultimately, my "workaround" here is that I found the specific garbage > strings after loading the data and replacing them with a sql udf I wrote > that basically does this: > > set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(163),'"') > set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(165),'"') > set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(214),'''') > set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(244),'-') > set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(201),'-') > > 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:355361 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL bulk inserts, ftps, and unicode special characters
On Tue, Mar 26, 2013 at 11:10 PM, Paul Hastings wrote: > > > SQL Server 2005 does not support UTF-8 apparently. > > sure it does. > No, it doesn't. Not really. http://msdn.microsoft.com/en-us/library/bb330962(v=sql.90).aspx I'm loading this data from UTF-8 encoded files coming from an SAP system, and I'm loading them using the BULK INSERT command. Even SQL Server 2012 doesn't support UTF-8. Look at this page ( http://msdn.microsoft.com/en-us/library/ms188365.aspx) and find "UTF-8") Ultimately, my "workaround" here is that I found the specific garbage strings after loading the data and replacing them with a sql udf I wrote that basically does this: set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(163),'"') set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(165),'"') set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(214),'''') set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(244),'-') set @comment = replace(@comment,nchar(915)+nchar(199)+nchar(201),'-') 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:355358 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Optimising SQL Statement
Hi, We have a problem with one of our MySQL statements and wondering if you guys can help point us in the right direction. Basically the following statement is taking 5 seconds to run. We have diagnosed it is down to the join of two select statement. When the select statements are run individually they take only 0.2 seconds but when combined with the JOIN it takes 5 seconds. WE have been told then when MySQL performs a join it creates temporary tables in the background. Is this correct? Is there anything you can see that we are doing wrong or can you see a better way? code start -- SELECT temp_4.primaryid, temp_1.`subjectID` , temp_4.`testOccasionID` ,`studyNumbers` ,`testDate` FROM ( SELECT * FROM ( SELECT primarys.primaryid , q_1 AS `subjectID` , q_2 AS `studyNumbers` FROM primarys LEFT OUTER JOIN questions_1_100 ON primarys.primaryid = questions_1_100.primaryid WHERE 0 = 0 AND q_1 IS NOT NULL GROUP BY primaryid) AS maintable_1 GROUP BY `subjectID` ) AS temp_1 JOIN (SELECT * FROM (SELECT primarys.primaryid , q_1 AS `subjectID` , q_4 AS `testOccasionID` , DATE_FORMAT(q_5, '%m/%d/%Y') AS `testDate` FROM primarys LEFT OUTER JOIN questions_1_100 ON primarys.primaryid = questions_1_100.primaryid WHERE 0 = 0 AND q_1 IS NOT NULL AND q_4 IS NOT NULL GROUP BY primaryid) AS maintable_4 GROUP BY `subjectID` ,`testOccasionID` ) AS temp_4 ON temp_1.`subjectID` = temp_4.`subjectID` code end -- Many thanks Richard ~| 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:355292 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CDC in sql 2008
Does the CDC feature not meet your needs? The best way to do this is at the database level since it will record all the changes people might make directly on the database, bypassing the application. You can supplement this using extra code at the application level, such as passing in the user name to every query, since the database probably doesn't know what user is making the change. The solution usually involves a lot of database triggers and extra columns added to track the user, timestamp, and action. ApexSQL is one company offering an audit tool that helps create these triggers. I remember those triggers being somewhat generic, although it will give you another example to look at. Most of the solutions involving triggers follow a similar design pattern. None of the commercial products would solve the issue of logging which Web site user made the change, which would require custom code both at the application level and the database level. This assumes you are using the same database connection string for every Web site user and that you care about who makes the database changes. -Mike Chabot On Thu, Mar 28, 2013 at 2:33 PM, Asim Manzur wrote: > > I need to keep track the changes in my all updates/deleted in sql. > > I was researching to find the solution and couldn't find anything useful. > other than CDC in sql 2008. > > I don't want to handle this on application level .i.e. saving extra record > in my audit table etc, I want to do it on database level. > > Really interested to know what others are doing > > > ~| 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:355193 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CDC in sql 2008
You could use triggers it may have an impact on the database though. Another option would be to create stored procedures for doing all of your updates and deletes. You could then copy the original rows into another table with a flag saying a delete or update as well as who did it and when. After the data is copied, then you could actually run the update/delete. Steve -Original Message- From: Asim Manzur [mailto:bytel...@gmail.com] Sent: Thursday, March 28, 2013 2:34 PM To: cf-talk Subject: CDC in sql 2008 I need to keep track the changes in my all updates/deleted in sql. I was researching to find the solution and couldn't find anything useful. other than CDC in sql 2008. I don't want to handle this on application level .i.e. saving extra record in my audit table etc, I want to do it on database level. Really interested to know what others are doing ~| 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:355185 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CDC in sql 2008
I need to keep track the changes in my all updates/deleted in sql. I was researching to find the solution and couldn't find anything useful. other than CDC in sql 2008. I don't want to handle this on application level .i.e. saving extra record in my audit table etc, I want to do it on database level. Really interested to know what others are doing ~| 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:355184 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL bulk inserts, ftps, and unicode special characters
On 3/27/2013 3:08 AM, Rick Root wrote: > AMEX � ADR Box > > The em dash seems to come through in my text file is 3 characters when I > view it in notepad++ that's because your data is garbaged or its encoding is either missing or misidentified or i guess notepad++ doesn't understand unicode (not sure, not familiar w/it). UTF-whatever are variable width encodings of unicode. UTF encodes all of the million or so unicode code points as 1-4 bytes (8-bit). if something "bad" happens those encodings can "blow apart" & look like multiple chars. > However, when the data is loaded into SQL Server, it goes in as three > characters. unicode 915-199-71 how are you inserting the data? is the column holding this data unicode capable (for sql server it should be one of the "N" datatypes). > SQL Server 2005 does not support UTF-8 apparently. sure it does. ~| 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:355167 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: (ot) SQL bulk inserts, ftps, and unicode special characters
Sorry, the em dash is 915-199-244, not 915-244-71. a unicode em dash is unicode 2015 .. I'm not sure how these numbers relate though On Tue, Mar 26, 2013 at 4:08 PM, Rick Root wrote: > > Hi all, > > I am getting some data feeds from our SAP system (god help me). Some of > the data contains unicode characters apparently like em dashes and such. > For example, take the following string: > > AMEX ADR Box > > The em dash seems to come through in my text file is 3 characters when I > view it in notepad++ > > IF I open the document in Word, it asks me for a character encoding and I > say UTF 8, and it appears to show the em dash as a single character: > > AMEX ADR (copy paste from word) > > However, when the data is loaded into SQL Server, it goes in as three > characters. unicode 915-199-71 > > In fact, it looks like pretty much all of these special characters start > with unicode 915-199 and then some other character. > > SQL Server 2005 does not support UTF-8 apparently. > > Has anyone run across this problem and implemented some kind of solution? > > My data files are fairly large (1.3GB of data in 26 files), and they are > loaded every night (full replace) > > Rick > > > -- > The beatings will continue until morale improves. > > ~| 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:355154 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
(ot) SQL bulk inserts, ftps, and unicode special characters
Hi all, I am getting some data feeds from our SAP system (god help me). Some of the data contains unicode characters apparently like em dashes and such. For example, take the following string: AMEX ADR Box The em dash seems to come through in my text file is 3 characters when I view it in notepad++ IF I open the document in Word, it asks me for a character encoding and I say UTF 8, and it appears to show the em dash as a single character: AMEX ADR (copy paste from word) However, when the data is loaded into SQL Server, it goes in as three characters. unicode 915-199-71 In fact, it looks like pretty much all of these special characters start with unicode 915-199 and then some other character. SQL Server 2005 does not support UTF-8 apparently. Has anyone run across this problem and implemented some kind of solution? My data files are fairly large (1.3GB of data in 26 files), and they are loaded every night (full replace) Rick -- The beatings will continue until morale improves. ~| 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:355153 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: How would you optimize this SQL?
> Will it even cache the plan without the 1 being a parameter? Sure, by default. If you just run a query like "SELECT * FROM mytable", the database server will cache that execution plan. Parameters usually let you more effectively reuse execution plans, that's all. For example, if you had two queries without parameters, each would have its own execution plan. Now, usually, the execution plans would just be very similar, and it would make sense to have one execution plan that works for any value of the parameter. But with bit/binary values, this is not the case. > In either case, I'd still advocate the use of schema prefixes/aliases. Me too. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:354606 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: How would you optimize this SQL?
Will it even cache the plan without the 1 being a parameter? In either case, I'd still advocate the use of schema prefixes/aliases. On 2/20/13 1:15 PM, "Dave Watts" wrote: > >> You could also add schema prefixes to your tables and columns (or alias >> them) as well as make the 1 a parameter. >> >> That should help with execution plan caching. > >Actually, caching the execution plan with a parameter here is probably >a bad idea - at least half the time! > >When you have a bit value (on/off) as a parameter, the execution plan >will either be very very good, or very very bad. You're better off not >using a parameter (which will give you two execution plans ultimately >that are more specific and both very very good) or using WITH >RECOMPILE to avoid the use of any cached execution plans at all. > >Dave Watts, CTO, Fig Leaf Software >http://www.figleaf.com/ >http://training.figleaf.com/ > >Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on >GSA Schedule, and provides the highest caliber vendor-authorized >instruction at our training centers, online, or onsite. > > ~| 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:354603 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: How would you optimize this SQL?
> You could also add schema prefixes to your tables and columns (or alias > them) as well as make the 1 a parameter. > > That should help with execution plan caching. Actually, caching the execution plan with a parameter here is probably a bad idea - at least half the time! When you have a bit value (on/off) as a parameter, the execution plan will either be very very good, or very very bad. You're better off not using a parameter (which will give you two execution plans ultimately that are more specific and both very very good) or using WITH RECOMPILE to avoid the use of any cached execution plans at all. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| 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:354602 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: How would you optimize this SQL?
You could also add schema prefixes to your tables and columns (or alias them) as well as make the 1 a parameter. That should help with execution plan caching. Declare @myBit int Set @myBit = 1 SELECT @intCourseCompletions = COUNT(m.intMemberStageID) FROM dbo.tblMemberStages m WHERE m.bitCompleted = @myBit; On 2/20/13 11:30 AM, "Torrent Girl" wrote: > >> Well, that code would be equivalent to: >> >> SELECT @intCourseCompletions = COUNT(intMemberStageID) FROM >> tblMemberStages WHERE bitCompleted = 1; >> >> However I believe to get any performance increase you will need an >> index on bitCompleted. >> >> CREATE INDEX myindexname ON tblMemberStages (bitCompleted); >> >> You could also try: SELECT @intCourseCompletions = >> sum(cast(bitCompleted as int)) FROM tblMemberStages; >> >> The conversion to INT may or may not take longer. The index is >> probably your best bet but try both ways to see which works best. >> >> Steve >> >> -Original Message- >> From: Torrent Girl [mailto:moniqueb...@gmail.com] >> Sent: Wednesday, February 20, 2013 11:07 AM >> To: cf-talk >> Subject: How would you optimize this SQL? >> >> >Thank you. > > > > ~| 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:354597 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: How would you optimize this SQL?
> Well, that code would be equivalent to: > > SELECT @intCourseCompletions = COUNT(intMemberStageID) FROM > tblMemberStages WHERE bitCompleted = 1; > > However I believe to get any performance increase you will need an > index on bitCompleted. > > CREATE INDEX myindexname ON tblMemberStages (bitCompleted); > > You could also try: SELECT @intCourseCompletions = > sum(cast(bitCompleted as int)) FROM tblMemberStages; > > The conversion to INT may or may not take longer. The index is > probably your best bet but try both ways to see which works best. > > Steve > > -Original Message- > From: Torrent Girl [mailto:moniqueb...@gmail.com] > Sent: Wednesday, February 20, 2013 11:07 AM > To: cf-talk > Subject: How would you optimize this SQL? > > Thank you. ~| 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:354596 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: How would you optimize this SQL?
Well, that code would be equivalent to: SELECT @intCourseCompletions = COUNT(intMemberStageID) FROM tblMemberStages WHERE bitCompleted = 1; However I believe to get any performance increase you will need an index on bitCompleted. CREATE INDEX myindexname ON tblMemberStages (bitCompleted); You could also try: SELECT @intCourseCompletions = sum(cast(bitCompleted as int)) FROM tblMemberStages; The conversion to INT may or may not take longer. The index is probably your best bet but try both ways to see which works best. Steve -Original Message- From: Torrent Girl [mailto:moniqueb...@gmail.com] Sent: Wednesday, February 20, 2013 11:07 AM To: cf-talk Subject: How would you optimize this SQL? Hello all I was told that the following sql is taking too long to run but the person doing load testing: SELECT @intCourseCompletions=(SELECT COUNT(intMemberStageID) as completions FROM tblMemberStages WHERE bitCompleted=1) It is a stored proc. Any suggestions on how I can optimize it for better performance? I just want to count the number of completions. ~| 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:354594 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
How would you optimize this SQL?
Hello all I was told that the following sql is taking too long to run but the person doing load testing: SELECT @intCourseCompletions=(SELECT COUNT(intMemberStageID) as completions FROM tblMemberStages WHERE bitCompleted=1) It is a stored proc. Any suggestions on how I can optimize it for better performance? I just want to count the number of completions. ~| 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:354593 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Truncating pages SQL 2000
Hi Rob, It is working fine now, many thanks for your reply. CF just wasn't retrieving past the 64000 value set in admin. I really should have spotted it. Cheers, Jenny -Original Message- From: Rob Parkhill [mailto:robert.parkh...@gmail.com] Sent: 18 February 2013 15:48 To: cf-talk Subject: RE: Truncating pages SQL 2000 So, the data is fine in the db, but not displaying all of it? What does the variable look like upon retrieval? Is there something odd in the data that it's causing the truncation? On 2013-02-18 10:39 AM, "Jenny Gavin-Wear" wrote: > > Actually, looking at it again. It's not the writing of the data, it's > the retrieval, so no actual truncation takes place? > > -Original Message- > From: Jenny Gavin-Wear [mailto:jenn...@fasttrackonline.co.uk] > Sent: 18 February 2013 15:28 > To: cf-talk > Subject: RE: Truncating pages SQL 2000 > > > Hi Rob, > > Thanks for your reply. I'm getting rusty! I forgot to enable long > text retrieval on a new server. > > Cheers, > Jenny > > -Original Message- > From: Rob Parkhill [mailto:robert.parkh...@gmail.com] > Sent: 18 February 2013 13:49 > To: cf-talk > Subject: Re: Truncating pages SQL 2000 > > > Jenny, > > There is a limit on the amount of text that can be written to the > database in CF. It's a part of the advanced settings for the database connection. > Limit is 65000 characters by default. You can up that to anything. > > Hope that helps, > > Rob > On 2013-02-18 8:06 AM, "Jenny Gavin-Wear" > > wrote: > > > > > Hi all, > > > > Is there anything that could cause truncation of data in an ntext > > field, either by sql or coldfusion, or some other way? > > > > I have a CMS app that has been running a site for a few years and > > the content field of the CMS pages has become truncated. I need to > > determine whether this was user error, or if it could have happened > > in > any > other way. > > > > Many thanks, > > Jenny > > > > > > -- > > I am using the free version of SPAMfighter. > > SPAMfighter has removed 8643 of my spam emails to date. > > Get the free SPAMfighter here: http://www.spamfighter.com/len > > > > Do you have a slow PC? Try a Free scan > > http://www.spamfighter.com/SLOW-PCfighter?cid=sigen > > > > > > > > > > > > > > ~| 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:354590 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Truncating pages SQL 2000
So, the data is fine in the db, but not displaying all of it? What does the variable look like upon retrieval? Is there something odd in the data that it's causing the truncation? On 2013-02-18 10:39 AM, "Jenny Gavin-Wear" wrote: > > Actually, looking at it again. It's not the writing of the data, it's the > retrieval, so no actual truncation takes place? > > -Original Message- > From: Jenny Gavin-Wear [mailto:jenn...@fasttrackonline.co.uk] > Sent: 18 February 2013 15:28 > To: cf-talk > Subject: RE: Truncating pages SQL 2000 > > > Hi Rob, > > Thanks for your reply. I'm getting rusty! I forgot to enable long text > retrieval on a new server. > > Cheers, > Jenny > > -Original Message- > From: Rob Parkhill [mailto:robert.parkh...@gmail.com] > Sent: 18 February 2013 13:49 > To: cf-talk > Subject: Re: Truncating pages SQL 2000 > > > Jenny, > > There is a limit on the amount of text that can be written to the database > in CF. It's a part of the advanced settings for the database connection. > Limit is 65000 characters by default. You can up that to anything. > > Hope that helps, > > Rob > On 2013-02-18 8:06 AM, "Jenny Gavin-Wear" > wrote: > > > > > Hi all, > > > > Is there anything that could cause truncation of data in an ntext > > field, either by sql or coldfusion, or some other way? > > > > I have a CMS app that has been running a site for a few years and the > > content field of the CMS pages has become truncated. I need to > > determine whether this was user error, or if it could have happened in > any > other way. > > > > Many thanks, > > Jenny > > > > > > -- > > I am using the free version of SPAMfighter. > > SPAMfighter has removed 8643 of my spam emails to date. > > Get the free SPAMfighter here: http://www.spamfighter.com/len > > > > Do you have a slow PC? Try a Free scan > > http://www.spamfighter.com/SLOW-PCfighter?cid=sigen > > > > > > > > > > > > > > ~| 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:354573 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Truncating pages SQL 2000
Actually, looking at it again. It's not the writing of the data, it's the retrieval, so no actual truncation takes place? -Original Message- From: Jenny Gavin-Wear [mailto:jenn...@fasttrackonline.co.uk] Sent: 18 February 2013 15:28 To: cf-talk Subject: RE: Truncating pages SQL 2000 Hi Rob, Thanks for your reply. I'm getting rusty! I forgot to enable long text retrieval on a new server. Cheers, Jenny -Original Message- From: Rob Parkhill [mailto:robert.parkh...@gmail.com] Sent: 18 February 2013 13:49 To: cf-talk Subject: Re: Truncating pages SQL 2000 Jenny, There is a limit on the amount of text that can be written to the database in CF. It's a part of the advanced settings for the database connection. Limit is 65000 characters by default. You can up that to anything. Hope that helps, Rob On 2013-02-18 8:06 AM, "Jenny Gavin-Wear" wrote: > > Hi all, > > Is there anything that could cause truncation of data in an ntext > field, either by sql or coldfusion, or some other way? > > I have a CMS app that has been running a site for a few years and the > content field of the CMS pages has become truncated. I need to > determine whether this was user error, or if it could have happened in any other way. > > Many thanks, > Jenny > > > -- > I am using the free version of SPAMfighter. > SPAMfighter has removed 8643 of my spam emails to date. > Get the free SPAMfighter here: http://www.spamfighter.com/len > > Do you have a slow PC? Try a Free scan > http://www.spamfighter.com/SLOW-PCfighter?cid=sigen > > > > ~| 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:354568 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Truncating pages SQL 2000
Hi Rob, Thanks for your reply. I'm getting rusty! I forgot to enable long text retrieval on a new server. Cheers, Jenny -Original Message- From: Rob Parkhill [mailto:robert.parkh...@gmail.com] Sent: 18 February 2013 13:49 To: cf-talk Subject: Re: Truncating pages SQL 2000 Jenny, There is a limit on the amount of text that can be written to the database in CF. It's a part of the advanced settings for the database connection. Limit is 65000 characters by default. You can up that to anything. Hope that helps, Rob On 2013-02-18 8:06 AM, "Jenny Gavin-Wear" wrote: > > Hi all, > > Is there anything that could cause truncation of data in an ntext > field, either by sql or coldfusion, or some other way? > > I have a CMS app that has been running a site for a few years and the > content field of the CMS pages has become truncated. I need to > determine whether this was user error, or if it could have happened in any other way. > > Many thanks, > Jenny > > > -- > I am using the free version of SPAMfighter. > SPAMfighter has removed 8643 of my spam emails to date. > Get the free SPAMfighter here: http://www.spamfighter.com/len > > Do you have a slow PC? Try a Free scan > http://www.spamfighter.com/SLOW-PCfighter?cid=sigen > > > > ~| 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:354565 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Truncating pages SQL 2000
Jenny, There is a limit on the amount of text that can be written to the database in CF. It's a part of the advanced settings for the database connection. Limit is 65000 characters by default. You can up that to anything. Hope that helps, Rob On 2013-02-18 8:06 AM, "Jenny Gavin-Wear" wrote: > > Hi all, > > Is there anything that could cause truncation of data in an ntext field, > either by sql or coldfusion, or some other way? > > I have a CMS app that has been running a site for a few years and the > content field of the CMS pages has become truncated. I need to determine > whether this was user error, or if it could have happened in any other way. > > Many thanks, > Jenny > > > -- > I am using the free version of SPAMfighter. > SPAMfighter has removed 8643 of my spam emails to date. > Get the free SPAMfighter here: http://www.spamfighter.com/len > > Do you have a slow PC? Try a Free scan > http://www.spamfighter.com/SLOW-PCfighter?cid=sigen > > > > ~| 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:354559 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Truncating pages SQL 2000
Hi all, Is there anything that could cause truncation of data in an ntext field, either by sql or coldfusion, or some other way? I have a CMS app that has been running a site for a few years and the content field of the CMS pages has become truncated. I need to determine whether this was user error, or if it could have happened in any other way. Many thanks, Jenny -- I am using the free version of SPAMfighter. SPAMfighter has removed 8643 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len Do you have a slow PC? Try a Free scan http://www.spamfighter.com/SLOW-PCfighter?cid=sigen ~| 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:354558 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
Yes indeed. We had some attempts to injection attack via a fake useragent variable in the CGI scope, as we were logging visiting useragents in a database table. Luckily they were not able to execute any code thanks to tight SQL permissions, but the code they were trying to execute was written to the table. I'd not even thought of that method till we saw it. But something to have an eye on. Regards, Ian. On 23/01/2013 19:09, Pete Freitag wrote: > On Wed, Jan 23, 2013 at 12:57 PM, Rob Voyle wrote: > > >> Hi Greg >> As I continue to update my security processes, I'm curious >> Was this injection attempt at the url or at a form input. >> >> > Keep in mind that vulnerabilites can come from any input that the attacker > can manipulate, eg form, url, cgi, cookie variables are all game. > > -- > Pete Freitag - Adobe Community Professional > http://foundeo.com/ - ColdFusion Consulting& Products > http://hackmycf.com - Is your ColdFusion Server Secure? > http://www.youtube.com/watch?v=ubESB87vl5U - FuseGuard your CFML in 10 > minutes > > > ~| 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:354042 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Sending a newID() SQL rs to a different table upon submit.
Hello,
I've been doing pretty well w/ my site so far but the powers-that-be requested
a last minute addendum to the code and I'm not sure how to write it. I was
hoping you folks could be of assistance to this developer-in-training.
As it stands, a certain page (random.cfm) will pull info based on the url.id
for a client company (demographics mainly), then accept two numbers: the first
being the number of that clients' employees they would like to drug-screen, and
the second being a few 'backup' employees in case one of the primarily chosen
employees is out sick or something like that. I have that portion of the page
working. In fact, if the end-user is for whatever reason dissatisfied with
their initial recordset, they can press the "Enter" button again and have a
different random set of donors pulled.
The addendum is we wish to have a button that, when pressed, takes the CURRENT
randomized (by newID() ) recordset and COMMITS it (all fields from the 'rs'
query with the addition of the company name and time/datestamp) to a different
table, called DONOR_LOG. Here, each record from the randomized recordset will
be given its own record as well, and will be able to be pulled up from a second
page on the site (but I don't imagine the coding of that to be very difficult).
I'm certain my problem is in the last block, but if someone could point
out those mistakes, I am still learning CF after all, and perhaps explain to me
a more elegant approach to effecting this functionality, I would be very
grateful. I assume it has something to do with me either reusing 'i' as an
index or the block of nested within the last block. Here is
the code, thanks in advance, all!
SELECT clientid,clientname,derFname,derLname,derPhone,derExt,derFax
FROM CLIENTS WHERE clientid='#url.id#'
SELECT kcid FROM donor WHERE company='#form.company#'
Random Donor Selection
#prtBtn{display:none;}
#entBtn{display:none;}
#homBtn{display:none;}
#comBtn{display:none;}
Company:#rsClient.clientname#
D.E.R. for #rsClient.clientname#:#rsClient.derFname#
#rsClient.derLname#
Total Employees:#numberRecords#
D.E.R. Phone No.:#rsClient.derPhone#
Ext. #rsClient.derExt#
Date:#DateFormat(now(), ' d, ')#
D.E.R. Fax No.:#rsClient.derFax#
Random Donors:
Alternates:
SELECT TOP #rows# * FROM DONOR WHERE company='#form.company#' ORDER BY newid()
EID
FIRST NAME
LAST NAME
SUPERVISOR
PHONE NO.
SSN
---PRIMARIES---
#rs.eid[i]#
#rs.first[i]#
#rs.last[i]#
#rs.supe[i]#
#rs.phone[i]#
#rs.ssn[i]#
---ALTERNATES---
INSERT INTO DONOR_LOG(
eid,
first,
last,
supe,
phone,
ssn,
company,
dos)
VALUES(
'#commit.eid[i]#',
'#commit.first[i]#',
'#commit.last[i]#',
'#commit.supe[i]#',
'#commit.phone[i]#',
'#commit.ssn[i]#',
'#commit.company[i]#',
'#dateFormat(now())#')
~|
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:354040
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
On Wed, Jan 23, 2013 at 12:57 PM, Rob Voyle wrote: > > Hi Greg > As I continue to update my security processes, I'm curious > Was this injection attempt at the url or at a form input. > Keep in mind that vulnerabilites can come from any input that the attacker can manipulate, eg form, url, cgi, cookie variables are all game. -- Pete Freitag - Adobe Community Professional http://foundeo.com/ - ColdFusion Consulting & Products http://hackmycf.com - Is your ColdFusion Server Secure? http://www.youtube.com/watch?v=ubESB87vl5U - FuseGuard your CFML in 10 minutes ~| 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:354032 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
It was attempted via the URL On Wed, Jan 23, 2013 at 11:57 AM, Rob Voyle wrote: > > Hi Greg > As I continue to update my security processes, I'm curious > Was this injection attempt at the url or at a form input. > > Thanks > Rob > > On 22 Jan 2013 at 11:12, Greg Morphis wrote: > > > > > I saw some request errors but what were they trying to do? > > This is what the onRequest error email showed > > > > "declare @q varchar(8000) select @q = > > 0x57414954464F522044454C4159202730303A30303A313527 exec(@q)" > > > > > > > > ~| > > 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:35 > > 3998 > > Subscription: > > http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm > > Unsubscribe: > > http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm > > > > > ~| 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:354031 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
Hi Greg As I continue to update my security processes, I'm curious Was this injection attempt at the url or at a form input. Thanks Rob On 22 Jan 2013 at 11:12, Greg Morphis wrote: > > I saw some request errors but what were they trying to do? > This is what the onRequest error email showed > > "declare @q varchar(8000) select @q = > 0x57414954464F522044454C4159202730303A30303A313527 exec(@q)" > > > > ~| > 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:35 > 3998 > Subscription: > http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm > Unsubscribe: > http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm ~| 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:354030 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
> Ah so they were just checking to see if they could get something to work > before possibly trying anything real. That's a pretty standard approach. If they can get the response to delay then they can mark that URL as a potential entry point to come back and explore more later. -Justin ~| 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:354001 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
Ah so they were just checking to see if they could get something to work before possibly trying anything real. Thanks! On Tue, Jan 22, 2013 at 11:15 AM, John M Bliss wrote: > > That's hex for, "?WAITFOR DELAY '00:00:15'" > > On Tue, Jan 22, 2013 at 11:12 AM, Greg Morphis wrote: > > > 0x57414954464F522044454C4159202730303A30303A313527 > > > > > > > -- > John Bliss - http://about.me/jbliss > > > ~| 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:354000 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: sql injection attempt
That's hex for, "?WAITFOR DELAY '00:00:15'" On Tue, Jan 22, 2013 at 11:12 AM, Greg Morphis wrote: > 0x57414954464F522044454C4159202730303A30303A313527 > -- John Bliss - http://about.me/jbliss ~| 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:353999 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
sql injection attempt
I saw some request errors but what were they trying to do? This is what the onRequest error email showed "declare @q varchar(8000) select @q = 0x57414954464F522044454C4159202730303A30303A313527 exec(@q)" ~| 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:353998 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: form-post to SQL-insert creates double-entry
If you add a "name" attribute to your submit button: then when the form is submitted, there will be a corresponding "submit" key added to the form scope. Then, as others have suggested, wrap your query in: //Your query here Again, as the others have stated, this will cause the query to only be executed if the form is actually submitted, rather than on every page load. HTH, -Carl V ~| 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:353940 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: form-post to SQL-insert creates double-entry
during your logic to action something on the page validate it in some slight way. EG. TAKE SOME ACTION On Tue, Jan 15, 2013 at 1:44 PM, Matt Quackenbush wrote: > > You are running the insert query each time you load the page. Remove your > s on the form fields, and wrap the query in an . > > > > > > HTH > > > On Tue, Jan 15, 2013 at 2:34 PM, B Griffith wrote: > >> >> Hello All, >> >> I appreciate everyone's help on my other posts and think I'm coming to >> understand CF a lot better than I did before I started posting here a few >> days ago, so kudos to you, friends! ~| 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:353915 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: form-post to SQL-insert creates double-entry
the better approach is to go ahead and keep the cfparam, and then
during your logic to action something on the page validate it in some
slight way.
EG.
TAKE SOME ACTION
On Tue, Jan 15, 2013 at 1:44 PM, Matt Quackenbush wrote:
>
> You are running the insert query each time you load the page. Remove your
> s on the form fields, and wrap the query in an .
>
>
>
>
>
> HTH
>
>
> On Tue, Jan 15, 2013 at 2:34 PM, B Griffith wrote:
>
>>
>> Hello All,
>>
>> I appreciate everyone's help on my other posts and think I'm coming to
>> understand CF a lot better than I did before I started posting here a few
>> days ago, so kudos to you, friends!
>>
>> My latest issue w/ my burgeoning new website is the input.cfm page, where
>> an end-user may (in theory) enter a donor's information into a webform,
>> click submit, and the data will subsequently be inserted into the SQL
>> Server (2005) table dbo.DONOR, which while it's under development, has only
>> six fields: kcid (auto-incrementing PK/UID for the database) and the other
>> five which you see in the code below and are probably pretty
>> self-explanatory. I have tried creating an empty structure called 'form'
>> before applying the code and finally settled on a block of
>> statements to set the vars to a default. I can't see a way around this
>> because NOT doing so causes the form to indeed show at the top of the
>> webpage, but it is followed by a CF error page. INCLUDING the
>> tags or or form = structNew(); causes the error output to
>> go away but results in TWO recordsets being sent to the DB, one blank
>> (except for the PK which is set up to auto-increment in SQL) and one with
>> the correct info, and taking the next PK number. I also tried setting the
>> fields to NOTNULL = TRUE in SQL but to no avail, apparently "" translates
>> into something besides null on the server-side. Here is the code, I would
>> greatly appreciate your thoughts on what I'm doing wrong and how to rectify
>> it:
>>
>>
>>
>> Input form for new donor
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> Donor First Name:
>>
>>
>>
>> Donor Last Name:
>>
>>
>>
>> Donor's Immediate Supervisor:
>>
>>
>>
>> Supervisor's Phone Number:
>>
>>
>>
>> Has this employee provided a sample in the past 30 days?
>> (Y/N):
>>
>>
>>
>>
>>
>> Back to homepage
>> Random Donor Generator
>>
>>
>>
>> INSERT INTO DONOR (first,last,flag,supe,phone)
>> VALUES
>> ('#form.first#','#form.last#','#form.flag#','#form.supe#','#form.phone#')
>>
>>
>>
>
>
~|
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:353914
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: form-post to SQL-insert creates double-entry
Fellas, Thank you very much! It worked like a charm. And I'm taking your suggestion re: data scrubbing/validation, I have a CF8 book that will hopefully shed a little light on that subject. I only just noticed there is a "ColdFusion Newbie" forum here and that is probably where my posts/issues fit as I just started developing in CF a couple months ago and before then had only cursory knowledge of writing HTML. Thanks again for your help and hope you all have a good day! ~| 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:353898 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: form-post to SQL-insert creates double-entry
> The FORM structure itself always exists. So the check needs to be on one of the individual fields, or you could check whether it is non-empty like Matt suggested. As an aside, once you get this solved you should read up cfqueryparam too. Among other things it helps protect against sql injection http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html -Leigh ~| 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:353897 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: form-post to SQL-insert creates double-entry
You are running the insert query each time you load the page. Remove your
s on the form fields, and wrap the query in an .
HTH
On Tue, Jan 15, 2013 at 2:34 PM, B Griffith wrote:
>
> Hello All,
>
> I appreciate everyone's help on my other posts and think I'm coming to
> understand CF a lot better than I did before I started posting here a few
> days ago, so kudos to you, friends!
>
> My latest issue w/ my burgeoning new website is the input.cfm page, where
> an end-user may (in theory) enter a donor's information into a webform,
> click submit, and the data will subsequently be inserted into the SQL
> Server (2005) table dbo.DONOR, which while it's under development, has only
> six fields: kcid (auto-incrementing PK/UID for the database) and the other
> five which you see in the code below and are probably pretty
> self-explanatory. I have tried creating an empty structure called 'form'
> before applying the code and finally settled on a block of
> statements to set the vars to a default. I can't see a way around this
> because NOT doing so causes the form to indeed show at the top of the
> webpage, but it is followed by a CF error page. INCLUDING the
> tags or or form = structNew(); causes the error output to
> go away but results in TWO recordsets being sent to the DB, one blank
> (except for the PK which is set up to auto-increment in SQL) and one with
> the correct info, and taking the next PK number. I also tried setting the
> fields to NOTNULL = TRUE in SQL but to no avail, apparently "" translates
> into something besides null on the server-side. Here is the code, I would
> greatly appreciate your thoughts on what I'm doing wrong and how to rectify
> it:
>
>
>
> Input form for new donor
>
>
>
>
>
>
>
>
>
>
>
>
>
> Donor First Name:
>
>
>
> Donor Last Name:
>
>
>
> Donor's Immediate Supervisor:
>
>
>
> Supervisor's Phone Number:
>
>
>
> Has this employee provided a sample in the past 30 days?
> (Y/N):
>
>
>
>
>
> Back to homepage
> Random Donor Generator
>
>
>
> INSERT INTO DONOR (first,last,flag,supe,phone)
> VALUES
> ('#form.first#','#form.last#','#form.flag#','#form.supe#','#form.phone#')
>
>
>
~|
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:353895
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: form-post to SQL-insert creates double-entry
Oops- forgot that you had CFPARAMs for all the variables. You'd need to
remove those with the conditional.
-Original Message-
From: Dave Jemison [mailto:djemi...@vinesse.com]
Sent: Tuesday, January 15, 2013 12:46 PM
To: 'cf-talk@houseoffusion.com'
Subject: RE: form-post to SQL-insert creates double-entry
Do you mean that all the code on one page (input.cfm)?
If so, this about it logically. The addDonor query is processed every time
the page is loaded- once where the user enters the data (blank data except
for the PK) and a second time (with the user entered data).
You need a conditional around the query to only fire if the form has been
submitted:
INSERT INTO DONOR
(first,last,flag,supe,phone) VALUES
('#form.first#','#form.last#','#form.flag#','#form.supe#','#form.phone#')
Additionally, you need to do some data scrubbing before the cfquery to
prevent SQL injection.
- Dave
-Original Message-
From: listmas...@houseoffusion.com [mailto:listmas...@houseoffusion.com] On
Behalf Of B Griffith
Sent: Tuesday, January 15, 2013 12:34 PM
To: cf-talk
Subject: form-post to SQL-insert creates double-entry
Hello All,
I appreciate everyone's help on my other posts and think I'm coming to
understand CF a lot better than I did before I started posting here a few
days ago, so kudos to you, friends!
My latest issue w/ my burgeoning new website is the input.cfm page, where an
end-user may (in theory) enter a donor's information into a webform, click
submit, and the data will subsequently be inserted into the SQL Server
(2005) table dbo.DONOR, which while it's under development, has only six
fields: kcid (auto-incrementing PK/UID for the database) and the other five
which you see in the code below and are probably pretty self-explanatory. I
have tried creating an empty structure called 'form' before applying the
code and finally settled on a block of statements to set the vars
to a default. I can't see a way around this because NOT doing so causes the
form to indeed show at the top of the webpage, but it is followed by a CF
error page. INCLUDING the tags or or form =
structNew(); causes the error output to go away but results in TWO
recordsets being sent to the DB, one blank (except for the PK which is set
up to auto-increment in SQL) and one with the correct info, and taking the
next PK number. I also tried setting the fields to NOTNULL = TRUE in SQL
but to no avail, apparently "" translates into something besides null on the
server-side. Here is the code, I would greatly appreciate your thoughts on
what I'm doing wrong and how to rectify it:
Input form for new donor
Donor First Name:
Donor Last Name:
Donor's Immediate Supervisor:
Supervisor's Phone Number:
Has this employee provided a sample in the past 30 days?
(Y/N):
Back to homepage
Random Donor Generator
INSERT INTO DONOR
(first,last,flag,supe,phone) VALUES
('#form.first#','#form.last#','#form.flag#','#form.supe#','#form.phone#')
~|
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:353894
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: form-post to SQL-insert creates double-entry
Do you mean that all the code on one page (input.cfm)?
If so, this about it logically. The addDonor query is processed every time
the page is loaded- once where the user enters the data (blank data except
for the PK) and a second time (with the user entered data).
You need a conditional around the query to only fire if the form has been
submitted:
INSERT INTO DONOR
(first,last,flag,supe,phone) VALUES
('#form.first#','#form.last#','#form.flag#','#form.supe#','#form.phone#')
Additionally, you need to do some data scrubbing before the cfquery to
prevent SQL injection.
- Dave
-Original Message-
From: listmas...@houseoffusion.com [mailto:listmas...@houseoffusion.com] On
Behalf Of B Griffith
Sent: Tuesday, January 15, 2013 12:34 PM
To: cf-talk
Subject: form-post to SQL-insert creates double-entry
Hello All,
I appreciate everyone's help on my other posts and think I'm coming to
understand CF a lot better than I did before I started posting here a few
days ago, so kudos to you, friends!
My latest issue w/ my burgeoning new website is the input.cfm page, where an
end-user may (in theory) enter a donor's information into a webform, click
submit, and the data will subsequently be inserted into the SQL Server
(2005) table dbo.DONOR, which while it's under development, has only six
fields: kcid (auto-incrementing PK/UID for the database) and the other five
which you see in the code below and are probably pretty self-explanatory. I
have tried creating an empty structure called 'form' before applying the
code and finally settled on a block of statements to set the vars
to a default. I can't see a way around this because NOT doing so causes the
form to indeed show at the top of the webpage, but it is followed by a CF
error page. INCLUDING the tags or or form =
structNew(); causes the error output to go away but results in TWO
recordsets being sent to the DB, one blank (except for the PK which is set
up to auto-increment in SQL) and one with the correct info, and taking the
next PK number. I also tried setting the fields to NOTNULL = TRUE in SQL
but to no avail, apparently "" translates into something besides null on the
server-side. Here is the code, I would greatly appreciate your thoughts on
what I'm doing wrong and how to rectify it:
Input form for new donor
Donor First Name:
Donor Last Name:
Donor's Immediate Supervisor:
Supervisor's Phone Number:
Has this employee provided a sample in the past 30 days?
(Y/N):
Back to homepage
Random Donor Generator
INSERT INTO DONOR
(first,last,flag,supe,phone) VALUES
('#form.first#','#form.last#','#form.flag#','#form.supe#','#form.phone#')
~|
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:353893
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: form-post to SQL-insert creates double-entry
Try something like this:
INSERT INTO DONOR (first,last,flag,supe,phone)
VALUES
('#form.first#','#form.last#','#form.flag#','#form.supe#','#form.phone#')
On Tue, Jan 15, 2013 at 2:34 PM, B Griffith wrote:
>
> Hello All,
>
> I appreciate everyone's help on my other posts and think I'm coming to
> understand CF a lot better than I did before I started posting here a few
> days ago, so kudos to you, friends!
>
> My latest issue w/ my burgeoning new website is the input.cfm page, where
> an end-user may (in theory) enter a donor's information into a webform,
> click submit, and the data will subsequently be inserted into the SQL
> Server (2005) table dbo.DONOR, which while it's under development, has only
> six fields: kcid (auto-incrementing PK/UID for the database) and the other
> five which you see in the code below and are probably pretty
> self-explanatory. I have tried creating an empty structure called 'form'
> before applying the code and finally settled on a block of
> statements to set the vars to a default. I can't see a way around this
> because NOT doing so causes the form to indeed show at the top of the
> webpage, but it is followed by a CF error page. INCLUDING the
> tags or or form = structNew(); causes the error output to
> go away but results in TWO recordsets being sent to the DB, one blank
> (except for the PK which is set up to auto-increment in SQL) and one with
> the correct info, and taking the next PK number. I also tried setting the
> fields to NOTNULL = TRUE in SQL but to no avail, apparently "" translates
> into something besides null on the server-side. Here is the code, I would
> greatly appreciate your thoughts on what I'm doing wrong and how to rectify
> it:
>
>
>
> Input form for new donor
>
>
>
>
>
>
>
>
>
>
>
>
>
> Donor First Name:
>
>
>
> Donor Last Name:
>
>
>
> Donor's Immediate Supervisor:
>
>
>
> Supervisor's Phone Number:
>
>
>
> Has this employee provided a sample in the past 30 days?
> (Y/N):
>
>
>
>
>
> Back to homepage
> Random Donor Generator
>
>
>
> INSERT INTO DONOR (first,last,flag,supe,phone)
> VALUES
> ('#form.first#','#form.last#','#form.flag#','#form.supe#','#form.phone#')
>
>
>
~|
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:353892
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
form-post to SQL-insert creates double-entry
Hello All,
I appreciate everyone's help on my other posts and think I'm coming to
understand CF a lot better than I did before I started posting here a few days
ago, so kudos to you, friends!
My latest issue w/ my burgeoning new website is the input.cfm page, where an
end-user may (in theory) enter a donor's information into a webform, click
submit, and the data will subsequently be inserted into the SQL Server (2005)
table dbo.DONOR, which while it's under development, has only six fields: kcid
(auto-incrementing PK/UID for the database) and the other five which you see in
the code below and are probably pretty self-explanatory. I have tried creating
an empty structure called 'form' before applying the code and finally settled
on a block of statements to set the vars to a default. I can't see a
way around this because NOT doing so causes the form to indeed show at the top
of the webpage, but it is followed by a CF error page. INCLUDING the
tags or or form = structNew(); causes the error output to go
away but results in TWO recordsets being sent to the DB, one blank (except for
the PK which is set up to auto-increment in SQL) and one with the correct info,
and taking the next PK number. I also tried setting the fields to NOTNULL =
TRUE in SQL but to no avail, apparently "" translates into something besides
null on the server-side. Here is the code, I would greatly appreciate your
thoughts on what I'm doing wrong and how to rectify it:
Input form for new donor
Donor First Name:
Donor Last Name:
Donor's Immediate Supervisor:
Supervisor's Phone Number:
Has this employee provided a sample in the past 30 days? (Y/N):
Back to homepage
Random Donor Generator
INSERT INTO DONOR (first,last,flag,supe,phone)
VALUES ('#form.first#','#form.last#','#form.flag#','#form.supe#','#form.phone#')
~|
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:353891
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Need help with inserting non-standard characters into SQL from CF
The short-circuit worked like a dream Paul, problem solved. Thank you very much for the assistance :) On 4 January 2013 11:18, Paul Hastings wrote: > > On 1/4/2013 6:08 PM, Edward Chanter wrote: > > > > Thanks Paul, the data isn't coming from a form it's being manually > entered > > you mean from a static cf page? and is that page UTF-8? where's the pound > symbol > coming from? > > if you're not using cfqueryparam, make sure to use unicode hinting > (N'text'). > > > Interestingly your reply to this thread shows on my system with my £ > > replaced with a �. That suggests fonts somewhere along the line... > > that's the way it came thru the mail servers. > > > if you want to short circuit this, use the unicode codepoints for those > symbols > > ie, N'#chr(163)#' for the pound sterling symbol. > N'#chr(8364)#' for the euro, etc. > > > ~| 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:353760 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
