Get the count of a result set

Question

Post reply

Get the count of a result set

kevin4u06

SSCrazy

Points: 2093
More actions
August 15, 2011 at 8:09 am

#246354

Hi I have a sql task which executes sql commands via a variable and the storing the result set as xml.
I have a requirement, when the sql command executes, if there are o records then the package should not send out an email. If the count >0 then it should send a email by transforming the xml result set into html.
how can i calculate the count of records from the xml result set.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 1

Please check this forum

http://qa.sqlservercentral.com/Forums/Topic462732-148-1.aspx

---------------------------------------------------------------------------------------
It begins by taking the first step.

kevin4u06 SSCrazy Points: 2093 More actions · Answer 2

Thanks! Leju. But i dont understand the xml task path

XML Task properties:

OperationType: XPATH

SourceType: Variable

Source: User::varResults

SaveOperationResult: True

Destination: User::varRowCount

SecondOperand: count(//table1)

so in the second operand do we have to type count(//table1)

in the xml source box with operand type as direct input. If that is so then there is a problem. All the sql commands i execute have different table names.

kevin4u06 SSCrazy Points: 2093 More actions · Answer 3

I'll Explain it again.

I have a variable which holds all the sql commands to be executed. So in the sql task the sql source type is variable. So the sql task executes this variable and stores the data in a xml result set. then i have to transform this xml into html and send an email.

Sometimes when a sql command is executed it returns 0 records so i should set up a logic where if count > 0 then send and email.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

My recommendation would be to lookup what @@ROWCOUNT might do for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

MWise SSCarpal Tunnel Points: 4074 More actions · Answer 5

You could add a return value or output of the record count in the execute sql task, store that return value in a variable and then check that the value >0 in your precedence constraint. If it is greater than zero then execute the send mail task otherwise execute a different task or nothing.

MWise

kevin4u06 SSCrazy Points: 2093 More actions · Answer 6

Hi Jeff,

That worked for me......But in order to do that I have to write a stored proc for all the databases which they dont want to do.....