Force addition/deletion of a linked server and correct syntax to import data into a table from Excel

Question

Hi all, I'm trying to create a linked server in Sql server 2008 R2, just tried downloading the '64-bit version of the Office 2010 Access AD Engine' exe from Microsoft.

Unfortunately, I had tried adding a linked server, BEFORE making this installation, and it failed:

EXEC sp_addlinkedserver 'LinkedServer1', 'Excel', 'Microsoft.Jet.OLEDB.4.0', 'D:\Folder\Excel1.xls', '', 'Excel 8.0', ''

(let me know if this way of adding is not correct if you have another way to do it !)

After making the download, it gave a message that this server already exists : "There are still remote logins or linked logins for the server 'LinkedServer1'."

Tried adding 'LinkedServer2' in the same way AFTER the download (used Excel5.0 as the parameter instead of Excel8.0). It works! However, this old linked server seems to still exist, though I've tried dropping it:

To be sure it's actually there, I wrote this:

select * from sys.servers where is_linked =1 

and the properties in detail:

 server_id: 1
 name= LinkedServer1
 product= Excel
 provider = Microsoft.Jet.OLEDB.4.0
 data_source= D:\Folder\Excel1.xls
 location:NULL 
 provider_string: Excel 8.0 
 is_linked=1 
 is_data_access_enabled=1
 modify_date= 2010-08-15 19:56:02.307

Let me know if you notice me doing something wrong! I tried dropping 'LinkedServer2', it works! However, even when I'm trying to drop the same linked server1 like this:

Exec sp_dropserver @server = 'LinkedServer1'

fails on me, same message! "There are still remote logins or linked logins for the server 'LinkedServer1'."

Tried adding a linked server using provider string as 'Excel 5.0' and it was added and dropped successfully.

---

Another Update: This is failing again: EXEC(' INSERT INTO Table1 SELECT col1, excel2col, excel3col, Replace(Replace(excel4col, ''"'', '''') FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 5.0;HDR=Yes;Database='+'D:\Folder\Excel1.xls'', [Sheet2$])')

Error: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. I have changed configuration to :

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;

Answer 1

Looks like a 64-bit/Sql Server R2 issue:

to delete a linked server explicitly , we should use: sp_dropserver 'LinkedServer1', 'droplogins';

This solves the problem of an invalid linked server. however, the Microsoft.jet.OLEDB.4 is not working, the same error keeps coming up:

<Errors> <Error>ERROR CAUGHT: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.</Error> </Errors>

Tried downloading the Microsoft.ACE.OLEDB.12.0 and used this to add the linked server. However, when I try to do an insert into table using OpenRowSet, a new error comes up:

    Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Anyone faced this before, or knows how to solve this?

Answer 2

What Changes were made to get it to work?