IsStoredProcedure

Question

Post reply

IsStoredProcedure

Martin Gleeson

Ten Centuries

Points: 1096
More actions
December 23, 2010 at 3:25 am

#146730

Just wondering if anyone knows what this Execute SQL Task property actually does under the hood?
Have tried playing around with it calling sprocs and sql statements while profiling but darned if I can figure out what difference it actually makes. 🙂
Thanks,
Martin

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

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

ssisdiva SSC Enthusiast Points: 137 More actions · Answer 1

Hi,

This is the information which we can get from the SQL Server Books Online.

Indicates whether the specified SQL statement to be run is a stored procedure. This property is read/write only if the task uses the ADO connection manager. Otherwise the property is read-only and its value is false.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

namratha.vangara (12/23/2010)
Hi,
This is the information which we can get from the SQL Server Books Online.
Indicates whether the specified SQL statement to be run is a stored procedure. This property is read/write only if the task uses the ADO connection manager. Otherwise the property is read-only and its value is false.

That's all well and good, but I think the question is: "so what?"

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Martin Gleeson Ten Centuries Points: 1096 More actions · Answer 3

Yep Phil, you read my mind!

Books online is all well and good but isn't really telling us what effect this property has on the package or how it handles the response from the stored procedure specified in the Execute SQL Task.

The best I can find is this understandably vague explanation by Allan Mitchell: "Certain optimisations are made by the task if it knows that the query is a Stored Procedure invocation. The docs say this will always be false unless the connection is an ADO connection."

http://www.sqlis.com/post/The-Execute-SQL-Task.aspx

I was hoping someone might be able either to point me in the direction of a more in depth explanation of the ins-and-outs of this property or give me a sturdy explanation why it should or shouldn't be set.

Or explain to me that I'm being a bit thick and missed the obvious. 😉

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

There is a property IsStoredProcedure?

Never heard of it 🙂

(that says enough I think :-))

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

I think it's there as a property so that you could "take stock" of what's in your server... sort of like what you might have to do if you were creating a data dictionary or if you saw something in profiler and just wanted to know what it was without having to scan through hundreds of SQL Server objects and without having to resort to sys.objects queries.

--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)