Post reply

IsStoredProcedure

  • 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

  • December 23, 2010 at 6:54 am

    #1265900

    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.

  • December 23, 2010 at 7:03 am

    #1265912

    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?"

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • December 23, 2010 at 7:44 am

    #1265939

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

  • December 26, 2010 at 7:48 am

    #1266312

    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

  • December 26, 2010 at 8:43 am

    #1266316

    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)
    Intro to Tally Tables and Functions

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

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