• matt32 (3/30/2010)


    the OPENROWSET within Eli Leibas SP to create a view from the output of another SP:-)

    http://www.sqlservercentral.com/articles/T-SQL/68233/

    this is cool and i use it often

    Let's take a look:

    1. The article uses 'allow updates' - which does not even function in 2005 onward

    2. Ad Hoc Distributed Queries is required to be ON - this is a security vulnerability and not recommended

    4. The view created contains SELECT * - so can never use SCHEMABINDING

    5. The view-creating procedure is named sp_

    6. The SERVER=. syntax only works for a default instance

    7. Object names are SYSNAME = NVARCHAR(128), not VARCHAR(80)

    OPENROWSET-specific problems:

    Since it is implemented in the query plan as a Remote Scan operator:

    1. Fixed cardinality guess of 10,000 rows

    2. No statistics are available to the optimizer

    3. Destroys the ability of a VIEW to be expanded into the calling statement's execution plan

    4. Subject to all the limitations of executing a procedure on a remote, linked server

    ...I could go on. Still think it is cool?