SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Import Excel 2007 as linked server using SQL 2005
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Grifter
Constraint Violating Yak Guru

260 Posts

Posted - 08/30/2013 :  04:13:14  Show Profile  Reply with Quote
Hi

I am trying to import an excel 2007 file in sql server 2005 using linked server. I found instructions how to use excel files as linked server in 2005 but for the provider string I used 'Excel 12.0' instead of 'Excel 8.0' because I am using Excel 2007 file.

From here:

http://support.microsoft.com/kb/306397/EN-US

quote:
In SQL Server Management Studio, expand Server Objects in Object Explorer.
Right-click Linked Servers, and then click New linked server.
In the left pane, select the General page, and then follow these steps:
In the first text box, type any name for the linked server.
Select the Other data source option.
In the Provider list, click Microsoft Jet 4.0 OLE DB Provider.
In the Product name box, type Excel for the name of the OLE DB data source.
In the Data source box, type the full path and file name of the Excel file.
In the Provider string box, type Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 workbook.
Click OK to create the new linked server.


I keep getting this error:

quote:
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "EXCEL_MY_FILE" does not contain the table "Report 1$". The table either does not exist or the current user does not have permissions on that table.


I went into security and added nt authority\system just in case it was permissions problem and it still gave me the error.

The excel table is called 'Report 1'.

My settings in linked server are as below:

quote:

Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider
Product Name: Excel
Data source: C:\Documents and Settings\UserName\Desktop\Main\FilesIns\MyExcelFile.xlsx
Provider String: Excel 12.0
Location:
Catalog:


Hope someone can tell me what is wrong?

Thanks

G

Update:

I tried running this in sql server:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO


As I was trying to use openrowset to see if that would work but I got error:

quote:
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.


Now I get:

quote:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Syntax error in FROM clause.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "Select * from C:\Documents and Settings\UserName\Desktop\Main\FilesIns\MyExcelFile.xlsx" for execution against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".


I'm not sure if my openrowset values are correct


SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;DATABASE=C:\Documents and Settings\UserName\Desktop\Main\FilesIns\MyExcelFile.xlsx', 'Select * from C:\Documents and Settings\UserName\Desktop\Main\FilesIns\MyExcelFile.xlsx')

Edited by - Grifter on 08/30/2013 04:25:37

Grifter
Constraint Violating Yak Guru

260 Posts

Posted - 08/30/2013 :  07:51:11  Show Profile  Reply with Quote
Panic over just imported it using database > right click > Tasks > Import and use flat file if you are doing this. Also remove column headings in excel/flat file as your sql table already has these.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000