Linked-Server Settings for SQL Server 2008 R2 to Excel 2010 Spreadsheet…
STEPS
TO CREATING A LINKED-SERVER FROM SQL 2008R2 (64 bit) TO AN EXCEL 2010
SPREADSHEET:
REMOTE (whichever RDP client you prefer)
directly into your SQL Server 2008R2 server with an ADMINISTRATOR-level account
(you’re downloading and installing stuff);
LAUNCH SQL Server 2008R2 Management Studio
CONNECT to your local SQL Server instance
(often connects by default)
NAVIGATE to SQL Server (instance) > Server
Objects > Linked Servers > Providers
CHECK if provider “Microsoft.ACE.OLEDB.12.0″
provider is already installed
IF (NOT Installed)
·
DOWNLOAD the AccessDatabaseEngine_64.exe
Provider Installer to your SQL Server’s hard drive, say C:\Application
Installers is a location I create for such activities – makes it easy to
see what “extra” stuff I’ve installed;
·
LAUNCH the AccessDatabaseEngine_64.exe
installer
=> the Microsoft.ACE.OLEDB.12.0 provider will now be
added to SQL Server’s list of Data Access Providers
·
REFRESH the Providers node in SQL Management
Studio (cool how you did NOT need to close/restart the studio!)
=> you will now SEE the Microsoft.ACE.OLEDB.12.0 provider
listed with all the other built-in providers
RIGHT-MOUSE-CLICK on Linked Servers > New
Linked Server
=> New Linked Server dialogue box displays
TYPE or SELECT the following information:
·
Linked Server (name) =
“EXPORT” (ANYTHING is acceptable, but shorter names are
preferred)
·
Server Type = Other Data Source (select this
radio button)
·
Provider = Microsoft Office 12 Access
Database Engine OLE DB Provider
·
Product Name = Excel
·
Provider String = Excel 12.0 Macro
·
Location = blank (don’t need it)
·
Catalog = blank (don’t need it)
=> SQL Managment Studio uses the info to
create & test a new connecton string, and completes creating the new Linked
Server
NAVIGATE into the new Linked Server
NAVIGATE down to a table
RIGHT-MOUSE-CLICK > Script Table >
Select to
=> a canned SELECT query is generated
=> you’ll see your table
contents returned – CONFIRMATION that you now have a working Linked Server!
That’s it!
Cheers,
SELECT DATA FROM EXCEL IN TO SQL SERVER
DATABASE
INSERT DATA INTO EXCE SHEET FROM SQL SERVER
Source:http://vogtland.ws/markedwardvogt/?p=991
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
--sp_helptext sp_configure
-------------Success connection for 2007-2010---------------
DROP TABLE
TEMP
select * into temp
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0
Xml;HDR=YES;Database=C:\data\data.xlsx',
'SELECT * FROM
[Sheet1$]');
select * FROM temp
------------------------------------------------------------
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\data\data.xlsx',
'SELECT * FROM [Sheet1$]')
---------------------- For 2003-2007 -----------------------
------ SELECT DATA FROM AN EXCEL SHEET IN SQL SERVER -------
SELECT * --INTO #productlist
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel
8.0;HDR=YES;Database=C:\data\Book1.xls',
'select * from
[Sheet1$]');
------ INSERT DATA IN TO EXCEL SHEET FROM SQL SERVER -------
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel
8.0;HDR=YES;Database=C:\testing.xls;',
'SELECT * FROM [Sheet1$]')SELECT GETDATE()
The
Basics of Excel Data Import to SQL Server
To
import data from Microsoft Excel to SQL Server OPENROWSET and OPENDATASOURCE functions with OLE
DB data source can be used.
The
basic format for the Microsoft.Jet.OLEDB.4.0 provider is:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\excel-sql-server.xls', [Sheet1$])
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\excel-sql-server.xls;Extended
Properties=Excel 8.0')...[Sheet1$]
The
basic format for the Microsoft.ACE.OLEDB.12.0 provider is:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel
12.0;Database=C:\excel-sql-server.xlsx', [Sheet1$])
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data
Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
The Microsoft.Jet.OLEDB.4.0 provider is used
with 32-bit SQL Server for Excel 2003 files.
The Microsoft.ACE.OLEDB.12.0 provider is used
with 64-bit SQL Server for any Excel files or
32-bit SQL Server for Excel 2007 files.
Configuration
Steps for Excel Data Import to SQL Server
#
|
Step
|
SQL Server x86
for Excel 2003
files *.xls
|
SQL Server x86
for Excel 2007
files *.xlsx, etc.
|
SQL Server x64
for any Excel
version files
|
1
|
Install Microsoft.ACE.OLEDB.12.0 driver
|
not needed
|
x86
|
x64
|
2
|
Configure Ad Hoc Distributed Queries
|
yes
|
yes
|
yes
|
3
|
Grant rigths to TEMP directory
|
yes
|
yes
|
not needed
|
4
|
Configure ACE OLE DB properties
|
not needed
|
yes
|
yes
|
Install Microsoft.ACE.OLEDB.12.0 driver
To
import Excel 2007/2010 files to SQL Server Microsoft.ACE.OLEDB.12.0 driver should be
installed.
To
download the driver use the following link:
Don't
worry about "Access" in the name.
Warning! x64 driver can not be installed if
Microsoft Office 2007/2010 x86 is already installed!
So there
is no way to import Excel data to SQL Server x64 using
OPENROWSET/OPENDATASOURCE functions on a machine with Microsoft Office x86!
The SQL Server Error
Message if Microsoft.ACE.OLEDB.12.0 is not installed
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider
"Microsoft.ACE.OLEDB.12.0" has not been registered.
Configure Ad Hoc Distributed Queries
To
configure Ad Hoc Distributed Queries use the following code:
sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
The SQL Server Error
Message if Ad Hoc Distributed Queries component is turned off
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 as part of
the security configuration for this server.
A
system administrator can enable the use of 'Ad Hoc Distributed Queries'
by
using sp_configure.
For more information about enabling 'Ad Hoc
Distributed Queries',
see "Surface Area Configuration"
in SQL Server Books Online.
Grant rigths to TEMP directory
This
step is required only for 32-bit SQL Server with any OLE DB provider.
The
main problem is that an OLE DB provider creates a temporary file during the
query in the SQL Server temp directory using credentials of a user who run the
query.
The
default directory for SQL Server is a default directory for SQL Server service
account.
If
SQL Server is run under Network Service account the temp directory is like:
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
If
SQL Server is run under Local Service account the temp directory is like:
C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp
Microsoft
recommends two ways for the solution:
1.
A change of SQL Server TEMP directory and a
grant of full rights for all users to this directory.
2.
Grant of read/write rights to the current
SQL Server TEMP directory.
Usually
only few accounts are used for import operations. So we can just add rights for
these accounts.
For
example, icacls utility can be used for
the rights setup:
icacls
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant vs:(R,W)
if
SQL Server is started under Network Service and login "vs" is used to
run the queries.
The SQL Server Error
Message if a user have no rights for SQL Server TEMP directory
OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"(null)".
Configure ACE OLE DB properties
This
step is required only if the Microsoft.ACE.OLEDB.12.0 provider is used.
Use
the following T-SQL code:
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
The SQL Server Error
Messages if OLE DB properties are not configured
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider
"Microsoft.ACE.OLEDB.12.0" for linked server "(null)"
reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider
"Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
How-To: Import Excel 2003 to SQL Server x86
Step 1. Configure Ad Hoc
Distributed Queries
sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Step 2. Grant rigths to
TEMP directory
icacls C:\Windows\ServiceProfiles\\AppData\Local\Temp /grant :(R,W)
The
most commonly used pathes:
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp
Use
Microsoft.Jet.OLEDB.4.0 OPENROWSET format
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel
8.0;Database=C:\excel-sql-server.xls', [Sheet1$])
Use
Microsoft.Jet.OLEDB.4.0 OPENDATASOURCE format
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\excel-sql-server.xls;Extended
Properties=Excel 8.0')...[Sheet1$]
How-To: Import Excel 2007
to SQL Server x86
Step 1. Install 32-bit
Microsoft.ACE.OLEDB.12.0 driver
Step 2. Configure Ad Hoc
Distributed Queries
sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Step 3. Grant rigths to
TEMP directory
icacls C:\Windows\ServiceProfiles\\AppData\Local\Temp /grant :(R,W)
The
most commonly used pathes:
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp
Step 4. Configure OLE DB
properties
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
Use
Microsoft.ACE.OLEDB.12.0 OPENROWSET format
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel
12.0;Database=C:\excel-sql-server.xlsx', [Sheet1$])
Use
Microsoft.ACE.OLEDB.12.0 OPENDATASOURCE format
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data
Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
How-To: Import Excel
2003/2007 to SQL Server x64
Step 1. Install 64-bit
Microsoft.ACE.OLEDB.12.0 driver
Step 2. Configure Ad Hoc
Distributed Queries
sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Step 3. Configure OLE DB
properties
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
Use
Microsoft.ACE.OLEDB.12.0 OPENROWSET format
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel
12.0;Database=C:\excel-sql-server.xlsx', [Sheet1$])
Use
Microsoft.ACE.OLEDB.12.0 OPENDATASOURCE format
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data
Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
http://www.accelebrate.com/library/tutorials/ssis-2008
