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
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,plink_recordid,capt_family
from vCompanyPhoneCaption
GROUP BY
Phon_FullNumber,Phon_AreaCode,Phon_CountryCode,Phon_Number, Plink_Type,plink_recordid,capt_family
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,plink_recordid,capt_family) and Phon_Deleted is null
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)
The equivailent for email is
select Emai_EmailAddress,Elink_Type,elink_recordid,capt_family
from vCompanyEmailCaption
GROUP BY
Emai_EmailAddress,Elink_Type,elink_recordid,capt_family
HAVING
COUNT(*) > 1
update Email
set emai_Deleted =1
where Emai_EmailId not in (
select min(Emai_EmailId)
from vCompanyEmailCaption
group by Emai_EmailAddress,Elink_Type,elink_recordid,capt_family)
and emai_Deleted is null
update EmailLink
set eLink_Deleted=1
where eLink_EmailId in (select emai_emailid from Email where emai_Deleted=1)
If you need to reset a tables identity columns here is a sample of sql to do this (applies to any install pre 7.2)
UPDATE dbo.Rep_Ranges
SET Range_RangeStart = 200000,
Range_RangeEnd = 250000,
Range_NextRangeStart = 300001,
Range_NextRangeEnd = 350001,
Range_Control_NextRange = 400000
WHERE Range_TableId =
(SELECT Bord_TableId FROM dbo.Custom_Tables
WHERE bord_name in ( 'tablename'))
UPDATE SQL_Identity SET Id_NextId = 200001
WHERE Id_TableId IN (SELECT Bord_TableId FROM dbo.Custom_Tables WHERE bord_name in ( 'tablename'))
To generate a new value run the following (*10256 is the table id of the sample table)
declare @x int
exec @x= crm_next_id 10256
select @x
SQL to update the ID value of a table
Fix for this is
UPDATE dbo.Rep_Ranges
SET Range_RangeStart = 200000,
Range_RangeEnd = 250000,
Range_NextRangeStart = 300001,
Range_NextRangeEnd = 350001,
Range_Control_NextRange = 400000
WHERE Range_TableId =
(SELECT Bord_TableId FROM dbo.Custom_Tables
WHERE bord_name in ( 'yourtablename'))
UPDATE SQL_Identity SET Id_NextId = 200001
WHERE Id_TableId IN (SELECT Bord_TableId FROM dbo.Custom_Tables WHERE bord_name in ( 'yourtablename'))
declare @x int
exec @x= crm_next_id 10256
select @x
update one table based on another
UPDATE Sales_Import SET Sales_Import.AccountNumber = RAN.AccountNumber FROM Sales_Import SI INNER JOIN RetrieveAccountNumber RAN ON SI.LeadID = RAN.LeadID
bcp export
sample code to run from a command line to export data via bcp (bulk copy utility)
bcp "select * from crm.dbo.company" queryout "C:\logs\company.dat" -U sa -P ramdom -c
- if your password contains any odd characters like an ampersand you need to escape them with the ^ character
EG. random^&
Backup/copy a table
To backup or copy a table (structure and contents) you can run something like the following:
SELECT * INTO company_backup FROM company
In this example a table will be created called company_backup with the same structure as company.
This is ideal to test any major data updates on a live system (*always fully backup your live data though)
Remove duplicate row from a table.
The idea here is to create a copy of the table structure you wish to clean up and from there populate with distinct values (removing duplicates)
EG drop table [CRMEmailPhoneData_new] go CREATE TABLE [dbo].[CRMEmailPhoneData_new](
[epd_DataID] [int] not NULL, [epd_EntityID] [int] NOT NULL, [epd_RecordID] [int] NOT NULL, [epd_EmailId] [int] NULL, [epd_EmailAddress] [nvarchar](255) NULL, [epd_PhoneId] [int] NULL, [epd_PhoneCountryCode] [nchar](5) NULL, [epd_PhoneAreaCode] [nvarchar](20) NULL, [epd_PhoneNumber] [nvarchar](20) NULL, [epd_PhoneFullNumber] [nvarchar](47) NULL, [epd_FaxId] [int] NULL, [epd_FaxCountryCode] [nchar](5) NULL, [epd_FaxAreaCode] [nvarchar](20) NULL, [epd_FaxNumber] [nvarchar](20) NULL, [epd_FaxFullNumber] [nvarchar](47) NULL, [epd_CreatedBy] [int] NULL, [epd_CreatedDate] [datetime] NULL, [epd_UpdatedBy] [int] NULL, [epd_UpdatedDate] [datetime] NULL, [epd_TimeStamp] [datetime] NULL, [epd_Deleted] [int] NULL
) ON [PRIMARY]
GO
INSERT [CRMEmailPhoneData_new]
select distinct ROW_NUMBER() OVER(ORDER BY epd_EntityID DESC) , epd_EntityID, epd_RecordID, epd_EmailId, epd_EmailAddress,
epd_PhoneId, epd_PhoneCountryCode, epd_PhoneAreaCode, epd_PhoneNumber,
epd_PhoneFullNumber, epd_FaxId, epd_FaxCountryCode,epd_FaxAreaCode,
epd_FaxNumber,epd_FaxFullNumber,epd_CreatedBy,epd_CreatedDate,epd_UpdatedBy
,epd_UpdatedDate,epd_TimeStamp,epd_Deleted
from [CRMEmailPhoneData]
select *
from [CRMEmailPhoneData_new]
EXEC sp_rename 'CRMEmailPhoneData', 'CRMEmailPhoneData_old'
go
EXEC sp_rename 'CRMEmailPhoneData_new', 'CRMEmailPhoneData'
go
The data types text and varchar are incompatible in the equal to operator.
SELECT * FROM [Village] WHERE CONVERT(VARCHAR, CastleType) = 'foo'
Ref: http://stackoverflow.com/questions/4350060/where-clause-on-sql-server-text-data-type
SQL to detect duplicate Case reference Id values
select Case_ReferenceId as ref, Case_CreatedDate as cd, Case_CreatedBy as cb, *
from Cases
where Case_ReferenceId in (
select case_referenceid
from cases
GROUP BY Case_ReferenceId
HAVING
COUNT(*) > 1)
order by Case_CreatedDate desc
