Forum Moderators: open
********************************************
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open Application("conn")
SQL = "select * from table1;"
Set oRS = oConn.Execute(SQL)
Do while not oRS.EOF
SQL2 = "UPDATE table1 SET URLs = '" & Replace(oRS("URLs"),"http://www.bad.com", "http://www.good.com") & "';"
oConn.Execute(SQL2)
oRS.Movenext
Loop
oRS.Close
Set oRS = Nothing
oConn.Close
Set oRS = Nothing
******************************************
Now... the person who gave that example above wanted to replace one URL in their database with another URL. So I have 2 questions.
1.) Will that script above replace just the characters I want replaced or will it effect the entire column of the table I'm editing? For example, if the database has "This is Mike from Wazoo" and I run a script to replace Mike with Tim, would the script change to "This is Tim from Wazoo"? I just want to make sure it won't replace the entire field. I want only the text I say to be changed... everything else stays the same.
2.) Can someone modify the above code to work with my database (I don't know enough ASP to mess around with it)? My table I need to edit is Products_Descriptions and the column I need to modify is ProductDescription. Lets say my server IP for the DB connection is 1.2.3.4 and my datbase is: database1, login is: login1, and pass is: pass1. For replacing characters I can figure that one out :)
Also... would I just upload that to my server and run it from a web browser?
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=mydatabase "
%>
<%
SQL = "select * from Products_Descriptions;"
Set oRS = cnn.Execute(SQL)
Do while not oRS.EOF
SQL2 = "UPDATE Products_Descriptions SET ProductDescription = '" & Replace(oRS("ProductDescription"),"©", "©") & "';"
cnn.Execute(SQL2)
oRS.Movenext
Loop
oRS.Close
Set oRS = Nothing
cnn.Close
Set oRS = Nothing
%>
Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired
/SQL_update.asp, line 14
Put this in the 2nd line of your page:
<% Server.ScriptTimeout = 3600 %>
This will let it run for 3600 seconds (1 hour). Adjust as needed.
I added your script timeout code on line two but it still timed out after about 30 seconds. Any ideas?
/**********************************************************/
CREATE PROCEDURE spu_textreplace
/*** Proc: spu_textreplace ********************************/
/* Description: */
/* A procedure that does replacement of text in a field */
/* defined by the user and of a TEXT data type. */
/* */
/* The SQL REPLACE fn doesn't work on TEXT data types, so */
/* we have to break the TEXT field into multiple VARCHARs */
/* and do REPLACE on those, then place them back into the */
/* field value through a text pointer. */
/* */
/* It is strongly advised that this script only executes */
/* during a time when nobody will be accessing the data. */
/* */
/* Input: table_name, text_field_name, record_id, */
/* find_text, replace_text */
/* */
/* Developer Task Date */
/* ------------------------------- ---- -------- */
/* Bri Gipson 06-22-2004 */
/* Created */
/* */
/* Bri Gipson 06-25-2004 */
/* Genericised to handle any table/field instead of */
/* just the table and field of a hard-coded object. */
/* (previously meta_data_glob.glob) */
/* */
/* Bri Gipson 06-30-2004 */
/* Encorporated Aaron Bertrand's optimizations to only */
/* commit an UPDATETEXT when necessary and to only do */
/* the update for the specified replacement, not an */
/* entire chunk of text. */
/* [aspfaq.com...] */
/**********************************************************/
/* Input Values */
@table_name VARCHAR(256),
@field_name VARCHAR(256),
@record_id NUMERIC,
@find VARCHAR(256),
@replace VARCHAR(256)
/* Output Values */
-- None
AS
BEGIN
SET NOCOUNT ON
PRINT 'Replacing the text "' + @find + '" with "' + @replace + '" for ' + @table_name + '.' + @field_name
/* local variables */
DECLARE @ptr BINARY(16)
DECLARE @pos INT
DECLARE @current_id NUMERIC
DECLARE @populate VARCHAR(8000)
DECLARE @update VARCHAR(8000)
DECLARE @find_len INT
DECLARE @update_txt_pidx VARCHAR(8000)
SELECT @find_len = DATALENGTH( @find )
CREATE TABLE #glob ( glob_id NUMERIC, glob_ptr BINARY(16), txt_pidx INT )
IF ( @record_id = 0 )
SET @populate = 'INSERT INTO #glob ( glob_id, glob_ptr, txt_pidx ) SELECT ' + @table_name + '_id, tptr = TEXTPTR( ' + @field_name + ' ), PATINDEX(''%' + @find + '%'', ' + @field_name + ' ) FROM ' + @table_name
ELSE
SET @populate = 'INSERT INTO #glob ( glob_id, glob_ptr, txt_pidx ) SELECT ' + @table_name + '_id, tptr = TEXTPTR( ' + @field_name + ' ), PATINDEX(''%' + @find + '%'', ' + @field_name + ' ) FROM ' + @table_name + ' WHERE ' + @table_name + '_id = ' +
CONVERT( VARCHAR(10), @record_id )
EXEC ( @populate )
DECLARE glob_cursor INSENSITIVE CURSOR FOR
SELECT glob_id, glob_ptr
FROM #glob
WHERE txt_pidx > 0
OPEN glob_cursor
FETCH NEXT FROM glob_cursor INTO @current_id, @ptr
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@pos = txt_pidx
FROM
#glob
WHERE
#glob.glob_id = @current_id
WHILE @pos > 0
BEGIN
PRINT 'Found "' + @find + '" in row id ' + CAST( @current_id AS VARCHAR(10) ) + ' at position ' + CAST( @pos AS VARCHAR(10) )
-- Do our replacement:
SELECT @pos = @pos - 1
SET @update =
'DECLARE @ptr BINARY(16) ' +
'SET @ptr = CONVERT( BINARY(16), CONVERT( UNIQUEIDENTIFIER, ''' + CONVERT( VARCHAR(255), CONVERT( UNIQUEIDENTIFIER, @ptr ) ) + ''' ) ) ' +
'UPDATETEXT ' + @table_name + '.' + @field_name + ' ' +
'@ptr ' +
CONVERT( VARCHAR(255), @pos ) + ' ' +
CONVERT( VARCHAR(256), @find_len ) + ' ' +
'''' + @replace + ''''
-- PRINT @update + ''''
EXECUTE ( @update )
SET @update_txt_pidx =
'UPDATE ' +
'#glob ' +
'SET ' +
'txt_pidx = PATINDEX(''%' + @find + '%'', ' + @table_name + '.' + @field_name + ' ) ' +
'FROM ' +
@table_name + ' ' +
'WHERE ' +
@table_name + '_id = ' + CAST( @current_id AS VARCHAR(10) ) + ' AND ' +
'glob_id = ' + CAST( @current_id AS VARCHAR(10) )
-- PRINT @update_txt_pidx + ''''
EXECUTE ( @update_txt_pidx )
SELECT
@pos = txt_pidx
FROM
#glob
WHERE
#glob.glob_id = @current_id
END
FETCH NEXT FROM glob_cursor INTO @current_id, @ptr
END
CLOSE glob_cursor
DEALLOCATE glob_cursor
SET NOCOUNT OFF
END
/* Use these commands to test FROM the command line: */
/* EXECUTE spu_textreplace 'meta_data_glob', 'glob', 0, 'house', 'mouse' */
GO
cnn.ConnectionTimeout = 3600
Right before your:
cnn.Execute(SQL2)
This tells the query to run for 60 minutes also. Note that you need both timeouts bumped up from their defaults - this is not a replacement for the item I suggested before.
I added the code right where you said and got this error:
ADODB.Connection error '800a0e79'
Operation is not allowed when the object is open.
/SQL_update.asp, line 14
Line 14 is the line you had me add. It looks like it may need to go somewhere else in that file?
Put that line of code right after you open your connection (cnn.Open ...), before you open a recordset against the connection.
ADODB.Connection error '800a0e79'
Operation is not allowed when the object is open.
/SQL_update.asp, line 12
That happens when I place it below cnn.open or anywhere else on the page. It seems the only place it doesn't throw an error immediately is when it placed after cnn.Execute(SQL2). But by putting the code there it still times out after 25 or so seconds.
Any other ideas?
