OPENROWSET VS LinkedServer

Question

OPENROWSET VS LinkedServer

MSBI Learner

Ten Centuries

Points: 1192
More actions
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 !!

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

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

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

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)

MSBI Learner Ten Centuries Points: 1192 More actions · Answer 3

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?

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 4

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.

dan-572483 SSCoach Points: 15870 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

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)

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 8

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)

Newbie2LinkedServer SSC Enthusiast Points: 167 More actions · Answer 9

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.