Neil McDonald's Dynamics CRM Blog

Posts Tagged ‘sql

Microsoft Dynamics CRM 2013 Installation

with one comment

Microsoft Dynamics CRM 2013 was made available for download earlier today. You can grab it from here if you’d like to try it out. Also, you can get the official implementation guides from here.

Since my CRM 2011 setup video proved quite popular, I thought I’d take the opportunity to record an updated version for 2013 while I setup my own development environment.

The video is embedded below, but I recommend watching it on YouTube in HD quality, otherwise some of the text can be quite hard to read.

I hope that you find it useful!

Written by neilmcd

October 8, 2013 at 10:51 pm

Posted in CRM 2013

Tagged with , , , , ,

Moved to WordPress

leave a comment »

Unfortunately, Posterous is closing down, so I’ve transferred my blog to WordPress.

Some of the SQL and JavaScript formatting got lost during the transfer, so I’m currently in the process of checking it all over. If you find anything not working, leave a comment and I’ll get it fixed.

Thanks!

Written by neilmcd

February 16, 2013 at 6:31 pm

Posted in Misc

Tagged with ,

Restricting Hosted CRM Users From Browsing Active Directory

with one comment

When adding users to CRM via the Add Multiple Users wizard, it’s possible to view all users in Active Directory and then add them as CRM users

Screen_shot_2012-12-20_at_17

In a hosted environment, this is not ideal, as admin users for each organisation will be able to see users from all other hosted customers.

With CRM 4, there was a method of restricting users to only browse users in a particular business unit (see here). Unfortunately, this tool does not work with CRM 2011.

By looking at an old CRM 4 instance, I had a hunt around to see which settings were actually changed by the config tool to see if they would still work in CRM 2011. Thankfully, I found where to add the settings and it still appears to work in CRM 2011.

Note: The rest of this post involves making changes directly to the MSCRM_CONFIG database and this is completely unsupported. Please only try this if you have your CRM databases backed up. Please try it on a test environment first. Read the rest of this entry »

Written by neilmcd

December 20, 2012 at 6:14 pm

Posted in CRM 2011

Tagged with , ,

Dynamics CRM 2011 beta setup

with 6 comments

I’ve created a short video guide for setting up the Dynamics CRM 2011 on-premise beta. I installed all of the services on to the same virtual machine, so some of the things done will not reflect best practices. The whole installation (excluding Windows setup and updates) took took just over an hour but I’ve tried to cut as many of the progress bars as possible.

Before I started recording, I installed Windows Server 2008 with SP2 and all available updates. I also turned off UAC and the Windows firewall.

If the video below does not work, please try here. Be sure to watch it in full screen HD, otherwise the quality is not great.

Written by neilmcd

September 13, 2010 at 12:08 am

Posted in CRM 2011

Tagged with , ,

Checking SQL table sizes

leave a comment »

Sqlsize

I wanted a way to find which tables were using the most storage in my SQL databases. The stored procedure sp_spaceused will show this data for a single table, but I wanted to view every table at once. The below SQL query will run against any SQL 2005 database and show you it’s table sizes in descending order. As expected, the ActivityMimeAttachment table in my CRM database was pretty huge!


SELECT sys.schemas.[name] AS [Schema],
sys.tables.name AS [Table],
COALESCE([Row Count].[Count], 0) AS [Rows],COALESCE(8192 * [Data Pages].[Count],0) AS [Data Bytes],
COALESCE(8192 * [Index Pages].[Count],0) AS [Index Bytes]
FROM sys.tables
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id
LEFT OUTER JOIN (SELECT object_id,SUM(rows) AS [Count]
FROM sys.partitions
WHERE index_id < 2
GROUP BY object_id) AS [Row Count] ON [Row Count].object_id = sys.tables.object_id
LEFT OUTER JOIN (SELECT sys.indexes.object_id,SUM(CASE WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0 END) AS [Count]
FROM sys.indexes
INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id
AND p.index_id = sys.indexes.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY sys.indexes.object_id) AS [Data Pages] ON [Data Pages].object_id = sys.tables.object_id
LEFT OUTER JOIN (SELECT sys.indexes.object_id,SUM(a.used_pages - CASE
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0 END) AS [Count]
FROM sys.indexes
INNER JOIN sys.partitions AS p ON p.object_id = sys.indexes.object_id
AND p.index_id = sys.indexes.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY sys.indexes.object_id) AS [Index Pages] ON [Index Pages].object_id = sys.tables.object_id
ORDER BY [Data Bytes] desc

Written by neilmcd

July 19, 2010 at 6:07 pm

Posted in Misc

Tagged with

Get past the 250 records per page limit

with one comment

Screen_shot_2010-07-18_at_17

Dynamics CRM 4 only allows users to view a maximum of 250 records per page within a view. This can be quite limiting when you need to run an on demand workflow against thousands of records.

There are 2 ways to get around this limit. The Microsoft supported solution is to use a plugin as described here. The unsupported and more risky workaround (and what I did…) is to update the organisation database directly as below.

UPDATE UserSettingsBase SET PagingLimit=1000 WHERE SystemUserId IN(Select SystemUserId from SystemUserBase WHERE FullName = 'neil mcdonald')
Screen_shot_2010-07-18_at_17.57.36.png.scaled1000

If using the SQL method, I recommend that you change it back to 250 rows or less once you’ve accomplished your tasks, as this is not supported by Microsoft and could cause errors with future updates.

Written by neilmcd

July 18, 2010 at 4:43 pm

Posted in CRM 4

Tagged with , ,

Changing an Active Directory username in CRM

leave a comment »

Add_user_error

One of our users had complained that their name was spelled incorrectly. I made the changes in AD, Exchange and in her CRM user account, but when I tried to change her domain login name in CRM, I received the error ‘You are attempting to create a user with a domain logon that is already used by another user…’

To get around it, I made the change directly in the organisation database. The domain login name is stored in the ‘DomainName’ column of the ‘SystemUserBase’ table, so I just edited the data within SQL management studio.

FYI – Although I do this, I don’t recommend making any changes to the CRM databases directly. A safer workaround is posted here

Written by neilmcd

July 7, 2010 at 6:06 pm

Posted in CRM 4

Tagged with ,