Fix : Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

I tried to read CSV file from SQL Server and seen vairous errors. Below thread helps to fix the issue

Syntax to read csv file from SQL Server

SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\kalyan\CsvFiles','SELECT * FROM filename.csv')

Error
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

If you run on a 64bit environment we need to download new version of 2010 office system driver which supports both 32bit, 64 bit environments,the provider name is Microsoft.ACE.OLEDB.12.0.
Download Microsoft Access Database Engine 2010 Redistributable from Microsoft and Install suitable version of software on the machine.

After successful installation of provider you can verify it in the below location
SSMS -- Server Objects - Linked Server -- Providers -- Microsoft.ACE.OLEDB.12.0.

Modify your query as below

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;
Database=C:\kalyan\CsvFiles','SELECT * FROM filename.csv')

But not like below
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Driver={Microsoft Text Driver (*.txt; *.csv)};
 DefaultDir==E:\DataSqlCmd2009','SELECT * FROM DataSqlCmd2008.csv')
If you run above syntax you will get the below error,
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".

Run the command
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;
Database=C:\kalyan\CsvFiles','SELECT * FROM filename.csv')

Error
 Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

To fix the above error, run the below command

Use master
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Re-run the command again

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;
Database=C:\kalyan\CsvFiles','SELECT * FROM filename.csv')

Again Error
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Make sure your SQL Service account has full permissions on the directory where csv files are exists.
Re-run the command again

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;
Database=C:\kalyan\CsvFiles','SELECT * FROM filename.csv')

Again Error
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "SELECT * FROM filename.csv". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

Make sure the service account has full permissions to
c:\users\service account folder\appdata\local\temp folder -- If you receive error after doing this also we need to modify a value in the registry

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\Providers\Microsoft.ACE.OLEDB.12.0\
Create a new dwordvalue with the below key name and value

key name - AllowInProcess value - "dword:00000001" Close the registry and now it will work.

Run the below commands
USE [master] 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 
GO 
If you are still facing the error might be below is the reason
OS - Windows 64 bit
SQL Server - 64 bit
Office Products - 32 bit
Then you need to uninstall 32bit office products and install 64bit Microsoft Access Database Engine.

Table Partitioning Basics - Part I

Basics of Table Partitions

Partitioning is the way to segregate your data into subsets. The main reason for doing this is for query
performance. Maintenance operations that are performed on subsets of data are also performed more efficiently because these operations target only the data that is required instead of whole table. 

Benefits of Partitioning
Manageability
Query Performance
Data Archival
Partitioned objects will increase the performance of Select, Insert, Update and Delete queries.


Note : This feature is available only in Enterprise Edition
Steps to Partitioning SQL Server Table
1. Create Partition Function
2. Create Partition Scheme
3. Create partition Table

Step 1 -- Partition Function - The partition function defines how you want to partition of Data. This partition function is not dependent on any table, we are generically defining a technique for splitting data. We need to define partitions by specifying the boundaries, for example we have a sales table that contains information of sales data for last 3 years. We can partition that table into three partitions using the following function.

Create partition function PartitionByYear (datetime) as range left for values
('2011-01-01T00:00:00', '2012-01-01T00:00:00', '2013-01-01T00:00:00')

If I used “RANGE RIGHT” the first partition include all values less than 2011, and second contains values between 2011 and 2012 and third values between 2012 and 2013. Range Left will have include values less than or equal to 31-12-2011, the second partition starts from 1-1-2012 onwards and third from 1-1-2013 onwards.

Step 2 – Partition Scheme Creation – After defining partition function we need to create a partition
scheme defining where you want to partition it. This scheme directly links with partitions to filegroups. If we have 3 filegroups (fg1, fg2, fg3 then below is the syntax to create partition scheme).

Create Partition Scheme Sales_PartScheme as partition partitionByYear to ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY])

If you observe the above example that partition scheme is also an independent object still we haven’t linked it to any specific table in the database, which means this is a reusable object where we can use this partition scheme on any number of database tables.

Step 3 – Partitioning a Table -- After creating partition scheme now we are ready to create a partitioned table. It is a straight forward process where we need to add partition scheme name to ON clause at the time of table creation.
For Example
Create table sales (sales_id bigint, item_name varchar(30), item_desc varchar(200), item_make varchar(100), sale_date datetime) on Sales_PartScheme(sale_date);

Below views used to view partitions
sys.partitions, sys.partition_functions, sys.partition_schemes

 

SQL Service status is change pending

SQL Service status is showing status as change pending in the configuration manager.

1. Review the error log file.
2. In our environment we have issue with resourcedb files.
3. I replaced the resource db mdf and ldf files and started the SQL Engine, now it is working fine.


It may vary for every environment, so reveiw the error log file carefully while doing any modifications.
 

Querying XML datatype using T-SQL

--- Extract Strings from XML data type using T-SQL ---

---- Fetch value in XML Node format ----

DECLARE @xmlstring XML
SELECT @xmlstring =
'<Employee>
  <name>Kalyan Kumar</name>
  <city>Hyderabad</city>
  <state>Andhra Pradesh</state>
  <email>kalyan@yahoo.com</email>
</Employee>'
SELECT @xmlstring.query('(/Employee/name)')  AS Emp_Name
SELECT @xmlstring.query('/Employee/name/text()')  AS Emp_Name

---- Fetch value in String format ----
DECLARE @xmlstring XML
SELECT @xmlstring =
'<Employee>
  <name>Kalyan Kumar</name>
  <city>Hyderabad</city>
  <state>Andhra Pradesh</state>
  <email>kalyan@yahoo.com</email>
</Employee>'
SELECT @xmlstring.value('(/Employee/name)[1]', 'CHAR(30)')  AS Emp_Name

---- Fetch value from XML Sub Node in string format----
DECLARE @xmlstring XML
SELECT @xmlstring =
'<Employee>
  <Details name="Kalyan Kumar">
  <city>Hyderabad</city>
  <state>Andhra Pradesh</state>
  <email>kalyan@yahoo.com</email>
  </Details>
  </Employee>'
SELECT @xmlstring.value('(//Details/@name)[1]','char(30)')
SELECT @xmlstring.value('(/Employee/Details/city)[1]', 'CHAR(30)')

How To Check Status of MySQL Service

shell > /etc/init.d/mysql status    -- To check status of MySQL Service.

shell> /etc/init.d/mysql start       -- To Start MySQL Service

shell> /etc/init.d/mysql stop        -- To Stop MySQL Service

shell > /etc/init.d/mysql restart    -- To restart MySQL Service

shell > /etc/init.d/mysql reload     -- To reload MySQL configuration file.