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