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
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 this is as follows
select Phon_FullNumber,Phon_AreaCode,Phon_CountryCode,Phon_Number, Capt_Code, capt_order, Plink_Type
from vCompanyPhoneCaption
GROUP BY
Phon_FullNumber,Phon_AreaCode,Phon_CountryCode,Phon_Number, Capt_Code, capt_order, Plink_Type
HAVING
COUNT(*) > 1
