Optionset showing values which haven't been added - feature from CRM but bug to me (Mystery in the start... but no more)

We have installed managed solutions into system test environment and then run the data import process by the business provided sheets which were converted to csv.

After a while business requested that one of the option is not triggering the functionality when looking at the option, that option wasn't even provided by us i.e. from dev environment. but it does exists in the system test and other environments.

After spending quite a bit of time, I figured it out the option was added due to business provided that option in the data import sheet, and actually that option wasn't specified with in the FSD and we didn't implement any logic against that option.

When data import is done for the optionset value CRM maps the value if the optionset value exists if it doesn't exists then CRM CREATES THE OPTIONSET VALUE AUTOMATICALLY, where I was expecting the records to fail.

Now I know what the reason is we explained the business what has happened and we need to work on to update customization i.e. javascript/plugin to consider the new value as well.

Triggering on demand workflow from ribbon button javascript

http://andreaswijayablog.blogspot.co.uk/2011/07/crm-2011-custom-button-on-custom-entity.html

To compare date part only in sql

To compare date part only in sql

WHERE CONVERT(VARCHAR, date1, 101) = CONVERT(VARCHAR, date2, 101)

http://stackoverflow.com/questions/1427469/compare-dates-in-t-sql-ignoring-the-time-part

SQL to reference different servers

I hit an requirement where I had to update the one of the field in the table for about couple of hundred records. I had the excel file which listed the records that needs updating.

We are not allowed to create temporary database/tables in the LI VE server, then I had two options either to read the excel file in SQL or create the temp database in dev envrnoment for the records that needs to be updated in live. I opted to go with the second option of creating the temp database with the records to update in live.

I used the import export option available in the SQL management studio to import all the records from excel to local database table.  and then used something similar to following logic

In environment where I have acess to LIVE I created a link to dev server to refer the table

To create a link server.

Exec sp_addlinkserver 'TempDevBox', '<space>', 'SQLNCLI', '<servername>', '<database name>'

--just to check if following select works fine
select * from TempDevBox.<database name>.dbo.Tablename


-- To update records in live table where record detail matched the dev table records.

Update livetable
set field = 1
where field = 0 and
recordnumber in
(
select recordnumber from TempDevBox.<database name>.dbo.Tablename
)
 

SQL exists (but not exists)

We did some business data import and after the import there were some difference happening in what business provided the data and what system calculated using the config data.

To find the differences and placing the difference in front of client I used something similar

Select * from table1 t1
exists
( select * from table2 t2
where t2.column1 = t1.column3 and ( t2.column2 like '%' + cast( t1.column4 as varchar(10)) + '%' or t2.column2 like '%' + cast(t1.column5) as varchar(2)) + '%' )

 

CRM tools

Diagnostics

https://OrgURL/tools/diagnostics/diag.aspx.

If the result is greater than 150ms then performance experience will suffer.
 

import records in excel in sql data base

To check if a the corresponding column contains 'some text' if so then return true otherwise false
=ISNUMBER(SEARCH("Some Text",A3))


To import records in excel file into SQL server database


http://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/