General SQL

• Always back up your DB before running any SQL jobs. You run any SQL found on this page at your own risk.

To get the reference id via SQL you run the code as follows:

 Declare @id nvarchar(128) 
 DECLARE @return_value int 
 execute @return_value = [eware_default_values] 'Cases', 'Case_CaseId', 'Case_ReferenceId', 00, @default_value = @id output 
 select id = @id

to run this code in ASP you would have to create the case record using "CreateRecord" and then update the case_referenceid

 var _sp='Declare @id nvarchar(128) '+
       'DECLARE @return_value int  '+
 'execute @return_value = [eware_default_values] \'Cases\', \'Case_CaseId\', \'Case_ReferenceId\', 00, @default_value = @id output  '+
 'select id = @id '+
 'update Cases '+
 'set Case_ReferenceId=@id '+
 'where Case_CaseId=1';
 var rec=CRM.ExecSQL(_sp);

---

If you need to re-create a table (say the triggers are corrupted or not running for example) you can do the following.

Copy a table into new table with/without data - SQL Server

Copy only the structure of an existing table into new table:

 SELECT * INTO tblNew FROM tblOld WHERE 1=2

The above query will copy the structure of an existing table(tblOld) into the new table(tblNew).

Copy only the structure with data of an existing table into new table:

 SELECT * INTO tblNew FROM tblOld

Ref: http://geekswithblogs.net/nagendraprasad/archive/2009/03/20/copy-a-table-into-new-table-with-and-without-data.aspx

E.G of recreating the phone tables

 SELECT * INTO Phone_new1 FROM phone
 
 SELECT * INTO PhoneLink_new1 FROM PhoneLink
   
 EXEC sp_rename 'Phone', 'Phone_old1'
 go
 
 EXEC sp_rename 'Phone_new1', 'Phone'
 go
 
 EXEC sp_rename 'PhoneLink', 'PhoneLink_old1'
 go
 
 EXEC sp_rename 'PhoneLink_new1', 'PhoneLink'
 go

---

SQL code to create a communication notification

NOTE: in CRM 7.2 this code will not be valid as the database structure has changed to have the id columns as identity fields

 Declare @communicationid int 
 EXEC @communicationid=eware_get_identity_id 'Communication' 
 INSERT INTO Communication(
   Comm_CommunicationId,  Comm_Type,  Comm_Action, Comm_Status,                              
   Comm_Priority, Comm_DateTime,  Comm_Note,  Comm_CreatedBy, Comm_CreatedDate,
   Comm_UpdatedBy, Comm_UpdatedDate,  Comm_TimeStamp,  Comm_NotifyTime,Comm_Description,
   Comm_Subject,Comm_SecTerr,comm_taskreminder)
 VALUES (
   @communicationid, 'Task','PhoneOut','Pending','Normal',
   getdate(),'test',-1,getdate(), -1,getdate(), getdate(),
   getdate(),'test ','test',-2147483640,'Y'
 )
 Declare @CmLi_CommLinkId int 
 EXEC @CmLi_CommLinkId=eware_get_identity_id 'comm_link' 
 INSERT INTO comm_link(
   CmLi_CommLinkId,CmLi_Comm_UserId, CmLi_Comm_CommunicationId,CmLi_CreatedBy, CmLi_CreatedDate,
   CmLi_UpdatedBy, CmLi_UpdatedDate,CmLi_TimeStamp,CmLi_Comm_PersonId, CmLi_Comm_CompanyId,CmLi_Comm_NotifyTime
 )VALUES (
   @CmLi_CommLinkId,1,@communicationid,-1,getdate(),-1,getdate(),getdate(),59,45,GETDATE()
 )
 Declare @Escl_EscalationId int 
 EXEC @Escl_EscalationId=eware_get_identity_id 'Escalations' 
 INSERT INTO Escalations(
   Escl_TableId,Escl_RecordId,Escl_DateTime,Escl_UserID,Escl_CreatedBy,Escl_CreatedDate,Escl_UpdatedBy,
   Escl_TimeStamp,Escl_UpdatedDate,Escl_EscalationId
 )VALUES (
   29,@CmLi_CommLinkId,getdate(),1,-1,getdate(),-1,getdate(),getdate(),@Escl_EscalationId
 )

---

SQL Trigger on column update. Useful if you need to watch a database column on a 3rd party database and create a notification (as above) in CRM.

 create trigger MyColumnWatcher on dbname.dbo.tablename
 after update
 as
  set NoCount ON
  if update(table_column_name)
  begin
    ---your sql code goes here
  end

---

Formating dates in SQL

See http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)

                                       – Oct  2 2008 11:01AM          

SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008

SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02

SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) – dd mon yyyy

SELECT convert(varchar, getdate(), 107) – mon dd, yyyy

SELECT convert(varchar, getdate(), 108) – hh:mm:ss

SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)

                                       – Oct  2 2008 11:02:44:013AM   

SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) – yyyymmdd

SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm

                                       – 02 Oct 2008 11:02:07:577     

SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm

                                       – 2008-10-02T10:52:47.513

– SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd

SELECT convert(varchar(7), getdate(), 126) – yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8) – mon yyyy

---

Sample code for Bulk import of data into SQL from csv file

 drop table ImportTable
 go
 CREATE TABLE ImportTable
  (reference varchar(20),
    FullName VARCHAR(100),
    sortcode VARCHAR(20),
    accno varchar(20))
 GO

 BULK INSERT ImportTable
   FROM 'C:\path\Importdata.csv'
   WITH
   (
   FIRSTROW = 2,
   FIELDTERMINATOR = '||',  --CSV field delimiter
   ROWTERMINATOR = '\n',   --Use to shift the control to next row
   ERRORFILE = 'C:\Marketing\ewt documents\CRMT\DDImportErrorRows.csv',
   TABLOCK
   )

---

Detecting duplicate rows in SQL

We have seen instances where CRM may have the same phone/email records over and over against the same company. Usually this is due to some import routine going wrong.

Some sample SQL to detect these duplicates is as follows

 select Phon_FullNumber,Phon_AreaCode,Phon_CountryCode,Phon_Number,  Plink_Type
 from vCompanyPhoneCaption 
 GROUP BY
     Phon_FullNumber,Phon_AreaCode,Phon_CountryCode,Phon_Number, Plink_Type
 HAVING
     COUNT(*) > 1

Next we need to fix the data so we soft delete the phone id

 update Phone
 set Phon_Deleted =1
 where Phon_PhoneId not in (
 select min(Phon_PhoneId) 
 from vCompanyPhoneCaption 
 group by Phon_FullNumber,Phon_AreaCode,Phon_CountryCode,Phon_Number, Plink_Type)

then we delete the phone link records that are linked to these deleted phone records

 update PhoneLink
 set PLink_Deleted=1
 where PLink_PhoneId in (select phon_phoneid from Phone where Phon_Deleted=1)