Showing posts from 2013

An error occurred while applying security information to

While attempting to move files from a hard drive I recovered I kept getting the following error on a specific set of files. An error occurred while applying security information to {G:\Folder} Failed to enumerate objects in the container. Access is denied. In order to fix it did the following. -Open a Command Prompt "As Administrator" -Run the following commands: takeown /f "G:\folder" /r /d y icacls "G:\folder" /grant administrators:F /T

IE11 __dopostback undefined

After attempting to access my web app in IE11 I noticed all the links were no longer working.  Looking through the JavaScript debugger I saw the error "__dopostback undefined" when attempting to click a link.  Solution turned out to be installing .NET 4.5 on the server.

Repeat Column Headers for Tablix in SSRS 2008

If you are having trouble getting the column headings to repeat on every page for a table/tablix in SSRS 2008 try this. Open the file in a text editor and look for the Tablixrowheirarchy->tablixMembers->TablixMember node. Then add the following elements. <keepwithgroup>After</keepwithgroup> <repeatonnewpage> true </repeatonnewpage> <keeptogether> true </keeptogether>

SQL - Generate Scripts to Drop Tables/Views

You can use the commands below to create SQL commands which will drop tables/views.  the were clause can be used to limit which tables/views are included in the statement. Drop Tables SELECT 'DROP TABLE [' + TABLE_NAME + ']' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME NOT LIKE 'CRM%' AND TABLE_TYPE = 'BASE TABLE' Drop Views SELECT 'DROP VIEW [' + TABLE_NAME + ']' FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME NOT LIKE 'CRM%'

SQL - Add Header Row to SSIS Generated CSV Files

After generating a flat file in SSIS I needed to update that file with the column headers as the first row.  In order to do this I used a SQL stored procedure. STEP 1 : Run the code below to create the stored procedure. USE MIGRATION_DB IF OBJECT_ID( 'GenerateBCPforSSIS' ) IS NOT NULL DROP PROC GenerateBCPforSSIS GO CREATE PROCEDURE GenerateBCPforSSIS ( @db_name varchar (1000), @table_name varchar (1000), @file_name varchar (1000) ) AS Declare @Headers varchar ( MAX ),@HeadersRaw varchar ( MAX ),@ sql varchar ( MAX ), @header_file varchar ( MAX ), @filename_short varchar ( MAX ) --Generate column names as a recordset Select @Headers = IsNull(@Headers + ',' , '' ) + '""' + Column_Name + '""' From INFORMATION_SCHEMA.COLUMNS Where Table_Name = @table_name ORDER BY ORDINAL_POSITION ASC -- Create a dummy file to have header data select @header_file= substring (@file_name,1,len(@file_name)-charind

SQL - Remove Special Characters

Removes all special characters from an input string. USE MIGRATION_DB SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID (N 'dbo.RemoveSpecialChars' , N 'FN' ) IS NOT NULL DROP FUNCTION dbo.RemoveSpecialChars; GO CREATE FUNCTION dbo.RemoveSpecialChars ( @InputString VARCHAR ( max ) ) RETURNS VARCHAR (8000) BEGIN IF @InputString IS NULL RETURN NULL DECLARE @OutputString VARCHAR ( max ) SET @OutputString = '' DECLARE @l INT SET @l = LEN(@InputString) DECLARE @p INT SET @p = 1 WHILE @p <= @l BEGIN DECLARE @c INT SET @c = ASCII( SUBSTRING (@InputString, @p, 1)) IF @c BETWEEN 48 AND 57 OR @c BETWEEN 65 AND 90 OR @c BETWEEN 97 AND 122 -- OR @c = 32 SET @OutputString = @OutputString + CHAR (@c)

SQL - Remove Non-Alpha Characters

This function will remove all non-alphanumeric characters. USE MIGRATION_DB SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID (N 'dbo.RemoveNonAlphaCharacters' , N 'FN' ) IS NOT NULL DROP FUNCTION dbo.RemoveNonAlphaCharacters; GO Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(max)) Returns VarChar(max) AS BEGIN DECLARE @KeepValues as varchar(50) = '%[^a-z]%' WHILE PatIndex(@KeepValues, @Temp) > 0 SET @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '' ) RETURN @Temp END GO

SQL - Remove Line Breaks From Text

This function will attempt to remove line breaks from text in SQL. USE MIGRATION_DB GO IF OBJECT_ID (N 'dbo.RemoveLineBreak' , N 'FN' ) IS NOT NULL DROP FUNCTION dbo.RemoveLineBreak; GO CREATE FUNCTION dbo.RemoveLineBreak (@TEXT VARCHAR ( MAX )) RETURNS VARCHAR ( MAX ) AS BEGIN DECLARE @fixedTEXT VARCHAR ( MAX ) = @TEXT SET @fixedTEXT = REPLACE(REPLACE(REPLACE(REPLACE(@TEXT, CHAR (10), '' ), CHAR (13), '' ), '\r' , ' ' ), '\n' , ' ' ) RETURN @fixedTEXT END GO

SQL - Get Nth Word

Need to find the Nth word in a sentence, try this function. USE MIGRATION_DB GO IF OBJECT_ID (N 'dbo.GetNthWord' , N 'FN' ) IS NOT NULL DROP FUNCTION dbo.GetNthWord; GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION dbo.GetNthWord ( @string varchar ( max ), @n int ) RETURNS varchar ( max ) AS BEGIN DECLARE @xml xml, @s nvarchar( max ), @wd varchar (100) set @xml = '<root><record>' + REPLACE(@string, ' ' , '</record><record>' ) + '</record></root>' ; With rs(id, word) AS ( select ROW_NUMBER () over ( order by ( select 1))id , * from ( select t. value ( '.' , 'varchar(150)' ) as [items] from @xml.nodes( '//root/record' ) as a(t)) data where len([items]) > 0 ) select @wd=word from rs where id=@n return (@wd) END

Wake Up IIS Websites

One of the biggest issues I have found with using authentication methods like Kerberos Constrained Delegation is that the first person who hits the site in the morning gets a 500 error until the website application pools have been rebuilt after their nightly recycle.  Recently I discovered a great module for IIS that can help prevent this. "IIS Application Initialization for IIS 7.5 enables website administrators to improve the responsiveness of their Web sites by loading the Web applications before the first request arrives. By proactively loading and initializing all the dependencies such as database connections, compilation of ASP.NET code, and loading of modules, IT Professionals can ensure their Web sites are responsive at all times even if their Web sites use a custom request pipeline or if the Application Pool is recycled. While an application is being initialized, IIS can also be configured to return an

Get Times for CRM Import Jobs

This script will give you the times for CRM import jobs that have been run on your system. --Shows all import records and the amount of time they took SELECT [Name], [StartedOn], [CompletedOn], DATEDIFF(MINUTE, [StartedOn], [CompletedOn]) AS [Diff In Minutes] FROM [Default_MSCRM].[dbo].[AsyncOperationBase] WHERE OperationType IN (3,4,5) --OperationType 3,4,5 are the ones which relate to imports --Shows total number of minutes for all imports SELECT SUM(DATEDIFF(MINUTE, [StartedOn], [CompletedOn])) AS [Total Diff In Minutes] FROM [Default_MSCRM].[dbo].[AsyncOperationBase] WHERE OperationType IN (3,4,5) --Shows total number of hours for all imports SELECT SUM(DATEDIFF(MINUTE, [StartedOn], [CompletedOn])) /60 AS [Total Diff In Hours] FROM [Default_MSCRM].[dbo].[AsyncOperationBase] WHERE OperationType IN (3,4,5)

SSIS Speed: DefaultBufferMaxRows

SSIS can sometimes take a long time to load.  Try messing witht he DefaultBufferMaxRows setting in the package.  In a first attempt on one table changing the setting from 10,000 (the default) to 1,000 saved me about 15 minutes off my run time. Thanks to Jamie Thomson for posting this info:

Reset Default Org for CRM Users

The script below will change the default org for users.  This can be helpful if you have disabled the original default org and users are now getting the crm error that the organization has been disabled.  Just change the @NewOrgName value to the value of your database name without the _MSCRM suffix. USE MSCRM_Config DECLARE @NewOrgName VARCHAR(64) SET @NewOrgName = 'DefenseReadyUM' UPDATE [MSCRM_CONFIG].[dbo].[SystemUser] SET [DefaultOrganizationId] = (SELECT [Id] FROM [MSCRM_CONFIG].[dbo].[Organization] WHERE [DatabaseName] = @NewOrgName + '_MSCRM') WHERE EXISTS (SELECT [Id] FROM [MSCRM_CONFIG].[dbo].[Organization] WHERE [Id] = [MSCRM_CONFIG].[dbo].[SystemUser].[DefaultOrganizationId])

CRM 2011 Performance Settings

REGISTRY Disable Loopback check HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa            New DWORD Value: DisableLoopbackCheck = 1 (Decimal) CRM Settings HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM           New DWORD Value: OLEDBTimeout = 86400 (Decimal)           New DWORD Value: ExtendedTimeout = 1000000 (Decimal)           New DWORD Value: NormalTimeout = 300000 (Decimal)           New DWORD Value: AsyncRemoveCompletedWorkflows = 1 (Decimal)           New DWORD Value: AsyncRemoveCompletedJobs = 1 (Decimal) TCP/IP Settings HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters           New DWORD Value: MaxUserPort = 65534 (Decimal)           New DWORD Value: TcpTimedWaitDelay = 260 (Decimal) SQL Max Degree of Parallelism and Other SQL Settings exec sp_configure 'show adv', 1; RECONFIGURE WITH OVERRIDE; --reduces locking by ensureing queries are only run against a single processor exec sp_configure 'max degree', 1; RECONFIGURE WI

Get Sketchup Working on Windows Surface Pro

After installing Sketchup every time I clicked on the "Start Using Sketchup" button located on the Welcome window I would get an error that closed the application.  Apparently the default drivers included on the Surface Pro were causing the problem.  After updating the drivers from the link below Sketchup started functioning perfectly.*&DownloadType=Drivers

SQL Statements to Get Basic Info About Tables

Here are two scripts I found that will help you generate basic info about your database.  This data can be useful when beginning the level of effort for a data migration. NUMBER OF COLUMNS PER TABLE SELECT TABLE_NAME, COUNT (*) AS COLUMN_COUNT FROM INFORMATION_SCHEMA.COLUMNS GROUP BY TABLE_NAME RECORD COUNT FOR TABLE SELECT + '.' + TableName , SUM (pa. rows ) RowCnt FROM sys.tables ta INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) GROUP BY, ORDER BY SUM (pa. rows ) DESC FOREIGN KEY CONSTRAINTS (Relationships) SELECT TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME

Check Out the DefenseReady Daily Standup App for Windows 8

DefenseReady Daily Standup is a Windows 8 app that provides military commanders and leadership with real-time insights into PERSTEMPO and strength levels throughout their organizations. Inspired by SOCOM’s Preservation of the Force and Families Task Force and designed exclusively for use with the DefenseReady platform, the app is optimized for touch-screen control via mobile devices using Windows 8. With a few easy swipes of the mobile device, military leaders can get detailed visibility into data about their forces that previously took significant man-hours and staff coordination to retrieve and compile. DefenseReady Daily Standup can work both online and offline and will sync with real time data when a DefenseReady server is available. View in the Windows Store

Fragmented indexes were detected in the Microsoft Dynamics CRM database

Re-index your CRM Org database if you receive this error. USE  Org_MSCRM GO EXEC  sp_MSforeachtable  @command1 = "print '?' DBCC DBREINDEX ('?', ' ', 80)" GO EXEC  sp_updatestats GO 

TimeGem – Free Tile Layout Software

As a true techie I always look to find computer solutions that can help me save time and money.  I decided to tackle putting down tile in my basement and wondered if there was any software that could help me plan the layout.  I was pleasantly surprised to find TileGem, a freeware app.  After taking measurements I added in the walls, chose the tile, and set the grout size.  Once the room was set up it was easy to drag around the tile grid to find the best way to completely the layout.

Update Thumbprint in Web.Config After Updating ADFS 2.0 Certificate

Recently I had to replace an expired certificate on my ADFS 2.0 machine.  I followed the instruction on the TechNet wiki found here. The instructions were great but there is one more step that you need to complete before your website will connect correctly. Once you have the thumbprint of the certificate you are using for ADFS 2.0 you must then update the web.config of each website that is utilizing ADFS for authentication.  Be careful when copying the thumbprint from the certificate properties window.  Make sure to remove all the spaces between the data before pasting it into the thumbprint property of the web.config.

Install/Update Certificate for Remote Desktop Gateway

  To import a certificate to the RD Gateway server in the (Local Computer)/Personal Store Open the Certificates snap-in console. If you have not already added the Certificates snap-in console, you can do so by doing the following: Click Start , click Run , type mmc , and then click OK . If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue .. On the File menu, click Add/Remove Snap-in . In the Add or Remove Snap-ins dialog box, in the Available snap-ins list, click Certificates , and then click Add . In the Certificates snap-in dialog box, click Computer account , and then click Next . In the Select Computer dialog box, click Local computer: (the computer this console is running on) , and then click Finish . In the Add or Remove Snap-ins dialog box, click OK . In the Certificates snap-in console, in the console tree, expand Certificates (Local Computer) , and then click Personal . Right-click the P

Page Not Found 404 After Install or Uninstalling CRM 2011 UR

After uninstalling an update rollup and rebooting the machine CRM starting coming back with a 404 Page Not Found error when trying to access the site. The trace log showed the following error: Crm Exception: Message: Invalid license. PidGen.dll cannot be loaded from this path C:\Program Files\Microsoft Dynamics CRM\Server\bin\PidGen.dll, ErrorCode: –2147167677 This machine was set to NOT automatically install windows updates.  To solve my issue run windows update and get all the current updates other than any new CRM Update Rollups you do not wish to install.  After the windows update and another reboot everything was working again. 

Deletion State Codes Fields Preventing Solution Import

If you have upgraded a CRM database from 4.0. to 2011 you may have some leftover fields that end in dsc.  These fields can later on come back to haunt you by showing up as missing components when import a solution into 2011. The fields will always end in “dsc” as you can see in the image below. John Hoven has written a SQL script which will remove these attributes from the database.  After removing them re-export the solution and attempt the import again. -- Delete the custom DSC attributes from the database.   DELETE   a   --select A.LogicalName   FROM    metadataschema . attribute   a          INNER   JOIN   metadataschema . entity   e            ON   e . entityid   =   a . entityid   WHERE   a . attributeof   IS   NOT   NULL          AND   a . attributetypeid   =   '00000000-0000-0000-00AA-110000000019'          AND   a . logicalname   LIKE   '%dsc'          AND   a . iscustomfield   =   1   -- Delete the dsc attribute map columns DELETE   am   --select AM.*