SQL Server - Read Excel, CSV 2003, 2007 files using OpenRowSet

In SQL Server, we can read the data directly from an Excel file or a .csv file using OpenRowSet function as given below:

For OPENROWSET function to work properly in SQL Server 2005, you need to check "Enable OPENROWSET and OPENDATASOURCE Support" using SQL Server Surface Area Configuration for Features.

 Once you are done with that you can make use of the below queries to read the data:

--Read .CSV file from a 32 bit SQL Server 
SELECT * FROM OPENROWSET
('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=D:\DWP Deliverables\CSPRojectIntegration\;Extensions=CSV;','SELECT * FROM CSProjects.csv')

--Read Excel file from a 32 bit SQL Server 
SELECT *  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\DWP Deliverables\CSProjects.xls',
'SELECT * FROM [Sheet1$]')

--Read Excel 2007 file from a 32 bit SQL Server 
Another workaround is trying to install Microsoft.Jet.OLEDB.12.0, and use your connection string with OLEDB 12.0, it can be downloaded at :
2007 Office System Driver:  http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891

On the other hand, you can save the 2007 file to 2003 format and try reding it from OLEDB4.0 driver

--Read files from 64 bit SQL Server
JET OLEDB Provider works only with 32 bit. For 64-bit OLEDB Provider for ODBC you have to install MSDASQL using the below link http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20065#overview


Hope this helps!!

MS CRM SQL Server CPU Utilization shows 100% - Troubleshooting steps

We had a weird issue with MS CRM SQL server recently where the CPU utilization of sql server constantly shows 100% for a long time. When we checked in the task manager we found that sql process alone is taking around 98 - 99%. We have application and database on different servers. We tried to reboot both the servers couple of time and the behavior is such a way that the utilization goes down whenever we restart the machine and raises up to 100% after 10-15 min.

But the only relief from our side is the CRM application is not entirely down but the performance was less.So, we got some time to debug into the issue.

We tried the following steps in our process of troubleshooting the issue:

1. First thing to check if there are any recent changes or deployments made to the system.
2. In the task manager check the process that is consuming the cpu utilization.
3. Check the application logs and system logs in EventViewer of the crm server. If the app server and sql servers are hosted in different servers then check the event logs in both the servers.
4. Stop the Asynchronous Service in MS CRM server and check the cpu utilization. If it gets down then issue is probably with the ms crm app server. It could be due to customizations you made which is running into deadlock situations.
5. If it is still showing 100% then stop the sql server services and check the utilization.
6. Start the CRM AsyncService and see the utilization.If it works fine, then start the sql services as well.
7. If you still find the issue, you can Start Running the SQL Profiler to see if any query is taking long time to execute. If you find any, then disable the functionality that is making use of that query for a while and check the utilization.
8. Check if any event log appears in System. Check if automatic windows updates are turned ON on the server. Sometimes windows updates might not run successfully and causes the issue so check the last updated time from windows and see if the error in the event log starts occurring from that time. It will  be set when the windows updates run for the next time or you can manually run the windows updates and restart the server.

 The reason in my case is the automatic window updates failed to run successfully that impacted the sql server process.

If the above steps did not resolve your issue and if you are making use of some third party services running on the same server then, instead of restarting the services, try stopping the services one at a time, check if there is any impact on the utilization and once you are done with stopping all the services and start the services one at a time. This way you can get closer to the root cause of the issue. 

Hope this saves sometime in your debugging process!!

MS CRM 4.0 How to view detailed Error Message

It is a common practice for the developers working on MS CRM to view the exact error message to fix the issues instead of seeing the custom error page displayed to the end user.
In order to view the error message, below are the different options available

1. Enable Trace which logs the details of the error. This can be implemented on live server so that end users still see the custom error message instead of the original error
2. Modify the web.config file to set "DevErrors" to "On" on the MS CRM server as per the below microsoft article http://support.microsoft.com/kb/857192