Excel 2007 Linked Server

Lets see How to create a linked server with Excel 2007. Well most of the stuff you'll find on the net on how to create a linked Server is using Microsoft Jet 4.0 OLE DB Provider which is no longer able to connect to Excel 2007.

So here you go...
We will use "Microsoft.ACE.OLEDB.12.0" which come with Office 2007 pack. If you don't have it on Provider's list you can download it from 2007 Office System Driver: Data Connectivity Components

After downloading and Installing the ACE driver you can create the Linked server as

exec sp_addLinkedServer
@server= 'XLFile',
@srvproduct = 'ACE 12.0',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'E:\Mangal.xls',
@provstr = 'Excel 12.0; HDR=No'

If you have the Column names in Excel do HDR=yes.

Now you can query your Excel just like any table - SELECT * FROM XLFile...[sheet1$]

Or if you don't want to create a Linked Server, and just want to execute the Excel then do -
SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=E:\Mangal.xls;HDR=No', 'Select * from [Sheet1$]' )
And if you execute the above query and you get the error
Msg 15281, Level 16, State 1, Line 1 SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off. That means first you need to enable the 'Ad Hoc Distributed Queries'.

For that first execute the following scripts. 
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
For Creating Linked Server from Management Studio:
Open the Management studio, connect to your server. And follow the steps
1. Go to "Server Objects" then Right CLick on "Linked Servers" and write "New Linked Server".
2. Give any name to your linked server. In our example I've given "XL".
3. Now select Server Type as "Other Data Source". Now select the provider"Microsoft Office 12.0 Access Database Engine OLE DB Provider."
4. Product Name : ACE 12.0
5. Date Source : E:\Mangal.xls --- Here you need to put the path of your Excel Sheet.
e.g. C:\Folder\New.xls Provider String : Excel 12.0; HDR=No 
Again remember if you have column names in Excel then HDR=Yes. 

- Mangal Pardeshi.

Linked Server Error : Msg 7399, Level 16, State 1

In this post I'll show you a simple solution to a very common error you get while running a query against a linked Server.
You create a Linked Server with Flat Files, Excel or DBF in SQL Server using Microsoft.Jet.OLEDB.4.0 and when you try run a query against it you get the error
--Error--
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server " " returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "".

Well whatever study I have done on this particular error, I have observed that you will get this error only when try to query from a remote machine. And this is something to do with the Security Context of the Linked Server.

Solution to this problem is very simple. Simply follow steps -
1.Open the Management Studio and navigate to Server Objects and then to Linked Server.
2.Right click on your Linked Server Name, and click on Properties.
3.Go to Security Page. Now for solving above problem you have 2 option, you can try any of the below 2 option.

A] Select the option “Be made without using a security context” by clicking on radio button.

OR

B] Select the last option “Be made using this security context ”.
As soon as click on the radio button before “Be made using this security context ”, the“Remote login” and “With password” text boxes become active to be filled in.
Now in the “Remote login”, enter Admin as the login user. Leave the password text box as blank.
Now just click on OK, and now can run the query against your linked server without any error.

- Mangal Pardeshi.

Linked Server With DBF Files

In my previous post I wrote about Linked Server with Excel. Now we will see how to create a linked server for DBF files. That is accessing and querying DBF files from SQL Server Management Studio.

First I’ll show you how to set up the Linked Server from Management Studio.

Open the Management Studio and navigate to Server Objects and then to Linked Server. Right click on the Linked Servers node and select “Create New Linked Server”.
Enter in the name of your linked server in the text box that appears next to the “Linked Server” label. This can be any name that you would recognize to describe the object.

Then under Server Type, choose the “Other Data Source” Radio button. And select the provider “Microsoft Jet 4.0 OLE DB Provider” from the list.
For “Product Name” enter “Microsoft Jet”
In the “Data Source” text box, enter the full folder path to your dbf files. For mine I entered this: “D:\DBF Project\Data”.
For the “Provider String” enter “dBASE 5.0”

Then go to the security page. Here you have 2 options.
Either select “Be made without using a security context”
OR
Select “Be made using this security context” radio button found near the bottom of the window. The “Remote login” and “With password” text boxes become active to be filled in.
In the “Remote login”, enter “Admin” as the login user. Leave the password text box blank. Well I don’t know the exact answer why this needs; I tried searching on Net but couldn’t find the answer, but this how it works.

And say “ok”, now your Linked Server DBF files is ready. Just expand the Linked Server and then now created Linked server, after expanding the Default you will see Tables and Views. Expand the Tables node to see a list of table objects for the folder.

Now you can query the DBF files just like any other table from management studio. Now open the new query window to write a query on DBF files. Just remember you have to include the linked server and table name in a four part address. Namely, [LinkedServername]…[TableName].

So your select query will be like
SELECT * FROM [Linked_Server_Name]…[TableName]

e.g.
SELECT * FROM DbfLinked…Items.

Here DbfLinked is the linked server name I gave and “Items” is the table name. Observe that there are 3 dots between linked server name and table name.

We can use almost any select statement to retrieve our dbf data. For example JOINS with other tables in Sql Server or with other DBF tables, WHERE conditions, GROUP BY, HAVING, ORDER BY, even the UPDATE, DELETE or for that matter any valid Sql statement you normally do with tables.


Same Linked Server you can create using Sql Statement.
-- To create Linked Server With DBF Files
EXEC master.dbo.sp_addlinkedserver
@server = 'DbfLinked',
@srvproduct = 'Microsoft Jet',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'D:\DBF Project\Data',
@provstr = 'dBASE 5.0'


/* Now use either of two secuirty Context*/
-- To select security Context “Be made without using a security context”

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = 'DB',
@useself = 'False',
@locallogin = NULL,
@rmtuser = NULL,
@rmtpassword = NULL

-- Or using “Be made using this security context”
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = 'DB',
@useself = 'False',
@locallogin = NULL,
@rmtuser = 'Admin',
@rmtpassword = ''


- Mangal Pardeshi.

Linked Server With Excel.

In this part I’ll help you in creating a Linked Server with Excel. Or simply how to import/query Excel in Sql Server.

In Sql Server 2005 you need to enable the 'Ad Hoc Distributed Queries'. For that first execute the following scripts.
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

And now here go
1.
SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source=E:\Mangal.xls;Extended Properties=Excel 8.0')...Sheet1$
2.
SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\Mangal.xls', Sheet1$)
3.
SELECT * FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]')

Here Excel 8.0 means this is applicable to Excel 2002, Excel 2000, or Excel 97 workbook.
Database means path of your excel book and its name.

Now you can do any Sql query with excel that you usualy do with any table. For example you can join excel with other tables, you can insert excel rows in other tables.
Lets say you want to insert first 3 columns of Excel sheet into table Customers.
You can do
INSERT INTO customers (CustId, CustomerName, Address )
SELECT Custid, CustomerName, Address
FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]')

Or to join a table with Excel

SELECT A.ColumnName, E.ColumnName
FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\Mangal.xls', 'SELECT * FROM [Sheet1$]' ) E
INNER JOIN Sql_Table A
ON a.id=e.id

- Mangal Pardeshi.