MSCRM 2011 - Exporting Security Roles (PDF,EXCEL, Word, XML, JPG)

If you want to write some documents about Security Roles this post is right for you :-)!!!


I've developed a Reporting Services Report to export Costum Security Roles. Below you can see the export file, output by report.

Here you can find the report.


Note: This report need Administrator CRM DB Role.




Best Regards, 

Nicola

SQL SERVER - Restore Database via T-SQL

Ck logical name with the following query:

Query

RESTORE FILEONLY FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\<My DB>_MSCRM.bak'

Result:

Run the following query:

Query

RESTORE DATABASE [<My DB>_MSCRM]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\<My DB>_MSCRM.bak'
WITH FILE = 1, MOVE N'mscrm' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\<My DB>_MSCRM.mdf',
MOVE N'mscrm_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\<My DB>_MSCRM_log.LDF',
NOUNLOAD, REPLACE, STATS = 10
RESTORE DATABASE [<MY DB>_MSCRM]

Nicola Grillo

CRM 2011 - How to find the Guid in CRM Data Base

If you want to find an Guid in the Microsoft Dynamics CRM Database, you should use the following query :

Query

USE <DB_NAME_MSCRM>

Declare @TN as varchar(200), @CN as varchar(200), @myValue varchar(38), @SQL as nvarchar(1000)

, @SN as varchar(200),

Create Table #myTable (Table_Name varchar(200), Column_Name varchar(200), Number_Of_Rows int)

-- Replace @myValue with the value you're searching for in the database

Set @myValue = 'Guidid' 

Declare myCursor Cursor For

Select T.Table_Name, C.Column_Name, T.Table_Schema

From INFORMATION_SCHEMA.TABLES T Inner Join INFORMATION_SCHEMA.COLUMNS C 

On T.Table_Schema = C.Table_Schema And T.Table_Name = C.Table_Name

Where T.Table_Name <> 'dtproperties' And Table_Type = 'Base Table'

And C.Data_Type In ('uniqueidentifier')

-- Replace if you want other type data

--And C.Data_Type In ('text','ntext')

--And C.Data_Type In ('tinyint','int','bigint','numeric','decimal','money','float','smallint','real','smallmoney')

--And C.Data_Type In ('datetime','dmalldatetime')

Open myCursor

Fetch Next From myCursor Into @TN, @CN, @SN

While @@Fetch_Status <> -1

Begin

                EXECUTE ( N'Insert Into #myTable Select ''' + @SN + '.' + @TN + ''', ''' + @CN + ''', Count(*) From [' + @SN + '].[' + @TN + '] Where [' + @CN + '] = CONVERT(uniqueidentifier ,''' + @myValue + ''' )')

                

                Fetch Next From myCursor Into @TN, @CN, @SN

End

Close myCursor

Deallocate myCursor

Select * From #myTable Where Number_Of_Rows > 0 Order By Table_Name

Drop Table #myTable

You should use this query in order to find also other data type.

Nicola Grillo

CRM 4.0 - Gathering Picklist information via SQL query in Microsoft Dynamics CRM (String Map CRM Table)

Use the following query to gathering information about CRM Picklist.

// Comment
SELECT
[ObjectTypeCode]
,[AttributeName]
,[AttributeValue]
,[LangId]
,[OrganizationId]
,[Value]
,[DisplayOrder]
,[VersionNumber]
,[StringMapId]
FROM DBNAME_MSCRM.[dbo].[StringMap]
where [AttributeName]='PicklistName'
Order by AttributeValue

Thanks,
Nicola Grillo

SQL SERVER - How to get Microsoft Dynamics CRM picklist Label via SQL Query (String Map CRM Table)

1. Add in your query the following Left join:

Workaround

LEFT JOIN [Organizationname_MSCRM].dbo.StringMap sm On 

sm.AttributeValue=picklistvalue AND
sm.ObjectTypeCode=3 (Example opportunity=3) AND
sm.AttributeName=''picklistname'' AND
sm.LangId=''1033'' 

2. Use sm.Value to retrieve the Picklist Label

Nicola Grillo

SQL SERVER - Database Suspected in SQL Server 2008

Connected to your database and check if it is in emergency mode, else run this comand:
ALTER DATABASE DB_NAME SET EMERGENCY
set single user in the database property and run this command:
DBCC CHECKDB ('DB_NAME',REPAIR_ALLOW_DATA_LOSS)

Nicola Grillo

SQL SERVER - Remove duplicate rows from a table in SQL Server

This query remove just the duplicate rows, if you have the tripled or quadrupled run more one it.

delete Table1

from
(select c1 as t

from Table1 p
having COUNT(*)>1) as t

where c1=Max(t.max)