Skip to main content

Linked Servers and OPENQUERY

So how do you access data from one database to another? (specifically talking about SQL SERVER)

One possible way you can do is to create a Linked Server to the database you want to connect to.
The easiest way to do this is to go to Management Studio, right click on "Server Objects" and click "New Linked Server"

Now on the "New Linked Server" dialog you have 2 ways you can create a Linked Server.
1) Create a Linked Server pointing directly to a SQL SERVER instance
2) Create a Linked Server pointing to several set of supported data sources, this includes OlE DB data sources and also SQL SERVER.

I prefer choosing the 2nd option to create a linked server to SQL SERVER, although I can do this easily by using the first option, the 2nd option gives me the flexibility to easily change my data source without effecting any objects that use it.

For an example, if you use option one, you can access an object (eg an SP) in the other database by using a 4 part name, as following

EXEC LinkedServerName.DataBaseName.SchemaName.ObjectName

so in this case if you change the database name, you need to go change all your objects that uses this Linked Server.

However, if we go for the 2nd option you don't need to, if you use OPENQUERY.

OPENQUERY is a way to execute distibuted queries through a linked server, so for an example, you can write a query like this.

SELECT * FROM OPENQUERY(LinkedServerName, "Select * from countries")

The catch here is that you need to pass in the query as a string to openquery, this means that your execution would be a dynamic SQL.
There is an advantage here as well, now the query executes in the other database, and you can ease up the processing on you database, for this to happen you need to make sure that the query that is passed into OPENQUERY returns a limited filtered set of data, that is the query has a restrictive WHERE clause.

The problem with OPENQUERY is that you won't be able to create your query on the fly with your parameters, nor can you pass in a VARCHAR. The only way you can create your query with your parameters is to create a query that encapsulate the OPENQUERY clause itself, execute the dynamic query and populate a table variable.

Let's hope the next version of SQL SERVER will ease up developer effort on using OPENQUERY.

Comments

Post a Comment

Popular posts from this blog

Hosting WCF services on IIS or Windows Services?

There came one of those questions from the client whether to use II7 hosting or windows service hosting for WCF services. I tried recollecting a few points and thought of writing it down. WCF applications can be hosted in 2 main ways - In a Windows service - On IIS 7 and above When WCF was first released, IIS 6 did not support hosting WCF applications that support Non-HTTP communication like Net.TCP or Net.MSMQ and developers had to rely on hosting these services on Windows Services. With the release of IIS 7, it was possible to deploy these Non-Http based applications also on IIS 7. Following are the benefits of using IIS 7 to host WCF applications Less development effort Hosting on Windows service, mandates the creating of a Windows service installer project on windows service and writing code to instantiate the service, whereas the service could just be hosted on IIS by creating an application on IIS, no further development is needed, just the service implementa
4 comments
Read more

The maximum nametable character count quota (16384) has been exceeded

Some of our services were growing and the other day it hit the quote, I could not update the service references, nor was I able to run the WCFTest client. An error is diplayed saying " The maximum nametable character count quota (16384) has been exceeded " The problem was with the mex endpoint, where the XML that was sent was too much for the client to handle, this can be fixed by do the following. Just paste the lines below within the configuration section of the devenve.exe.config and the svcutil.exe.config files found at the locations C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE , C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin Restart IIS and you are done. The detailed error that you get is the following : Error: Cannot obtain Metadata from net.tcp://localhost:8731/ Services/SecurityManager/mex If this is a Windows (R) Communication Foundation service to which you have access, please check that you have enabled metadata publishing at the specified address. F
5 comments
Read more

ASP.NEt 2.0 Viewstate and good practices

View state is one of the most important features of ASP.NET because it enables stateful programming over a stateless protocol such as HTTP. Used without strict criteria, though, the view state can easily become a burden for pages. Since view state is packed with the page, it increases size of HTTP response and request. Fortunately the overall size of the __VIEWSTATE hidden field (in ASP.NET 2.0) in most cases is as small as half the size of the corresponding field in ASP.NET 1.x. The content of the _VIEWSTATE field (in client side) represent the state of the page when it was last processed on the server. Although sent to the client, the view state doesn't contain any information that should be consumed by the client. In ASP.NET 1.x, if you disable view state of controls, some of them are unable to raise events hence control become unusable. When we bind data to a grid, server encodes and put whole grid in to view state, which will increase size of view state (proportional to the
Post a Comment
Read more