Querying Linked Server without OPENQUERY

UmaShankar-Patel, 2014-02-14

I already post an article how to setup Linked Server in SQL Server and querying on linked Server using open query. If you are not aware about this you can go through below link

Also you can create linked server object for SQL Server and other databases in the same way.

In this article I describe step to query on linked server directly through RPC without using OPENQUERY.

Suppose I have a linked server name: PROD with another SQL Server.


If I query


select top 5 *From openquery(PROD,‘select *from tblUser’)


It works fine.


Now if you don’t want to use openquery then

select top 5 * from prod.db_customer.dbo.tbluser


The syntax to execute query on linked server without open query is


LINKEDSERVERNAME.DATABASENAME.SCHEMANAME.TABLENAME


Also you can execute stored procedure, tables, and functions from this approach.

Querying data from a table/View
Execute Stored Procedure

select top 5 * from prod.db_customer.dbo.tbluser

exec prod.db_customer.dbo.[select_recent_requests] ‘123’


Execute above procedure, is it run successfully?

Yes then no problem.

But some time error occurred as “Msg 7411, Level 16, State 1 Server is not configured for RPC”.

To resolve this error go to Properties of your linked server .Check for Server Options.

RPC is used to allow remote procedures calls “from” the linked server.

RPC Out is used to allow remote procedure calls “to” the linked server.























Make both RPC and RPC Out options true and OK.


You can check other properties of linked server at TECHNET.


Now your server is configured for RPC.Execute query

exec prod.db_customer.dbo.[select_recent_requests] ‘123’



Now it works fine.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads