SQL query to display when my SSAS cubes were processed


Doing reporting on the processing times of SSAS cubes is actually surprisingly tedious so I figured I’d write a script for collecting this data and share it with you guys. Hopefully you’ll save some time:

SET NOCOUNT ON

DECLARE @SQL nvarchar(2000)
DECLARE @CubeName varchar(200)
DECLARE @ConnString varchar(2000) 
DECLARE @ConnString_part1 varchar(2000) = 'Data Source=servername\tabular;Initial Catalog='
DECLARE @ConnString_part2 varchar(2000) = ';User ID=domain\username;Password=xyzsomepasswordxyz'

DECLARE @Cubenames table (
	ID INT IDENTITY(1, 1),
	Cubename varchar(200)
)
DECLARE @Processingtimes table (
	ID INT IDENTITY(1, 1),
	Cubename varchar(200),
	Last_data_update datetime
)

--> Create a table with all the cube names. Connect to any of the cubes on the server for this query:
SET @ConnString = @ConnString_part1 + 'MySalesCube' + @ConnString_part2
SET @SQL = 
	'SELECT a.*  
	FROM OPENROWSET(''MSOLAP'', ''' + @ConnString + ''',
		''SELECT [CATALOG_NAME]
		FROM $system.dbschema_catalogs'') AS a;'
INSERT INTO @Cubenames (Cubename)
EXEC sp_executesql @SQL

--> Check which cubes that are collected
--> SELECT * FROM @Cubenames

--> Then iterate through each cube, connect to it and find the LAST_DATA_UPDATE for each of them
DECLARE cur CURSOR FOR
SELECT Cubename FROM @Cubenames

OPEN cur
FETCH NEXT FROM cur INTO @CubeName

WHILE (@@FETCH_STATUS = 0)
	BEGIN
		SET @ConnString = @ConnString_part1 + @CubeName + @ConnString_part2
		
		SET @SQL = 
			'SELECT Cubename = ''' + @Cubename + ''', a.*  
			FROM OPENROWSET(''MSOLAP'', ''' + @ConnString + ''',
				''SELECT TOP 1 [LAST_DATA_UPDATE]
				FROM $System.MDSCHEMA_CUBES
				ORDER BY [LAST_DATA_UPDATE] DESC'') AS a;'
		INSERT INTO @Processingtimes (Cubename, Last_data_update)
		EXEC sp_executesql @SQL


		FETCH NEXT FROM cur INTO @CubeName
	END

CLOSE cur
DEALLOCATE cur

SELECT * FROM @Processingtimes
Advertisements

SSIS table usage – how to find which tables are used in which packages


When you have big SSIS projects it’s often problematic to have full control over which database tables are in use in which packages. So, to figure it out I created this “little” script that collects the names of every table in your database of choice, then searches through an entire directory (with subdirectories) for dtsx-files that contain this table name. It also runs somewhat fast; a total of 257 table names was matched with roughly 300 dtsx-files in 16 different folders in around 45 seconds. Export the results to Excel, add column header filters and you have a nice documentation of your table usage in a jiffy!

/*
Script to find table/view usage inside SSIS packages.
Will loop through every single table/view in the current database 
and find which SSIS-packages they are used in.
*/
 
 
SET NOCOUNT ON
GO
 
--> Database to search
USE MyDatabase
GO
 
--> Add name of folder containing .dtsx-files to search
DECLARE @DtsxFolder varchar(255) = 'C:\Temp\ssis\'
 
DECLARE 
 @cmd varchar(2000),
 @Table_Catalog varchar(255), 
 @Table_Schema varchar(255), 
 @Table_Name varchar(255), 
 @Table_Type varchar(255)
 
DECLARE @cmd_table table (
 id int identity(1, 1) not null, 
 cmd_output varchar(255)
 )
DECLARE @results_table table (
 id int identity(1, 1) not null, 
 Table_Catalog varchar(255), 
 Table_Schema varchar(255), 
 Table_Name varchar(255), 
 Table_Type varchar(255), 
 DtsxFile varchar(255)
 )
 
DECLARE cur CURSOR FOR 
 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_SCHEMA, TABLE_NAME
 
OPEN cur
FETCH NEXT FROM cur INTO @Table_Catalog, @Table_Schema, @Table_Name, @Table_Type
 
WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @cmd = 'findstr /M /s /C:"' + @Table_Name + '" '+ @DtsxFolder + '*.dtsx' --> Add /s to search subfolders
 
 INSERT INTO @cmd_table
 EXEC xp_cmdshell @cmd
 
 INSERT INTO @results_table
 (Table_Catalog, Table_Schema, Table_Name, Table_Type, DtsxFile)
 SELECT @Table_Catalog, @Table_Schema, @Table_Name, @Table_Type, cmd_output
 FROM @cmd_table
 WHERE cmd_output IS NOT NULL
 
 DELETE @cmd_table
 
 FETCH NEXT FROM cur INTO @Table_Catalog, @Table_Schema, @Table_Name, @Table_Type
 END
 
CLOSE cur
DEALLOCATE cur
 
SELECT * FROM @results_table

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

 

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

Error accessing SSRS Reports


I reinstalled my computer recently and as a result I had to reconfigure my Reporting Services installation as well. After configuring everything I was sort of surprised when I got this message when I tried accessing the http://localhost/Reports url: “User ‘NO-DOM\hef’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”

And it actually took me quite a few minutes (in the 30’s! yes, it’s late in the day…) to figure out the solution even though it was quite simple. By Starting the browser as an Administrator everything worked perfectly… :)

 

My first blog post…ever!


Well…I have been thinking about this for a while, and I’m not exactly sure how this is gonna end up but I’ve been wanting to have somewhere to post all my sql server related stuff. And well…TADA! Here it is :) Hopefully someone will find it useful…and if not then…hm…bummer.

I will at least post all my scripts here so please feel free to comment on both the good and the bad stuff about them. And by all means, if you have better solutions to problems or queries or whatever I post here: don’t be shy!! I’m in this to learn just as much as you are. Hopefully there will be something in it for all of us :)

– Henning