title: Force addition/deletion of a linked server and correct syntax to import data into a table from Excel
body: <p>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.</p>
  
  <p>Unfortunately, I had tried adding a linked server, BEFORE making this installation, and it failed: </p>
  
  <pre><code>EXEC sp_addlinkedserver 'LinkedServer1', 'Excel', 'Microsoft.Jet.OLEDB.4.0', 'D:\Folder\Excel1.xls', '', 'Excel 8.0', ''
  </code></pre>
  
  <p>(let me know if this way of adding is not correct if you have another way to do it !) </p>
  
  <p>After making the download, it gave a message that this server already exists :
  <em>"There are still remote logins or linked logins for the server 'LinkedServer1'."</em></p>
  
  <p>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: </p>
  
  <p>To be sure it's actually there, I wrote this:</p>
  
  <pre><code>select * from sys.servers where is_linked =1 
  </code></pre>
  
  <p>and the properties in detail:</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>Exec sp_dropserver @server = 'LinkedServer1'
  </code></pre>
  
  <p>fails on me, same message! 
  <em>"There are still remote logins or linked logins for the server 'LinkedServer1'."</em></p>
  
  <p>Tried adding a linked server using provider string as 'Excel 5.0' and it was added and dropped successfully. </p>
  
  <hr>
  
  <p><strong>Another Update:</strong>
  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$])')</p>
  
  <p>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 : </p>
  
  <pre><code>EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
  GO
  RECONFIGURE;
  </code></pre>