Post reply

OPENROWSET VS LinkedServer

  • June 28, 2012 at 6:29 am

    #260649

    Hi All,

    In one of our Table Functions, OPENROWSET is being used and as per my understanding, we can achieve the same by using Linked Server as well.

    I was asked to fine tune a package where in one of the tasks, OPENROWSET is being used. I am trying to check in Blogs to see which is faster, still wanted to get some expert advice on the same.

    Please suggest. Thanks !!

  • June 28, 2012 at 11:46 am

    #1506768

    Here is the BOL link for OPENROWSET.

    Basically, it is only recommended for adhoc queries to a remote server that is not a linked server. This creates a new connection to the server each time it is called.

    If this function is called on a regular basis, it is advisable to create a linked server and address that directly.

    My impression is that a linked server will perform better.

  • June 28, 2012 at 9:50 pm

    #1506936

    Oddly enough, we use OPENROWSET to get the data from a Linked Server because it performs much better especially against a DB2 linked server.

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

  • June 29, 2012 at 12:27 am

    #1506955

    Thanks for your suggestions Jeff & Jerry.

    In my script, only one place we are using OPENROWSET which is in Table Function however this Table Function is being called about 5 - 6 times.

    So, using Linked Server would probably increase my query performance rather OPENROWSET?

  • June 29, 2012 at 11:10 am

    #1507226

    I would try both and compare the time to return the results.

    Go with the best overall. You may be surprised as to which works best for each different scenario.

  • June 29, 2012 at 4:42 pm

    #1507374

    According to my Exam Study Guide OPENROWSET is discouraged because it presents security risks. Linked Servers is more secure.

  • June 29, 2012 at 7:08 pm

    #1507403

    dan-572483 (6/29/2012)

    According to my Exam Study Guide OPENROWSET is discouraged because it presents security risks. Linked Servers is more secure.

    Ok... since you brought it up... WHAT are the security risks associated with OPENROWSET and WHY are Linked Servers more secure.

    Heh... I'll bet you also think that xp_CmdShell is also a security risk. Most people do. It's also because most people don't actually have a secure server to begin with.

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

  • June 29, 2012 at 7:59 pm

    #1507412

    Since you asked. A Linked Server is more secure because the credentials are stored within the object and not a parameter of the OPENROWSET.

  • June 30, 2012 at 5:08 pm

    #1507506

    Actually, since Dan originally brought it up, I wanted him to answer. I hate it when people say things without a decent explanation as to why something might be true or not.

    Also, you don't need exposed credentials for OPENROWSET if your systems are correctly setup for security.

    SELECT a.*

    FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;[font="Arial Black"]Trusted_Connection=yes;[/font]',

    'SELECT GroupName, Name, DepartmentID

    FROM AdventureWorks.HumanResources.Department

    ORDER BY GroupName, Name') AS a;

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

  • May 22, 2015 at 5:53 am

    #1799968

    I am newbie to linked server concept, i am looking for help to resolve my issue. Please suggest.

    My requirement:

    In DB2 data base I have a stored procedure and from SQL 2008 R2 using Linked server I need to be able to execute stored procedure. I am getting the error while I am trying to execute.

    select * from tablename (4part table name syntax is adhered) and i get the results, which means my linked server DB2 connectivity is fine.

    Then

    exec [LinkedServer].[db2DB].[db2admin].SP_MyStoredProcedure

    Error:

    OLE DB provider "DB2OLEDB" for linked server "LinkedServer" returned message "Routine "*rocedure"?SQL150518145704050?...erver"."SP_MyStoredProcedure"?*?4" (specific name "") is implemented with code in library or path "", function "" which cannot be accessed. Reason code: "". SQLSTATE: 42724, SQLCODE: -444".

    Msg 7212, Level 17, State 1, Line 1

    Could not execute procedure 'SP_MyStoredProcedure' on remote server 'LinkedServer'.

    Here in the error Reason code: "" is empty, no much information is available. Not able to find the root cause for this.

    Need help on this.

Viewing 10 posts - 1 through 9 (of 9 total)

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