CRM 2011 How to increase the upload size

With CRM 4 when we want to increase the upload size we went to the web configuration file and
changed the maxRequestLength property to the size that we wanted to.

However with CRM 2011 you don’t need to update the web configuration file, but update a field in
the config database.

By running the below statement you can find out the upload size of the documents.

select IntColumn from ServerSettingsProperties where columnname=’ImportMaxAllowedFileSizeInMB’

You can change this by running the following statement in SQL Server.

Update ServerSettingsProperties set IntColumn = 100
where columnname=’ImportMaxAllowedFileSizeInMB’

This will increase the upload amount to 100 MB. Please note after doing this you will have to do an iisreset for the upload size to take the size of the changed value.

We were helped by the following article when we had the same problem.
http://www.ahmetcankaya.com/increase-upload-file-size-in-crm-2011/

CRM4 SQL Functions

There are a lot of SQL functions that we can use when we are doing reports in CRM. The following  article describes about them.

http://blogs.infinite-x.net/2009/02/20/using-built-in-crm-functions-when-writing-sql-reports/

Reading Uncommited data In SQL Server

Most of the time when we want to do an update, delete or insert to the database we use begin, rollback SQL statements.
If only you are very sure that you will be using commit tran.

Why we use rollback tran is to make sure that what ever that we do has not caused the data in the DB to be in inconsistent state. Lets take a look at the below example.

1. begin tran
2. update HumanResources.Employee
3. set NationalIDNumber = 60
4. where EmployeeID = 257
5. rollback tran

If your transaction is not completed then you will not be able to check the changes in the data in the DB as the changes have not been committed yet.

During such times you can run the below statement and get this job done.

set transaction isolation level read uncommitted

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/

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

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

Migrating Data To CRM 3.0

It is possible to migrate data from and existing system to CRM database using SQL statements.

Although microsoft recommends us not to use direct SQL statements, this method is easy as we don't have write code to migrate data. But we need to be very careful when we are doing this as we need to populate the references before migrating data.

I don't know whether CRM 4.0 provide a method to insert data to custom entities. CRM 3.0 doesn't provide us this. That is the reason why we have to choose an alternative.

Truncating CRM tables

Is it possible to truncate CRM table data?

The answer is for some it's Yes. you can truncate the extensionbase tables in the CRM cause mostly they contain data and foreign keys. We can truncate these tables.

But we cannot truncate the base tables as they contain the references to other tables. You need to use the delete command to delete data in these tables.