sql server
How to export table schema and data with Insert statements in SQL Server?
In this example, have created Persons Table with the data. Will see how to export the table schema and data with insert statements.
Follow the below steps.
How to query CRM Global OptionSet Values in SQL?
Use the below select query to get the Global OptionSet values in SQL,
SELECT OS.NAME, L.LABEL, AV.VALUE FROM ATTRIBUTEPICKLISTVALUEASIFPUBLISHEDLOGICALVIEW AV JOIN OPTIONSETASIFPUBLISHEDLOGICALVIEW OS ON AV.OPTIONSETID = OS.OPTIONSETID JOIN LOCALIZEDLABELASIFPUBLISHEDLOGICALVIEW L ON L.OBJECTID = AV.ATTRIBUTEPICKLISTVALUEID WHERE OS.ISGLOBAL = 1 AND OS.ISCUSTOMOPTIONSET = 1 AND L.OBJECTCOLUMNNAME = 'DISPLAYNAME' AND OS.NAME ='<Provide Global OptionSet Schema Name>'
Increase number of records per page in MS CRM
Follow the below process to increase number of records for a particular user,
Supported Way:
Step 1: Open Microsoft Dynamics CRM Online / On-premise. Click on Gear Icon on Top Right Corner. Click on Options.
Step 2: Under General tab, select “Records Per Page” to the required records count per page. (Min: 25 and Max: 250)
Step 3: Click on OK.
UnSupported Way:
In MS CRM OnPremises, Open Microsoft SQL Server Management Studio.
Right Click on the required MSCRM Organization (OrganizationName_MSCRM) and click on New Query.
For Example, I am providing PAGINGLIMIT = 500 AND FULLNAME = ‘Arun Potti’ in the below query
Copy & Paste the below query and Execute,
UPDATE USERSETTINGS SET PAGINGLIMIT=500 WHERE SYSTEMUSERID = ( SELECT TOP 1 SYSTEMUSERID FROM SYSTEMUSER WHERE FULLNAME ='Arun Potti' )
Refresh CRM Page and open any Entity or Advanced Find to see the effect.
Provide your valuable feedback on the article.
Convert SQL query to Microsoft Dynamics CRM FetchXML
Found interesting Online Tool to convert SQL query to Microsoft Dynamics CRM FetchXML. Click here to have a look.
How to get Organization & Sql Server Database name in MS CRM using SQL Server
It is not mandatory to have the same CRM Organization Name (On-Premises) with same Database Name in Sql Server.
So to check which Oragnization pointing to the database can be known by using the below query
SELECT DatabaseName [DATABASE NAME], FriendlyName [ORGANIZATION NAME], SqlServerName [SQL SERVER NAME], SrsUrl [SSRS URL], [ORGANIZATION STATUS] = CASE WHEN STATE=1 THEN 'ENABLED' WHEN STATE=0 THEN 'DISABLED' END FROM [MSCRM_CONFIG].[DBO].[ORGANIZATION]
Please share your valuable feedback on this article.
Arun Potti's Power Platform blog 