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)