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
