Array?

[nick122211]

Ok this is what I am trying to do. I need to select all rows from one table and feed them into a execute statement and have that statement run for each row of the other table.

Not sure where to start?


declare @ServerName VarChar(100)
begin
select @serverName = (Select server from SMTPAlert.dbo.servers)

exec SMTPAlert..sp_send_cdosysmail @serverName

end

Any help would be much appreciated

 

[gmmastros]

You need to loop through all the servers. I suspect you don't know how many there are, which is why you are asking this question.

Here's how I would do it...

1. Create a table variable with a RowId column set to integer identity(1,1).
2. Loop through this table variable getting each server name
3. Call the stored procedure for each server.

By using a rowid column, you know the records will start with 1 and increment nicely for each record. Set up you loop to go from 1 to Max(RowId). Then, you would just use your loop counter to select the server name from the table variable.

Something like this...

Declare @Temp Table(RowId Integer Identity(1,1), ServerName VarChar(100))

Insert Into @Temp(ServerName) 
Select Server
From   SMTPAlert.dbo.servers

Declare @Max Int
Declare @i Int
Declare @ServerName VarChar(100)

Select  @Max = Max(RowId),
        @i = 1
From    @Temp

While @i <= @Max
  Begin
    Select @ServerName = ServerName
    From   @Temp
    Where  RowId = @i

    exec SMTPAlert..sp_send_cdosysmail @serverName

    Set @i = @i + 1
  End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nick122211]

Thanks that worked. I should have asked earlier. Spent 1/2 of a day trying to figure it out.

 

[gmmastros]

Time well spent. I suspect you learned a little in the process.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom