Introduction aux données spatiales dans SQL Server

Guazzo Jean-Marc - 2010/08/25

Résumé

Cet article présente une introduction volontairement simplifiée des fonctionnalités spatiales maintenant fournies avec SQL Server. Le but étant d'exposer les différents types de données spatiales et en détailler certains en profondeur.Il est agrémenté de quelques exemples d'utilisation de données spatiales.

Cet article s'adresse aux architectes ou aux développeurs ayant des connaissances de base en SQL.

Qu'est-ce qu'une donnée spatiale ?

Une donnée spatiale est une coordonnée représentant une localisation dans un espace quelconque. L'exemple le plus connu étant les notions de longitude et de latitude utilisés pour se localiser sur terre.

De la même manière, l'affichage de composant logiciel (icônes, boutons,...) sur un ordinateur se fait avec des coordonnées spatiales, X indiquant la position sur le plan horizontal, Y celle sur le plan vertical et Z la notion de profondeur ou d'élévation qui permet d'indiquer la localisation dans les 3 dimensions. Ces notions ne sont pas étrangères aux mathématiciens...

Ces trois axes sont souvent représentées avec les variable X,Y & Z tel que montré sur le schéma ci-dessous

Cet article présente une donnée spatiale comme la représentation de ces trois points sous un format utilisable par un système automatique. La définition officielle de Industrie Canada est plus riche : Données et renseignements qui sont liés à un emplacement à la surface de la Terre par des coordonnées scientifiques précises, comme des cartes, des cartes spécialisées, des photos aériennes, des images satellitaires et des données d'arpentage ou des relevés hydrographiques. http://www.ic.gc.ca/eic/site/trm-crt.nsf/fra/rm00196.html



Qu'est-ce qu'une donnée spatiale pour SQL Server ?


Pour SQL Server, les données spatiales représentent autant les points "POINT" (représentés par une coordonnée) que les ensembles de points reliés sous forme de ligne "LINESTRING" ou de polygones "POLYGON".

Vecteurs "LINESTRING"

© 2010 Microsoft Corporation. All rights reserved.

Polygones "POLYGON"

© 2010 Microsoft Corporation. All rights reserved.

Les types de données GEOMETRY et GEOGRAPHY font partie d'une hiérarchie d'objet présentés ici
© 2010 Microsoft Corporation. All rights reserved.

La différence entre GEOMETRY et GEOGRAPHY consiste en une application de règles de calcul différentes entre les deux. En effet, le calcul d'une distance en géométrie planaire se fait avec l'application du théorème de Pythagore alors que les calculs entre des degrés de latitude ou longitude n'est pas linéaire, mais dépendante de la courbure de la terre.

De là, il existe des différences dans les calculs entre GEOMETRY et GEOGRAPHY.
GEOMETRY donne des résultats sans unité de mesure alors que les résultats pour GEOGRAPHY sont exprimés en mètres ou m².

Il existe d'autres limitations liés aux types qui sont expliqués sur la page :
"Types de données spatiales"http://technet.microsoft.com/fr-fr/library/bb964711.aspx.

Les exemples de cet article concernent principalement les données de type GEOGRAPHY.

Il existe plusieurs méthodes pour assigner une(des) valeur(s) à une variable de type GEOGRAPHY:

• STGeomFromText http://technet.microsoft.com/fr-fr/library/bb933834.aspx
• STPointFromText http://technet.microsoft.com/fr-fr/library/bb933979.aspx
• Liste complète http://technet.microsoft.com/fr-fr/library/bb933988.aspx

Longitude et Latitude en X Y ?


Pour la spécification des "GEOGRAPHY", il faut utiliser les coordonnées Longitude et latitude sous forme "degré décimal" (DD).

La conversion de 44°23"18' se fait avec le calcul suivant :

D = Degrés = 44

.d = (Minutes + (Secondes / 60) / 60 = (23 + (18/60))/60

DD = D + .d = 44.03883333

La longitude exprime l'angle à partir d'un méridien de référence, Greenwich.
Cet angle indique les orientations Ouest ou Est. En DD, une longitude Ouest est négative, une Est positive.

La latitude exprime l'angle à partir d'un plan de référence, l'équateur.
Cet angle indique les orientations Nord ou Sud. En DD, une latitude Sud est négative, une Nord positive.

Exemples:
Plattsburgh (Etat de NY) 44° 40'N 73° 30'W ¦ 44.670 -73.500
Quebec 46° 49'N 71° 15'W ¦ 46.833 -71.250
Londres 51° 30'N 0° 10'W ¦ 51.500 -0.167 (Juste "à côté" du méridien de Greenwich)
Quito 0° 13'S 78° 30'W ¦ -0.233 -78.500 (Presque sur l'équateur)

Et 0° 0'S 0° 0'W, c'est où ? Océan atlantique, 600km au sud du Ghana !

Pour la simplification des exemples, la longitude sera représentée par L et la latitude par l.

Utilisation des données "POINT"

Comme son nom l'indique une donnée POINT représente un point dans un espace.

Un point est défini au minimum par X et Y. Il est aussi possible de spécifier l'élévation Z et une mesure quelconque M (au format DOUBLE).

M permet de définir plus finement votre point : la date de relevé, la station ayant effectué le relevé,...

Z et M peuvent être NULL.

La création d'un point avec STGeomFromText : GEOGRAPHY::STGeomFromText('POINT(L l élévation mesure)',4326)

La méthode STGeomFromText est souvent la plus utilisée car elle permet d'analyser une chaîne de caractère et d'en retourner l'information indépendamment de son type (Point, LineString,...)

De plus cette méthode permet d'indiquer le système de référence de calcul qui doit être utilisé. Cette référence est importante car elle permet à SQL Server de savoir s'il s'agit de coordonnées pour une représentation "plate" de la terre, d'une représentation Sphérique ou de tout autre modèle de référence.

SQL Server gère en natif ± 390 références, la plus utilisée est la N°4326 "WGS 84", utilisée par les GPS.

WGS84 sur Wikipedia http://fr.wikipedia.org/wiki/WGS_84.

la liste des références valides pour un sql server peut être obtenue avec l'instruction select * from sys.spatial_reference_systems

Attention Les coordonnées géographiques sont toujours exprimées en lat/Long, ce qui représente une coordonnées y/x.

Pour la création avec STGeomFromText, il faut alors utiliser la séquence Longitude/Latitude !

Selon la documentation Microsoft, la création d'un point peut aussi se faire avec : = GEOGRAPHY::Point(X, Y, 4326);
Hors, dans ce cas, X est la latitude et Y la longitude !
L'Instruction GEOGRAPHY::STGeomFromText('POINT(-71.306 46.831)',4326) est donc égale à GEOGRAPHY::Point(46.831, -71.306, 4326) !

Ceci est dû au fait que l'instruction STGeomFromText prend en paramètre une chaîne de caractère au format WKT qui défini le point sous la forme POINT(Longitude latitude Élévation).

Si vous utilisez une technique, il est conseillé de ne pas utiliser l'autre dans un même script !

Exemple SQL:
DECLARE @bureauDMR GEOGRAPHY
SET @bureauDMR= GEOGRAPHY::STGeomFromText('POINT(-71.306 46.831 25)',4326) --[i]Localisation de DMR sur un plan XYZ.
SELECT @bureauDMR

La valeur retournée est 0xE6100000010DEE7C3F355E6A4740DD24068195D351C00000000000003940.



Vu la difficulté d'interpréter cette valeur, il existe maintenant un onglet résultat dans SQL Server de type "Spatial" qui représente une grille :
La flèche bleue représente le point dessiné.

Opérations possibles sur des "POINT"

• Il est possible d'effectuer des opérations de calculs entre des points :

ex : Distance (à vol d'oiseau) Trois Rivières-Toronto

DECLARE @distanceTroisRiviersToronto GEOGRAPHY
SET @distanceTroisRiviersToronto= GEOGRAPHY::STGeomFromText('POINT(-72.567 46.350 )',4326)
select @distanceTroisRiviersToronto.STDistance(GEOGRAPHY::STGeomFromText('POINT(-79.417 43.700)',4326))

Résultat (en mètres)
614657,203710419 = ±615km

• Vérifier que 2 points sont identiques avec STEquals
• Vérifier qu'un point est à l'intérieur d'un polygon avec STWithin

Utilisation des données "LINESTRING"

LINESTRING est la représentation d'un vecteur composé au minimum de 2 points distincts, ou vide.

© 2010 Microsoft Corporation. All rights reserved.


La création d'un linestring avec STGeomFromText :
GEOGRAPHY::STGeomFromText('LINESTRING(L l [elevation] [mesure],L l [elevation] [mesure],...)',4326)

Exemple d'un vecteur représentant le trajet Québec,Trois Rivières,Montréal,toronto
GEOGRAPHY::STGeomFromText('LINESTRING(-71.250 46.833 ,-72.567 46.350 ,-73.600 45.500 ,-79.417 43.700)',4326)

et
1: Québec 2: Trois-Rivières 3: Montréal 4: Toronto

Transposition de ce vecteur sur google Earth:
© 2010 Google. All rights reserved.

Opérations possibles sur des "LINESTRING"

• Déterminer la longueur d'un vecteur avec STLength

DECLARE @g GEOGRAPHY
set @g= GEOGRAPHY::STGeomFromText('LINESTRING(-71.250 46.833 ,-72.567 46.350 ,-73.600 45.500 ,-79.417 43.700)',4326)
select @g.STLength()

Résultat (en mètres)
741324,790446812 = ±741km

• Déterminer Le point d'intersection de deux vecteurs

declare @line1 geography
declare @line2 geography
set @line1 = GEOGRAPHY::STGeomFromText('LINESTRING(0 0, 12 10)',4326)
set @line2 = GEOGRAPHY::STGeomFromText('LINESTRING(0 11, 10 00)',4326)
select @line1 --affichage de la ligne 1
union all
select @line1.STIntersection(@line2) --affichage du point d'intersection
union all
select @line2 --affichage de la ligne 1

Résultat


Utilisation des données "POLYGON"

POLYGON représente une surface définie par chacun de ses points d'angle.

© 2010 Microsoft Corporation. All rights reserved.

Le chemin parcouru entre les différents points d'un polygone ne peut se croiser.
Un POLYGON peu contenir des "trous".
Chaque point accepte 4 valeurs : X, Y, Z (l'élévation) et M.

Les coordonnées du dernier point d'un POLYGON doivent toujours être identique à celle du premier point.

La création d'un POLYGON avec STGeomFromText :
GEOGRAPHY::STGeomFromText('POLYGON((L1 l1, L2 l2, L3 l3, L4 l4, [U] L1 l1 [U]))',4326)


Exemple d'un polygone Québec,Trois Rivières,Montréal,toronto
DECLARE @g GEOGRAPHY
set @g= GEOGRAPHY::STGeomFromText('POLYGON((-71.250 46.833, -72.567 46.350, -73.600 45.500, -79.417 43.700, -71.250 46.833))',4326)
select @g

! Erreur ! :

Extrait : " Cette erreur a souvent pour origine une mauvaise orientation de l'anneau du polygone."
Ceci est dû au fait que la création d'un polygone doit se faire dans le sens anti horlogique pour le pourtour et dans le sens horlogique pour les "trous".

Notre exemple devient donc :
Exemple d'un polygone Québec,toronto,Montréal,Trois Rivières,
DECLARE @g GEOGRAPHY
set @g= GEOGRAPHY::STGeomFromText('POLYGON((-71.250 46.833,-79.417 43.700, -73.600 45.500, -72.567 46.350, -71.250 46.833))',4326)
select @g


1: Québec 2: Trois-Rivières 3: Montréal 4: Toronto

La définition d'un "trou" doit être ajoutée à la définition du POLYGON :
Exemple avec des données de type GEOMETRY:
select GEOMETRY ::STGeomFromText( 'POLYGON ((302 263, 303 263, 303 265, 305 268, 308 272, 310 275, 314 279, 316 282, 318 284, 319 284, 321 286, 323 288, 325 290, 327 292, 331 295, 334 297, 337 300, 341 303, 345 305, 346 306, 347 307, 349 309, 350 310, 353 312, 354 314, 356 316, 357 318, 358 320, 360 323, 362 325, 365 328, 367 331, 369 334, 371 337, 375 340, 377 342, 380 345, 384 348, 386 351, 390 353, 392 355, 393 356, 395 357, 395 358, 398 360, 399 361, 399 362, 399 363, 397 370, 395 375, 391 382, 378 397, 374 404, 368 410, 360 416, 355 420, 347 423, 339 427, 330 429, 327 430, 319 430, 307 430, 300 430, 282 427, 267 423, 253 418, 240 412, 229 405, 221 398, 214 393, 206 382, 199 373, 192 363, 176 343, 168 328, 160 312, 148 286, 141 262, 138 239, 138 207, 153 173, 166 154, 187 131, 201 121, 210 115, 222 108, 253 94, 276 84, 294 80, 319 73, 340 70, 356 72, 379 75, 398 82, 411 91, 421 102, 435 118, 436 122, 435 122, 434 122, 426 129, 403 148, 388 167, 369 185, 357 202, 350 211, 342 219, 330 230, 316 242, 313 246, 312 246, 311 247, 310 247, 310 248, 308 248, 302 263), (271 371, 271 372, 274 375, 280 378, 284 379, 294 379, 303 377, 307 375, 309 373, 312 367, 313 364, 314 358, 315 351, 314 346, 310 339, 307 336, 302 333, 299 333, 292 333, 284 336, 283 337, 280 340, 277 343, 273 350, 270 353, 270 354, 269 359, 268 360, 268 362, 268 363, 268 365, 268 367, 269 369, 270 370, 271 371))'
,0)



Cas pratiques

• Cas 1 : Respect d'une zone de pêche

Pour cet exemple, imaginons une zone de pêche couvrant un périmètre allant de Rimouski à St John's, principalement le golf du St Laurent.
geography::Parse('polygon((
-69.86402778 48.72454722,
-68.86096944 47.81318611,
-67.180925 47.01669722,
-66.47033611 43.1619,
-64.36702222 43.46948611,
-59.93453333 44.30665,
-56.79570556 45.03044444,
-53.26256389 44.82328333,
-51.64941667 46.93513611,
-63.43413333 50.99363333,
-69.86402778 48.72454722)
)');

Nous pouvons alors imaginer que certains bateaux de pêches sont munis de GPS qui génèrent des fichiers reprenant le parcours de pêche effectué.
Ceci servant à établir une base statistique des bateaux qui restent ou sortent de cette zone.

Représentation :

Dans notre exemple, 2 tracés de bateaux.
Le premier représenté en bleu est resté dans la zone limitée :
geography::Parse('LINESTRING(
-64.21 48.52,
-64.00 48.00,
-63.50 47.50,
-63.00 47.00,
-63.00 48.00,
-63.50 48.00,
-64.00 48.00
)')

Le deuxième représenté en rose a dépassé la zone limitée :
geography::Parse('LINESTRING(
-64.21 48.52,
-64.00 48.52,
-63.50 48.52,
-63.00 48.52,
-62.50 48.52,
-62.00 48.52,
-61.50 48.52,
-61.00 48.52,
-60.50 48.52,
-60.00 48.52,
-59.80 49.00,
-59.60 49.48,
-59.40 49.96,
-59.20 50.44,
-59.00 50.92,
-58.80 51.40,
-58.60 51.88,
-58.40 52.36
)')


SQL Server peut nous dire si un chemin est dans un polygone ou s'il en déborde.
En faisant une union entre le POLYGON et le LINESTRING, le résultat doit être égal au POLYGON.
S'il est différent, le LINESTRING a des points en dehors du POLYGON.

declare @zoneDePeche geography
set @zoneDePeche= geography::Parse('polygon((-69.86402778 48.72454722,-68.86096944 47.81318611,-67.180925 47.01669722,-66.47033611 43.1619,-64.36702222 43.46948611,-59.93453333 44.30665,-56.79570556 45.03044444,-53.26256389 44.82328333,-51.64941667 46.93513611,-63.43413333 50.99363333,-69.86402778 48.72454722))')
declare @bateau1 geography
set @bateau1= geography::Parse('LINESTRING(-64.21 48.52,-64.00 48.00,-63.50 47.50,-63.00 47.00,-63.00 48.00,-63.50 48.00,-64.00 48.00)')
declare @bateau2 geography
set @bateau2=geography::Parse('LINESTRING(-64.21 48.52,-64.00 48.52,-63.50 48.52,-63.00 48.52,-62.50 48.52,-62.00 48.52,-61.50 48.52,-61.00 48.52,-60.50 48.52,-60.00 48.52,-59.80 49.00,-59.60 49.48,-59.40 49.96,-59.20 50.44,-59.00 50.92,-58.80 51.40,-58.60 51.88,-58.40 52.36)')

select @zoneDePeche.STEquals( @zoneDePeche.STUnion(@bateau1))
==> Résultat = 1. Le bateau est resté dans la zone

select @zoneDePeche.STEquals( @zoneDePeche.STUnion(@bateau2))
==> Résultat = 0. Le bateau est sorti de la zone

• Cas 2 : Top 10 des villes Canadiennes par population

Pour cette requête, il faut une table reprenant les villes du monde entier extraites du fichier CITIES1000.ZIP du site Geonames, http://www.geonames.org.
Ce fichier, reprenant toutes les villes du monde de plus de 1000 habitants est disponible ici :http://download.geonames.org/export/dump/cities1000.zip
Etape 1 : Création de la table "Villes"
CREATE TABLE [dbo].[Villes]
(
[geonameid] INT PRIMARY KEY,
[Nom] NVARCHAR(200),
[NomAscii] VARCHAR(200),
[AutresNoms] VARCHAR(5000),
[latitude] DECIMAL(38,10),
[longitude] DECIMAL(38,10),
[classGeoname] VARCHAR(10),
[codeGeoname] VARCHAR(10),
[codePays] VARCHAR(2),
[cc2] VARCHAR(60),
[CodeAdmin1code] VARCHAR(20),
[CodeAdmin2code] VARCHAR(80),
[CodeAdmin3code] VARCHAR(20),
[CodeAdmin4code] VARCHAR(20),
[Population] bigINT,
[Elevation] INT,
[gtopo30] INT,
[TimeZone] VARCHAR(100),
[MAJdate] DATETIME
)
GO

Etape 2 : Insertion "BULK" des données dans la table :
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT Villes
FROM ''D:\source\cities1000\cities1000.txt''
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)
GO

Etape 3 : Ajout de la colonne de type GEOGRAPHY
ALTER TABLE [dbo].[Villes]
ADD SQLLocalisation GEOGRAPHY
GO

Etape 4 : Calcul de la valeur de la colonne de type GEOGRAPHY
UPDATE [dbo].[Villes]
SET [SQLLocalisation] = Geography::Parse('POINT(' +
CAST([longitude] AS VARCHAR(20)) + ' ' +
CAST([latitude] AS VARCHAR(20)) + ')')
GO

La table Villes contient maintenant les données requises pour faire notre requête et en générer le graphique
select top 10 NomAscii,population,SQLLocalisation from Villes
where codePays='CA'
order by population desc



Dans ce cas, l'onglet de résultat Spatial de SQL Server Management Studio n'est pas très utile.
Il faut utiliser "SQL Server Business Intelligence Development Studio" qui permet de générer des rapports qui pourraient être hébergé sur le server.
Il existe un contrôle "Map" qui permet d'afficher une carte et de la lier à Microsoft Bing Maps.


Note : Les informations de localisation de Geonames sont correctes mais concernant la population, elles ne sont pas récentes et ne prennent pas en compte les valeurs renseignées par le recensement 2006 de "Statistique Canada" disponible ici : http://www12.statcan.ca/english/census06/data/popdwell/Table.cfm?T=201&S=3&O=D&RPP=150

Indexation

Comme tous la plupart des types de données utilisés par SQL Server, il possible de créer un index sur les données spatiales.
Instruction :
CREATE SPATIAL INDEX NomDeLIndex ON NomDeLaTable(NomDeLaColonne);

Néanmoins, il faut savoir que l'utilisation par défaut ne couvre pas toutes les possibilités et situations.
En effet, de par sa nature même, le type de données GEOGRAPHY nécessite une gestion des index très particulière.

Les données géographiques simples telle que POINT sont déjà à deux dimensions alors qu'un index classique ne travaille que sur une dimension.
Les données multiples (LINESTRING,POLYGON,...) sont encore plus complexes à indexer.
Il existe donc plusieurs méthodes d'indexation qu'il est inutile d'expliquer ici vu l'excellent article de la documentation de SQL Server à ce sujet :
"Vue d'ensemble de l'indexation spatiale" http://technet.microsoft.com/fr-fr/library/bb964712.aspx.

Liens :

Industrie Canada - Définition de Donnée spatiale http://www.ic.gc.ca/eic/site/trm-crt.nsf/fra/rm00196.html
Microsoft - Types de données spatiales http://technet.microsoft.com/fr-fr/library/bb964711.aspx.
Wikipedia - WGS84 http://fr.wikipedia.org/wiki/WGS_84.
Go4Answers - Exemple de polygone avec "Trou" http://www.go4answers.com/Example/create-geography-multipolygon-wkt-51932.aspx.
Geonames - une base de données Open Source contenant les coordonnées de villes, pays, montagne,... http://www.geonames.org.
Statistique Canada - Liste des villes et leur population http://www12.statcan.ca/english/census06/data/popdwell/Table.cfm?T=201&S=3&O=D&RPP=150
Microsoft - Vue d'ensemble de l'indexation spatiale http://technet.microsoft.com/fr-fr/library/bb964712.aspx.
Microsoft - Getting Started with the geography Data Type http://msdn.microsoft.com/en-us/library/bb895266(v=SQL.100).aspx
Jason Follas - SQL Server Spatial Data part 1 http://www.jasonfollas.com/blog/archive/2008/03/14/sql-server-2008-spatial-data-part-1.aspx
SQLMusings - SQL Server Spatial Data for Canada http://www.sqlmusings.com/2010/03/19/sql-server-spatial-data-for-canada/

Merci à Thierry Blanchart et Laurent Docquir !

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...