SQL Server Snippets | Paul Hewson

I had one of those annoying problems with executing a SSIS package that took up most of the morning today (and a couple of hours yesterday) where you get an error which many people have had, but none of their solutions work. I guess it is such a generic error code it can relate to many different issues hence dozens of different (wrong) suggestions. What confused matters was that although it completed successfully in SSDT, it failed when being validated on our newly created SSISDB Catalog. I didn't know whether it was a problem with the package and/or its parameters or the SSISDB Catalog installation or Environment. If you haven't set up an Integration Services Catalog (a new feature of SQL 2012) yet I definitely think it is worth looking at. I found an excellent guide here that takes you through it all. In our case we had two connection managers, the source was a table in a SQL Server 2012 database and the destination a table in an Access 2010 database. The server with the ...

At many companies, access to employees PC resources (USB drive, CD ROM drive, root of C: etc) is managed by Group Policy. Sometimes Group Policy with the requisite settings is applied to a Security Group so members of that group inherit the settings of that GPO. So you may end up with many different policies applied to many different groups. This is a straight forward easy way to manage GPOs - if you want to know what access a user has to their PC simply look at their group membership. Being a database type person it occurred to me, it would be nice if we could query Active Directory at regular intervals to see who has what permissions to their PC (for security auditing), populate a table with the results and perhaps point Excel at the table to create a nice report for management. (I won't cover the Excel part in this post but there is very little to it) I looked (Googled) various different ways this could be achieved but nothing quite did it the way I wanted so in the end ha...

From SQL Server version 2008 forward the data types text , ntext and image have been deprecated . The code below will cursor through all the user databases on your server and bring back the database, tables and columns that use these data types, if they exist. -- Find Deprecated datatypes in all databases USE master ; IF EXISTS ( SELECT 1 FROM tempdb .. sysobjects WHERE id = OBJECT_ID ( 'tempdb..#DataTypes' )) DROP TABLE #DataTypes CREATE TABLE #DataTypes (     DBName VARCHAR ( 50 )     , TableName VARCHAR ( 100 )     , ColName VARCHAR ( 100 )     , ExistingDataType VARCHAR ( 20 )     , ChangeToDataType VARCHAR ( 20 )     ) Declare @DBName VARCHAR ( 50 ); Declare @SQL VARCHAR ( MAX ); DECLARE DeprecatedDTCur CURSOR FOR         SELECT Name from sysdatabases where name not in ( 'master' , 'model' , ...

This is a very handy script to give you a quick overview of the report subscriptions you have on your Microsoft SQL Server Reporting Services server. It works on SSRS 2005 to 2012. -- Show reports and their subscriptions USE ReportServer ; SELECT CASE               WHEN ( SubString ( c . [Path] , 1 , Len ( c . [Path] ) - ( CharIndex ( '/' , Reverse ( c . [Path] )) - 1 ))) = ''                      THEN '<root folder>'               ELSE SubString ( c . [Path] , 1 , Len ( c . [Path] ) - ( CharIndex ( '/' , Reverse ( c . [Path] )) - 1 ))               END AS Folder        , C . Name        ,...

Here is a useful (if rather verbose) script that will show users and their permissions from the root folder on SQL Server Reporting Services downwards. It should work on most if not all versions of the product. You can use it as is, or as a starting point to create your own aggregations etc. USE ReportServer ; SELECT CASE               WHEN ( SubString ( c . [Path] , 1 , Len ( c . [Path] ) - ( CharIndex ( '/' , Reverse ( c . [Path] )) - 1 ))) = ''                      THEN '<root folder>'               ELSE SubString ( c . [Path] , 1 , Len ( c . [Path] ) - ( CharIndex ( '/' , Reverse ( c . [Path] )) - 1 ))               END AS Folder   ...