SQL Server - Linked Server

How to import an Excel file using Linked Server

1.- Download the Microsoft Access Database Engine 

http://www.microsoft.com/en-us/download/details.aspx?id=13255

In my case I installed the x64 version, to avoid problems I did through the console.

• Open a cmd console as Administrator
• Change the path where is your AccessDatabaseEngine_x64.exe file
• Execute the file adding at the end /passive, example: 

C:\Users\ERODVEL\Documents>AccessDatabaseEngine_x64.exe  /passive
2.- Open your SQL Server Management Studio

• Review that the Provider has been installed

• Select Microsoft.ACE.OLEDB.12.0 and right click in Properties, and review that the only selected option is "Allow inprocess".

• Other form is by code    

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

• Add a new Linked Server using the following code

EXEC master.dbo.sp_addlinkedserver
    @server = 'ExcelServer2',
    @srvproduct=N'ACE 12.0',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\Users\YOUR_USER\Documents\YOUR_EXCEL_FILE.xls',
    @provstr = 'Excel 12.0;HDR=YES;'

• In the properties, configure the Security 

If not the error will be:
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server 

Queries

To do a query

• Reference the table as [Linked_Server_Name]...[Tab_name$]

Linked Server to another Sql Server Instance

1. Create a New Linked Server
2. In the General section
• Type in Linked Server the name or IP of the sql server to connect
• In Server Type: Select SQL Server,
3. In the  Security Tab, select "Be made using this security context" 
4. Type OK

Using XLSM

When create the Linked Server in the Provider String property @provstr  change to Excel 12.0 Macro, where Macro is the key

EXEC master.dbo.sp_addlinkedserver
    @server = 'ExcelServer2',
    @srvproduct=N'ACE 12.0',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\Users\YOUR_USER\Documents\YOUR_EXCEL_FILE.xls',
    @provstr = 'Excel 12.0 Macro;HDR=YES;' 

Security

To configure the security add  to the script
 
 EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Test',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

Update 

    UPDATE DATA
    SET [Result] = @result
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Macro;HDR=Yes;DATABASE=C:\\SRC\Period.xlsx',
    'SELECT [Result] FROM [Period$] WHERE [Result] IS NULL') AS DATA

References

http://akawn.com/blog/2012/01/query-and-update-an-excel-2010-spread-sheet-via-a-linked-server/

http://vogtland.ws/markedwardvogt/?p=991

http://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm