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 )
Example for the bcp above
BULK INSERT saminc.dbo.ARCSM
FROM 'C:\temp\importfiles\ARCSM.dat' WITH (
ROWTERMINATOR = '\n'
);
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 in (
select Phon_PhoneId
from Phone p
inner join PhoneLink pl
on pl.PLink_PhoneId=p.Phon_PhoneId
where
Phon_PhoneId not in (
select min(Phon_PhoneId)
from vCompanyPhoneCaption
group by Phon_Number,PLink_Type,plink_recordid,capt_family)
and phon_Deleted is null
and pl.pLink_EntityID=5)
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 in (
select Emai_EmailId
from Email e
inner join EmailLink el
on el.ELink_EmailId=e.Emai_EmailId
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
and el.ELink_EntityID=5)
update EmailLink
set eLink_Deleted=1
where eLink_EmailId in (select emai_emailid from Email where emai_Deleted=1)
Finally rebuild the 'CRMEmailPhoneData' table
Ref: https://community.sagecrm.com/user_community/f/84/p/11716/32118.aspx#32118
exec populate_CRMEmailPhoneData
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
Cross db examples
UPDATE
crmdemo.dbo.company
set
comp_name = RAN.comp_name
FROM
crmdemo..company SI
INNER JOIN
crm71.dbo.company RAN
ON
SI.comp_companyid = RAN.comp_companyid
UPDATE
crmdemo.dbo.person
set
pers_firstname = RAN.pers_firstname ,
pers_lastname = RAN.pers_lastname
FROM
crmdemo..person SI
INNER JOIN
crm71.dbo.person RAN
ON
SI.pers_personid = RAN.pers_personid
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^&
See Bulk import for details on importing this data
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
-code to check for duplicate rows
select epd_EmailAddress, COUNT(epd_EmailAddress) from CRMEmailPhoneData where epd_EmailAddress is not null and epd_EntityID = 13 group by epd_EmailAddress having COUNT(epd_EmailAddress)>3
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
Example: delete all duplicates except one
Here is SQL to set the acc_deleted flag for the duplicates (one copy is left)
It needs to be run more than once or until there are 0 records affected ((0 row(s) affected))
- Example here is deleting duplicates from CRM account table where there are duplicate acc_companyid's
update account
set acc_deleted=1
WHERE acc_accountid IN(
SELECT MAX(acc_accountid)
FROM account
where acc_deleted is null
GROUP BY acc_companyid
HAVING COUNT(acc_companyid) >1
)and acc_deleted is null
Code to check the database settings
dbcc useroptions go sp_configure 'default language' go sp_helplanguage
Note: sp_configure 'default language' will return a code and you use sp_helplanguage to check out the value
SELECT @@VERSION
Some queries to estimate the size of a database
SELECT
database_name = DB_NAME(database_id) , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT) WHERE database_id = DB_ID() -- for current db GROUP BY database_id
Ref: http://stackoverflow.com/questions/18014392/select-sql-server-database-size
with fs as (
select database_id, type, size * 8.0 / 1024 size from sys.master_files
) select
name, (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB, (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
Ref: http://stackoverflow.com/questions/5945360/sql-server-2008-how-to-query-all-databases-sizes
SQL to backup your CRM and CRMSelfService DB
BACKUP DATABASE [CRM] TO DISK = N'C:\DB Backups\CRM.bak' WITH FORMAT, INIT, NAME = N'CRM-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
BACKUP DATABASE [CRMSelfService] TO DISK = N'C:\DB Backups\CRMSelfService.bak' WITH FORMAT, INIT, NAME = N'CRMSelfService-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
- C:\DB Backups\ is a folder on the server
SQL to reset admin password and remove lockout
update Users set User_Password=, user_accountlockedout=null where User_Logon='admin'
Refresh views in a database. Useful for random SQL errors that have nothing in the log for them.
DECLARE @sqlcmd NVARCHAR(MAX) = SELECT @sqlcmd = @sqlcmd + 'EXEC sp_refreshview + name + ;' FROM sys.objects AS so WHERE so.type = 'V' SELECT @sqlcmd --EXEC(@sqlcmd)
ref: http://www.sqlservercentral.com/scripts/Views/147316/
Updating (fixing) Territories
The select statement below should return all communication records that don't match the parent company
select comp.Comp_SecTerr,comm.Comm_CommunicationID
FROM
communication comm, company comp, Comm_Link cmli
where
comm.Comm_CommunicationID = cmli.CmLi_Comm_CommunicationID and
cmli.CmLi_Comm_CompanyID=comp.Comp_CompanyId
and comm.Comm_SecTerr<>comp.Comp_SecTerr
and Comm_TargetListId is null
To get these communications to match run the following SQL
DECLARE @Comm_SecTerr as INT;
DECLARE @Comm_CommunicationID as INT;
DECLARE @commCursor as CURSOR;
SET @commCursor = CURSOR FOR
select comp.Comp_SecTerr,comm.Comm_CommunicationID
FROM
communication comm, company comp, Comm_Link cmli
where
comm.Comm_CommunicationID = cmli.CmLi_Comm_CommunicationID and
cmli.CmLi_Comm_CompanyID=comp.Comp_CompanyId
and comm.Comm_SecTerr<>comp.Comp_SecTerr
and Comm_TargetListId is null; --Comm_TargetListId-no updates on emarketing items
OPEN @commCursor;
FETCH NEXT FROM @commCursor INTO @Comm_SecTerr,@Comm_CommunicationID;
WHILE @@FETCH_STATUS = 0
BEGIN
update Communication
set Comm_SecTerr=@Comm_SecTerr
where
Comm_CommunicationId=@Comm_CommunicationID
--print 'update Communication set Comm_SecTerr='+cast(@Comm_SecTerr as VARCHAR (50)) +' where Comm_CommunicationId='+cast(@Comm_CommunicationID as VARCHAR (50))
FETCH NEXT FROM @commCursor INTO @Comm_SecTerr, @Comm_CommunicationID;
END
CLOSE @commCursor;
DEALLOCATE @commCursor;
- note that if there is no parent company there will be no update so you may need to change the query or provide a separate query to update any orphan records
get the TCPIP port
USE master GO xp_readerrorlog 0, 1, N'Server is listening on', N'any', NULL, NULL, N'asc' GO
