Posts

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.

http://www.microsoft.com/en-us/download/details.aspx?id=30653

Repeat Column Headers for Tablix in SSRS 2008

Image
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 TablesSELECT'DROP TABLE [' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOTLIKE'CRM%'
AND TABLE_TYPE = 'BASE TABLE'


Drop ViewsSELECT'DROP VIEW [' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME NOTLIKE'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') ISNOTNULL
DROPPROC GenerateBCPforSSIS
GO

CREATEPROCEDURE GenerateBCPforSSIS
(
@db_name varchar(1000),
@table_name varchar(1000),
@file_name varchar(1000)
) AS

Declare @Headers varchar(MAX),@HeadersRaw varchar(MAX),@sqlvarchar(MAX), @header_file varchar(MAX), @filename_short varchar(MAX)

--Generate columnnamesas a recordset
Select @Headers = IsNull(@Headers + ',', '') + '""' + Column_Name + '""'
From INFORMATION_SCHEMA.COLUMNS
Where Table_Name = @table_name ORDERBY ORDINAL_POSITION ASC

--Create a dummyfileto have header data
select @header_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.csv'

set @sql = 'bcp "sel…

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') ISNOTNULL
DROPFUNCTION dbo.RemoveSpecialChars;
GO
CREATEFUNCTION dbo.RemoveSpecialChars
( @InputString VARCHAR(max)
)
RETURNSVARCHAR(8000) BEGIN
IF @InputString ISNULLRETURNNULL

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)
SET @p = @p + 1
ENDIF LEN(@OutputString) = 0 RETURNNULL
RETURN @OutputString
END
GO

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') ISNOTNULL
DROPFUNCTION dbo.RemoveLineBreak;
GO
CREATEFUNCTION dbo.RemoveLineBreak
(@TEXT VARCHAR(MAX))
RETURNSVARCHAR(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') ISNOTNULL
DROPFUNCTION dbo.GetNthWord;
GO
SET QUOTED_IDENTIFIER ON
GO
CREATEFUNCTION dbo.GetNthWord
(
@string varchar(max),
@n int
)
RETURNSvarchar(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( orderby (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.

http://www.iis.net/downloads/microsoft/application-initialization

"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 alter…

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: http://consultingblogs.emc.com/jamiethomson/archive/2007/12/18/SSIS_3A00_-A-performance-tuning-success-story.aspx

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

Image
REGISTRYDisable 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)
SQLMax 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 WITH OVERRIDE; --reduces ASYNC_…

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.

https://downloadcenter.intel.com/Detail_Desc.aspx?agr=Y&DwnldID=22842&lang=eng&OSVersion=Windows%208%2C%2064-bit*&DownloadType=Drivers


SQL Statements to Get Basic Info About Tables

Image
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
GROUPBY TABLE_NAME



RECORD COUNT FOR TABLE
SELECT sc.name +'.'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNERJOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNERJOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUPBY sc.name,ta.name
ORDERBYSUM(pa.rows) DESC




FOREIGN KEY CONSTRAINTS (Relationships)
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
ORDERBY TABLE_NAME

Check Out the DefenseReady Daily Standup App for Windows 8

Image
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

Image
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.http://www.tilegem.com

Update Thumbprint in Web.Config After Updating ADFS 2.0 Certificate

Image
Recently I had to replace an expired certificate on my ADFS 2.0 machine.  I followed the instruction on the TechNet wiki found here.http://social.technet.microsoft.com/wiki/contents/articles/2554.ad-fs-2-0-how-to-replace-the-ssl-service-communications-token-signing-and-token-decrypting-certificates.aspxThe 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 Personal folder, point to All Tasks,…

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

Image
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.
DELETEa
--select A.LogicalName
FROMmetadataschema.attributea
INNERJOINmetadataschema.entitye
ONe.entityid