Concatenated results in a select in SQL Server

My colleague wanted to have concatenated result in a query like this :

Class Participants

C1 John, Albert, Frank, Neda, Abdelkader, Ismaël

C2 Liisi, Daniel, Didier, Dimitri,

...

There are some solutions out there like with Using a CTE, doing a simple concatenation in a variable.

My solution is simpler and direct :

Do your standard select with XML Explicit

SELECT 1 AS TAG,NULL AS PARENT,FirstName AS [Users!1!FirstName] FROM Users FOR XML EXPLICIT

this bring a result like

<Users FirstName="John"/><Users FirstName="Albert"/><Users FirstName="Frank"/><Users FirstName="Neda"/><Users FirstName="Abdelkader"/><Users FirstName="Ismaël"/><Users FirstName="Liisi"/><Users FirstName="Daniel"/><Users FirstName="Didier"/><Users FirstName="Dimitri"/>

I specify XML Explicit so that I can replace the tag's name when I insert the query as an inner query and be sure that it's really the tag name !

SELECT ClassName,

REPLACE( REPLACE((SELECT 1 AS TAG,NULL AS PARENT,FirstName AS [Users!1!FirstName] FROM Users, classes_users WHERE Users.userid=Classes_users.Users AND classes_users.classes = classId FOR XML EXPLICIT ),'',', ') AS Users

FROM classes

And it works :

The unwanted side effect is that now you should remove the trailing , at the end.

Could be done with a substring or in the application that fetches the data.

Delete workspace on Team Foundation Server

How is it possible to delete a workspace on a machine who's main user account is locked ?
1. Get the whole list of workspaces :

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE>tf workspaces 
/server:http://servername:serverport/ /owner:*

this gives a list like :

Workspace                          Owner      Computer   Comment
---------------------------------- ---------- ---------- ---------------------
CIS000274                          USER1      CIS000274
CIS000605                          USER2      CIS000605
CIS000957                          USER3      CIS000957
CIS001242                          GONEUSR    CIS001242
CIS001242                          USER4      CIS001242
CIS001317                          USER5      CIS001317
...

2. Find the one you want to delete.
3. To delete the workspace for user GONEUSR, I only need to do :

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE>tf workspace /delete 
CIS001242;DOMAIN\GONEUSR /server:http://servername:serverport/

A deleted workspace cannot be recovered.
Workspace 'CIS001242;DOMAIN\GONEUSR' on server 'http://servername:serverport/' has
151 pending change(s).
Are you sure you want to delete the workspace? (Yes/No) y

And it's done.

DAYS360 in SQL Server

Ok, so this function is quite interesting in Excel but doesn't exists in SQL Server.
I couldn't find one quickly on the web so I did it myself.

What's the goal ?
Determine the number of days covering a period. This number is often used for accounting because it's based on a year of 360 days and on months of 30 days.
Ex : 2007/11/01 to 2009/04/18 = 527 in DAYS360 but 534 "real" days.
And this method works the same way for any period :
days360(2007/01/01,2007/03/01) = days360(2008/01/01,2008/03/01).

Here is my solution :

DROP FUNCTION DAYS360
GO

CREATE FUNCTION DAYS360(@DATE1 DATETIME,@DATE2 DATETIME,@METHOD BIT = 0)
RETURNS INT 
AS 
BEGIN

DECLARE @dFrom DATETIME,@dTo DATETIME
DECLARE @y INT,@m INT,@d INT,@totalDays INT
SELECT @y=0,@m=0,@d=0

--prepare date that will be used. the smallest goes to @dFrom & the biggest to @dTo
IF @DATE1<@DATE2
 SELECT @dFrom=@DATE1,@dTo=@DATE2
ELSE
 SELECT @dFrom=@DATE2,@dTo=@DATE1

IF @METHOD=1 -- EU MODE : Cfr Excel help
BEGIN 
 IF day(@dFrom)=31 SET @dFrom = dateadd(d,-1,@dFrom)
 IF day(@dTo)=31 SET @ dTo = dateadd(d,-1,@ dTo)
END 

--Get year
SET @y = datediff(yy,@dFrom,@dTo)
--Get month
SET @m = month(@dTo)
--if month from biggest is smaller than month from smallest, we add 12months and remove 1 year
IF @m<month(@dFrom)
BEGIN
 SET @y=@y-1
 SET @m=@m+12
END
SET @m=@m - month(@dFrom)
--Get day
SET @d=day(@dTo)
--if day from biggest is smaller than day from smallest, we add 30 days and remove 1 month. 
--(We remove One year if the value for months was 0)
IF @d<day(@dFrom)
BEGIN
 IF @m=0
 BEGIN
  SET @y=@y-1
  SET @m=@m+12
 END
        SET @m=@m-1
 SET @d=@d+30
END
SET @d=@d - day(@dFrom)

--Compute total days
SET @totalDays=(@Y*360)+(@m*30)+@d

RETURN  @totalDays
 
END

Call this with :

SELECT dbo.days360('20070101','20070301',DEFAULT)

And it returns 60 !

SQLServer : Convert varchar date part in DateTime

DECLARE @month int
SET @month=1
DECLARE @day int
SET @day=7
DECLARE @year int
SET @year = 2004

DECLARE @dte varchar(10)
SET @dte = RIGHT(CAST('0000' + CAST(@year as varchar) as varchar), 4) +RIGHT(CAST('00' + CAST(@month as varchar) as varchar), 2) + RIGHT(CAST('00' + CAST(@day as varchar) as varchar), 2)
DECLARE @myDate datetime
SET @myDate=convert(datetime,@dte,112)
SELECT @myDate

SQL Server 2005 Default Backup Location

Why didn't they put this available through SSMS ?
They have set this at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\BackupDirectory

consuming SQL Server web services

As everyone knows, it's now possible to create - quickly - a web service on sql server.
This web service can contains several methods which are all linked to one stored procedure.

This is done with an EndPoint
Ex :
We have a table called "buildings" where all the building of our organization are stored
We have 2 stored procedures

• up_building_getall() : gets all the building like the figure above (+ other information)
  
• up_building_getone(buildingid,textid) : gets one building by it's id and/or by it's textid.
  

Creating a web service :

DROP ENDPOINT BuildingsEndPoint;
GO

CREATE ENDPOINT BuildingsEndPoint
STATE = STARTED
AS HTTP(
PATH = '/buildings',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR),
site = 'mydbServer'
)
FOR SOAP (
WEBMETHOD 'GetOne' (NAME='mydb.dbo.Up_Building_GetOne'),
WEBMETHOD 'GetAll' (NAME='mydb.dbo.Up_Building_GetAll'),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'mydb'
);
GO

The WSDL for this web service can be accessed at http://mydbserver/buildings?wsdl and gives this nice output :

Add a web reference to your project :





And it's done.

You now have in your project under "Web References" a folder "mydbserver".
Anywhere in your code you can now create a mydbserver.BuildingsEndPoint as in the example here under :

static void Main(string[] args)
{
try
{
TmpConsole.mydbserver.BuildingsEndPoint be = new TmpConsole.mydbserver.BuildingsEndPoint();
be.UseDefaultCredentials = true;

object[] products = be.GetAll();
if (products[0].ToString() == "System.Data.DataSet")
{
DataSet ds = (System.Data.DataSet)products[0];
DataTable dtt = ds.Tables[0];
//simple console method that display a datatable
interpretDataTable(dtt);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}

public static void interpretDataTable(DataTable dtt)
{
if (!(dtt == null || dtt.Rows == null))
{
foreach (DataRow dr in dtt.Rows)
{
Console.Write("- {0}={1}", dtt.Columns[0].ColumnName, dr[0].ToString());
for (int i = 1; i < dtt.Columns.Count; i++)
Console.Write("/{0}={1}", dtt.Columns[i].ColumnName, dr[i].ToString());
Console.WriteLine();
}
}
else
Console.WriteLine("Null or no rows.");
}


Watch out for the "UseDefaultCredentials"
Otherwise, the great HTTP 401 may show it's strength...

Thread with ASP.NET and Integrated security

Recently, we decided to move to integrated security on all our Dev machines so that we won't need complex security management on the dev db.
Therefore, we used the "impersonate" tag in the web/machine .config as following :
<system.web>
<identity impersonate="true" userName="domain\user" password="password" />

Everything was fine except for the process that created thread for launching parallel DB actions.

Ex of Non working code :

threads.Add(new Thread(new ThreadStart(this.LaunchOneSqlCommand)));

The method "LaunchOneSqlCommand" pops one SQLCommand from the stack of commands to execute and launch them with integrated security specified in the connection string :

public void LaunchOneSqlCommand(object windowsIdentity)

{

SqlCommand com = null;

using (SqlConnection con = new SqlConnection(connectionString))

{

try

{

com = popOne();

if (com != null)

con.Open();

while (com != null)

{

com.Connection = con;

int res = com.ExecuteNonQuery();

...

com = popOne();

}

con.Close();

}

catch (Exception ex)

{
...

throw new MultiThreadedSqlCommandsException(ex.Message);

}

}

...

}

This gave us the terrific error :
Login failed for user ''. The user is not associated with a trusted SQL Server connection.

After some test, we guessed that the thread launched by a Dll under asp.net use the same windowsIdentity as aspnet, and not the one specified with the "impersonate" tag.

Therefore, we had to do some adaptations :

threads.Add(new Thread(new ParameterizedThreadStart(this.LaunchOneSqlCommand)));

And the start must be as following :
for (int i = 0; i < threads.Count; i++)
{
((Thread)thrds[i]).Start(System.Security.Principal.WindowsIdentity.GetCurrent());
}

And the method LaunchOneSqlCommand became

public void LaunchOneSqlCommand(object windowsIdentity)

{

string[] rolesArray = {"role1"};

((WindowsIdentity)windowsIdentity).Impersonate();

Thread.CurrentPrincipal = new System.Security.Principal.GenericPrincipal((IIdentity )windowsIdentity, rolesArray);

...
}