Archive
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
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
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
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
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
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.
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://” 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!
Concatenate files to a single file
copy *.txt, myfile.txt
How to create a line in WinForms app
// separator bevel line
label1.AutoSize = false;
label1.Height = 2;
label1.BorderStyle = BorderStyle.Fixed3D;
How to enable CLR Stored Procedures
Here are two ways to enable CLR Stored Procedures
1. Explore “SQL Server 2005/Configuration Tools/Surface Area Configuration” in your Start menu.
2. Select “Surface Area Configuration for Features”
3. You will find “CLR Integration” option, activate it and save.
sp_configure ‘clr enabled’, 1
go
reconfigure
go
Kool-Aid Drinker 