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;