Querying Active Directory / LDAP with SQL


Querying Active Directory or LDAP to obtain all user account is a little tedious, and the fact that you can only get 1000 records per query makes it a little difficult. Here is a script that will query AD for every combination of the three first letters of the account name, i.e. ‘AAA*’, ‘AAB*’, ‘ABA*’, ‘CAA*’…

Just remember to add the correct domain lame in the LDAP-query and you should be fine:

SET NOCOUNT ON

DECLARE @SearchTerm varchar(10)
DECLARE @RemoteQuery varchar(2000)
DECLARE @ErrCount int = 0
DECLARE @alphabet table (
Letter char(1)
)
INSERT INTO @alphabet (Letter)
VALUES
('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('A'), ('B'), ('C'), ('D'), ('E'),
('F'), ('G'), ('H'), ('I'), ('J'), ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'),
('U'), ('V'), ('W'), ('X'), ('Y'), ('Z')


IF OBJECT_ID('tempdb..#AD_Users') IS NOT NULL
DROP TABLE #AD_Users

CREATE TABLE #AD_Users (
givenName varchar(255),
initials varchar(255),
sn varchar(255),
userPrincipalName varchar(255),
sAMAccountName varchar(255),
displayName varchar(255),
name varchar(255),
physicalDeliveryOfficeName varchar(255),
telephoneNumber varchar(255),
mail varchar(255)
)

DECLARE cur CURSOR FOR
SELECT
CONCAT(char1.Letter, char2.Letter, char3.Letter, '*')
FROM @alphabet char1
CROSS JOIN @alphabet char2
CROSS JOIN @alphabet char3
WHERE char1.Letter >= 'A'

OPEN CUR

FETCH NEXT FROM cur INTO @SearchTerm
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @RemoteQuery = '
SELECT
givenName, initials, sn, userPrincipalName, sAMAccountName, displayName, name, physicalDeliveryOfficeName, telephoneNumber, mail
FROM OPENQUERY( AD,
''SELECT givenName, initials, sn, userPrincipalName, sAMAccountName, displayName, name, physicalDeliveryOfficeName, telephoneNumber, mail
FROM ''''LDAP://domain.name''''
WHERE objectCategory = ''''User''''
AND sAMAccountName = ''''' + @SearchTerm + ''''''')'


BEGIN TRY
INSERT INTO #AD_Users
EXEC ( @RemoteQuery )
END TRY
BEGIN CATCH
SET @ErrCount = @ErrCount + 1
END CATCH

FETCH NEXT FROM cur INTO @SearchTerm
END

CLOSE cur
DEALLOCATE cur


SELECT * FROM #AD_Users

SELECT @ErrCount


 

Monitor progress of an SSIS Master package


Monitoring progress of an SSIS Master package (a package that runs other packages) is a painful process so I decided to take stab at writing a query that could pull it off by using the data found in the dbo.sysssislog tables.

For this script to work all packages must be set up with Log provider for SQL Server and it only works for “old school” package deployment mode. Output looks like this:2017-03-10 09_12_19-mRemoteNG - confCons.xml - 172.30.8.203 - Dev:

use msdb
go

DECLARE @ShowErrorMessages int = 0
DECLARE @MasterPackageName varchar(200) = 'MASTER_Stage'

DECLARE @id_min int = (SELECT MAX(id) FROM [BOS_NBDW_Config].[dbo].[sysssislog] WHERE event = 'PackageStart' AND source = @MasterPackageName)
DECLARE @id_max int = ISNULL((SELECT MAX(id) FROM [BOS_NBDW_Config].[dbo].[sysssislog] WHERE event = 'PackageEnd' AND source = @MasterPackageName AND id > @id_min), 999999999)

;WITH cte AS (
 SELECT 
 PackageStatus = CASE 
 WHEN event IN ('PackageStart','User:PackageStart') THEN '1 Running'
 WHEN event IN ('OnError','User:OnError') THEN '3 Error'
 ELSE '2 Completed'
 END,
 *
 FROM [dbo].[sysssislog]
 WHERE event in ('PackageStart','PackageEnd','User:PackageStart','User:PackageEnd', 'OnError','User:OnError')
 AND id BETWEEN @id_min AND @id_max
 )
SELECT 
 ID = MIN(ID), 
 source, 
 PackageStatus = MAX(PackageStatus),
 StartTime = MIN(StartTime),
 EndTime = CASE WHEN MAX(PackageStatus) = '1 Running' THEN NULL ELSE MAX(endtime) END,
 Elapsed = FORMAT(DATEADD(second, 
 CASE 
 WHEN MAX(PackageStatus) = '1 Running' THEN DATEDIFF(second, MIN(StartTime), GETDATE())
 ELSE DATEDIFF(second, MIN(StartTime), MAX(endtime))
 END, '1900-01-01'), 'd\d HH:mm:ss')
FROM cte
--WHERE source in (SELECT DISTINCT source FROM [dbo].[sysssislog] WHERE Event = 'PackageStart' and id >= @id)
GROUP BY source
--ORDER BY Elapsed desc
ORDER BY MIN(ID)

IF @ShowErrorMessages = 1
 SELECT DISTINCT
 [event]
 ,
 ,[starttime]
 ,endtime
 ,[message]
 FROM [BOS_NBDW_Config].[dbo].[sysssislog]
 WHERE event like '%error%'
 and id BETWEEN @id_min AND @id_max
 ORDER BY source

 

SSIS 2012 – easily copy environment variables to new servers or new environments


Following my investigations on SSIS 2012 and the issues mentioned in my previous blog post, create environment variables from project parameters, I created a script that easily lets you copy all the existing environment variables either to a new environment on the same server or to a new (or existing) environment on a completely different server.

Please note: this script will NOT make any changes to your environments by itself! It merely generates a sql script that has to be copy/pasted in to a SSMS query window and executed manually.

/*
This script will take all environment variables in an existing environment and make "insert" scripts 
out of them so you can easily deploy them to a new server or a new environment on the same server.

Script created by Henning Frettem, www.thefirstsql.com, 2013-05-28
*/

SET NOCOUNT ON

DECLARE 
	@folder_name nvarchar(200)				= 'ProjectFolderName',
	@environment_name_current nvarchar(200) = 'DEV',
	@environment_name_new nvarchar(200)		= 'TEST',
	@name sysname, 
	@sensitive bit, 
	@description nvarchar(1024), 
	@value sql_variant, 
	@type nvarchar(128)

PRINT 'DECLARE 
	@folder_id bigint,
	@environment_id bigint'

PRINT ''

--> Create folder if it doesn't exist and get folder_id
PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = N''' + @folder_name + ''')
	EXEC [SSISDB].[catalog].[create_folder] @folder_name=N''' + @folder_name + ''', @folder_id=@folder_id OUTPUT
ELSE 
	SET @folder_id = (SELECT folder_id FROM [SSISDB].[catalog].[folders] WHERE name = N''' + @folder_name + ''')'

PRINT ''

--> Create environment if it doesn't exist
PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environments] WHERE folder_id = @folder_id AND name = N''' + @environment_name_new + ''')
	EXEC [SSISDB].[catalog].[create_environment] @environment_name=N''' + @environment_name_new + ''', @folder_name=N''' + @folder_name + ''''

PRINT ''

--> Get the environment_id
PRINT 'SET @environment_id = (SELECT environment_id FROM [SSISDB].[catalog].[environments] WHERE folder_id = @folder_id and name = N''' + @environment_name_new + ''')'

PRINT ''

--> Making cursor because mapping of sql_variant datatype is different than the normal datatypes
DECLARE cur CURSOR FOR 
	SELECT c.name, c.sensitive, c.description, c.value, c.type
	FROM [SSISDB].[catalog].[folders] a
		INNER JOIN [SSISDB].[catalog].[environments] b
			ON a.folder_id =  b.folder_id
		INNER JOIN [SSISDB].[catalog].[environment_variables] c
			ON b.environment_id = c.environment_id
	WHERE a.name = @folder_name
		AND b.name = @environment_name_current

OPEN cur
FETCH NEXT FROM cur INTO @name, @sensitive, @description, @value, @type

PRINT 'DECLARE @var sql_variant'
PRINT ''

WHILE (@@FETCH_STATUS = 0)
	BEGIN
		PRINT 'SET @var = N''' + CONVERT(nvarchar(max), @value) + ''''

		PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environment_variables] WHERE environment_id = @environment_id AND name = N''' + @name + ''')
			EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N''' + @name + ''', @sensitive=' + CONVERT(varchar(2), @sensitive) + ', @description=N''' + @description + ''', @environment_name=N''' + @environment_name_new + ''', @folder_name=N''' + @folder_name + ''', @value=@var, @data_type=N''' + @type + ''''

		PRINT ''

	FETCH NEXT FROM cur INTO @name, @sensitive, @description, @value, @type
	END

CLOSE cur
DEALLOCATE cur

SSIS 2012 – create environment variables from project parameters


A little late in the game perhaps but I just recently started looking in to SQL Server 2012 and especially SSIS, and there were some shortcomings (in my opinion) in regards to the configurations. The new deployment model with versioning and everything is utterly fantastic (!!), and making project parameters is easy peasy. However, when it comes to making the project parameters configurable across different environments it’s a different story. Assuming you already know the deal about the SSISDB catalog and the environments, I feel that the actual creation of the environment variables is very cumbersome. They are usually a very close replica of the design time project parameters, so to make the creation of the variables easier on myself I created a script that takes all the project parameters of a deployed project (deployed to your dev machine is sufficient), check if an environment variable with the same name exists and if it doesn’t then create it with the design time value.

Important: changes to the project parameters are only detected once the project is deployed to the Integration Services catalog!

/*
This script will take all project parameters with their design time values in your SSIS-project and create
an "insert script" for making them in to SSIS environment variables. It will NOT make any changes to
your databases!
  
Script created by Henning Frettem, www.thefirstsql.com, 2013-05-28
*/
  
DECLARE
    @folder_name nvarchar(200)      = 'ProjectFolderName',
    @environment_name nvarchar(200) = 'DEV',
    @parameter_name varchar(200),
    @design_default_value sql_variant,
    @sensitive bit, 
    @description nvarchar(1024), 
    @data_type nvarchar(128)
 
--> Checking that the folder exists
IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = @folder_name)
    BEGIN
        RAISERROR('Provided folder name does not exist in the Integration Services catalog. Create the folder first, then run the script again', 16, 1)
        RETURN
    END
 
PRINT 'DECLARE 
    @folder_id bigint,
    @value sql_variant,
	@folder_name nvarchar(200)      = ''' + @folder_name + ''',
	@environment_name nvarchar(200) = ''' + @environment_name + ''''
 
PRINT ''
 
PRINT '--> Need folder_id for ''' + @folder_name + ''' 
    SET @folder_id = (SELECT folder_id FROM [SSISDB].[catalog].[folders] WHERE name = @folder_name)'
  
PRINT ''
 
PRINT '--> Create ' + @environment_name + ' environment in ''' + @folder_name + '''-folder if it does not exist
    IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environments] WHERE folder_id = @folder_id AND name = @environment_name)
		BEGIN
			EXEC [SSISDB].[catalog].[create_environment] @environment_name = @environment_name, @folder_name = @folder_name
			PRINT ''CREATED: Environment ' + @environment_name + ' in folder ' + @folder_name + '''
		END
	ELSE
		PRINT ''EXISTS: Environment ' + @environment_name + ' exists'''
 
PRINT ''
  
--> Create cursor for all project parameters
DECLARE cur CURSOR FOR
    SELECT a.parameter_name, a.design_default_value, a.sensitive, a.description, a.data_type
    FROM [SSISDB].[catalog].[object_parameters] a
        INNER JOIN [SSISDB].[catalog].[projects] b
            ON a.project_id = b.project_id
        INNER JOIN [SSISDB].[catalog].[folders] c
            ON b.folder_id = c.folder_id
    WHERE c.name = @folder_name
        AND SUBSTRING(a.parameter_name, 1, 3) <> 'CM.'
    ORDER BY a.parameter_name
  
OPEN cur
FETCH NEXT FROM cur INTO @parameter_name, @design_default_value, @sensitive, @description, @data_type
  
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        PRINT '--> Creating variable ''' + @parameter_name + ''' if it doesn''t exist'
        PRINT ' SET @value = N''' + CONVERT(nvarchar(max), @design_default_value) + ''''
        PRINT '
            IF NOT EXISTS (SELECT 1 
                            FROM [SSISDB].[catalog].[environments] b 
                                INNER JOIN [SSISDB].[catalog].[environment_variables] c
                                    ON b.environment_id = c.environment_id
                            WHERE b.folder_id = @folder_id 
                                AND b.name = @environment_name
                                AND c.name = ''' + @parameter_name + ''')
				BEGIN
					EXEC [SSISDB].[catalog].[create_environment_variable] 
						@variable_name = ''' + @parameter_name + ''', 
						@sensitive = ' + CONVERT(varchar(2), @sensitive) + ', 
						@description = ''' + @description + ''', 
						@environment_name = @environment_name, 
						@folder_name = @folder_name, 
						@value = @value, 
						@data_type = ''' + @data_type + '''
					PRINT ''CREATED: Environment-variable ' + @parameter_name + '''
				END
			ELSE
				PRINT ''EXISTS: Environment-variable ' + @parameter_name + ''''

		PRINT ''
  
    FETCH NEXT FROM cur INTO @parameter_name, @design_default_value, @sensitive, @description, @data_type
    END
  
CLOSE cur
DEALLOCATE cur

Querying msdb for job history


I recently had the need to create an SSRS report showing the status of some jobs from the 10 last days, so I wrote this script that I thought could come in handy to others as well. The contents should be pretty self explanatory except maybe for the RowNum column which is there to separate each iteration of the job:

WITH cte_jobhistory AS (
		--> Creating a cte only to make the columns run_date and run_time into a "datetime-friendly" format  
		--  so it can be used for filtering
		SELECT 
			RunDateString = SUBSTRING(CAST(run_date as VARCHAR(20)), 1, 4) + '-' +  SUBSTRING(CAST(run_date as VARCHAR(20)), 5, 2) + '-' + SUBSTRING(CAST(run_date as VARCHAR(20)), 7, 2),
			RunTimeString = SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 1, 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 3, 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 5, 2),
			*
		FROM msdb.dbo.sysjobhistory
		)
SELECT 
	a.name,
	RowNum = ROW_NUMBER() OVER (PARTITION BY a.name, b.step_id ORDER BY instance_id DESC),
	b.step_id,
	b.step_name,
	b.message,
	RunStatus = CASE b.run_status 
		WHEN 0 THEN 'Failed'
		WHEN 1 THEN 'Succeded' 
		WHEN 2 THEN 'Retry' 
		WHEN 3 THEN 'Cancelled' 
		WHEN 4 THEN 'In Progress' 
		END,
	b.run_duration,
	b.RunDateString,
	b.RunTimeString
FROM msdb.dbo.sysjobs a
	INNER JOIN cte_jobhistory b
		ON a.job_id = b.job_id
WHERE CONVERT(datetime, b.RunDateString) > GETDATE()-10
ORDER BY a.name ASC, b.instance_id DESC

ORA-00936 Missing Expression in SSIS 2005


I’ve been doing quite a bit of SSIS 2005 lately and I’ve come across a weird problem that almost had the best of me. It’s no secret that I absolutely *hate* Oracle and this problem I just had didn’t make it any better.

What I had was a quite long and complex pl/sql query (over 250 lines of code!) that was running smoothly in sql developer, the Oracle OLE DB provider in SSIS had absolutely no problems with the code when previewing data or mapping the column metadata. But when I saved the package and ran it I got the error message saying “ORA-000938: missing expression”. Sooo…what to do? Obiously there was some kind of syntax error but I have a query that runs perfecty in SQL Developer, it validates without a hitch in SSIS and I can even preview the data in the OLE DB source object in SSIS. Quite amazing to be honest!

So, after 7 hours (!! I kid you not!) of debugging this query I had a colleague step in to my office for a few minutes. And then he dropped the bomb: “Did you remove all the comments in your query??”. I just stared back at him in disbelief but at that point I was ready to do just about anything so I tried it and; Voila. The query ran through. After a bit more debugging I learned that it was the “–” (double dash) it didn’t like and that the /* */ works just fine and I think it has something to do with th driver actually removing all the carriage returns in the statement so anything after the first comment is also commented out. Go Oracle!

In conclusion:
Don’t ever comment your PL/SQL queries in ssis using “–” (double dash), it simply will not work!

Updated: create point-in-time restore script automatically


The script I created for generating a point-in-time restore t-sql code has been updated and the last version should support sql server 2005, 2008 and 2012 versions.

There is one prerequisite:
– Only backups made to file on disk is supported

Here is the final script:

/*
Generate Point-in-time restore script, given only DBName and Point-in-time timestamp
The script WILL NOT do any changes to your databases, it will only generate sql code 
that you need to manually execute.
 
Created by Henning Frettem, www.thefirstsql.com
*/
SET NOCOUNT ON
GO
 
DECLARE
  @DBName varchar(200),
  @PointInTime datetime,
  @Filename varchar(200),
  @tab varchar(1) = char(9),
  @cr varchar(2) = char(13)+char(10),
  @Full_BackupStartDate datetime,
  @Diff_BackupStartDate datetime,
  @Log_BackupStartDate datetime,
  @SQL varchar(max) = ''
 
SET @DBName  = 'MyDatabase'
SET @PointInTime = '2013-05-21 15:40:00'
 
BEGIN TRY
  --> Performing some checks
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName)
    RAISERROR(N'The database has never been backed up', 16, 1)
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND type = 'D' AND backup_start_date <= @PointInTime)
    RAISERROR(N'No full backup exists prior to the specified PointInTime', 16, 1)
  ELSE IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DBName AND type = 'L' AND backup_start_date > @PointInTime)
    RAISERROR(N'No backup of the log exist after the specified PointInTime, do a tail log backup first', 16, 1)
 
  --> Getting the filename and the date of the last full backup prior to the PointInTime
  SELECT TOP 1 
      @Filename = b.physical_device_name,
      @Full_BackupStartDate = backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date <= @PointInTime
  ORDER BY a.backup_start_date DESC
 
  SET @SQL = 'USE master' + @cr + 
    'GO' + @cr + @cr +
    'ALTER DATABASE ' + QUOTENAME(@DBName) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' + @cr + 
    'GO' + @cr + @cr
  
  PRINT @SQL
  SET @SQL = ''
 
  --> Restore the last full backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', REPLACE, STATS=5, NORECOVERY' + @cr + 'GO' + @cr,
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date = @Full_BackupStartDate
  ORDER BY a.backup_start_date DESC   

  PRINT @SQL
  SET @SQL = ''
   
  --> Restore the last differential backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', NORECOVERY' + @cr + 'GO',
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'I'
    AND a.backup_start_date > @Full_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date DESC
  
  PRINT @SQL
  SET @SQL = ''
   
  IF @Diff_BackupStartDate IS NULL
    SET @Diff_BackupStartDate = @Full_BackupStartDate
     
  --> Generate all log restores except the last one
  SELECT
    @SQL = @SQL + 'RESTORE LOG ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', NORECOVERY' + @cr + 'GO' + @cr,
    @Log_BackupStartDate = a.backup_start_date   
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @Diff_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date
 
  PRINT @SQL
  SET @SQL = ''

  --> Generate last log restore script with the stopat command and recovery
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE LOG ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', RECOVERY, STOPAT = ''' + CONVERT(varchar(20), @PointInTime, 120) + '''' + @cr + 'GO' + @cr
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @PointInTime
  ORDER BY a.backup_start_date ASC
 
  PRINT @SQL
 
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE()
END CATCH