Outils

L'indispensable pour l'analyse de votre base :
http://www.microsoft.com/downloads/details.aspx?FamilyId=da0531e4-e94c-4991-82fa-f0e3fbd05e63&displaylang=en
Modifier

Change owner

Création de script permettant le changement de propriétaire sur un ensemble de talbes.

{BR}
SELECT 'EXEC(''sp_changeobjectowner @objname = '''''+{BR}
ltrim(u.name) + '.' + ltrim(s.name) + '''''' + ', @newowner = dbo'')'{BR}
FROM sysobjects s,sysusers u{BR}
WHERE s.uid = u.uid AND u.name <> 'dbo'{BR}
AND xtype in ('V', 'P', 'U','FN','IF','TF'){BR}
AND u.name not like 'INFORMATION%' order by s.name{BR}

Modifier

Table temporaire en mémoire

Plusieurs tables temporaires sont possibles en TSQL
Il faut limiter l'utilisation de table en mémoire sur de petites procédures stockée ne nécessitant pas de recompilations ni une masse importante de données.
Pour vérifier la pertinence d'une solution ou d'une autre utiliser l'option WITH RECOMPILE sur votre procédure stockée de façon à observer les différences de perf lors d'une recompilation. L'affichage des performance est possible en indiquant
set statistics io on
set statistics time on

EN MEMOIRE
Limitation à la transaction ou procédure dans laquelle elle est crée. Ne supporte pas la création d'index cluster.
Pas de gestion de lock donc pas de conflit. La table est gérée dans le cache en mémoire, mais si les données sont trop importantes la table est gérée dans tempdb

Ex :
declare @TmpActors table (
PUBLISHER_ACTORID int,
Points int ,
PUBLISHER_ACTOR nvarchar (250)
)
utilisation
INSERT INTO @TmpActors
SELECT
TOP 30 WebSearchActors.PUBLISHER_ACTORID,
ct.RANK as Point,
PUBLISHER_ACTOR
FROM
WebSearchActors

LOCALE
en utilisant #Table

GLOBALE
en utilisant ##Table

Modifier

Full Text Search

Tips Full Text Search

Modifier

Modification de l'incrément automatique

Select 'Script a exécuter sur les deux bases, il sagit ensuite de positionner les
incrément automatique au même numérique le plus élevé'
Select 'Directory'
DBCC CHECKIDENT ('Directory', NORESEED)
Select 'Filter'
DBCC CHECKIDENT ('Filter', NORESEED)
Select 'Parameter'
DBCC CHECKIDENT ('Parameter', NORESEED)
' Maintenant on repositionne les ID au niveau le plus élevé
DBCC CHECKIDENT ('Test',RESEED,XXXXX)
' XXXXX est le numérique retourné précédement le plus élevé des deux bases + 1
' après il faut supprimer sur la source le step ou paramètres bloquant et le recréer.

Modifier
Fonction scalar simple : retourne une table
CREATE FUNCTION whichContinent
(@Country nvarchar(15))
RETURNS varchar(30)
AS
BEGIN
declare @Return varchar(30)
select @return = case @Country
when 'Argentina' then 'South America'
when 'Belgium' then 'Europe'
when 'Brazil' then 'South America'
when 'Canada' then 'North America'
when 'Denmark' then 'Europe'
when 'Finland' then 'Europe'
when 'France' then 'Europe'
else 'Unknown'
end

return @return
end

UTILISATION
print dbo.WhichContinent('USA')

select dbo.WhichContinent(Customers.Country), customers.*
from customers

create table test
(Country varchar(15),
Continent as (dbo.WhichContinent(Country)))

insert into test (country)
values ('USA')

select * from test

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Country Continent
--- ------
USA North America

RETOURNE UNE TABLE
CREATE FUNCTION CustomersByContinent
(@Continent varchar(30))
RETURNS TABLE
AS
RETURN
SELECT dbo.WhichContinent(Customers.Country) as continent,
customers.*
FROM customers
WHERE dbo.WhichContinent(Customers.Country) = @Continent
GO

SELECT * from CustomersbyContinent('North America')
SELECT * from CustomersByContinent('South America')
SELECT * from customersbyContinent('Unknown')

Modifier
Création des scripts sql d'une base de données
Bonjour,
Il existe des scripts permettant l'export des données base en SQL
L'avantage étant ensuite de faire des snapshot et de comparer via les outils standard, même chose pour recréer des script de mise à jour.

1] Téléchargez : http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard
2] Executer la ligne
C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz script -d AdventureWorks "C:\AdventureWorks.sql" -dataonly

nb : c'est aussi possible pour les schema/objets/droits
3] Comparez les fichiers dev/inté/prod

Modifier
SQL 2005 : Try and catch
CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS

BEGIN TRY
BEGIN TRANSACTION ~~Start the transaction~~

Delete the Employee's phone numbers
DELETE FROM EmployeePhoneNumbers
WHERE EmployeeID = @EmployeeID

Delete the Employee record
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID

If we reach here, success!
COMMIT
END TRY
BEGIN CATCH
Whoops, there was an error
IF
TRANCOUNT > 0{BR} ROLLBACK{BR} {BR} -- Raise an error with the details of the exception{BR} DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int{BR} SELECT @ErrMsg = ERROR_MESSAGE(),{BR} @ErrSeverity = ERROR_SEVERITY(){BR} {BR} RAISERROR(@ErrMsg, @ErrSeverity, 1){BR} END CATCH{BR} {BR} == Gestion des erreurs SQL 2000 == Gestion des erreurs au niveau des procédures stockées lorsque l'on arrive sur un projet un peu plus gros au niveau base{BR} {BR} {BR} 1] On déclare 2 variables le code erreur et le message{BR} DECLARE @_Error nvarchar(100){BR} DECLARE @ErrorState int{BR} 2] puis retour d'erreurs programmée{BR} IF EXISTS({BR} SELECT top 1 *{BR} FROM Address{BR} WHERE (AccountID = @AccountID) AND (PhoneNumber = @PhoneNumber){BR} ){BR} BEGIN{BR} Select top 1{BR} @ErrorState = 51, -- Les erreurs supérieures à 50 sont non bloquante.{BR} @_Error = 'Erreur doublon sur l''enregistrement du numéros de téléphone '+cast(PhoneNumber as varchar(20))+', identifiant existant : '+cast(IdentityID as varchar(20))+''{BR} FROM Address a{BR} inner join [identity] b on b.fk_AddressID = AddressID{BR} WHERE (AccountID = @AccountID) AND (PhoneNumber = @PhoneNumber){BR} return{BR} END{BR} 3] Les erreurs sont bloquantes ou non en fonction de leur code de retour{BR} >50 warning, non bloquant{BR} <50 fatal, erreur{BR} 4] avec transactions{BR} IF (
ERROR <> 0)
BEGIN
Select
@ErrorState = 52,
@_Error = 'Erreur sur linsertion de ladresse en base'
GOTO ROLLBACK_ON_ERROR
END
5] Gestion d'erreur en fin de procédure stockée
RETURN
ROLLBACK_ON_ERROR:
ROLLBACK TRANSACTION

RAISERROR (@_Error, 15, @ErrorState)

RETURN Erreur

GO

________

Exemple complet

/*
Insertion des données pour créer une identité primaire
Insertion dans les tables Adress, Identity, Description, Professional, Publication.

/
CREATE PROCEDURE dbo.PrimaryCreateIdentity
Adresse
@Town nvarchar(60),
@Address0 nvarchar(50)= NULL,
@Address1 nvarchar(50)= NULL,
@Address2 nvarchar(50)= NULL,
@Address3 nvarchar(50)= NULL,
@Cedex char(5)=NULL,
@District char(2)= NULL,
@fk_ClientID bigint=2, Client B2B=1 ou client B2C=2
@PhoneNumber char(10),
@POBox char(10)=NULL,
@PostalCode char(5)=NULL,
@AccountID nvarchar(250),
@NetworkIdentifier char(4)='TISC',
@Portability bit=0, Pas de portabilité par défaut
@PortabilityOperator char(4)=NULL, Pas de portabilité par défaut donc pas de code opérateur de portaiblité
Identité
@Civility int=NULL,
@Name nvarchar(100),
@Surname nvarchar(30)= NULL,
@ValidationState bit=0, non validé par défaut
@Email nvarchar(200)= NULL,
Description
@FirstSurnameInitial bit=0,
@InverseSearch bit=1, Recherche inverse autorisée
@LineFunction char(2)='TF', Ligne téléphonique par défaut (TF : Téléphone Fixe)
@NoProspecting bit=1, Prospection non autorisée
@OtherOperatorReferencing bit=1, Référencement autorisé
@PhoneNumberAppearance char(10)=null, Format par défaut
@Publication bit = 1, Publication dans l'annuaire par défaut
@AddressPublication bit = 1,
@UserType char(1)= 'R', Si le UserType est P ou E on active la partie professionnelle.
Professional (non présent lors de l'initialisation)
@CompanyNaming nvarchar(100)=NULL,
@APE char(4) = NULL,
@SIRET char(14) = NULL,
@Activity nvarchar(100) = NULL,
@LineUsing nvarchar(100)= NULL,
@AdditionalCompanyNaming nvarchar(40) = NULL,
@PublicationDistrict char(2) = NULL,
@PublicationZipCode char(5) = NULL

AS
DECLARE @_IdentityID bigint, @fk_AddressID bigint
DECLARE @_Error nvarchar(100)
DECLARE @ErrorState int

Pas de signe de ponctuation, ni d’italique, ni de souligné à partir de la ligne 4 « Numéro et libellé de la voie », cela peut occasionner une mauvaise reconnaissance des caractères par les lecteurs optiques utilisés pour le tri postal.
declare @test nvarchar(100)
select @test = dbo.funcStripForAddress2(@Address2)
if @test != @Address2
set @ValidationState=0
select @test = dbo.funcStripForAddress2(@Name)
if @test != @Name
set @ValidationState=0
select @test = dbo.funcStripForAddress2(@Surname)
if @test != @Surname
set @ValidationState=0

init
set @ErrorState = 1
set @_Error='Erreur lors de l~~enregistrement en base de données'~~
On vérifie d'abord qu'un doublon ne soit pas inséré en base
IF EXISTS(
SELECT top 1 *
FROM Address
WHERE (AccountID = @AccountID) AND (PhoneNumber = @PhoneNumber)
)
BEGIN

Select top 1
@ErrorState = 51, Les erreurs supérieures à 50 sont non bloquante.
@_Error = 'Erreur doublon sur lenregistrement du numéros de téléphone '+cast(PhoneNumber as varchar(20))+', identifiant existant : '+cast(IdentityID as varchar(20))+
FROM Address a
inner join identity b on b.fk_AddressID = AddressID
WHERE (AccountID = @AccountID) AND (PhoneNumber = @PhoneNumber)

return
END
Sous forme de transaction avec retour d'erreur.

~~Adresse~~
L'arrondissement est initialisé automatique avec les deux derniers chiffres du code postal pour Paris Lyon Marseille.
IF LEN(@PostalCode)>3 AND SUBSTRING(@PostalCode,3,1)='0' AND ( LEFT(@PostalCode,2)='69' OR LEFT(@PostalCode,2)='13' OR LEFT(@PostalCode,2)='75' )
BEGIN
Select @District=RIGHT(@PostalCode,2)
END

BEGIN TRANSACTION

INSERT INTO Address
(Address0, Address1, Address2, Address3, Cedex, District, fk_ClientID, PhoneNumber, PhoneNumberState, POBox, PostalCode, AccountID, Town, NetworkIdentifier,PortabilityOperator)
VALUES (@Address0, @Address1, @Address2, @Address3, @Cedex, @District, @fk_ClientID, @PhoneNumber, 0, @POBox, @PostalCode, @AccountID, @Town, @NetworkIdentifier,@PortabilityOperator )

IF (

ERROR <> 0){BR}
BEGIN{BR}
Select{BR}
@ErrorState = 52,{BR}
@_Error = 'Erreur sur l''insertion de l''adresse en base'{BR}
GOTO ROLLBACK_ON_ERROR{BR}
END{BR}
-- Identité{BR}
SELECT @fk_AddressID =

IDENTITY

INSERT INTO dbo.Identity(
Civility,
fk_AddressID,
Name,
OrderID,
State,
Surname,
ValidationState,
Email
) VALUES (
@Civility,
@fk_AddressID,
@Name,
0, OrderID
1, State : 1 valide
@Surname,
@ValidationState,
@Email
)
IF (

ERROR <> 0) OR (

ROWCOUNT = 0)
BEGIN
Select
@ErrorState = 53,
@_Error = 'Erreur sur linsertion de lidentité en base'
GOTO ROLLBACK_ON_ERROR
END

set @_IdentityID =

IDENTITY{BR}
{BR}
-- Description{BR}
INSERT INTO dbo.[Description] ({BR}
[FirstSurnameInitial],{BR}
[fk_IdentityID],{BR}
[InverseSearch],{BR}
[LineFunction],{BR}
[NoProspecting],{BR}
[OtherOperatorReferencing],{BR}
[PhoneNumberAppearance],{BR}
[Publication],{BR}
[UserType],{BR}
[AddressPublication]{BR}
) VALUES ({BR}
@FirstSurnameInitial,{BR}
@_IdentityID,{BR}
@InverseSearch,{BR}
@LineFunction,{BR}
@NoProspecting,{BR}
@OtherOperatorReferencing,{BR}
@PhoneNumberAppearance,{BR}
@Publication,{BR}
@UserType,{BR}
@AddressPublication{BR}
){BR}
IF (

ERROR <> 0) OR (

ROWCOUNT = 0){BR}
BEGIN{BR}
Select{BR}
@ErrorState = 53,{BR}
@_Error = 'Erreur sur l''initialisation des paramètres de description'{BR}
GOTO ROLLBACK_ON_ERROR{BR}
END{BR}
-- Partie professionnelle{BR}
IF (@UserType ='E' or @UserType='P'){BR}
BEGIN{BR}
INSERT INTO Professional{BR}
(fk_IdentityID, CompanyNaming, APE, SIRET, Activity, LineUsing, AdditionalCompanyNaming, PublicationZipCode, PublicationDistrict){BR}
VALUES (@_IdentityID, @CompanyNaming, @APE, @SIRET, @Activity, @LineUsing, @AdditionalCompanyNaming,@PublicationZipCode,@PublicationDistrict){BR}
IF (

ERROR <> 0) OR (

ROWCOUNT = 0){BR}
BEGIN{BR}
Select{BR}
@ErrorState = 53,{BR}
@_Error = 'Erreur sur la création des données professionnelles'{BR}
GOTO ROLLBACK_ON_ERROR{BR}
END{BR}
END{BR}
-- Publication{BR}
/*{BR}
INSERT INTO Publication{BR}
(fk_IdentityID, Reject, try, PublicationState){BR}
VALUES (@_IdentityID, 0, 0, 0){BR}
IF (

ERROR <> 0) OR (@@ROWCOUNT = 0)
BEGIN
Select @_Error = 'Erreur sur l''initialisation des données de publication'
GOTO ROLLBACK_ON_ERROR
END

COMMIT TRANSACTION

Retour de la structure
Exec IdentityGetInfo @_IdentityID
RETURN

ROLLBACK_ON_ERROR:
ROLLBACK TRANSACTION
RAISERROR (@_Error, 15, @ErrorState)
RETURN Erreur
GO

Modifier

sql 2005 : Rendre la base accessible depuis l'extérieur

Comment rendre une base sql2005 accessible depuis l’exterieur :

Il existe un utilitaire avec un mom barbare situé dans :

Microsoft SQL Server 2005 -> outils de configuration

Il s’agit dans la langue de Shakespeare de :

"SQL Server Surface Area Configuration"

Au lancement et dans la fenetre qui apparait deux liens sont disponible :

« Surface Area Configuration for Services and Connections »
« Surface Area Configuration for Features»

Il suffit donc de vérifier que les connexions distantes sont autorisées et relancer le service sql, ou redémarrer la machine.

Simple, efficace.

Modifier

SQL 2005 Express: Sauvegarde des bases

Ce script permet de créer une Procédure Stockée afin d'effectuer la sauvegarde de l'ensemble des bases de
données et journaux de transaction, puis de compacter ces fichiers avec l'outil de Microsoft MakeCab.
Ainsi, il faut déjà commencer par modifier un paramètre de notre serveur SQL Express afin d'autoriser
l'exécution des commandes externes depuis le moteur SQL Server.

Ceci se fait en allant dans :
· Démarrer > Programmes > Microsoft SQL Server 2005 > Outils de configuration > Configuration de la
surface d'exposition SQL Server

Si vous souhaitez plus d'informations, vous trouverez l'explication sur la configuration :
· Les Outils de Configuration de SQL Server 2005 Express Edition

Il faut alors cliquer sur "Configuration de la surface d'exposition pour les fonctionnalités" afin d'activer la
possibilité de lancer des commandes XP_CMDSHELL.

On valide par OK. On peut donc maintenant exécuter le script de création de notre procédure stockée :
· master.dbo.SP_System_SauvegardesTotales
Comme expliqué sur le site, nous avons plusieurs mode d'exécution de ce script, nous prendrons celui qui
effectue le backup des logs de transaction et le backup des fichiers de données. Nous sauvegarderons tous ces
fichiers dans le répertoire créé à cet effet "C:\BackupExpress\".
Cela se traduit donc par la commande suivante :
· EXEC master.dbo.SP_System_SauvegardesTotales 1, 1, 'C:\BackupExpress\'
Une fois cette commande exécutée, nous trouvons un ensemble de fichiers CAB au format :
· NomDB_Date.CAB : pour les fichiers de données
· NomDBLog_Date.CAB : Pour les fichiers de log
Nous pouvons maintenant créer notre fichier BATCH qui va simplement effectuer l'exécution de cette procédure
stockée avec les paramètres précédents.
sqlcmd
-S MONSERVEUR\SQLEXPRESS -U sa -P MotDePasse
-Q "EXEC master.dbo.SP_System_SauvegardesTotales 1, 1, 'C:\BackupExpress\'"
Nous pouvons donc maintenant planifier cette exécution automatique dans le planificateur des taches de
Windows.

Modifier

Forcer la colonne identity

vous trouverez ci dessous quelques instruction permettant de remettre "en ordre" les colonne identité d'une table, ceci en cas de différence entre la prod et le dev.
Attention cependant il n'y a aucun check de cohérence sur la remise à une valeur données sur la colonne identité lorsque vous forcez celle-ci. En cas d'erreur de valeur vous risqueriez de ne plus pouvoir insérer de valeur en base (réexecuter alors avec les bonnes valeurs l'instruction de forcing)

Code
/*

CREATE TABLE dbo.Test (
TestID int IDENTITY (1, 1) NOT NULL ,
Nom char (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON PRIMARY
GO

Insertion dans la table d'une valeur de clé spécifique
SET IDENTITY_INSERT test ON
INSERT Test(TestID,Nom) VALUES (5,'Nom1') Forcing de l'ID
SET IDENTITY_INSERT test OFF
Insertion normal, la valeur du champ est recalculé à partir de la valeur la plus haute, donc 6
INSERT Test(Nom) VALUES ('Nom1')
Permet d'obtenir la valeur du champ max Identity
DBCC CHECKIDENT ('Test', NORESEED) ~~Retourne 6~~
Suppression de l'enregistrement 6
Delete from test where TestID=6 ~~La prochaine insertion aura un ID = 7~~
Remet sur la plus haute valeur en base le champ Identity
DBCC CHECKIDENT ('Test',RESEED,5) ~~Remet l'id à 6 pour la prochaine insertion~~
test d'insertion
INSERT Test(Nom) VALUES ('Nom6') -- l'Id en base est 6

Nano Krill

Outils

Change owner

Table temporaire en mémoire

Full Text Search

Modification de l'incrément automatique

Fonction scalar simple : retourne une table

Création des scripts sql d'une base de données

SQL 2005 : Try and catch

sql 2005 : Rendre la base accessible depuis l'extérieur

SQL 2005 Express: Sauvegarde des bases

Forcer la colonne identity

Points importants