Introduction

Le script suivant permet :

• de récupérer le résultat d’une requête SQL dans un fichier .csv,
• de renseigner les actions réalisées dans l’EventViewer,
• de retourner un code d’erreur (0 ou 1) suite à l’exécution du script.

Prérequis

• Powershell
• SQL

Présentation du script

Variables :

[string] $server = "localhost" : Serveur SQL
[string] $database = "base" : Base SQL
[string] $query = "SELCT * FROM Table" : Requête ou procédure stockée
[string] $extractFilePath = "C:\test.csv" : Emplacemnt de génération du fichier .csv
[string] $delimiter = ";" : Délimiteur à utiliser pour la génération du fichier .csv
[int] $skipline = 1 : Nombre de ligne à tronquer (utile si l’on souhaite retirer la ligne d’entête.

Script

# Variables d'entrée
param
    (
    [string] $server = "localhost", 
    [string] $database = "base",
    [string] $query = "SELECT * FROM Table",
    [string] $extractFilePath = "C:\test.csv",
    [string] $delimiter = ";",
    [int] $skipline = 1

    )

#MAIN
function main
{

#Variables calculées
$full_date = Get-Date;
$custom_date = $full_date.ToString("yyyyMMdd");
$DataSet = New-Object System.Data.DataSet;
$eventsource ="CsvFromSQL_Powershell";
$eventlogname = "Application";
[int] $errorcode = 1;


#Génération de la source d'evenements si inexistante
if ([System.Diagnostics.EventLog]::SourceExists($eventsource) -eq $false) 
    {
        #write-host "Creating event source $eventsource on event log $eventlogname"
        [System.Diagnostics.EventLog]::CreateEventSource($eventsource, $eventlogname)
        #write-host -foregroundcolor green "Event source $eventsource created"
    }
        else
    {
        #write-host -foregroundcolor yellow "Warning: Event source $eventsource already exists. Cannot create this source on Event log $eventlogname"
    }

<#
$logFileExists = Get-EventLog -list | Where-Object {$_.logdisplayname -eq $eventlogname} 
if (! $logFileExists) 
    {
        New-EventLog -LogName $eventlogname -Source $eventsource
    }
#>

#Execution des fonctions et récupération du code d'erreur
$errorcode = Test-SQLConn -_server $server -_database $database;
$errorcode = sql_to_dataset -_server $server -_database $database -_query $query -_dataset $DataSet;
$errorcode = dataset_to_csv -_dataset $DataSet -_extractFilePath $extractFilePath;
return $errorcode;
}

#Test connection SQL
Function Test-SQLConn ($_server, $_database)
{
    $errorcode=0;
    $connectionString = "Data Source=$_server;Integrated Security=true;Initial Catalog=$_database;Connect Timeout=3;";
    $sqlConn = new-object ("Data.SqlClient.SqlConnection") $connectionString;
    trap
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "Cannot connect to server $_server or database $_database.";
        $errorcode=1;
        Write-Host $errorcode;
        exit
        }
    $sqlConn.Open()
    if ($sqlConn.State -eq 'Open')
        {
        $sqlConn.Close();
        Start-Sleep -s 1;
        Write-EventLog  -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "Connected to server $_server on database $_database.";
        }
}

#Rempli un dataset à partir d'une requête SQL
function sql_to_dataset
    {
    param($_server, $_database, $_query, $_dataset);
    $connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};";
    $connectionString = [string]::Format($connectionTemplate, $_server, $_database);
    $errorcode=0;
    try
        {
        $connection = New-Object System.Data.SqlClient.SqlConnection -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The SQL connection has been created successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when creating the SQL connection. Error Message : $_.Exception.Message";
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }

    $connection.ConnectionString = $connectionString;

    try
        {
        $command = New-Object System.Data.SqlClient.SqlCommand -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The SQL command has been created successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when creating the SQL command. Error Message : $_.Exception.Message";
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }
    
    $command.CommandText = $_query;
    $command.Connection = $connection;

    try
        {
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The SQL DataMapper has been created successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when creating the SQL DataMapper. Error Message : $_.Exception.Message"; 
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }
    
    $SqlAdapter.SelectCommand = $command;

    try
        {
        $SqlAdapter.Fill($_dataSet) | Out-Null -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The Dataset has been filled successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when filling the Dataset. Error Message : $_.Exception.Message";
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }

    $connection.Close();
    return $errorcode;
    }

#Rempli un csv avec un dataset
function dataset_to_csv
{
param($_dataset, $_extractFilePath);
$errorcode=0;
    try
        {
        $_dataSet.Tables[0] | ConvertTo-Csv -NoTypeInformation -Delimiter $delimiter | select -Skip $skipline  | Set-Content $_extractFilePath -ErrorAction stop;
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 0 -EntryType Information -Message "The CSV file has been created successfully.";
        }
    catch
        {
        Start-Sleep -s 1;
        Write-EventLog -LogName $eventlogname -Source $eventsource -EventID 1 -EntryType Error -Message "An error occurred when generating the CSV file. Error Message : $_.Exception.Message";
        $errorcode=1;
        Write-Host $errorcode;
        exit;
        }
        return $errorcode;
}

main

Introdution

ATTENTION, l’action proposée par cet article ne fait pas l’objet d’une KB.

La valeur “Maximum package processing threads” permet de limiter le nombre de distribution concurrente de packages.

Cette valeur est bornée de 1 à 50, elle peut être modifiée depuis le console d’administration dans “Configure Site Components”

Problématique

Suite à une mauvaise manipulation, cette valeur a été modifiée à 0. La valeur 0 étant en dehors de [0..50], l’erreur suivante apparait :

Résolution

Le script suivant permet de récupérer la valeur actuelle :

Select * from SC_Component_Property cp join SC_Component c on c . ID = cp . ComponentID join SC_SiteDefinition sd on sd . SiteNumber = c . SiteNumber where cp . name = 'Thread Limit' and ComponentName = 'SMS_DISTRIBUTION_MANAGER' and sd . SiteCode = 'SITECODE'

Le script suivant permet de modifier la valeur à 3 :

Update CP Set Value3 = 3 from SC_Component_Property cp join SC_Component c on c . ID = cp . ComponentID join SC_SiteDefinition sd on sd . SiteNumber = c . SiteNumber where cp . name = 'Thread Limit' and ComponentName = 'SMS_DISTRIBUTION_MANAGER' and sd . SiteCode = 'SITECODE'

Introdution

ATTENTION, l’action proposée par cet article ne fait pas l’objet d’une KB.

La valeur “Maximum package processing threads” permet de limiter le nombre de distribution concurrente de packages.

Cette valeur est bornée de 1 à 50, elle peut être modifiée depuis le console d’administration dans “Configure Site Components”

Problématique

Suite à une mauvaise manipulation, cette valeur a été modifiée à 0. La valeur 0 étant en dehors de [0..50], l’erreur suivante apparait :

Résolution

La valeur peut être modifié depuis SQL (cf. article précèdent), mais également depuis WMI :

Lancer l’outil WMI Tester :

Se connecter à “root\SMS\site_NOMDUSITE” puis cliquer sur “Enum Classes…” :

Rechercher la classe “SMS_SCI_Component” puis double cliquer dessus :

Cliquer sur “props” puis cliquer sur “Instances” :

Choisir “SMS_Distribution_Manager” :

Double cliquer sur “Props” :

Cliquer sur “view embedded” :

Vérifier les entrées suivantes jusqu’à trouver une valeur appelée “Thread Limit”

Modifier la valeur “Value” à la valeur souhaitée, dans cet exemple on passe de 0 (0x0) à 3 (0x0)  :

Fermer toutes les fenêtre en sauvegardant chacune.

Contexte

Afin de purger une table, deux solution sont possibles :

“TRUNCATE table Table_a_vider” ou “DELETE from Table_a_vider”

La principale différence entre ces deux commandes est la suivante :

• TRUNCATE est une commande DDL, cette commande est beaucoup plus rapide, mais son action est irrémédiable,
• DELETE est une commande DML, sa durée d’exécution dépends du volume de données à traiter.

Ces deux commandes ont malgré tout pour point commun de nécessiter un lock exclusif sur les données à supprimer. Si ce lock n’est pas obtenu, la commande vas continuer de s’exécuter jusqu’à l’obtenir.

Afin de ne pas attendre, il faut détecter la session provoquant le blocage et la stopper si possible.

Résolution

La commande suivante permet de lister toutes les transactions bloquées :

USE Master
GO
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO

La commande sp_who2 liste toute les sessions avec leur numéro de SPID, il suffit de retrouver la session remontée par la commande précédente puis de se référer à la colonne “BlkBy” afin d’identifier le process qui la bloque.

La commande suivante permet d’afficher la commande exécutée par le SPID indiqué, cela permet d’évaluer le risque avant de stopper la session à l’aide de la commande “kill”

dbcc inputbuffer(SPID)

Contexte

Ce post explique comment créer un job qui permet de vérifier automatiquement la cohérence d’une base de donnée.

Résolution

Depuis SQL Server Management Studio, depuis le dossier “Management”, créer un nouveau plan de maintenance :

Ajouter la tâche de vérification de l’intégrité de la base depuis la ToolBox :

Indiquer les bases à vérifier :

Eventuellement, indiquer l’action à réaliser en cas de succès ou d’échec du job :

Contexte

Ce post explique comment restaurer une base de donnée sous un différent nom/emplacement

Résolution

Récupérer le nom logique de la base :

RESTORE FILELISTONLY FROM DISK='c:\backup.bak'

Restaurer la base sous un autre nom logique / emplacement :

RESTORE DATABASE NouveauNomLogique FROM DISK='c:\backup.bak'
WITH 
   MOVE 'NomLogiqueduMDF' TO 'c:\NouveauNomLogiqueduMDF.mdf',
   MOVE 'NomLogiqueduLDF' TO 'c:\NouveauNomLogiqueduLDF_log.ldf'

Contexte

Suite à un reboot, l’exécution d’un data collector set créé depuis Perfmon est interrompue.

La console PerfMon permet uniquement de planifier le lancement d’un DCS en fonction de dates spécifiques et non en fonction d’un évènement :

Ce post explique comment automatiquement lancer un Data Collector Set à chaque démarrage du serveur.

Résolution

Depuis le Task Scheduler naviguer vers “Library -> Microsoft -> Windows –> PLA” :

Sélectionner le DCS souhaité puis ajouter un trigger du type “At system startup” :

Contexte

Sur une même machine Windows, il est impossible de réaliser plus de 3 sysprep, au bout de la 4eme tentative, l’utilitaire sysprep affiche l’erreur suivante :

Microsoft recommande d’utiliser une image d’un système “sysprepé” afin de contourner cette limite.

Cette limite a été mise en place afin que sysprep ne soit pas utilisé pour réinitialiser la période de grâce de 30 jours que Microsoft fourni pour activer la licence.

Résolution

Pour sysprep une même machine plus de trois fois, les étapes suivantes doivent être réalisées :

1. Depuis l’éditeur de registre,

Depuis la l’entrée “HKEY_LOCAL_MACHINE\System\Setup\Status\SysprepStatus”, modifier la valeur de la clé “CleanupState” à 2 et la valeur de la clé “GeneralizationState” à 7 :

Depuis la l’entrée “HKEY_LOCAL_MACHINE\Software\Microsoft\WindowsNT\CurrentVersion\SoftwareProtectionPlatform”, modifier la valeur de la clé “SkipRearm” à 1 :

2. Depuis un command prompt lancé en tant qu’administrateur,

Exécuter les commandes suivantes :

msdtc -uninstall
msdtc -install

3. Depuis le répertoire “C:\Windows\System32\Sysprep” supprimer le dossier “Panther” :

Contexte

Il arrive souvent qu’un compte utilisateur soit utilisé pour l’exécution d’un job SQL, il s’agit d’une mauvaise pratique car le compte utilisateur peut être amené à changer de mot de passe ce qui provoqueras une erreur d’exécution des jobs en question.

Ce post explique comment détecter puis modifier les comptes utilisés.

Analyse

Le script suivant permet de détecter les jobs exécuté via un compte user (DOMAIN\USER-%) tout en ne remontant pas les jobs lancés par un compte de service (DOMAIN\SERVICE-%).

Les variables “DOMAIN\USER-%” et “DOMAIN\SERVICE-%” sont à modifier en fonction de la nomenclature utilisée pour nommer les comptes. Le “%” correspond à toute chaîne de zéro caractères ou plus Ce caractère générique peut être utilisé comme préfixe ou comme suffixe.

select s.name,l.name
from  msdb..sysjobs s
  left join master.sys.syslogins l on s.owner_sid = l.sid
where l.name like 'DOMAIN\USER-%' and  l.name not like 'DOMAIN\SERVICE-%'

Résolution

Il faut ensuite récupérer le SID du compte utilisateur à changer depuis l’active directory, ainsi que le SID du compte à utiliser, par exemple, SA.

Pour récupérer le SID d’un utilisateur SQL, executer la commande suivante “select sid,name,denylogin from MASTER..syslogins where name='SQLUser'”

Le script suivant permet de modifier l’owner des jobs d’une instance SQL depuis le SID “0x02” vers le SID “0x01” :

UPDATE msdb..sysjobs
SET owner_sid = CONVERT(varbinary(max), '0x01', 1)
where owner_sid = CONVERT(varbinary(max), '0x02', 1)

Contexte

Lors d’un renouvellement de certificat WEB sur une instance SSRS, le mapping du nouveau certificat échoue avec l’erreur suivante :

Microsoft.ReportingServices.WmiProvider.WMIProviderException: An SSL binding already exists for the specified IP address and port combination. The existing binding uses a different certificate from the current request. Only one certificate can be used for each IP address and port combination. To correct the problem, either use the same certificate as the existing binding, or remove the existing SSL binding and create a new binding using the certificate of the current request.

Explication

Le message d’erreur précise que la combinaison IP:Port est déjà mappée à un certificat existant (celui que l’on tente de renouveler).

La commande suivante permet de visualiser le binding existant :

netsh http show sslcert

On remarque que le certificat que l’on tente de renouveller est mappé à la combinaison IP:Port suivante : [::]:443

Résolution

A l’aide de la commande précédemment citée, récupérer la combinaison IP:Port utilisée par le certificat que l’on tente de renouveler, puis supprimer le mappage à l’aide de la commande suivante :

netsh http delete sslcert ipport=[::]:443