RE: SQL Global String Replace

2014-03-06 Thread Robert Harrison

> 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

2014-03-06 Thread Russ Michaels

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

2014-03-06 Thread Robert Harrison

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

2014-03-04 Thread Jeff Garza

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

2014-03-04 Thread Robert Harrison

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

2014-03-04 Thread Ben Forta

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

2014-03-04 Thread Robert Harrison

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

2014-03-04 Thread Jeff Garza

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

2014-03-04 Thread Russ Michaels

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

2014-03-04 Thread Robert Harrison

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