SharePoint 2010 – Remove a Content Type from the New Document drop down

Leave a comment

While moving SSRS reports to our production environment I ran in to an issue with removing a particular content type from the New Document listing. I’m sure you can customize a view in some way to accomplish this . . or maybe not, but here is my solution. Head to the site affected and navigate to Library Tools>Library, then Library Settings.

Navigate to Library Settings

Once in Library Settings scroll down to Content Types and click “Change new button order and default content type”

Click "Change new button order and default content type"

All that remains is to “uncheck” the Content Type you would like to remove visibility for.   Bingo!

Change the visibility property of the content type.


SQL Triggers

Leave a comment

Below trigger includes the exclusion of the @@ROWCOUNT system function due to issues with the MERGE statement and erratic behavior.

SQL Code


Sharepoint 2010 – Add Document properties drop down

Leave a comment

The document drop down comes in handy when working with documents via version control (checking in&out) as well as working or editing reports in SSRS from a SQL Report Server running in SharePoint mode. Here is how to add the document properties drop down to the column of our library.

Navigate to the library in question.

Click on Library in Library Tools, then select Modify View






Inside of the View Edit page add the Name (linked to document with edit menu) column. This column will add a Name column that also provides the drop down properties functionality as well.








Final Result

SharePoint 2010 add description to folder

1 Comment

SharePoint 2010 does not support adding descriptions to your folders. BIG BUMMER. There is, however, a way that metadata can be created and associated as a description of the folder via custom content types.

Navigate to Site Action in the upper left pane of the page and select Site settings.














From within the site setting page select Site Content Types.









Select Create to create a new content type






Click Add from new site column


















From this page enter your column name that will be displayed as a column on the page followed by selecting the data type and format














Navigate to the library in which you would like to create a new folder with a description.




Select Advanced Settings












Enable Custom content types




Also from within Library settings click the Add from existing site content types under Content Types





Add your custom content type







To create a new folder with description simply click documents from Library Tools

Select New Document followed by clicking the new content type that you created









In the creation window type the Folder Name and Folder Description

To make the column visible go into Library Tools>Modify view and click the column name








SSRS Multiple Datasets Error

Leave a comment

Received this error while trying to populate a textbox value from a dataset.

“The value expression for the text box ‘textbox1’ refers directly to the filed ‘field’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions whick specify a dataset scope.”

This error occurs when there are multiple datasets within the report. The solution is to add scope to your textbox expression.




=(Fields!Title.Value, “DataSet“)

easy peesy 😉


SSRS Multi-Value Select drop down

Leave a comment

I recently discovered an alternative way to implement the multi-value select drop down control. My primary solution was to create a parameter followed by creating the same parameter of the same data type in my SQL stored procedure code. My alternative is below . . .


Create a parameter the same way you would for a stored procedure via right click paramters>Add parameters

Add Parameter





Once you have applied the needed properties required of the parameter right-click your dataset and select properties.

Datasource Properties






From within the Dataset’s properties select Filters from the left pane. Click the Add button and select the expression or column you would like the parameter to apply to followed by selecting the “In” clause as your operator. Lastly, type the name of your parameter into the Value textbox within square brackets prefixed with the @ character [@parameter].

System Views – Database Extended Properties

Leave a comment

This clever little guy will grab most of the extended database properties from within sys.databases for each database for the server the query is run against.  The schema scope of extended properties (column metadata) will be included in a later post.

Took me a while to code the Case Statements but Here ya go!

db_name SYSNAME

DECLARE @command VARCHAR(1000)
SELECT @command = ‘USE [?] insert into #temp select db_name(), name, value from sys.extended_properties where class = 0 ‘
EXEC sp_MSforeachdb @command

SELECT #temp.db_name
,CASE WHEN sys.databases.is_read_only = 0 THEN ‘Read Only’
WHEN is_read_only = 1 THEN ‘Read Write’
END AS is_read_only
,CASE WHEN sys.databases.is_auto_close_on = 1 THEN ‘On’
WHEN sys.databases.is_auto_close_on = 0 THEN ‘Off’
END AS is_auto_close_on
,CASE WHEN sys.databases.is_auto_shrink_on = 1 THEN ‘On’
WHEN sys.databases.is_auto_shrink_on = 0 THEN ‘Off’
END AS is_auto_shrink_on
,CASE WHEN sys.databases.is_cleanly_shutdown = 1 THEN ‘On’
WHEN sys.databases.is_cleanly_shutdown = 0 THEN ‘Off’
END AS is_cleanly_shutdown
,CASE WHEN sys.databases.is_supplemental_logging_enabled = 1 THEN ‘On’
WHEN sys.databases.is_supplemental_logging_enabled = 0 THEN ‘Off’
END AS is_supplemental_logging_enabled
,CASE WHEN sys.databases.is_read_committed_snapshot_on = 1 THEN ‘On’
WHEN sys.databases.is_read_committed_snapshot_on = 0 THEN ‘Off’
END AS is_read_committed_snapshot_on
,CASE WHEN sys.databases.is_auto_create_stats_on = 1 THEN ‘On’
WHEN sys.databases.is_auto_create_stats_on = 0 THEN ‘Off’
END AS is_auto_create_stats_on
,CASE WHEN sys.databases.is_auto_update_stats_async_on = 1 THEN ‘On’
WHEN sys.databases.is_auto_update_stats_async_on = 0 THEN ‘Off’
END AS is_auto_update_stats_async_on
,CASE WHEN sys.databases.is_ansi_null_default_on = 1 THEN ‘On’
WHEN sys.databases.is_ansi_null_default_on = 0 THEN ‘Off’
END AS is_ansi_null_default_on
,CASE WHEN sys.databases.is_ansi_nulls_on = 1 THEN ‘On’
WHEN sys.databases.is_ansi_nulls_on = 0 THEN ‘Off’
END AS is_ansi_nulls_on
,CASE WHEN sys.databases.is_ansi_padding_on = 1 THEN ‘On’
WHEN sys.databases.is_ansi_padding_on = 0 THEN ‘Off’
END AS is_ansi_padding_on
,CASE WHEN sys.databases.is_ansi_warnings_on = 1 THEN ‘On’
WHEN sys.databases.is_ansi_warnings_on = 0 THEN ‘Off’
END AS is_ansi_warnings_on
,CASE WHEN sys.databases.is_arithabort_on = 1 THEN ‘On’
WHEN sys.databases.is_arithabort_on = 0 THEN ‘Off’
END AS is_arithabort_on
,CASE WHEN sys.databases.is_concat_null_yields_null_on = 1 THEN ‘On’
WHEN sys.databases.is_concat_null_yields_null_on = 0 THEN ‘Off’
END AS is_concat_null_yields_null_on
,CASE WHEN sys.databases.is_numeric_roundabort_on = 1 THEN ‘On’
WHEN sys.databases.is_numeric_roundabort_on = 0 THEN ‘Off’
END AS is_numeric_roundabort_on
,CASE WHEN sys.databases.is_quoted_identifier_on = 1 THEN ‘On’
WHEN sys.databases.is_quoted_identifier_on = 0 THEN ‘Off’
END AS is_quoted_identifier_on
,CASE WHEN sys.databases.is_recursive_triggers_on = 1 THEN ‘On’
WHEN sys.databases.is_recursive_triggers_on = 0 THEN ‘Off’
END AS is_recursive_triggers_on
,CASE WHEN sys.databases.is_cursor_close_on_commit_on = 1 THEN ‘On’
WHEN sys.databases.is_cursor_close_on_commit_on = 0 THEN ‘Off’
END AS is_cursor_close_on_commit_on
,CASE WHEN sys.databases.is_local_cursor_default = 1 THEN ‘Local’
WHEN sys.databases.is_local_cursor_default = 0 THEN ‘Global’
END AS is_local_cursor_default
,CASE WHEN sys.databases.is_fulltext_enabled = 1 THEN ‘On’
WHEN sys.databases.is_fulltext_enabled = 0 THEN ‘Off’
END AS is_fulltext_enabled
,CASE WHEN sys.databases.is_trustworthy_on = 1 THEN ‘On’
WHEN sys.databases.is_trustworthy_on = 0 THEN ‘Off’
END AS is_trustworthy_on
,CASE WHEN sys.databases.is_db_chaining_on = 1 THEN ‘On’
WHEN sys.databases.is_db_chaining_on = 0 THEN ‘Off’
END AS is_db_chaining_on
,CASE WHEN sys.databases.is_parameterization_forced = 1 THEN ‘FORCED’
WHEN sys.databases.is_parameterization_forced = 0 THEN ‘SIMPLE’
END AS is_parameterization_forced
,CASE WHEN sys.databases.is_master_key_encrypted_by_server = 1 THEN ‘On’
WHEN sys.databases.is_master_key_encrypted_by_server = 0 THEN ‘Off’
END AS is_master_key_encrypted_by_server
,CASE WHEN sys.databases.is_published = 1 THEN ‘On’
WHEN sys.databases.is_published = 0 THEN ‘Off’
END AS is_published
,CASE WHEN sys.databases.is_subscribed = 1 THEN ‘On’
WHEN sys.databases.is_subscribed = 0 THEN ‘Off’
END AS is_subscribed
,CASE WHEN sys.databases.is_merge_published = 1 THEN ‘On’
WHEN sys.databases.is_merge_published = 0 THEN ‘Off’
END AS is_merge_published
,CASE WHEN sys.databases.is_distributor = 1 THEN ‘On’
WHEN sys.databases.is_distributor = 0 THEN ‘Off’
END AS is_distributor
,CASE WHEN sys.databases.is_sync_with_backup = 1 THEN ‘On’
WHEN sys.databases.is_sync_with_backup = 0 THEN ‘Off’
END AS is_sync_with_backup
,CASE WHEN sys.databases.is_broker_enabled = 1 THEN ‘Yes’
WHEN sys.databases.is_broker_enabled = 0 THEN ‘No’
END AS is_broker_enabled
,CASE WHEN sys.databases.is_date_correlation_on = 1 THEN ‘Yes’
WHEN sys.databases.is_date_correlation_on = 0 THEN ‘No’
END AS is_date_correlation_on
,CASE WHEN sys.databases.is_cdc_enabled = 1 THEN ‘Yes’
WHEN sys.databases.is_cdc_enabled = 0 THEN ‘No’
END AS is_cdc_enabled
,CASE WHEN sys.databases.is_encrypted = 1 THEN ‘Encrypted’
WHEN sys.databases.is_encrypted = 0 THEN ‘Not Encrypted’
END AS is_encrypted
,CASE WHEN sys.databases.is_honor_broker_priority_on = 1 THEN ‘On’
WHEN sys.databases.is_honor_broker_priority_on = 0 THEN ‘Off’
END AS is_honor_broker_priority_on
FROM #temp
RIGHT OUTER JOIN sys.databases
ON #temp.db_name =

Older Entries

%d bloggers like this: