IsStoredProcedure

  • 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

  • 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.

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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. 😉

  • 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

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

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

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