Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Querying Microsoft SQL Server

I am a technology enthusiast and software developer by profession. I am developing .Net/database based enterprise applications from past 3 years.

My skills includes C# ,ASP.NET,SQL Server 2008 and MVC . My areas of interests are database development and application software development using Microsoft Technologies.

Querying Linked Server without OPENQUERY

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.

Comments

Leave a comment on the original post [www.queryingsql.com, opens in a new window]

Loading comments...