General SQL

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