Crmtogetherkb:

File:Errorpage2.png

2016-05-18T09:39:53Z

Crmtogetherkb:

Config

2016-05-18T09:39:40Z

Crmtogetherkb:

Turn on debugging in IE and IIS (for when you see 500 errors - these are useless)

IE settings (uncheck “Show friendly HTTP error messages)

[[File:findclick.png]]

Open IIS and select the CRM instance

[[File:asp.png]]

Click ASP

[[File:senderror.png]]

Change the “Send Errors to Browser” to be True
Select the CRM name again

[[File:errorpage1.png]]

Double Click Error Pages

[[File:errorpage2.png]]

Click “Edit Feature Settings…”

[[File:errorsettings.png]]

Select the “Detailed errors”
Click OK

----

You see this error when you try change pages in the admin area.

"Error, you need to be an administrator on the CRM server to use this function."

In IIS, expand the server name in the list where web deployment or CRM is running, then expand Web Sites, then Default Web Site.

Right-click the name of the CRM instance and click Properties.

Click the Directory Security tab, then Edit in the Anonymous access and authentication control.

In the Authentication Methods window, check the "Integrated Windows authentication" option.

Click OK to exit the windows.

An IIS reset will be warranted after making this change.

*If the option is missing see how to enable it at this link

http://weblogs.asp.net/zroiy/archive/2008/08/17/missing-windows-authentication-provider-for-iis-7-on-windows-server-2008.aspx

----

HTTP Error 404.11 - Not Found The request filtering module is configured to deny a request that contains a double escape sequence.

Encountered on some environments

Ref: http://www.britishdeveloper.co.uk/2013/02/the-request-filtering-module-is.html

The quick fix

This can be easily achieved with a simple web.config change (located in "\WWWRoot\web.config");

<system.webServer>
<security>
<requestFiltering allowDoubleEscaping="true" />
</security>
</system.webServer>

----

File:Errorpage1.png

2016-05-18T09:38:59Z

Crmtogetherkb:

File:Senderror.png

2016-05-18T09:38:40Z

Crmtogetherkb:

File:Asp.png

2016-05-18T09:38:23Z

Crmtogetherkb:

2015-07-15T08:37:03Z

Crmtogetherkb:

Screen Customisation

2015-06-25T13:33:12Z

Crmtogetherkb:

List Customisation

2015-06-25T09:55:50Z

Crmtogetherkb:

Client-side function to clear out empty rows on a table (tested in 7.1 only)

-Makes an assumption about the first Td being empty

function clearEmpptyTableRows()
{
var trcol=document.getElementsByTagName("TR");
for (var i=1;i<trcol.length;i++)
{
//get the first td
var _td=trcol[i].childNodes[0];
if (_td!=null){
if ((_td.innerHTML=="")||(_td.innerHTML==null)||(_td.innerHTML==" ")){
if ((_td.className=='ROW2')||(_td.className=='ROW1')){
trcol[i].style.visibility='hidden';
trcol[i].style.display='none';
}
}else{
//console.log(i+"-"+_td.innerText);
//console.log(i+"-"+_td.innerHTML);
}
}
}
}

2015-06-25T08:41:54Z

Crmtogetherkb:

Javascript

2015-05-07T14:20:25Z

Crmtogetherkb:

Useful ASP javascript code to see all "Request.ServerVariables"

Could also be used on "Request.Form" etc

for(f = new Enumerator(Request.ServerVariables()); !f.atEnd(); f.moveNext())
{
var x = f.item();
Response.Write("" + x + " = " + Request.ServerVariables(x));
}

For Request form

for(f = new Enumerator(Request.Form()); !f.atEnd(); f.moveNext())
{
var x = f.item();
Response.Write("<br>" + x + " = " + Request.Form(x));
}

Client side DOM - enumerate all checkboxes and set them as checked

var _cb=document.getElementsByTagName("input");
for(var i=0;i<_cb.length;i++)
{
var box=_cb[i];
if (box.type=="checkbox")
box.checked=true;
}

----

Useful update from Jeff Richards regarding compressing javascript

http://jscompress.com/

http://javascriptcompressor.com/

Ref: https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2013/12/31/sage-crm-7-2-adding-protection-to-your-code-in-script-libraries.aspx

----

Useful sites

http://devdocs.io/javascript/

----

FileSystemObject - read a file on the server

function readFile(filename){
var fso = new ActiveXObject("Scripting.FileSystemObject");
var ForReading = 1;
var f1 = fso.OpenTextFile(filename, ForReading);
var text = f1.ReadAll();
f1.close();
return text;
}

--you might need the path the ASP page is on

var pageroot=Request.ServerVariables("APPL_PHYSICAL_PATH");

ASP

2015-04-22T09:06:15Z

Crmtogetherkb:

VIES

2015-04-22T09:05:28Z

Crmtogetherkb: Blanked the page

External Links

2015-04-16T12:55:19Z

Crmtogetherkb:

Developer Help

[http://sagecity.na.sage.com/support_communities/sage_crm/default.aspx 'NA Sage city forum']

[https://community.sagecrm.com/developerhelp/ 'Sage CRM Developer Help']

[https://community.sagecrm.com/adminhelp/ 'Sage CRM Admin Help']

[http://help.sagecrm.com/js-api/classes/caption.html 'Client Side API']

[https://community.sagecrm.com/ 'Sage CRM Community']

[http://community.sagesoftware.com/ 'NA Sage Community Forums']

[http://community.sagesoftware.com/t5/Sage-Pro-ERP-Forum/bd-p/SageProERPForum 'Sage Pro ERP Forum']

Useful tools

[http://regexpal.com/ 'Test Regex expressions']

Filter screen

2015-04-03T08:19:23Z

Crmtogetherkb:

In filter screens when you set the default value for a field you must do an IIS reset to see the result work (reloading metadata does not work - as of CRM71).

DefaultValue="Complete"

Building Filter screens

Ref: https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2009/07/29/building-list-screens-with-filterboxes-take-2.aspx

Sample code from referenced link above - shows how to build a screen with a filter (essentially its a search screen)

var FilterBoxBlock = eWare.GetBlock("TestFilter");
var strFilterButton = eWare.Button("filter", "filter.gif", "javascript:document.EntryForm.submit();");
FilterBoxBlock.NewLine = false;
FilterBoxBlock.ButtonLocation = Bottom;
FilterBoxBlock.ButtonAlignment = Left;
var ListBlock = eWare.GetBlock("TestList");
var Container = eWare.GetBlock("Container");
Container.DisplayButton(Button_Default) = false;
Container.AddBlock (ListBlock);
Container.AddBlock (FilterBoxBlock); // here we are adding the filter block after the list block
ListBlock.ArgObj = FilterBoxBlock;
if ((Request.Form('HIDDENSCROLLMODE') != '2') && (Request.Form('HIDDENSCROLLMODE') != '3'))
{
Container.Execute(); // This is the first execute which we drop.
}
FilterBoxBlock.AddButton(strFilterButton);
eWare.AddContent(Container.Execute());
Response.Write(eWare.GetPage());

Filter screen

2015-04-03T08:18:33Z

Crmtogetherkb:

In filter screens when you set the default value for a field you must do an IIS reset to see the result work (reloading metadata does not work - as of CRM71).

DefaultValue="Complete"

Building Filter screens

Ref: https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2009/07/29/building-list-screens-with-filterboxes-take-2.aspx

var FilterBoxBlock = eWare.GetBlock("TestFilter");
var strFilterButton = eWare.Button("filter", "filter.gif", "javascript:document.EntryForm.submit();");
FilterBoxBlock.NewLine = false;
FilterBoxBlock.ButtonLocation = Bottom;
FilterBoxBlock.ButtonAlignment = Left;
var ListBlock = eWare.GetBlock("TestList");
var Container = eWare.GetBlock("Container");
Container.DisplayButton(Button_Default) = false;
Container.AddBlock (ListBlock);
Container.AddBlock (FilterBoxBlock); // here we are adding the filter block after the list block
ListBlock.ArgObj = FilterBoxBlock;
if ((Request.Form('HIDDENSCROLLMODE') != '2') && (Request.Form('HIDDENSCROLLMODE') != '3'))
{
Container.Execute(); // This is the first execute which we drop.
}
FilterBoxBlock.AddButton(strFilterButton);
eWare.AddContent(Container.Execute());
Response.Write(eWare.GetPage());

Javascript

2015-03-09T11:16:30Z

Crmtogetherkb:

Javascript

2015-03-09T11:16:00Z

Crmtogetherkb:

General SQL

2015-03-01T11:23:30Z

Crmtogetherkb:

*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

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

----

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 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)

----

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^&

----

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