MERGE

MERGE INTO DemoDW.dw.FactSalesOrders AS tgt
USING
    -- Requête pour retourner les données des ventes intermédiaires et consulter les clés de dimension
    (SELECT p.ProductKey,
            c.CustomerKey,
            s.SalespersonKey,
            sh.ShipperKey,
            d.DateKey,
            o.OrderNo,
            o.ItemNo,
            o.Quantity,
            o.SalesAmount,
            o.Cost
    FROM DemoDW.stg.SalesOrders o
    INNER JOIN DemoDW.dw.DimProduct p ON o.ProductID = p.ProductAltKey
    INNER JOIN DemoDW.dw.DimCustomer c ON o.CustomerID = c.CustomerAltKey AND c.CurrentRecord = 1
    INNER JOIN DemoDW.dw.DimSalesperson s ON o.SalespersonID = s.SalesPersonAltKey AND s.CurrentRecord = 1
    INNER JOIN DemoDW.dw.DimShipper sh ON o.ShipperID = sh.ShipperAltKey
    INNER JOIN DemoDW.dw.DimDate d ON CAST(CONVERT(VARCHAR(8), dateadd(dd, -2, o.OrderDate), 112) AS int) = d.DateKey)
    AS src (ProductKey, CustomerKey, SalespersonKey,ShipperKey,  OrderDateKey, OrderNo, ItemNo, Quantity, SalesAmount, Cost)
   
    -- Definition des clé de comparaison entre la source et la destination
    ON (
        src.OrderNo = tgt.OrderNo
        AND
        src.ItemNo = tgt.ItemNo
       )

    -- Si correspondance, mettre à jour l'enregistrement
    WHEN MATCHED THEN
        UPDATE
        SET ProductKey = src.ProductKey,
            CustomerKey = src.CustomerKey,
            SalespersonKey = src.SalespersonKey,
            ShipperKey = src.ShipperKey,
            OrderdateKey = src.OrderDateKey,
            Quantity = src.Quantity,
            SalesAmount = src.SalesAmount,
            Cost = src.Cost

    -- sinon, insérer un nouvel enregistrement
    WHEN NOT MATCHED THEN
        INSERT (ProductKey, CustomerKey, SalespersonKey, ShipperKey, OrderDateKey, OrderNo, ItemNo, Quantity, SalesAmount, Cost)
        VALUES (src.ProductKey, src.CustomerKey, src.SalespersonKey, src.ShipperKey, src.OrderDateKey, src.OrderNo, src.ItemNo, src.Quantity, src.SalesAmount, src.Cost);

Récupérer la liste des étendues d’un DHCP en powershell

Si vous voulez travailler depuis un poste autre que le serveur contenant le rôle DHCP. Allez sur le Windows qui gère le rôle DHCP pour activer WinRM (Windows Remote Management implémentation Microsoft du standard WS-Management, basé sur SOAP. [ports : HTTP 5985, ou HTTPS 5986]). Lancer la commande powershell:

Enable-PSRemoting

Vous pouvez ensuite depuis votre poste lancer cette commande :

$scope = $args[0]
$a = Invoke-Command -ComputerName si-ctrl-01.eurobat.com -scriptblock{netsh dhcp server XXX.XXX.XXX.XXX show scope}
$tabl= $a[7..($a.length-6)]
$ip = New-Object System.Collections.ArrayList
foreach($a in $tabl) {$ip.add( $a.trim().split("-")[0].trimend()) > $null }
$ip.sort()
$ip

Gestion des erreurs

SET @Error = @@ERROR
    --set @Error = 58
    -- If error detected then raise an error for the package to pick-up
    IF (@Error <> 0)
        RAISERROR (N'Erreur insertion lignes à traiter dans la table TbCptAnaSocPeriodePalo. %1s',  -- Message text.
                    15, -- Severity,
                    1, -- State,
                    @NomProc) -- First argument supplies the string
    -- OK

SQL Serveur droits effectifs

/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user cccount.  This could also be an Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the
                  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE,
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.  
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.          
ObjectName      : Name of the object that the user/role is assigned permissions on.  
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.                
*/


--List all access provisioned to a sql user or windows user/group directly
SELECT  
    [UserName] = CASE princ.[TYPE]
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[TYPE]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,  
    [DatabaseUserName] = princ.[name],      
    [ROLE] = NULL,      
    [PermissionType] = perm.[permission_name],      
    [PermissionState] = perm.[state_desc],      
    [ObjectType] = obj.type_desc,--perm.[class_desc],      
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals princ  
LEFT JOIN
    --Login accounts
    sys.login_token ulogin ON princ.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
    princ.[TYPE] IN ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  
    [UserName] = CASE memberprinc.[TYPE]
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[TYPE]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = memberprinc.[name],  
    [ROLE] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],      
    [PermissionState] = perm.[state_desc],      
    [ObjectType] = obj.type_desc,--perm.[class_desc],  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin ON memberprinc.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',      
    [ROLE] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],      
    [PermissionState] = perm.[state_desc],      
    [ObjectType] = obj.type_desc,--perm.[class_desc],  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Roles
    sys.database_principals roleprinc
LEFT JOIN        
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]                  
JOIN
    --All objects  
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[TYPE] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc]