SQL two column list to two column table

set
ANSI_NULLS
ON

set
QUOTED_IDENTIFIER
ON

go

 

 

ALTER
FUNCTION [dbo].[CharList_To_Table_2_Value]


(@list ntext,

@delimiter nchar(1)
= N‘,’)


RETURNS @tbl TABLE
(listpos int
IDENTITY(1, 1)
NOT
NULL,


str
varchar(4000),

                             str2 varchar(4000)

            )
AS

 


BEGIN


DECLARE @pos int,

@textpos int,

@chunklen smallint,

@tmpstr nvarchar(4000),

@leftover nvarchar(4000),

@tmpval nvarchar(4000),

             @tmpval2 nvarchar(4000),

             @num        int

 

     SET @num = 0


SET @textpos = 1


SET @leftover =


WHILE @textpos <=
datalength(@list)
/ 2


BEGIN


SET @chunklen = 4000
datalength(@leftover)
/ 2


SET @tmpstr = @leftover +
substring(@list, @textpos, @chunklen)


SET @textpos = @textpos + @chunklen

 


SET @pos =
charindex(@delimiter, @tmpstr)

 


WHILE @pos > 0


BEGIN

            if @num = 0

            begin

     SET @tmpval =
ltrim(rtrim(left(@tmpstr, @pos 1)))

                set @num = 1

            end

            else

            begin

                SET @tmpval2 =
ltrim(rtrim(left(@tmpstr, @pos 1)))

     INSERT @tbl (str, str2)
VALUES(@tmpval, @tmpval2)

                set @num = 0

            end

            
 


SET @tmpstr =
substring(@tmpstr, @pos + 1,
len(@tmpstr))


SET @pos =
charindex(@delimiter, @tmpstr)


END

 


SET @leftover = @tmpstr


END

 


INSERT @tbl(str, str2)
VALUES
(@tmpval,
ltrim(rtrim(@leftover)))


RETURN


END

 


 

Advertisements
Categories: SQL Server

SQL string list to table function

From SQLServerCentral.com

set
ANSI_NULLS
ON

set
QUOTED_IDENTIFIER
ON

go

 

ALTER
FUNCTION [dbo].[CharList_To_Table]


(@list ntext,

@delimiter nchar(1)
= N‘,’)


RETURNS @tbl TABLE
(listpos int
IDENTITY(1, 1)
NOT
NULL,


str
varchar(4000),

nstr nvarchar(2000))
AS

 


BEGIN


DECLARE @pos int,

@textpos int,

@chunklen smallint,

@tmpstr nvarchar(4000),

@leftover nvarchar(4000),

@tmpval nvarchar(4000)

 


SET @textpos = 1


SET @leftover =


WHILE @textpos <=
datalength(@list)
/ 2


BEGIN


SET @chunklen = 4000
datalength(@leftover)
/ 2


SET @tmpstr = @leftover +
substring(@list, @textpos, @chunklen)


SET @textpos = @textpos + @chunklen

 


SET @pos =
charindex(@delimiter, @tmpstr)

 


WHILE @pos > 0


BEGIN


SET @tmpval =
ltrim(rtrim(left(@tmpstr, @pos 1)))


INSERT @tbl (str, nstr)
VALUES(@tmpval, @tmpval)


SET @tmpstr =
substring(@tmpstr, @pos + 1,
len(@tmpstr))


SET @pos =
charindex(@delimiter, @tmpstr)


END

 


SET @leftover = @tmpstr


END

 


INSERT @tbl(str, nstr)
VALUES
(ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))


RETURN


END


 

Categories: SQL Server

SQL Integer list to table function

 

Probably from SQLServerCentral.com

 

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 

 

ALTER FUNCTION [dbo].[IntList_To_Table]

(@list ntext,

@delimiter nchar(1) = N’,’)

RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

num int) AS

 

BEGIN

DECLARE @pos int,

@textpos int,

@chunklen smallint,

@tmpstr nvarchar(4000),

@leftover nvarchar(4000),

@tmpval nvarchar(4000)

 

SET @textpos = 1

SET @leftover = ”

WHILE @textpos <= datalength(@list) / 2

BEGIN

SET @chunklen = 4000 – datalength(@leftover) / 2

SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)

SET @textpos = @textpos + @chunklen

 

SET @pos = charindex(@delimiter, @tmpstr)

 

WHILE @pos > 0

BEGIN

SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos – 1)))

INSERT @tbl (num) VALUES(cast(@tmpval as int))

SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))

SET @pos = charindex(@delimiter, @tmpstr)

END

 

SET @leftover = @tmpstr

END

 

INSERT @tbl(num) VALUES (cast(ltrim(rtrim(@leftover)) as int))

RETURN

END


 

Categories: SQL Server

SQL Create A Comma Delimited List From a Column

From SQLServerCentral.com

Create A Comma Delimited List From a Column

Arnie Rowland, March 12, 2008

 

On occasion, there is a desire to concatenate column values from multiple rows to create a comma delimited list.

For example, from the following data:

 

MemberID     Team     FirstName

1     1     Jim

2     1     Mary

3     1     Bob

4     2     Sue

5     2     Ralph

6     2     Ellen

7     3     Bill

8     3     Linda

 

the desired output is a list of Teams and Team members, in the form of:

 

Team     Members

1     Bob, Jim, Mary

2     Ellen, Ralph, Sue

3     Bill, Linda

 

There are several methods to accomplish the desired output. First a solution for SQL Server 2005 / SQL Server 2008 , and then a solution for SQL Server 2000.

 

Create Sample Data for Both Solutions

 

— Suppress data loading messages

SET NOCOUNT ON

 

— Create Sample Data

CREATE TABLE TeamInfo

( MemberID int IDENTITY,

TeamID int,

FirstName varchar(50)

)

 

— Load Sample Data

INSERT INTO TeamInfo VALUES ( 1, ‘Jim’ )

INSERT INTO TeamInfo VALUES ( 1, ‘Mary’ )

INSERT INTO TeamInfo VALUES ( 1, ‘Bob’ )

INSERT INTO TeamInfo VALUES ( 2, ‘Sue’ )

INSERT INTO TeamInfo VALUES ( 2, ‘Ralph’ )

INSERT INTO TeamInfo VALUES ( 2, ‘Ellen’ )

INSERT INTO TeamInfo VALUES ( 3, ‘Bill’ )

INSERT INTO TeamInfo VALUES ( 3, ‘Linda’ )

 

Return to Top

 

 

SQL Server 2005 / SQL Server 2008 Solution

 

–Retrieve desired data

SELECT

t1.TeamID,

MemberList = substring((SELECT ( ‘, ‘ + FirstName )

FROM TeamInfo t2

WHERE t1.TeamID = t2.TeamID

ORDER BY

TeamID,

FirstName

FOR XML PATH( ” )

), 3, 1000 )FROM TeamInfo t1

GROUP BY TeamID

 

— Results

TeamID MemberList

1    Bob, Jim, Mary

2    Ellen, Ralph, Sue

3    Bill, Linda

 

Return to Top

 

 

SQL 2000 Solution

 

— SQL 2000, Retrieve desired data

— With SQL 2000, we will create a User Defined Function to do the concatenation.

— While this solution can also be used with SQL Server 2005/SQL Server 2008,

— the previous suggestion is more efficient.

 

CREATE FUNCTION dbo.fnMakeTeamList

( @TeamID int )

RETURNS varchar(1000)

AS

BEGIN

DECLARE @TempTeam table

( Firstname varchar(20) )

DECLARE @TeamList varchar(1000)

SET @TeamList = ”

INSERT INTO @TempTeam

SELECT FirstName

FROM TeamInfo

WHERE TeamID = @TeamID

IF @@ROWCOUNT > 0

UPDATE @TempTeam

SET @TeamList = ( @TeamList + FirstName + ‘, ‘ )

RETURN substring( @TeamList, 1, ( len( @TeamList ) – 1 ))

END

 

— Usage

SELECT

TeamID,

MemberList = dbo.fnMakeTeamList( TeamId )

FROM TeamInfo

GROUP BY TeamID

 

— Results

TeamID MemberList

1    Jim, Mary, Bob

2    Sue, Ralph, Ellen

3    Bill, Linda

 

Return to Top

 

Clean up the test environment

 

DROP FUNCTION dbo.fnMakeTeamList

DROP TABLE TeamInfo

 

 

Additional Resouces

Pass and Handle a Delimited String similar to handling an Array() of Values.

 

Return to Top

 

___________________________________________________________________________________________________________________

Page Created By: Arnie Rowland, Mar 12, 2008

Categories: SQL Server

High-speed Hierarchies

 

Remember collation is VERY important

[SETSTRING] [nvarchar](255)
COLLATE Latin1_General_BIN NULL

 

Theory here:

 

http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm


 

Categories: Uncategorized

Consuming Web Services over HTTPS (SSL)

When Webservices are used, a common concern is security: SOAP messages are transferred in plain text over the network, so anyone with a sniffer could intercept the SOAP message and read it. In my opinion this could happen also to binary data, but probably it requires a little bit more hacker skills. So a solution is to use HTTPS (SSL) instead of HTTP, so the communication is encrypted. To accomplish this, you need to get and install a certificate (issued by a Certificate Authority) on your webserver. In a production environment you would buy a certificate from Verisign or another well known CA, or you would install your own CA, which is a component of Windows Server. If you only want to play with HTTPS, SSL and certificates or your project is in the development phase, you can also generate a test certificate using the MakeCert.exe tool (included in the .NET Framework SDK). After that you have to add this certificate to a website in IIS, and set a port which HTTPS should use.

 

When you browse to a HTTPS site, you probably get a dialog window asking you if you want to trust the certificate provided by the webserver. So the responsibility of accepting the certificate is handled by the user. Let’s get back to the webservice scenario, if you want to invoke a webservice located on a webserver which uses SSL and HTTPS there is a problem. When you make the call from code, there is no dialog window popping up, and asking if you trust the certificate (luckily because this would be pretty ugly in server-side scenarios); probably you’ll get following exception:

An unhandled exception of type ‘System.Net.WebException’ occurred in system.dll

 

Additional information: The underlying connection was closed: Could not establish trust relationship with remote server.

 

But there is a solution for this problem, you can solve this in your code by creating your own CertificatePolicy class (which implements the ICertificatePolicy interface). In this class you will have to write your own CheckValidationResult function that has to return true or false, like you would press yes or no in the dialog window. For development purposes I’ve created the following class which accepts all certificates, so you won’t get the nasty WebException anymore:

public class TrustAllCertificatePolicy : System.Net.ICertificatePolicy

{

public TrustAllCertificatePolicy()

{}

 

public bool CheckValidationResult(ServicePoint sp,

X509Certificate cert,WebRequest req, int problem)

{

return true;

}

}

 

As you can see the CheckValidationResult function always returns true, so all certificates will be trusted. If you want to make this class a little bit more secure, you can add additional checks using the X509Certificate parameter for example. To use this CertificatePolicy, you’ll have to tell the ServicePointManager to use it:

System.Net.ServicePointManager.CertificatePolicy = new TrustAllCertificatePolicy();

This must be done (one time during the application life cycle) before making the call to your webservice.

Categories: Uncategorized

Consuming Web Services with SSL and HTTP Basic

 

Consuming WebServices with SSL And HTTP BASIC Authentication

by Peter A. Bromberg, Ph.D.

 

    Peter Bromberg

 

I have to admit that I thought this one was going to be easy, and it ended up taking several frustrating hours to get it all “right”, mostly due to a lack of quality documentation on the subject, and secondarily because of some seemingly logical assumptions that any developer would make, which turned out to be false, as we will soon see.

Accepting Server SSL Certificates Gracefully

 

First, let’s deal with the SSL issue. This is not particularly odd, many vendors run their webservices under Apache with modSSL while requiring HTTP BASIC authentication “under the hood”, and the .NET Framework does not provide an easy – to – use, out of the box way to accept Server Certificates on a normal WebService proxy method call.

Ads by Google

Vacation Certificates     Free guide filled with ideas on growing your business.     www.SunrisePremiums.net

Ssl Certificate Authority     Get the strongest SSL from VeriSign Protect Important Data – Learn More     www.VeriSign.com

SSL Certificates – thawte     Choose from a complete range of certificates with the strongest SSL     www.thawte.com

SOAP Test Client     Send and Recieve SOAP Messages Test your Web Services Today!     www.IntegrationCentral.com

SSL Certificates $27.95     Simple low priced SSL 24/7 Live Customer Support     www.skysoftinc.com

 

The trick to the SSL issue is that we must override the default System.Net.ServicePointManager’s CertificatePolicy, which underlies every SOAP call, with a custom class derived from ICertificatePolicy. Essentially what this class will do when it’s revised boolean CheckValidationResult is automatically called, is return true (accept any Server Certificate) regardless of whether or what errors may exist. The class I present here (which can be found on MSDN) also allows to return a problem message based on a standardized enumeration:

 

//SSL Certificate code

public enum CertificateProblem : long

{

CertEXPIRED = 0x800B0101,

CertVALIDITYPERIODNESTING = 0x800B0102,

CertROLE = 0x800B0103,

CertPATHLENCONST = 0x800B0104,

CertCRITICAL = 0x800B0105,

CertPURPOSE = 0x800B0106,

CertISSUERCHAINING = 0x800B0107,

CertMALFORMED = 0x800B0108,

CertUNTRUSTEDROOT = 0x800B0109,

CertCHAINING = 0x800B010A,

CertREVOKED = 0x800B010C,

CertUNTRUSTEDTESTROOT = 0x800B010D,

CertREVOCATION_FAILURE = 0x800B010E,

CertCN_NO_MATCH = 0x800B010F,

CertWRONG_USAGE = 0x800B0110,

CertUNTRUSTEDCA = 0x800B0112

}

 

public class TrustAllCertificatePolicy : ICertificatePolicy

{

// Default policy for certificate validation.

public static bool DefaultValidate = false; // set to true for testing

 

public bool CheckValidationResult(ServicePoint sp, X509Certificate cert,

WebRequest request, int problem)

{

bool ValidationResult=false;

Console.WriteLine(“Certificate Problem with accessing ” +

request.RequestUri);

// Console.Write(“Problem code 0x{0:X8},”,(int)problem);

//Syslog send-

Sender.Send( GetProblemMessage((CertificateProblem)problem));

 

ValidationResult = DefaultValidate;

return ValidationResult;

}

 

private String GetProblemMessage(CertificateProblem Problem)

{

String ProblemMessage = “”;

CertificateProblem problemList = new CertificateProblem();

String ProblemCodeName = Enum.GetName(problemList.GetType(),Problem);

if(ProblemCodeName != null)

ProblemMessage = ProblemMessage + “-Certificateproblem:” +

ProblemCodeName;

else

ProblemMessage = “Unknown Certificate Problem”;

return ProblemMessage;

}

}

 

You can place this class in the same namespace as your WebService proxy WSDL – generated code, and that makes it easier. To use it, you would use the following sample code:

 

MyWebServiceProxy myWebServiceProxy = new MyWebServiceProxy();

// set a default CertificatePolicy that accepts ALL Server certificates�.

System.Net.ServicePointManager.CertificatePolicy = new TrustAllCertificatePolicy();

myWebServiceProxy.MyMethod(myparam1,myparam2) ;

 

Everything else with the WebService call over SSL is a no-brainer. Provided that the Url Property has “https://&#8221; at the beginning, it will automatically negotiate transmission over the default port of 443.

Negotiating HTTP BASIC “true” Pre-Authentication

 

That takes care of the SSL issue. Now let’s deal with the HTTP BASIC issue:

 

The “Gotcha” for the average developer is that the WebService Proxy class has UserName, Password, etc. properties and this leads us to believe we can simply set these properties and get “Authentication”. It also sports a PreAuthenticate property that fills in the puzzle. Unfortunately, it’s just not that simple.

 

HTTP Webservers support multiple authentication options. On Windows, IIS supports Basic, NTLM, Digest, and Negotiate (a.k.a. Integrated Windows Authentication). Of these, Basic & Kerberos both support “PreAuthentication”.

 

However, “PreAuthentication” is often misunderstood to mean “Send the credentials on the very first request”. That is not true. For Preauthenticate to work, the following must occur:

 

1) You make a webrequest to a server (a SOAP Call). You may or may not have specified credentials on the first request.

 

2) The server, which is configured for BASIC auth, wants to authenticate the request you are making, so it replies with a 401.

 

3) The client does the authentication handshake with the server, and sends your credential (a Base64 encoded string consisting basically of “username:password”).

 

4) If “Basic” was chosen as the authentication scheme by the client, then Pre-Authentication is enabled for this request. So, every subsequent request from now on to the same Uri-Prefix will result in the credentials being sent without any challenge required.

 

The main point to note here is that there must be at least one request before the client can expect pre-authentication. Credentials are not automatically sent on the first request just because PreAuthenticate is set to true. I figure that if this was a wakeup call for me (which it was) , then I am probably far from alone!

 

To force credentials to be sent on the first request (and thus avoid the first authenticate handshake), you can set credentials manually on the request:

 

MyWebServiceProxy myWebServiceProxy = new MyWebServiceProxy();

myWebServiceProxy.Url =this.endpointUrl; //items from your appConfig

NetworkCredential netCredential = new NetworkCredential(this.endpointUserName,this.endpointPassword );

Uri uri = new Uri(this.endpointUrl);

ICredentials credentials = netCredential.GetCredential(uri, “Basic”);

myWebServiceProxy.Credentials = credentials;

// Be sure to set PreAuthenticate to true or else authentication will not be sent.

mMyWebServiceProxy.PreAuthenticate = true;

 

What this does is set the authorization header on the request, and send it on the first request itself. If the credentials are correct, the first handshake will not occur. However, remember that unless you have set Preauthenticate to true, you have to use this code to set the authorization header on every request. This is because you have bypassed the authentication handshake, and the SOAP HttpWebRequest has no knowledge that the server requires authentication. So it won’t do pre-authentication by itself anymore.

 

The recommended way to get preauthentication is to set the credentials on the request, set PreAuthenticate=true, and send the request. Once the first authentication handshake happens, and credentials are accepted by the server, preauthentication will happen behind the scenes for every request to the same Uri-Prefix.

 

Now here is a final block of sample code that combines both the HTTP BASIC with PreAuthenticate along with the SSL “Accept all certificates” code that (at least for me) got me onto first base:

 

MyWebServiceProxy myWebServiceProxy = new MyWebServiceProxy();

// set a default CertificatePolicy that accepts ALL Server certificates

System.Net.ServicePointManager.CertificatePolicy =

new TrustAllCertificatePolicy();

 

myWebServiceProxy.Url =this.endpointUrl; //items from your appConfig

NetworkCredential netCredential = new NetworkCredential(this.endpointUserName,this.endpointPassword );

Uri uri = new Uri(this.endpointUrl);

ICredentials credentials = netCredential.GetCredential(uri, “Basic”);

myWebServiceProxy.Credentials = credentials;

// Be sure to set PreAuthenticate to true or else authentication will not be sent.

myWebServiceProxy.PreAuthenticate = true;

myWebServiceProxy.MyMethod(myparam1,myparam2) ;

 

Good Luck!

Categories: Uncategorized