August 4, 2005 at 7:20 am
Ok, here's the code I have so far...
Alter PROCEDURE ExcelAttachWorkbook
@Path nvarchar(4000),
@AttachAs nvarchar(128)
AS
EXEC sp_addlinkedserver
@server = @AttachAs,
@srvproduct = 'Microsoft Excel Workbook',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = @Path,
@provstr = 'Excel 11.0'
EXEC sp_addlinkedsrvlogin @AttachAs, 'false'
Exec ExcelAttachWorkbook 'D:\www\BICCS\SalesForce_ContactReport.xls', '[ServerName]'
Select * from [ServerName]...SalesForce_ContactReport
Getting this error...
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'Local Server'.
OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].
August 4, 2005 at 10:37 am
I probably wouldn;t use linked server.
Try opendatasource instead.
http://www.databasejournal.com/features/mssql/article.php/3331881
August 4, 2005 at 10:48 am
Ok, now I'm getting this error...
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
August 4, 2005 at 10:50 am
Not sure, perhaps your having compatibility issues with the version of excel, and version of the oledb you declared in the opendata source call.
Post your code.
What version of sql server, and what version is the excel document?
August 4, 2005 at 10:55 am
Do you have column names in the XLS?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 4, 2005 at 10:55 am
Excel 2003 and SQL 2000 with latest patches. Here's the code...
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="D:\www\BICCS\SalesForce_ContactReport.xls";
User ID=Admin;Password=;Extended properties=Excel .0')...SalesForce_ContactReport
August 4, 2005 at 10:57 am
Yes, as far as I know.
August 5, 2005 at 10:18 am
I import into SQL from Sales_Force and use the Attached Link Server just fine. Just a qwirk, but try renaming the Sheet name with a single character. SF has mixed characters in the Worksheet naming scheme that the Jet doesn't know how to handle. If that isn't it, I will post the SPs I use.
Also I use this:
exec sp_dropserver 'theServerName' ,'DropLogins'
before adding the linked server.
August 5, 2005 at 10:46 am
Also, make sure the spreadsheet is not open and that it is located on a "Shared" source that can be seen by SQL.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply