Ir al contenido principal

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 

Solution:
Change the services: SQL Server(MSSQLSERVER) and SQL Server Agent (MSSQLSERVER) to start with the local account
Later start the SQL Server Manager with the local account

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


Comentarios

Publicar un comentario

Entradas populares de este blog

Android - Basic Steps (Service)

Service Run in the main thread of the hosting application Android can kill the Service if need the resources Purpose Supporting inter-application method execution Performing background processing Start a Service Call Context.startService(Intent intent)\ To call from a Fragment use getActivity().getApplicationContext().startService( intentService); Executing the service After call startService(...)  In the Service is executed the method onStartCommand(...) If the method returns the constant START_NOT_STICKY then Android will not restart the service automatically if the the process is killedp Foreground To execute the service foreground call the method startForeground() Use this if the user is aware of the process Bind to a Service Call the method Context.bindService( Intent service ServiceConnection con int flags ) Send Toast from the Service On the method onStartCommand receive the message   ...
Publicar un comentario
Leer más

Android - Basic Steps (Location & Maps)

Location Is composed by Latitude Longitude Time-stamp Accuracy Altitude Speed Bearing LocationProvider Types: Network  Wifi access points Cell phone towers GPS Passive Piggyback on the readings requested by other application Permissions Network  android.permission.ACCESS_COARSE_LOCATION android.permission.ACCESS_FINE_LOCATION GPS android.permission.ACCESS_FINE_LOCATION Passive Provider android.permission.ACCESS_FINE_LOCATION LocationManager System service for accessing location data getSystemService( Context.LOCATION_SERVICE ) Functions Determine the last known user location Register for location update Register to receive intents when the device nears or move away from a given geographic area LocationListener Defines callbacks methods that are called when Location or LocationProvider status change. Methods onLocationChanged(...) onProviderDisabled(...) onProviderEnabled(...) onStatusChan...
Publicar un comentario
Leer más

IIS - Permisions

IIS Permissions To enable the Active Directory connection in the IIS, follow the next steps: Go to IIS Go to Application Pool Select your App Pool Select Advanced Settings in the right side In the section Process Model Select in Identity value the property " NetworkService " You don´t need to restart your application
Publicar un comentario
Leer más