How to Add a Linked Server

Adding a Linked server can be done by either using the GUI interface or the sp_addlinkedserver command.

Adding a linked Server using the GUI

There are two ways to add another SQL Server as a linked server.  Using the first method, you need to specify the actual server name as the “linked server name”.  What this means is that everytime you want to reference the linked server in code, you will use the remote server’s name.  This may not be beneficial because if the linked server’s name changes, then you will have to also change all the code that references the linked server.  I like to avoid this method even though it is easier to initially setup.  The rest of the steps will guide you through setting up a linked server with a custom name:

To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer.

• In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”)


Add New Linked Server
• The “New Linked Server” Dialog appears.  (see below).


Linked Server Settings

• For “Server Type” make sure “Other Data Source” is selected.  (The SQL Server option will force you to specify the literal SQL Server Name)
• Type in a friendly name that describes your linked server (without spaces). I use AccountingServer.
• Provider – Select “Microsoft OLE DB Provider for SQL Server”
• Product Name – type: SQLSERVER (with no spaces)
• Datasource – type the actual server name, and instance name using this convention: SERVERNAMEINSTANCENAME
• ProviderString – Blank
• Catalog – Optional (If entered use the default database you will be using)

1. 
   
2. Prior to exiting, continue to the next section (defining security)
3. Define the Linked Server Security

Linked server security can be defined a few different ways. The different security methods are outlined below.  The first three options are the most common:

Option Name	Description
Be made using the login’s current security context	Most Secure. Uses integrated authentication, specifically Kerberos delegation to pass the credentials of the current login executing the request to the linked server. The remote server must also have the login defined. This requires establishing Kerberos Constrained Delegation in Active Directory, unless the linked server is another instance on the same Server.  If instance is on the same server and the logins have the appropriate permissions, I recommend this one.
Be made using this security context	Less Secure. Uses SQL Server Authentication to log in to the linked server. The credentials are used every time a call is made.
Local server login to remote server login mappings	You can specify multiple SQL Server logins to use based upon the context of the user that is making the call.  So if you have George executing a select statement, you can have him execute as a different user’s login when linking to the linked server.  This will allow you to not need to define “George” on the linked server.
Not be made	If a mapping is not defined, and/or the local login does not have a mapping, do not connect to the linked server.
Be made without using a security context	Connect to the server without any credentials.  I do not see a use for this unless you have security defined as public.

1. Within the same Dialog on the left menu under “Select a Page”, select Security
2. Enter the security option of your choice.
3. Linked Server Security Settings
4. Click OK, and the new linked server is created

Execute Keyword in SQL Server 2012

Today, I have provided an article showing you an improved version of the execute keyword in SQL Server 2012. The EXECUTE keyword is used to execute a command string. You cannot change the column name and datatype using the execute keyword in SQL Server 2005/2008. You have to modify the stored procedure respectively. The previous version of SQL Server only has the WITH RECOMPILE option to force a new plan to be re-compiled. The ability to do that in SQL Server 2012dramatically improves this part.  In SQL Server 2012, there is no need to modify a stored procedure. You can change the column name and datatype using the execute keyword. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio. 

The table looks as in the following:

Create TABLE UserDetail

(

       User_Id int NOT NULL IDENTITY(1,1),     

       FirstName varchar(20),

       LastName varchar(40) NOT NULL,

       Address varchar(255),     

       PRIMARY KEY (User_Id)

)

INSERT INTO UserDetail(FirstName, LastName, Address)

VALUES ('Smith', 'Kumar','Capetown'),

      ('Crown', 'sharma','Sydney'),

      ('Copper', 'verma','Jamaica'),

      ('lee', 'verma','Sydney')

go

Now create a stored procedure for the select statement in SQL Server 2008: 

Create PROCEDURE SelectUserDetail

as

begin

select FirstName,LastName, Address from UserDetail

end  

Now use an Execute command to run the stored procedure: 

-- SQL Server 2008

execute SelectUserDetail

Output

SQL Server 2012

Now we see how we can change the column name and datatype using an execute keyword in SQL Server 2012. The previous version of SQL Server only has the WITH RECOMPILE option to force a new plan to be re-compiled. To do that in SQL Server 2012 dramatically improves this part. We change the FirstName to Name and the datatype varchar(20) to char(4). Now execute the following code in SQL Server 2012:

WITH result SETS 

 ( 

 (   

      Name CHAR(4), 

      Lastname VARCHAR(20), 

     Address varchar(25)    

 ) 

 ); 

Now Press F5 to run the query and see the result:

Magic of Derived Tables

Using Derived Tables to Simplify the SQL Server Query Process

Problem
Sometimes querying data is not that simple and there may be the need to create temporary tables or views to predefine how the data should look prior to its final output.  Unfortunately there are problems with both of these approaches if you are trying to query data on the fly. 

With the temporary tables approach you need to have multiple steps in your process, first to create the temporary table, then to populate the temporary table, then to select data from the temporary table and lastly cleanup of the temporary table.

With the view approach you need to predefine how this data will look, create the view and then use the view in your query.  Granted if this is something that you would be doing over and over again this might make sense to just create a view, but let's look at a totally different approach.

Solution
With SQL Server you have the ability to create derived tables on the fly and then use these derived tables within your query.  In concept this is similar to creating a temporary table and then using the temporary table in your query, but the approach is much simpler, because it can all be done in one step.

Let's take a look at an example where we query the Sales database to try to find out how many customers fall into various categories based on sales.  The categories that we have predefined are as follows:

Ø  Total Sales between 0 and 5,000 = Micro

Ø  Total Sales between 5,001 and 10,000 = Small

Ø  Total Sales between 10,001 and 15,000 = Medium

Ø  Total Sales between 15,001 and 20,000 = Large

Ø  Total Sales > 20,000 = Very Large

There are several ways that this data can be pulled, but let's look at an approach using a derived table.

The first step is to find out the total sales by each customer, which can be done with the following statement.

SELECT   o.CustomerID,           SUM(UnitPrice * Quantity) AS TotalSales  FROM     [Order Details] AS od           INNER JOIN Orders AS o             ON od.OrderID = o.OrderID  GROUP BY o.CustomerID

This is a partial list of the output:

CustomerID	TotalSales
ALFKI	4596.2000
ANATR	1402.9500
ANTON	7515.3500
...
WOLZA	3531.9500

The next step is to classify the TotalSales value into the OrderGroups that were specified above:

SELECT   o.CustomerID,           SUM(UnitPrice * Quantity) AS TotalSales,           CASE              WHEN SUM(UnitPrice * Quantity)                  BETWEEN 0 AND 5000 THEN 'Micro'             WHEN SUM(UnitPrice * Quantity)                  BETWEEN 5001 AND 10000 THEN 'Small'             WHEN SUM(UnitPrice * Quantity)                  BETWEEN 10001 AND 15000 THEN 'Medium'             WHEN SUM(UnitPrice * Quantity)                  BETWEEN 15001 AND 20000 THEN 'Large'             WHEN SUM(UnitPrice * Quantity)                  > 20000 THEN 'Very Large'           END AS OrderGroup  FROM     [Order Details] AS od           INNER JOIN Orders AS o             ON od.OrderID = o.OrderID  GROUP BY o.CustomerID

This is a partial list of the output:

CustomerID	TotalSales	OrderGroup
ALFKI	4596.2000	Micro
ANATR	1402.9500	Micro
ANTON	7515.3500	Small
...
WOLZA	3531.9500	Micro

The next step is to figure out how many customers fit into each of these groups and this is where the derived table comes into play.  Take a look at the following query which uses a derived table called OG.  What we are doing here is using the same query from the step above, but calling this derived table OG. Then we are selecting data from this derived table for our final output just like we would with any other query.  All of the columns that are created in the derived table are now available for our final query.

SELECT   OG.OrderGroup,           COUNT(OG.OrderGroup) AS OrderGroupCount  FROM     (SELECT   o.CustomerID,                     SUM(UnitPrice * Quantity) AS TotalSales,                     CASE                        WHEN SUM(UnitPrice * Quantity)                          BETWEEN 0 AND 5000 THEN 'Micro'                       WHEN SUM(UnitPrice * Quantity)                          BETWEEN 5001 AND 10000 THEN 'Small'                       WHEN SUM(UnitPrice * Quantity)                          BETWEEN 10001 AND 15000 THEN 'Medium'                       WHEN SUM(UnitPrice * Quantity)                          BETWEEN 15001 AND 20000 THEN 'Large'                       WHEN SUM(UnitPrice * Quantity)                          > 20000 THEN 'Very Large'                     END AS OrderGroup            FROM     [Order Details] AS od                     INNER JOIN Orders AS o                       ON od.OrderID = o.OrderID            GROUP BY o.CustomerID) AS OG  GROUP BY OG.OrderGroup

This is the complete list of the output from the above query.

OrderGroup	OrderGroupCount
Large	10
Medium	11
Micro	33
Small	15
Very Large	20