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/

Basics for Silverlight controls and MVVM

http://www.youtube.com/watch?v=-gPzJw_9QAI

MVVM
http://www.youtube.com/watch?v=f0C-HhqRTOc

Permissions required for data import

Following are the required entities permission to allow a user to import data into crm through data import wizard.

 - Data Import (all)
 - Data Map (all)
 - Import Source File (all)
 - Web Wizard (all)
 - Web Wizard Access Privilege (all)
 - Wizard Page (all)

 - Entity privilege which user needs to import data for.

I am not sure if we need all privilege here as I have used some of the entities mentioned above with less privilege and it worked fine.

http://social.microsoft.com/Forums/en-US/crm/thread/deca129e-189a-4c11-9e74-c6e93469e664