Shrink database log file in 2008

I have posted a command that should be used to truncate database logs previously But that is not valid for SQL 2008 databases as truncate only command has been deprecated. I have added the command tat should be used to truncate transaction log in SQL 2008 below.

Use Test
Alter Database Test Set Recovery Simple
DBCC SHRNIKFILE (Test_log)
Alter Database Test Set Recovery Full

I was helped by the following post.
http://www.uhleeka.com/blog/2009/08/sql-2008-shrink-log-file-size-with-no_lo/

Shrink database log file

Even though from SQL manager we have the option to shrink database log files, sometimes it does not work as the log files size is big. Then you need to use TSQL statements to do this. I have given below such a statement that can be used to do so.


USE Test1
GO
DBCC SHRINKFILE(Test1_log, 10)
BACKUP LOG Test1 WITH TRUNCATE_ONLY
DBCC SHRINKFILE(Test1_log, 10)
GO

if you do not know the log file name use the following command to find it.

select * from sys.database_files

I was helped by the following link.
http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/

Javascript issues with IE 7 and IE8

For the past 2 years we have been using CRM 3 web URL with IE 6 and & and have done the javascripts according to these browsers. But not we get javascript errors. This is due to the fact that the customer have upgraded IE 7 to IE 8. Accessing labels are bit different in IE8. I will post some issues that we have faced in a nother post.

Me and My Wife

My wife has been telling me that i am putting a l lot of details about me in to this blog, but not mentioning anything about her here. My Wife's name is Harshi. What i should mention here is she is a very understanding wife. Although we were planning to go on a holiday for our first anniversary becuase of some deadlines i had to cancel my leave. She understood this very well, but was bit dissapointed. I will be going on a holiday with her next month to one of the beautiful hotels in Sri Lanka.

Thank you very much dear for being so understanding.

By the way this might be the first blog that i mention about my family.

Mass Edit in CRM 4

I was fixing a bug in our CRM 4 defect list and i found that mass edit was not working properly. I checked with my logins which was the domain admin and it was working fine for me. Then i asked for the login which was giving trouble and i found that the issue reported was true. I enabled the CRM trace and found out the issue. Some permission was missing from this user role. When i checked it.(as per the http://charithr.blogspot.com/2009/08/issues-with-que-privileges-in-crm-4.html ).

I found out that this user role was missing the assign right for this entity. although the operation was happening the CRM is giving that an error which says that something has gone wrong.

Please check the assign permission when doing mass editing if it's not wokring.

Remove Existing Buttons from CRM Pages - Updates

While i have done the testing in my browser after i have done the previous post i was told by some members of the QA team that this functiolaity was not working. I checked from my Broswer and it was woring fine in my machine. Then i inquired which browser they were using as mine was IE8. They were using IE7. I checked the DIV tag returned from each section and found out that in IE 8 ID attribute is followed by the class tag, but in IE 7 it's followed by Title tag.

I changed the javascript after that to check the browser version and it worked fine for both IE 8 and 7. |I will be publishing the changed javascript shortely.

Remove Existing Buttons from CRM Pages

This is something that when we upgraded one of our systems from 3 to 4 identified. This was a something we wanted to hide and i wanted to get some ideas from the web regarding this.
Fortunetly for me there was very good article by Dave Hawes. http://blog.davehawes.com/post/2008/04/23/MSCRM-4-Remove-Add-Existing-xxxxx-button.aspx

I used this article to get an idea. After reading this article i was able to get a clear idea of how to do this, but i wanted a generic solution as i wanted to apply this to all the CRM forms. i have pasted below the javascript i used.



var navGroup= document.getElementById('crmNavBar');
if (navGroup != null)
{
var x= navGroup.getElementsByTagName('li');
if (x != null)
{
//First 3 links are for the same entity, activity and activty history, Hence i removed them.
for( var i = 2; i < x.length ; i++ )
{
//getting the dive area. Refer below
var Div = x[i].innerHTML;
//get the index of id
var T = Div.indexOf('id');
//get the index of class
var T2 = Div.indexOf('class');
//get the value of title
var Title = (Div.substring(T+3,T2));

if (Title.indexOf('nav_') !=-1)
{
Title = Title.substr(4,Title.length-5);
}
else if (Title.indexOf('nav') !=-1)
{
Title = Title.substr(3,Title.length-4);
}

//Sub Accounts are different when treated by this javascript
if (Title.toLowerCase() != 'subact')
{
HideAssociatedViewButtons2(Title);
}
else
{

HideAssociatedViewButtons('SubAccts', ['Add existing Member to this record']);

}

}
}
}


function HideAssociatedViewButtons2(loadAreaId)
{

var isNativeEntity = false;
var navElement = document.getElementById('nav_' + loadAreaId);

if (navElement == null) {
navElement = document.getElementById('nav' + loadAreaId);
isNativeEntity = true;
}



if (navElement != null) {
navElement.onclick = function LoadAreaOverride()
{
if (isNativeEntity)
{
loadArea('area' + loadAreaId);
HideViewButtons2(document.getElementById('area' + loadAreaId + 'Frame'));
}
else
{
loadArea(loadAreaId);
HideViewButtons2(document.getElementById(loadAreaId + 'Frame'));
}
}
}
}



function HideViewButtons2(Iframe)
{


if (Iframe != null ) {
Iframe.onreadystatechange = function HideTitledButtons() {
if (Iframe.readyState == 'complete') {
var iFrame = frames[window.event.srcElement.id];
var liElements = iFrame.document.getElementsByTagName('li');
for (var i = 0; i < liElements.length; i++) {
if (liElements[i].getAttribute('title').indexOf('Add existing') !=-1){
liElements[i].style.display = 'none';
break;
}
}
}
}
}
}


function HideAssociatedViewButtons(loadAreaId, buttonTitles)
{

var navElement = document.getElementById('navSubAct');
navElement.onclick = function LoadAreaOverride()
{
loadArea('area' + loadAreaId);
HideViewButtons(document.getElementById('area' + loadAreaId + 'Frame'), ['Add existing Member to this record']);
}

}



function HideViewButtons(Iframe, buttonTitles)
{
if (Iframe != null ) {
Iframe.onreadystatechange = function HideTitledButtons() {
if (Iframe.readyState == 'complete') {
var iFrame = frames[window.event.srcElement.id];
var liElements = iFrame.document.getElementsByTagName('li');
for (var j = 0; j < buttonTitles.length; j++) {
for (var i = 0; i < liElements.length; i++) {
if (liElements[i].getAttribute('title').indexOf('Add existing') !=-1)
{
liElements[i].style.display = 'none';
liElements[i].style.visibility = 'hidden';
break;
}
}
}
}
}
}
}

DIV is similar to the below statement.

Associate Members


I would like to thank Dave and all the contributors for the their comments in the above mentioned post as without that i wouldn't be able to come up with this. Thanks mates.

Cheers.

Permisson Issue

Earlier today i post a link for checking permissions.

Below I'm giving the link which should be referred to identify which permission is lacking for the user.

http://support.microsoft.com/kb/953962

ISV Config button enabling

In CRM3 when we wanted to enable ISV buttons all we had to do was to go to the CRM web Config and change the ISV elements value to the required value (Web). This would have displayed the ISV buttons.

But in CRM4 this is not the case. This has been taken from the web config and have embeded to the web site. I have given below a link which helped me to find this.

http://blogs.msdn.com/jannemattila/archive/2008/01/10/crm-4-0-and-isv-config-modifications.aspx

Thanks for the helpful link.

Enable Trace in CRM 4

If you want to enable trace in CRM 4 you can use the following article which describes how it does. Please make sure that you disable the trace as soon as possible as CRM is creating a lot of text files with trace per minute.

http://support.microsoft.com/kb/907490

Issues with Que privileges in CRM 4

One of the issues we faced after upgrading from CRM3 to CRM4 is that some users were unable to save the activities. They were able to save the activity, but an error is popping up which says that the user doesn't have permission to view this activity. The users were able to save and close/ save and new without any issue,

After putting the trace to work we found out that the user role has not been given read privilege to Queue. After giving the permission to this user roles we were able to proceed with out an issue.

Plugin Execution - Post Plugins

With CRM4 most of the time we face that the pre plugins are running smoothly. But the post plugins are not running. We check our code to see whether there are any issues with what we have coded. Seems everything is correct and the plug in should run smoothly.

Then only we check whether the asynce service is running and then only we find out that is has been stopped. This is not the same case in CRM3 as both pre and post call outs are running from the workflow service. So if your pre plugin is running but the post async plugins are not running the first thing you should check is the microsoft async service.

Rebuilding Of Indexes

We need to re build the indexes of our tables after running a lot of transactions. This will improve the performance of the data retrievals. the following T SQL command will help you do that.


DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name IN ('master') //Name of the database
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''

-- create table cursor
EXEC (@cmd)
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN

-- if it is a SQL 2000 server use the below commented command
--DBCC DBREINDEX(@Table,' ',@fillfactor)

-- if it is a SQL 2005 server us this command
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)

FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE TableCursor
DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor


I was able to find this query from the following web site. Thanks guys for helping us.

http://www.mssqltips.com/tip.asp?tip=1367

Lising of all the tables and their row counts

Recently i needed to delete tables with a lot of records in CRM. i needed to find out the table name and the row count. i was able to do this by the following T SQL Command.


SET NOCOUNT ON
DECLARE @TableName sysname
, @Rows int
, @SQL nvarchar(4000)

CREATE TABLE #tablelist
(
TableName varchar(128),
Records int
)

DECLARE tables_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sysobjects
WHERE type = 'U' AND name NOT LIKE 'dt%'
ORDER BY name

OPEN tables_cursor
FETCH NEXT FROM tables_cursor into @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = 'SELECT @Rows = COUNT(*) FROM ['+@TableName+']'
SET @Rows = 0
EXEC sp_executesql @SQL, N'@Rows int out', @Rows out

set @SQL = 'INSERT INTO #tablelist (TableName,Records) ' +
'VALUES ( '+'''' + CONVERT(varchar,@TableName) + ''''+', ' + CONVERT(varchar,@Rows) + ' )'
-- PRINT @SQL
EXEC(@SQL)

FETCH NEXT FROM tables_cursor into @TableName
END

CLOSE tables_cursor
DEALLOCATE tables_cursor

SET NOCOUNT OFF

SELECT * FROM #tablelist ORDER BY TableName

DROP TABLE #tablelist
GO


i was mostly helped by the following article which contained the above T SQL command. Thanks guys.

http://www.sqlservercentral.com/scripts/Miscellaneous/30765/

CRM 4 Entity Import Error

There was an issue with importing customizations from one CRM4 environment to another CRM4 environment with one of our projects. Both the environments were upgraded ones from CRM3 and both contains the same customizations. How ever we were not able to import customizations.

So what we did was to enable the CRM trace and then read the trace. We used the stunware CRm diagnostic tools for this(Thanks a lot for making these tools which makes the life easier for any developer). We found out NullReferenceException form the trace. So after googling we were able to find more porsts simmiler to what we were facing. Finally we found one article which helped us.

http://thecrmgrid.wordpress.com/2009/03/10/corrupted-entity-nullreferenceexception-when-publishing-or-exporting/

Also some time you might get timeout errors when you import customizations. Then you need to change some registry values. Please find a very useful link for this issue below.

http://support.microsoft.com/kb/918609

CRM 4 Bulk Edit form

When we upgraded from CRM 3 to CRM 4 one of the issues we faced was this form. Javascripts that we have written for this particular form didn't work at all.

After doing some research through google we found out that with CRM 4 javascripts are not supported in the bulk edit form. Also if a field has javascript enabled that field would be disabled from in the bulk edit form.

After searching again we found a good article written in stunnware which we were able to use. I have pasted the link below and it contains some unsupported customization. Thanks to stunnware web site we were able to get it done.

http://www.stunnware.com/crm2/topic.aspx?id=BulkEdit

Custom Lookup Filters for CRM 4

Hi Guys,

We were facing some issues with the unsupported customizations that we have done to our CRM 3.0 Look up as these unsupported JavaScript customizations were not working with CRM 4.0.
Hence we had find alternative ways of getting this done.

Thanks to some nice articles by very good people we found a way.
i have posted a link which states how to do this. also i have copied an example here.
http://social.microsoft.com/forums/en-US/crmdevelopment/thread/3f6c8c3d-73a7-4601-98a3-f72cc4c7ea9b/

but the thing is nobody can guarantee that these will work with all the roll ups that Microsoft are putting out. hence be careful with these.

Example:-

var fetchStr = "";
crmForm.all.new_accountid.lookupbrowse = 1;
crmForm.all.new_accountid.additionalparams = "search=" + fetchStr;

T SQL order of the way a query is processed

I don't think that most of us ever thought about the order a T SQL is getting processed. I Know i haven't thought about it before.
I have given the order below. This i have taken from a book written about Linq.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH
7. HAVING
8. SELECT
9. TOP
10. ORDER BY

Change the default filter in activities in crm 3.0

We needed to change the default activity history filter as a customer asked us to do that. I searched the net to find some answers and there were 2 very good articles regarding this. i have pasted the links below.

http://www.stunnware.com/crm2/topic.aspx?id=js11
http://ts2community.com/blogs/larrylentz/archive/2008/12/19/changing-the-default-activities-filter-on-view.aspx

Change the for title and title bar in CRM 3.0 forms

I have given below the javascripts that needs to be used to change this.

var doc = document.getElementsByTagName('td');
for (var i = 0; i < doc.length; i++)
{
if(doc[i].className == "formTitle")
{
var header = doc[i].innerText;
strName=crmForm.all.name.DataValue;
strTitle=crmForm.all.titleid.DataValue[0].name;
header = strTitle +". "+ strName ;
doc[i].innerText = "Member : " + header;
document.title ="Member : " + header;
}
}

This scripts will change both the form title and CRM form title bar.
i was helped by this post.
http://robbakkers.blogspot.com/2006/03/update-header-information.html

Error Messages in CRM 3.0

we have been having issues with the error message returned by CRM 3.0 web service as it always returned "Server was unable to process the request".

 

we had to check everything to find the actual cause of error.

Recently i found a very ggod artical written by Ronald Lemmen explaining error details returned by CRM web service. This is avery good article. Anyone can refenrece it from this location.

http://ronaldlemmen.blogspot.com/2009/01/error-messages-in-crm.html

Thanks alot Ronald for your valuable time and information.