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 Reports - Email Subscription and Scheduling

Once you are done with creating a report in ms crm, the next probable step is to subscribe the report inorder to automatically send the report to a particular emailaddress at particular intervals. We use the Report Manager Subscriptions for this automation purpose.

Inorder to create a subscription, follow the below steps:
1. Open the Report Manager from the browser using the below url - 
    http://reportingserver/reports
    here, ReportingServerName should be replaced by the name of your server on which  
    SSRS is installed and which is pointed by MS CRM during the installation process.
2. Open the folder where the name of the folder is same as your CRM Organization name
3. Open your specific report. If it is insisting for credentials then you need to update the 
    DataSource in the Properties tab to automatically supply the credentials.
4.In the Properties tab, under General, give appropriate name to the report which can be 
   displayed when this report has been sent as an attachment from the subscription process. 
   By default, this is having a GUID value.
5. Go to Data Sources. Best practice is to point to a Shared data source with the following 
    properties.
    Connection type: Microsoft SQL Server
    Connection string: Data Source=SQLServerName; Initial Catalog=DatabaseName
    Connect using:
      Credentials stored securely in the report server
      Provide the windows credentials that are having necessary privileges to run the report
      Check the Use as Windows credentials when connecting to the data source.
6. Apply it
7. Go to Subscriptions and click on New Subscription. Provide the necessary fields like To, 
    Reply-To, Subject..
8. Check Include Report and Select required Render Format
9. Select Schedule and click Ok

You can test the subscription by scheduling this for "Once" in Select Scehdule and verify if the report is getting delivered in the way it is required.
If you come across any error during the delivery, most of the times it happens with the credentials. The credentials you supplied do not have enough privileges to run the report. so, try with admin credentials to confirm this and provide the credentials with updated privileges.

ms sql query against null values - ansi_nulls is ON

We use the below query to check against null values
select * from table_name where column_name is null which returns the records whose values are null for that particular column.

But, the below query will not yield any result eventhough there are records which contains null values.select * from table_name where column_name = null 

In the same way
select * from table_name where column_name != null will also produce no results.

This is because, ansi_nulls is set to ON by default in SQL.To make the above query work use set ansi_nulls offNow we get the same results with the following queries  

select * from table_name where column_name = null and
select * from table_name where column_name != null

Giving alias name or different name to Linked Server in MS SQL

Today I would like to put sometime on giving alias named to Linked Server. I got some request to update the linked server name from Server_ABC to Server_XYZ. It sounds pretty simple to update the name but I came to know the real depth in it when I tried to find the dependencies. There are many dependencies for this linked server and most of the cases I found it in Store Procedures. So, I have to update the linked server name at all the places to reflect the change. In Order to get rid of this, we can make use of alias concept where we keep the linked server name to be more generic and pointing the data source points to original server name.

Follow these settings to create an alias name for linked server:

Right click on the target linked server and Properties
General Tab -
Linked Server: generic name
Server Type: choose other data source
Provider: SQL Native Client
Product: sql_server
DataSource: original server name

Security Tab - choose the appropriate security context
If you are connecting to a remote server,  click Be made using this security context and enter sql login credentials to connect to the linked server.

Server Options -
Collation Compatible: false
Data Access - True
Rpc - True
Rpc Out - True
Use Remote Collation - True
Connection Timeout & Query Timeout are by default set to 0 which is unlimited. Any value specified greater than 0 is treated in seconds.

This alias name helps us more when we work on the linked servers in both dev and production environment. We can make use of this alias concept to maintain same linked server name on both the environments.


Hope this helps!