[thelist] stuck in a loop (classic asp/mssql)

[Brian Cummiskey]

Ian Anderson wrote:

> Or perhaps simpler to use the step option in the for loop?


> for i = 0 to Ubound(aryValues) step 3
>    strSQL = "update " & aryValues(i+2) & ".dbo.Database." & 
> aryValues(i+1) &" set status = 'U' where rowid = '" & aryValues(i) & "'" "
>    ...execute the sql...
> next

Perfect!  I tweaked this a little bit and i no have a working update 
script, cross-server :)

<%
aryValues = Split(request.form("recordid"), ",")

for i = 0 to Ubound(aryValues) step 3
	
	if aryValues(i+2) = "MBC" then
		
		
		server.ScriptTimeout = 9999
		set oConn = Server.CreateObject("ADODB.Connection")
		set comm = Server.CreateObject("ADODB.Command")
		set rst = Server.CreateObject("ADODB.Recordset")
		comm.CommandTimeout = 9999
		comm.CommandText = "sp_Multi_DNC_process"
		comm.CommandType = adCmdStoredProc
		'---- connstr1 = mbc
		oConn.Open connstr1 		
		comm.ActiveConnection = connstr1

		comm.Parameters("@rowid") = aryValues(i)
		comm.Parameters("@jobid") = aryValues(i+1)
		comm.Execute
	
		rowsupdated = comm.Parameters("@rowsupdated")
		rowsdeleted = comm.Parameters("@rowsdeleted")
		set comm = Nothing
	
	
		response.write "<p>rowid " & aryValues(i) & " for " & aryValues(i+1) & 
" has been updated</p>"
	
	end if


	'----- and repeat the if for each server, replacing with connstr2 and 
so forth down the line
Next



and my mirrored SP looks like this:



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO





ALTER    procedure sp_Multi_DNC_process
(
@rowid varchar(50),
@jobid varchar(25),
@rowsupdated int output,
@rowsdeleted int output
)
as
begin

declare @query varchar(4000)

set @rowsupdated = 0
set @rowsdeleted = 0

	SET @query = 'update ' + @jobid + ' set status = ''U'', resultcode = 
''05'', ' +
	'resultother = ''DO NOT CALL'' where rowid =''' + @rowid + ''' ' 	
	exec(@query)
	set @rowsupdated = @rowsupdated + @@rowcount


	set @query = 'delete from ' + @jobid + '_callrecordschedule where rowid 
=''' + @rowid + ''' '
	exec(@query)
	set @rowsdeleted = @rowsdeleted + @@rowcount

end


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO





Thanks guys for all the ideas and suggestions.