Create Linked server with Excel 2007 worksheet in SQL Server 2005

I have written one article to create linked server with Access database but I seen in many forums that people mostly would like to use excel file and wants to import its data. However, there are many ways to import Excel data to SQL Server but Linked server gives you more freedom over data. Let us see how we can do this. Before you start doing this, I would like you to create one excel 2007 file named “Dept” and put it in C: drive.

See my previous article for linked server with Access at

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

There will be two column in your excel 2007 file with following data in Sheet1

Department Description
MIS IT department
Acct Account Department
Chem Chemist Department

Now let us move to SQL server and make linked server.

EXECUTE sp_addlinkedserver

@server = ‘DeptExcel’,

@srvproduct = ‘ACE 12.0’,

@provider = ‘Microsoft.ACE.OLEDB.12.0’,

@datasrc = ‘C:\Dept.xlsx’,

@provstr = ‘Excel 12.0’

GO

–excel’s credential if any

EXEC sp_addlinkedsrvlogin ‘DeptExcel’, ‘false’

go

–list all tables from excel file

exec sp_tables_ex ‘DeptExcel’

go

–see table in sheet1

SELECT * FROM [DeptExcel]sheet1$

go

–create table in SQL and imports data

select * into tempTable FROM [DeptExcel]sheet1$

go

–check your SQL table

select * from tempTable

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Linked Server Error 7303- Cannot initialize the data source object of OLE DB provider

Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41

Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “TestLinkServer”.

OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “ TestLinkServer” returned message “Cannot open database ”. It may not be a database that your application recognizes, or the file may be corrupt.”.

After reading my linked server’s article in SQL Server 2005 at

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

http://www.sqlhub.com/2009/03/linked-server-is-not-configured-for.html

One of my subordinate wanted to use it in one of our small application. He started using the same code I gave in one of my above article about Linked Server but he was facing above described error. He tried everything and every possible solution from internet but didn’t find his luck. Finally he told me to look at the error and try to solve it. At the very first glance I found problem with his OLE DB adapter. He was using the MDB file which has been developed in ACCESS 97 but there was ACCESS 2007 in our SQL’s development server. As MDB was old, he was using “Microsoft.Jet.OLEDB.4.0” but he should use ” Microsoft.ACE.OLEDB.12.0” as our development server has ACCESS 2007 and it doesn’t understand old provider.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Linked Server is not configured for data access Error: 7411 in SQL Server 2005

I wrote one article to set Linked server at:

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

Sometime you properly set the link server and while trying to execute SELECT statement over it, it may show you following error.

Msg 7411, Level 16, State 1, Line 2

Server ‘LinkToAceess’ is not configured for DATA ACCESS.

Solution of this error is really pretty much simple. Before we move to solution of error I would like to raise this error. Run the linked server example given in above article and see whether it is working properly. One you done with that let us change one property of Linked Server which will raise above said error.

–set data access property to ‘false’ which will generate error

–when you try to execute any SELECT statement against your linked server

exec sp_serveroption [LinkToAceess],‘Data Access’,‘false’

Now try to execute SELECT statement and greet the error:

Msg 7411, Level 16, State 1, Line 2

Server ‘LinkToAceess’ is not configured for DATA ACCESS.

Solution is so simple; just enable your data access to linked server with following command.

exec sp_serveroption [LinkToAceess],‘Data Access’,‘true’

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Linked Server in SQL Server 2005 from ACCESS 2007

Linked Server is one of the cool features of Microsoft SQL Server especially for those who want to access remote database, remote database could be of any type SQL Server, Sybase, Access etc. you can access those databases with OLEDB provider. Let us see how can we do it?

Before you move further I would like to request my readers that please create one Access MDB file named “Testing.MDB” and create at least one table named “empDetails” in that MDB.

Now, we will link “Testing.MDB” database to SQL Server 2005.

Note: You can do it by wizard and by query but I prefer query so will show you that path only.

Below query will cover file steps to link the server.

1.) Add linked server

2.) Add linked server credential, if any

3.) Check sys.servers whether database is added as linked server

4.) List all available tables in MDB file

5.) Perform SELECT on “empDetails” table.

–add link server

exec sp_addlinkedserver

@server=‘LinkToAceess’,

@srvproduct=‘AccessDatabase’,

–if you have older version of access then kindly use old jet provider

–Microsoft.Jet.OLEDB.4.0

@provider=‘Microsoft.ACE.OLEDB.12.0’,

@datasrc=‘C:\Testing.mdb’

GO

–add MDB’s credental, if any

EXEC sp_addlinkedsrvlogin ‘LinkToAceess’, ‘false’

–check whether ‘LinkToAccess’ has been added

select * from sys.servers

–list all tables available in Testing.MDB

exec sp_tables_ex ‘LinkToAceess’

–perform SELECT on empDetail table.

SELECT * FROM [LinkToAceess]empDetails

So, finally you have used Linked Server from within your SQL Server

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com