Skip to main content


Showing posts from 2014

The AcquireConnection method call to the connection manager failed with error code 0xC0202009

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 Int…

Querying Active Directory from SQL Server

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 had to …

Show deprecated SQL Server Data Types

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 USEmaster;
IFEXISTS(SELECT 1 FROMtempdb..sysobjectsWHEREid=OBJECT_ID('tempdb..#DataTypes'))DROPTABLE#DataTypes CREATETABLE#DataTypes( DBNameVARCHAR(50) ,TableNameVARCHAR(100) ,ColNameVARCHAR(100) ,ExistingDataTypeVARCHAR(20) ,ChangeToDataTypeVARCHAR(20) )
Declare@DBNameVARCHAR(50); Declare@SQLVARCHAR(MAX);
DECLAREDeprecatedDTCurCURSORFOR SELECTNamefromsysdatabaseswherenamenotin('master','model','msdb','tempdb')orderbyname
OPENDeprecatedDTCur FETCHNEXTFROMDeprecatedDTCurINTO@DBName; WHILE@@FETCH_STATUS= 0 BEGIN PRINT'Processing Database: '+@DBName; SET@SQL='USE ['+@DBName+'];          INSERT #DataTypes          SELECT                 DB_Name() AS DB…

Show all SSRS reports and their subscriptions

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 USEReportServer;
SELECTCASE WHEN (SubString(c.[Path], 1,Len(c.[Path])-(CharIndex('/',Reverse(c.[Path]))- 1)))='' THEN'<root folder>' ELSESubString(c.[Path], 1,Len(c.[Path])-(CharIndex('/',Reverse(c.[Path]))- 1)) ENDASFolder ,C.Name ,s.Description ,s.LastRunTime ,REPLACE(REPLACE(CAST(CAST(extensionsettingsASXML).query('/ParameterValues/ParameterValue/Value[../Name = ''TO'']')ASVARCHAR(MAX)),'</Value>',''),'<Value>','')AS'To:' ,REPLACE(REPLACE(CAST(CAST(extensionsettingsASXML).query('/ParameterValues/ParameterValue/Value[../Name = ''CC'']')ASVARCHAR(MAX)),'</Value>',''),'<Value>','')AS'CC:' ,REPL…

Show permissions on all SSRS folders

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.

SELECT CASE WHEN (SubString(c.[Path], 1,Len(c.[Path])-(CharIndex('/',Reverse(c.[Path]))- 1)))='' THEN'<root folder>' ELSESubString(c.[Path], 1,Len(c.[Path])-(CharIndex('/',Reverse(c.[Path]))- 1)) ENDASFolder ,CASE WHENc.Type= 1 THEN'Path' WHENc.Type= 2 THEN'Report' WHENc.Type= 3 THEN'Resource' WHENc.Type= 4 THEN'Linked Report' WHENc.Type= 5 THEN'Data Source' WHENc.Type= 6 THEN'Report Model' WHENc.Type= 7 THEN'Report Part' WHENc