SQL Server – Generating PERMUTATIONS using T-Sql

Were you ever asked to generate string Permutations using TSql? I was recently asked to do so, and the logic which I could manage to come up at that point is shared in the below script.

DECLARE @Value AS VARCHAR(20) = 'ABCC' --Mention the text which is to be permuted

DECLARE @NoOfChars AS INT = LEN(@Value)

DECLARE @Permutations TABLE(Value VARCHAR(20)) --Make sure the size of this Value is equal to your input  string length (@Value)

;WITH NumTally AS (--Prepare the Tally Table to separate each character of the Value.

  SELECT 1 Num

  UNION ALL

  SELECT 

    Num + 1 

  FROM 

    NumTally 

  WHERE 

    Num < @NoOfChars

),Chars AS ( --Separate the Characters

SELECT

  Num,

  SUBSTRING(@Value,Num,1) Chr

FROM

  NumTally  

)

--Persist the Separated characters.

INSERT INTO @Permutations

SELECT Chr FROM Chars

--Prepare Permutations

DECLARE @i AS INT = 1

WHILE(@i < @NoOfChars)

BEGIN

  --Store the Permutations

  INSERT INTO @Permutations

  SELECT DISTINCT --Add DISTINCT if required else duplicate Permutations will be generated for Repeated  Chars.

    P1.Value + P2.Value

  FROM 

    (SELECT Value FROM @Permutations WHERE LEN(Value) = @i) P1 

  CROSS JOIN 

    (SELECT Value FROM @Permutations WHERE LEN(Value) = 1) P2

  --Increment the Counter.      

  SET @i += 1  

  --Delete the Incorrect Lengthed Permutations to keep the table size under control.

  DELETE FROM @Permutations WHERE LEN(Value) NOT IN (1,@i)

END

--Delete InCorrect Permutations.

SET @i = 1

WHILE(@i <= @NoOfChars)

BEGIN

  --Deleting Permutations which has not used "All the Chars of the given Value".

  DELETE 

  FROM 

    @Permutations

  WHERE

    Value NOT LIKE '%' + SUBSTRING(@Value,@i,1) +'%'

  --Deleting Permutations which have repeated incorrect character.  

  DELETE 

  FROM 

    @Permutations

  WHERE

    LEN(Value) - LEN(REPLACE(Value,SUBSTRING(@Value,@i,1),'')) != 

    LEN(@Value) - LEN(REPLACE(@Value,SUBSTRING(@Value,@i,1),''))

  SET @i += 1  

END

--Selecting the generated Permutations. 

SELECT Value FROM @Permutations

Hope, this script helps!

Please share your suggestions if you have any to improve this logic.

Steps to change Server URL/Machine name after deploying Dynamics CRM 2011

Recently, I came across a situation where I needed to rename my development machine having MS Dynamics CRM 2011 & SQL server 2008 installed on it. If you change the machine name casually then you will not be able to browse any of the existing organizations. Also, the CRM Asynchronous service won’t be started. Ideally, there are only 3 simple steps, which if followed correctly, can bring all Organizations again up & running with a new url. Below are the steps – Word of Caution: Please take backups of your registry and Database before making any change to them or take help of the concerned Administrator to help you. 1. Rename the Machine

• Rename the Machine and change it IP Address if required.
• Restart the machine.

2. Update the Registry entry

• Open the Registyr Editor using - Start > Run > Regedit.exe
• Then Move to KEY_LOCAL_MACHINE\Software\Microsoft\MSCRM node and
• Double Click on configdb key and change the DataSource from OldServerName to NewServerName
• Double Click on ServerUrl key and change the URL to point to the NewServerName

3. Update the details in MSCRM_CONFIG database

• Open SQL Server Management Studio and run the below script -

USE [MSCRM_CONFIG]

GO

UPDATE [Server] SET [Name] = 'NewServerName';

UPDATE ConfigSettings SET [HelpServerUrl] = 'http://NewServerName:5555/';

UPDATE Organization SET

[ConnectionString] = REPLACE([ConnectionString],'OldServerName ','NewServerName’),

[SqlServerName] = '

NewServerName ',

[SrsUrl] = '

http://NewServerName /reportserver';

• Restart the machine.

Disclaimer: Following of the above steps is purely the reader’s sole decision and in no case the author could be held responsible for loss of any kind arising after following.
Referred Link:
http://weblogs.asp.net/navaidakhtar/archive/2012/03/09/Microsoft-Dynamics-CRM-2011-_2F00_-4.0-Configuration-in-Case-of-Machine-rename-_2F00_-CRM-Database-server-change-_2F00_-Domain-Controller-Change.aspx

SQL Server – TSql to find Records matching certain criteria in all the tables of a DB.

Generally, we try to find out records matching a certain criteria from a single or few tables. However, there are times when we need to find out records matching a criteria from all the tables of a SQL Database and today I will explain you a simple way to retrieve those records.

Recently, I was asked by my colleague, who was working on a MS Dynamics CRM migration project, to let him know the records which were created after a particular date in the source. So that, he could analyze only those records and strategize the Migration process.

I quickly opened up the SSMS and came up with the below script -

USE <DBName> --Replace this with the actual DBName

Go

DECLARE @ColumnName AS VARCHAR(50) = 'CreatedOn' --The name of the column on which you need to put the criteria

DECLARE @Criteria AS VARCHAR(50) = 'CONVERT(DATE,' + @ColumnName + ') >= ''20130225''' -- The Actual criteria/WHERE Clause of the query

--The below will list the TSQL Statements which could be copied & executed in a separate query window.

SELECT 

  'IF EXISTS(SELECT 1 FROM ' + T.name + ' WHERE ' + @Criteria + ') ' +

  'SELECT ''' + T.name + ''' TableName, * FROM ' + T.name + ' WHERE ' + @Criteria 

FROM 

  sys.columns C

INNER JOIN sys.tables T

  ON T.object_id = C.object_id   

WHERE 

  C.name = @ColumnName

The above script will list down the SELECT statements which could be copied and executed in a separate query window connecting to the same Database. On execution, you will get the list of records from each table base on the specified criteria.

Hope, this helps!

CRM 2011–Managed Solution–Transport Gotchas

Today, I would like to share some gotchas of managed solution while transporting the customization changes on another organization:

1. You cannot activate/deactivate the views and transport the same status on production server via overwriting the managed solution.

2. You cannot setup default public view and transport the same status on production server via overwriting the managed solution.

3. Similarly, you cannot setup default dashboard and transport the same status on production server via overwriting the managed solution.

4. To expand or collapse tabs on forms, like you cannot expand Notes & Activities tab although you tick “Expand this tab by default” on entity form.
In a same manner, you cannot collapse the tabs like General, Notes & Activities etc. although you unchecked “Expand this tab by default”.

5. Start Auditing on any of your entities is not being transported on production server.

The above points are in my knowledge. Please feel free to append this list with your experiences as well.

Dynamics CRM 2011 - Odd behaviour of Product default lookup view on Order Product record

Yesterday, I faced a weird behaviour of order product entity on production server & in fact did not visualize the same during development phase. This is related to a default lookup view of existing product on Order Product entity record. In Dynamics CRM a default lookup view for this entity is “Products in Parent Price List”. Means you will only be allowed to select the products of Price List that was selected on Order record (Parent record). On yesterday I found that the default view was changed to “Product Lookup View” and it now allows you to select products of all Price Lists. Weird…. After goggling for an hour I came to know that this is because I wrote some JavaScript code on change event of this lookup field to do some calculation of pricing based on product selection. So when you write custom code on the OnChange event of such "special" lookups CRM loses its normal behaviour. Workaround

• Create a new temporary unmanaged solution on development organization.
• Add an Order Product entity to this solution.
• Export this solution as unmanaged.
• Unzip the customization files.
• · In customiztaion.xml, change the default view GUID back to “Products in Parent Price List” ({BCC509EE-1444-4A95-AED2-128EFD85FFD5}) by replacing the current GUID of “Product Lookup” ({{8BA625B2-6A2A-4735-BAB2-0C74AE8442A4}}).
• Import the solution back to the development organization & publish the changes.
• You can now validate the default lookup view for Product lookup.
• Now, export your original unmanaged solution as Managed to be imported on production server.

Referred links http://social.msdn.microsoft.com/Forums/en-US/crm/thread/5eac1b84-ca66-41ba-9da7-06bbc49ffdad#e6908e92-e5a5-4d57-bc54-2fcaf393b7e2  http://blogs.msdn.com/b/apurvghai/archive/2012/02/15/exisitng-product-lookup-lists-all-the-products.aspx

TFS – Workspace Mapping issues for Different Users

I have been using TFS 2010 from quite sometime now and have found it really cool! However, the management of workspaces can be a bit tricky at times.

In real scenario, many times we come across a situation when some user has to work on someone else machine and on the same project; e.g. old team member leaves the project & a new one replaces him and is allotted the same machine, a team member might leave the company, etc. 

So, the need arises to create a new workspace & map it to the same physical folder tree to avoid replication of the files at different location. (Please note : This step can be avoided if the existing workspace was a Public Workspace). While mapping the workspace to the same folder which is already mapped to the workspace of some other user, we get a message - “The working folder {PATH} is already in use by the workspace MachineName;Username on computer MachineName.” and this is by design - that we can not map the same working folder to multiple workspaces, in order to maintain the state consistency.
Now, what is the solution to this issue. The solutions are -

1. Remove the existing mapping.
2. Or Delete the existing Workspace.

Workspaces can be removed using some command line TFS commands. However, there are situations when we even can not remove the workspaces; reason being, the owning domain user of the workspace to be removed - “DOES NOT EXIST” and as a workaround to this, I have written a simple SQL script, which could be executed on the TFS Database of our Project to remove the mappings.
Please note that this is not a “Supported Way”. I am sharing this just because it has worked for me and might save some of your time too. Also, please take appropriate DB backup before executing the script and if possible execute it under any DBA’s guidance. So, here is the time saver script… :)

DECLARE @WorkspaceID AS INT

DECLARE @WorkspaceName AS NVARCHAR(64)

--Set the Name of the Workspace whose mappings are to be removed.

SET @WorkspaceName = '{NameOfWorkspace}'

--Get the WorkspaceID of the Workspace to be removed.

SELECT

  @WorkspaceID = W.WorkspaceId

FROM

  dbo.tbl_Workspace W

WHERE

  W.WorkspaceName = @WorkspaceName

--Delete the details of working folder for this workspace.

DELETE

FROM

  dbo.tbl_WorkingFolder

WHERE

  WorkspaceID = @WorkspaceID

--Delete the mapping details for this workspace.

DELETE

FROM

  dbo.tbl_WorkspaceMapping

WHERE

  WorkspaceID = @WorkspaceID

Hope, this helps you. Also, please revert if you have some other workarounds too.

SQL Server # Moving MASTER database in cluster environment

Few months back I have wrote post about moving MASTER and MSDB database to new location in stand alone machine.
In recent past we had a situation where customer asked us to move MASTER database to new location, below are the steps I have taken:

1.     Connect to the Server
2.     Open Configuration Manager -> SQL Server Service
3.     Right Click and say Properties
4.     Click on the Start-up Parameter
5.     Remove start-up parameter (the highlighted one)

  -dOLDLocation\master.mdf
  -eOLDLocation\ErrorLog
  -lOLDLocation\mastlog.ldf

      6.     Add new start-up parameters with new values (per your configuration)

  -dNewLocation\master.mdf
  -eNewLocation\ErrorLog
  -lNewLocation\mastlog.ldf

      7.    Check and confirm which node is active
      8.    PAUSE current PASSIVE Node  to avoid fail-over
      9.    Take SQL Server resources offline, i.e. SQL Server, SQL Agent, MSDTC, SQLCLUSTER Name (do not take SQL Cluster IP Offline)
    10.    Copy MASTER.MDF and MASTLOG.LDF to NEW Location ( S:\SQLDATA, yours could be different)
    11.    Log into Cluster Administrator and bring SQL Server Resources online
    12.    Resume current PASSIVE Node


That's all, you should be able to see your master database on new location now!!!


-- Regards,

Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Cross posting: http://www.pythian.com/news/35829/moving-master-database-to-new-location-in-sql-cluster/