By Gregory A. Larsen

If you are like most, you probably have looked into using Windows Authentication as a method to authenticate users to SQL Server 2005. Windows Authentication is the preferred and more secure method of connecting to SQL Server. If your goal is to use Windows authentication for everything then under some situations it does present some challenges. One of those challenges is setting up linked servers to impersonate the local login when connecting to a linked server. This article will discuss how to set up delegation on your SQL Server instances so you can use the impersonate options when setting up the security properties of linked server definitions.

What is Delegation?

Delegation is when a middle tier server, impersonates the client login when connecting to a backend server. When users connect to a backend server through a middle server this is commonly called a double hop. In order to make Windows Authentication work in this situation the middle tier server need to impersonate the user when connecting to the backend machine. This impersonation allows the backend machine to know the login of the original user so queries can be run in the security context of the original user. For the purposes of this article, this means that when a client connects to SQL Server, the client login will be impersonated when using a linked server to connect to a backend SQL Server machine.

Machine Configuration

For the purpose of this article, I also need to provide you with a layout of the computer topology for this article. To help convey the hardware architecture for my delegation examples below please review the following diagram.

Here there are three machine involved, one user machine and two servers. The user machine is referred to as “client” and requires that the user needs to logon to the network with a domain account. For the purpose of this article, assume that user is “SDS\GREG” and the user connects to SERVER1 using windows authentication. The application on the client machine needs to be able to run a number of queries that retrieves data from both SERVER1 and SERVER2. Any time the application retrieves data from SERVER2 it will be done using a linked server from SERVER1. Each server is running a single default instance of SQL Server. All of the SQL Server services for SERVER1 instance are running under the domain account named SERVER1_DF, and all services on the SERVER2 instance are running under the SERVER2_DF domain account.

It is worth mentioning here that one problem I encountered while setting up delegation was associated with the account I used to run my SQL Server services. When I ran my SQL Server Services under the computer account, delegation wouldn’t work. You need to make sure your services are running under an account that is different from the machine name, hence the reason my service account names are <machine name>_DF. The DF stands for default instance.

How to Setup Delegation

The setup of delegation is not complicated, but does require a number of steps. Keep in mind the method I will show you is specific to my SQL Server setup. If your setup is different, additional and/or different steps might be needed.

One of the first things you need to do is make sure all service and user accounts involved in your delegation situation are allowed to be delegated. Active directory definitions for accounts identifies whether or not an account can be delegated. Delegation is controlled via a check box within the “Account Options” section of the “Account” tab on the domain account properties window. The check box “Account is sensitive and cannot be delegated” needs to be unchecked. The following screen shot shows this for user “SDS\GREG”:

You need to make sure all accounts that will be using linked servers have the “Account options” set appropriately to allow their account to be delegated. In my example, you need to make sure both SERVER1_DF and SERVER2_DF have this check box, unchecked.

The next step to setting up delegation is to establish a Server Principle Name (SPN) entry for each SQL Server instance. To do this you use the SETSPN tool. This tool is part of the windows support tools, which can be downloaded from Microsoft for your version of Windows. For my two servers in the above diagram I ran the following set of SETSPN commands from the command prompt. Keep in mind these commands need to be executed under an account that has domain administration permissions.

SETSPN -A MSSQLSvc/SERVER1:1433 SDS\SERVER1_DF
SETSPN -A MSSQLSvc/SERVER1.SDS.COM:1433 SDS\SERVER1_DF


SETSPN -A MSSQLSvc/SERVER2:1433 SDS\SERVER2_DF
SETSPN -A MSSQLSvc/SERVER2.SDS.COM:1433 SDS\SERVER2_DF

Here the first two commands define SPN’s for SERVER1 and the second two commands for SERVER2. Note the first SPN command in each set registers the SPN by referencing just the machine name, and the second one identifies the fully qualified domain name of the server. To verify the SPNs are registered correctly for a service account you can run the following command:

SETSPN –L SDS\SERVER1_DF

This command will list all the SPNs associated with domain account “SDS\SERVER1_DF”.

The next step in setting up delegation is to make sure the SQL Server service accounts are set up so they can perform delegation. To do this you set the appropriate delegation options for the SQL Server accounts under the “Delegation” tab when reviewing the domain account properties. Note the delegation tab will not be displayed for an account until the SETSPN command for that account has been established. So, in my example I need to set the delegation options for SERVER1_DF and SERVER2_DF accounts. There are two different options you can pick when setting the delegation options for an account, constrained and un-constrained. I decided to use constrained delegation for my set up, since that minimizes the number of services that can perform delegation. Below is a screen shot of the options I used to for setting up my SERVER1_DF services account:

Here you can see that I select “Use Kerberos only” radio button and then specified the specific service type that would be doing the delegation. For SQL Server the service type is “MSSQLSvc”. I also specified the computer name “SERVER1” and the port that SQL Server is listening on.

You also need to verify that the computer account within Active directory is also set up to support delegation. To do this edit the computer properties in Active directory to look like this:

Here I have set up my SERVER1 machine to delegate using Kerberos, just as I did with the service account above.

Lastly, you need to verify that the local security policies on the middle tier server are set up to allow delegation. This is done by using the “Local Security Policy” tool under “Administrative tools”. Expand the “Local Policy” item under the “Security section”, and then expand the “User rights assignment”. Then double click on the “Impersonate a client after authentication” item to modify the properties. Use the “Add Users of Groups…” button to add the account that the SQL Server services are running under. In my case that would be “SERVER1_DF”. After I added my account the “Impersonate a client after authentication” properties looks like this:

Here, I have added account “SDS\SERVER1_DF” to the local security policies on SERVER1, my middle tier server.

Testing Delegation

Once you have set up your accounts and machines you need to verify that delegation works using linked servers. To do this, log on to a client machine using a windows account. Make sure the account you use has a login established on your middle tier and backend SQL Server machines. Once logged on to your client machine, connect to your middle tier SQL Server machine. I normally do this using a client machine that has SQL Server Management studio installed. When I am connected, I then open a new query window and verify that I have connected to the middle tier server via Kerberos. To do this I issue the following command:

select auth_scheme from sys.dm_exec_connections
  where session_id = @@SPID

If the displayed “auth_scheme” is “KERBEROS” then I know I have successfully connected to the middle tier server using Kerberos authentication method. If “NTLM” is displayed for the “auth_scheme” then I know I did not successfully set up my middle tier server for delegation, and I go back to make sure I didn’t miss a step.

Once I have successfully verified that I am connected to the middle tier server using Kerberos, then the final test I do is to submit a linked server request to my backend server. In my case, I would be submitting a linked server request to SERVER2. So for me to verify my delegation is set up I would issue the following command:

select name from SERVER2.master.sys.servers where server_id = 0

If delegation is set up correctly this command should return the name “SERVER2”. If delegation is not set up, an authentication error will be displayed.

Troubleshooting Delegation Setup

It isn’t extremely straightforward to set up delegation. In fact, I had a number of failed attempts before I successfully set up my first set of SQL Server machines for delegation. To help me troubleshoot my delegation setup, I used the following document:

http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

This document walks through a number of different situations and provides steps for verifying that your delegation setup is correct.

Conclusion:

Being able to use windows authentication for linked servers provides a more secure architecture then defining login mappings. It also minimizes the work needed to set up and maintain linked server definitions. Setting up delegation does require a number of steps to successfully set up your servers, and possibly some troubleshooting but it is worth the effort. This article provides you with the steps and tools necessary to set up and troubleshoot setting up your SQL Server environment to use delegation.

 

By Gregory A. Larsen

When dealing with data you come across many different situations. In this article I will discuss how to deal with a few situations that involve working with comma separated data. Comma separated data can come in many forms. It can be input, a text string stored in a column, or a number of other situations. This article will deal with two different comma separated data situations.

Displaying Multiple Records from a Single Record

In this situation there is a column in a table that holds a series of values that are separated by a comma. For each record, the comma separated column needs to be parsed apart and returned as separate row. So the final output record set will contain multiple records for a given single record stored in a SQL Server table.

To demonstrate this I will run the following code:

set nocount on 
-- Create Example1 Table and Populate with Data
create table Example1 (Id int, 
                       TypeOfValues varchar(20), 
                       ColumnOfValues char(30))
CREATE UNIQUE CLUSTERED INDEX ID_ind
   ON Example1(Id)
   WITH IGNORE_DUP_KEY
    
insert into Example1 values(1, 'Colors','Red,Green,Blue,Black,White')
insert into Example1 values(2, 'Models','Normal,Deluxe,Super Deluxe')
insert into Example1 values(3, 'Years','2004,2005,2006')
-- Create Number Table
SELECT IDENTITY(INT) AS Number
   INTO Numbers
   FROM sysobjects s1
  CROSS JOIN sysobjects s2

CREATE UNIQUE CLUSTERED INDEX Number_ind
   ON Numbers(number)
   WITH IGNORE_DUP_KEY
SELECT Id,
       TypeOfValues, 
       SUBSTRING( ColumnOfValues, Number, 
       CHARINDEX( ',', ColumnofValues + ',', Number ) - Number ) as Value
  FROM Example1
 INNER JOIN Numbers
    ON SUBSTRING( ',' + ColumnOfValues, Number, 1 ) = ',' 
where Number <= Len(ColumnOfValues) + 1
drop table Example1
drop table Numbers

When I run this code on my server I get the following results:

Id          TypeOfValues         Value
----------- -------------------- ------------------------------
1           Colors               Red
1           Colors               Green
1           Colors               Blue
1           Colors               Black
1           Colors               White    
2           Models               Normal
2           Models               Deluxe
2           Models               Super Deluxe    
3           Years                2004
3           Years                2005
3           Years                2006                

Here you can see that for each “Id” there are multiple rows. Each row has only one value from the comma delimited column “ColumnOfValues”. To understand how this was accomplished let’s review my code.

First I create the table “Example1”, and then populate it with three different records. Each record contains a type column (“TypeOfValues”) and a value column (“ColumnOfValues”) which contains a comma delimited string of with different values. The “ColumnOfValues” column will be the column that that is parsed apart to create multiple records for each record in the Example1 table.

Next I create a Numbers table, by joining sysobjects to itself. This table will contain a series of sequential numbers starting from 1.

Finally the SELECT statement parses apart the “ColumnOfValue” column into multiple records. It does this by using the Numbers table to identify the offset of each comma. This is done by joining the Numbers table to a single character substring of the “ColumnOfValues” column, starting with the first character, then second, and so on. Whenever the join condition finds a comma, it uses the Number value to identify the starting point of the SUBSTRING and CHARINDEX functions, so these functions can extract a single character string of value out of the “ColumnOfValues” columns. To increase the performance of this statement a WHERE clause is added to reduce the number of rows from the Numbers table that needs to be joined to the Example1 table.

Displaying a Single Record with a Comma Separated Column from Multiple Records

Some times you might have a table that contains a series of records that contain a key and a value column. In your table there might be many different values for a given key. This example will show you how to collapse all those key value pairs into a single record. That single record will contain a unique key followed by a comma separate string composed of all the values associated with the key.

Here is an example of the table I will be using that contains a key (id_no) and a value (item):

id_no       item
----------- --------------------
1           Skiing
1           Diving
2           Diving
2           Skiing
2           Hunting
2           Fishing
4           Sailing
4           Skiing
5           Skiing

In this table for each “id_no” there is one or more “items” identified. Each record contains a single “item” value. I will use the code listed below to populate the above table:

-- create example table
CREATE TABLE Example2(id_no int not null, item varchar(20) not null)
-- populate the example table
INSERT INTO Example2 VALUES (1, 'Skiing')
INSERT INTO Example2 VALUES (1, 'Diving')
INSERT INTO Example2 VALUES (2, 'Diving')
INSERT INTO Example2 VALUES (2, 'Skiing')
INSERT INTO Example2 VALUES (2, 'Hunting')
INSERT INTO Example2 VALUES (2, 'Fishing')
INSERT INTO Example2 VALUES (4, 'Sailing')
INSERT INTO Example2 VALUES (4, 'Skiing')
INSERT INTO Example2 VALUES (5, 'Skiing')

The next code snippet returns a record set that contains a single record for each “id_no”, followed by a comma delimited string that concatenates each “item” value together into a single column value:

-- declare local variables
declare @p varchar(1000)
declare @i char(5)
declare @sm int
declare @m int
-- Print Report Heading
print 'id_no' + ' items'
print '----- ' + '------------------------------------------'
set @p = ''
-- set @m to the first id number
select top 1 @m = id_no from Example2
order by id_no
set @sm = 0 
-- Process each id_no until no more items
while @m <> @sm
begin
set @sm = @m
-- string together all items with a comma between
select @i = id_no, @p = case when @p = '' then item else @p + ', ' + item end
from Example2 a
where id_no = @m
-- print id_no, and comma delimited string
print @i + ' ' + @p
-- increment id number 
select top 1 @m = id_no from Example2
where id_no > @sm
order by id_no
set @p = ''
end
-- remove example table 
drop table Example2

When I run this code against my Example2 table I get the following output:

id_no items
----- ------------------------------------------
1     Skiing, Diving
2     Diving, Skiing, Hunting, Fishing
4     Sailing, Skiing
5     Skiing

Let me explain how this code works. This code iteratively process each “id_no” value using a WHILE loop. Each pass through the WHILE loop strings together all the “item” values for a given “id_no”. The variable @m contains the value of the “id_no” for the records being collapsed into a single record. The following SELECT statement does all the work to collapse all the records for a given “id_no” value into a single row in the output:

select @i = id_no, @p = case when @p = '' then item else @p + ', ' + item end
from Example2 a
where id_no = @m

This code concatenates a comma with the value of the “item” column and adds it to the variable @p. This method allows you a way to summarize a character string, in this case the value of the “item” column followed by a comma. After this command has completed execution the variable @i contains the “id_no”, and the @p variable contains a comma delimited string of “item” column values for the give “id_no”.

The PRINT statement is used to display each row of comma delimited values for a given “id_no”. The last SELECT statement in the WHILE loop set the @m variable to the next “id_no” to be processed. This WHILE loop continues to creating comma delimited strings for each “item” column processing one “id_no” at a time until all “id_no” records have been processed.

Conclusion

This article showed you only two examples of how to deal with comma separated data. One example showed you how to break apart comma separated data, where as the other one showed you how to join multiple records into a single record where the data was separated by commas. Hopefully next time you have to deal with comma separated data these examples will give you a jump start on writing your T-SQL code to work with comma separated data.

http://www.databasejournal.com/features/mssql/article.php/3634381/Dealing-with-Comma-Delimited-Strings.htm

 

By Gregory A. Larsen

More and more shops are implementing web services. Doing this provides an architecture that allows applications to consume services to retrieve data. These services could be within your own organization or from a business partner. One of the problems you might run into when building applications that consume web services is how you can use web services data within a SQL Server instance. One of the reasons you might want to do this is so you can join a record set that is returned from a web service with one of your SQL Server tables. This can easily be done within an application, but how do you do this within a stored procedure that only runs within the context of SQL Server. In this article I will discuss one approach for doing this.

Using Web Services Data within SQL Server

If you need to write a T-SQL statement to join some web service information with a SQL Server table how might you go about doing this? Clearly, a web service is not a table or a view that allows you to easily join it with other compatible SQL Server objects. If you want to incorporate data from a web service into your server side logic like in a stored procedure, you need a method to call a web services directly from within SQL Server.

When Microsoft introduced SQL Server 2005, they implemented the CLR component. With a CLR, you can create a User Defined Function (UDF) that consumes a web service and returns a table, or sometimes referred to as a table value function. By using a UDF that calls a web service you are able to implement a solution that allows you to easily join a record set returned from a web service with a table or view. Using this methodology, you can now encapsulate a call to a web service within the code of a stored procedure.

Example of Building a CLR and a UDF to Consume a Web Service

For my example, I will be using the AdventureWorks database. I will be building a web service named “Product” to retrieve all the Production.Product data from the AdventureWorks database. This web service will be then be consumed by a UDF so I can join the information returned from this web service with the Sales.SalesOrderDetail table to display the Product Name information for each SalesOrderID.

First, let me show the code for my simple “Product” web services. Here is the C# sharp code for my web service:

using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace MyWebService
{
    [WebService(Namespace = "MyWebSerice", Description = "Product")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

    public class wsProduct : System.Web.Services.WebService
    {
        [WebMethod(Description = "Returns all Products")]
        public System.Data.DataSet GetProduct()
        {
            DataSet ds = new DataSet();
            DataSet data = new DataSet();
            SqlConnection conn = new SqlConnection();
            conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Product"].ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT ProductId, Name FROM Production.Product", conn);
            // cmd.CommandType = CommandType.Text;  
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter();
            try
            {
                adapter.SelectCommand = cmd;
            }
            catch
            {
                return null;
            }
            adapter.Fill(data, "Product");
            return data;
        }
    }
}

This web service when called returns a record set that contains all the products in the Production.Product table. The record set returns a record set that only contains two columns: ProductId and Name.

Once my web service is up and running I can build my CLR. To build my CLR that calls the “Product” web service I will be using Visual Studio 2005. The first step in building my CLR is to create a new project. When I create my new project, I select the “SQL Server Project” template like so:

Before I can create my UDF object, I need to add a web reference to my project for the “Product” web service. To do this I right click on “Web Reference” in the Solution Explorer and select the “Add Web Reference…” item. When I do this, the following screen is displayed:

On this screen, I enter the web address (URL) of my web service into the URL textbox, like so:

Here you can see I entered “http://localhost/MyWebService/Product.asmx?WSDL. Once the address is typed, I click on the Go arrow. Doing this brings up the following window:

Here you can see it found my web server named “Product”. This web service only contains a single method named “GetProduct”. To finalize creating my web reference I will just need to change the “Web reference name” to something more appropriate than “localhost”. In my case, I enter “Product” in the “Web reference name” textbox and then click on the “Add Reference” button. This adds my “Product” web service as a web reference to my project.

The next step to building my solution is to create my UDF CLR object. To do this I use the Solution Explorer to add a new item. When I select the “Add New Item”, the following window is displayed:

Here I select the “User-Defined Function” template, and “Name” my UDF “GetProduct.cs”.

Here is the code for my CLR UDF:

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using UDF_CLR.Product;
public partial class UserDefinedFunctions
{
    /*
     * Author: Greg Larsen 
     * Description:  
     * This code creates a User Define Table Value Function that calls the GetProduct web service.
     * This Function is CLR that needs to be defined in SQL Server before it can be used in a T-SQL 
     * Statement. Keep in mind when building this code the following 
     * post processing is required to create a XML serialized assembly: 
     * "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force "$(TargetPath)"
     * Also a web reference called "Product" needs to be created that references: 
     * http://localhost/MyWebService/wsProduct.asmx?WSDL
    */
    [SqlFunction(
        DataAccess = DataAccessKind.Read,
        FillRowMethodName = "GetProduct_FillRow",
        // define columns returned 
        TableDefinition =
        "ProductID int, " +
        "Name ncharvar(50)  " 
        )
    ]
    public static IEnumerable GetProduct()
    {
        return new wsProduct().GetProduct().Tables[0].Rows;
    }

    public static void GetProduct_FillRow(
        object ProductObj,
        out SqlInt32 ProductID,
        out SqlString Name
        )
    {
        DataRow r = (DataRow)ProductObj;
        ProductID = new SqlInt32(Convert.ToInt32(r["ProductID"].ToString()));
        Name = new SqlString(r["Name"].ToString());
    }
};

Let me walk through this code.

In this code, I first defined my UDF using SqlFunction attribute using the following code:

    [SqlFunction(
        DataAccess = DataAccessKind.Read,
        FillRowMethodName = "GetProduct_FillRow",
        // define columns returned 
        TableDefinition =
        "ProductID int, " +
        "Name ncharvar(50)  " 
        )
    ]

In this code snippet, I identified that my UDF will:

• Be read only



• Be populated using the “GetProduct_FillRow” method in my class



• Define that there will only be two columns “ProductID” and “Name” in the table returned.

In the next section of code I create the “GetProduct() “IEnumerable” object. This code returns the data from my “wsProducts” web service one row at a time.

The last section of code in the above C# code shows the “GetProduct_FillRow” method. This method is used to populate my UDF record set from the wsProduct object returned from the “IEnumerable” object. In this section of code, I convert the data returned from my web service to the appropriate SQL Server data types for each column.

Once the code above is included in my Visual Studio project the next step is to build the solution to create the ddls for my CLR. When I was working though building my first UDF CLR object I found out the XML objects are not serialized. In order to serialize my CLR I had to perform some post-processing to incorporate the XMLSerialization object into my CLR solution. This is done by using the sgen executable. You can either setup your Visual Studio project to perform this post-processing every time you build your solution, or call this sgen executable manually. I set up my Visual Studio project to do this automatically.

It is easy to set up your Visual Studio project to automatically do the XML serialization via the post processing properties of a project. To do this with my project I right clicked on my project in the Solution Explorer window and then selected “Properties” from the drop down window. When the properties window displayed, I then clicked the “Build Event” tab item in the menus on the left. Doing that displayed the window below:

In the “Post-build event command line:” item I enter the following code to execute the sgen executable:

"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sgen.exe" /force "$(TargetPath)"

At this point my project is all set up and ready to be built to create my CLR dlls using the “Build” menu item. During the build process, Visual Studio creates two dlls and places them in the “Output Path:” location identified under the “Build” properties of my solution. One dll is named UDF_CLR.dll, and the other is named UDF_CLR.Xmlserializer.dll. These are the two dlls I will need to incorporate into my SQL Server environment in order to get my UDF function to work.

To include both these two dlls into my SQL Server environment I first copy them to a drive on my SQL Server machine. For my example, I copied them to a directory named C:\CLR. Once my dlls are copied, I run the following T-SQL code on my SQL Server machine:

use AdventureWorks
go
-- allows you to create external access CLRs
ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON;
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'GetProductWS')
   DROP FUNCTION GetProductWS
go
IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'XmlSerializers') 
	DROP ASSEMBLY [XmlSerializers]
IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'GetProductCLR')
   DROP ASSEMBLY GetProductCLR
GO

CREATE ASSEMBLY GetProductCLR FROM 'C:\CLR\UDF_CLR.dll'
WITH PERMISSION_SET = External_Access

CREATE ASSEMBLY [XmlSerializers] from
'C:\CLR\UDF_CLR.XmlSerializers.dll'
WITH permission_set = SAFE
GO

CREATE FUNCTION GetProductWS() 

RETURNS TABLE (
ProductID int,
Name nvarchar(50)  
   )
AS EXTERNAL NAME GetProductCLR.UserDefinedFunctions.[GetProduct]
GO

As you can see this T-SQL code used two different “CREATE ASSEMBLY” statements to incorporate my dlls into SQL Server. The first one creates the CLR for my GetProductCLR object, and the other one to create the XmlSerializers CLR. After my assembly are created I then use the CREATE FUNCTION statement to create my GetProductWS user defined function. At this point, I am done setting up my CLR. All that is left is to test my user defined function to determine if it can successfully return the data from my GetProduct method of my wsProduct web service. To do that testing I run the following code:

SELECT * from db.GetProductWS();

This is basically all it takes to execute my UDF that call a web service. Now that I have my UDF GetProductWS, I can join the output from my web service to a SQL Server table by running some code like this:

SELECT B.SalesOrderID, A.Name [ProductName]
FROM dbo.GetProductWS() A 
JOIN Sales.SalesOrderDetail  B 
ON A.ProductID = B.ProductID

Running code like this allows me to easily include data from by web service into a T-SQL script.

Incorporating a Web Service into a T-SQL Solution

With the proliferation of web services sooner or later you will find a need to join the output of a web service with a SQL Server table using T-SQL code. The example I showed you above created a UDF function to call a web service via a CLR and return that data as a table valued function. The output from a table valued function can then be joined to a SQL Server table quite easily. This method allows you a way to incorporate output from a web services into a T-SQL solution.

 

http://www.databasejournal.com/features/mssql/article.php/3821271/Calling-a-Web-Service-from-within-SQL-Server.htm

1-

Take the number 192 and multiply it by each of 1, 2, and 3:

192 1 = 192
192 2 = 384
192 3 = 576

By concatenating each product we get the 1 to 9 pandigital, 192384576. We will call 192384576 the concatenated product of 192 and (1,2,3)

The same can be achieved by starting with 9 and multiplying by 1, 2, 3, 4, and 5, giving the pandigital, 918273645, which is the concatenated product of 9 and (1,2,3,4,5).

What is the largest 1 to 9 pandigital 9-digit number that can be formed as the concatenated product of an integer with (1,2, ... , n) where n 1?

 

2-

The first two consecutive numbers to have two distinct prime factors are:

14 = 2 7
15 = 3 5

The first three consecutive numbers to have three distinct prime factors are:

644 = 2² 7 23
645 = 3 5 43
646 = 2 17 19.

Find the first four consecutive integers to have four distinct primes factors. What is the first of these numbers?

 

 

3-

The series, 1¹ + 2² + 3³ + ... + 10¹⁰ = 10405071317.

Find the last ten digits of the series, 1¹ + 2² + 3³ + ... + 1000¹⁰⁰⁰.

 

4-

There are exactly ten ways of selecting three from five, 12345:

123, 124, 125, 134, 135, 145, 234, 235, 245, and 345

In combinatorics, we use the notation, ⁵C₃ = 10.

In general,

ⁿC_r =

n!

r!(nr)!

,where r n, n! = n(n1)...321, and 0! = 1.

It is not until n = 23, that a value exceeds one-million: ²³C₁₀ = 1144066.

How many, not necessarily distinct, values of  ⁿC_r, for 1 n 100, are greater than one-million?

 

5-

A googol (10¹⁰⁰) is a massive number: one followed by one-hundred zeros; 100¹⁰⁰ is almost unimaginably large: one followed by two-hundred zeros. Despite their size, the sum of the digits in each number is only 1.

Considering natural numbers of the form, a^b, where a, b 100, what is the maximum digital sum?

 

find more at:

http://projecteuler.net/index.php?section=problems