Execute Task For Each Row in the Temp table

Question

Post reply

Execute Task For Each Row in the Temp table

Nitin Bansal-349869

SSC Enthusiast

Points: 145
More actions
September 20, 2006 at 5:29 am

#153190

hi! i have a task (SP and an active-X Script) to be executed for each row of the Temp Table.
What should I Do. Pls Help...
Regards,
Nitin'

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Holy One SSC Veteran Points: 291 More actions · Answer 1

Dear Newbie;

First of all, I must say that the script below I scammed from somebody else -- apologies to whoever you are!

In the interests of coaching you with proper form, I present a solution that avoids the use of cursors, which would be the usual solution to this problem. The key is that your temp table (or TABLE variable, which is what is used here), contain an identity column:

DECLARE @tbl TABLE(

RowID INT IDENTITY(1, 1),

CompanyName VARCHAR(100),

ContactName VARCHAR(50))

/*Local variables */

DECLARE @ContName VARCHAR(100),

@CompName VARCHAR(50),

@count int, /*create local @@fetch_status*/

@iRow int /*row pointer (index)*/

/* create array simulator */

INSERT @tbl

SELECT CompanyName, ContactName

FROM Suppliers

WHERE ContactName LIKE 'c%'

/*get array Upper Bound (highest ID number)*/

SET @count = @@ROWCOUNT

/*initialize index counter*/

SET @iRow = 1

/*establish loop structure*/

WHILE @iRow <= @count

BEGIN

/*get row values*/

SELECT @ContName = CompanyName, @CompName = ContactName

FROM @tbl

WHERE RowID = @iRow

/*perform operations with single row -- you could call your sp here*/

PRINT 'My cursor row | ' + @ContName + ' | ' + @CompName

/*go to next row*/

SET @iRow = @iRow + 1

END

Regards,

D'Arcy

Nitin Bansal-349869 SSC Enthusiast Points: 145 More actions · Answer 2

Buddy, Thanx for ur suggestion

But the roadblock that i have in the course of success is, I have made use of the same procedure to perform the SQL task that i have to....But the task doesnt ends here .... The Details in the temp table thus formed with the SQL Task....needs to be plugged in to an eMAIL and sent to all the Row Details(to all the Company Names in your case..)

And the maajor thing comes here is that this will be scheduled as a job (daily run...)

so i thought of creating it as an SSIS Package, but is confused with ForEach Container and FOR Loop...

If possible, kindly suggest...

Thanks & Regards

Nitin'

Regards,

Nitin'

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 3

A little more clarity please.

from what I've read:

You have a temp table that contains some data. (Please provide sample of structure of table, and data.)

You need to process the data in the temp table (Please provide example of current processing requirements - i.e. the current SP), and then send the results in an email to a set of recipients?

It sounds like a task for a DATA FLOW task in SSIS, and then a SEND MAIL TASK, although you maybe have to use a EXECUTE SQL TASK to get the data/resultset into the email.

Nitin Bansal-349869 SSC Enthusiast Points: 145 More actions · Answer 4

U got it pretty right buddy,

I have alread tried my hands on wat u have suggested me...But the problem is i m not able to pass the result set thus genrated from the SP that got executed from the Execute SQL Task to the Send Mail Task...

Thanks And Regards

Nitin'

Regards,

Nitin'

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 5

Would it not be possible to orchestrate everything in a top-level SP, which could possible then be invoked by a sp_send_dbmail call, which could be run from SQL Server Agent, and not require SSIS.

SQL Agent Job 1:

-STEP 01: Exec sp_send_dbmail

--usp_DoEverything

---usp_ProcessTemp Table

?

Nitin Bansal-349869 SSC Enthusiast Points: 145 More actions · Answer 6

Yes u r right....But the thing is that sp_sendmail from db requires windows authentication on the db server...which we dont have on the production servers. so in place i m using ActiveX Script to send mails from CDONTS...

Thanks & Regards

Nitin'

Regards,

Nitin'

Nitin Bansal-349869 SSC Enthusiast Points: 145 More actions · Answer 7

Yes u r right....But the thing is that sp_sendmail from db requires windows authentication on the db server...which we dont have on the production servers. so in place i m using ActiveX Script to send mails from CDONTS...

Thanks & Regards

Nitin'

Regards,

Nitin'

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 8

but have you looked at SP_SEND_DBMAIL (SQl2005)?

I've looked over the BOL, and it would appear you can run via SQL authentication. There are some things you can only do with windows authentication, like:

"Database Mail uses the Microsoft Windows security context of the current user to control access to files. Therefore, users who are authenticated with SQL Server Authentication cannot attach files using @file_attachments. Windows does not allow SQL Server to provide credentials from a remote computer to another remote computer. Therefore, Database Mail may not be able to attach files from a network share in cases where the command is run from a computer other than the computer that SQL Server runs on."

HTH