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

BI - SSIS ( Basics I )

SSIS - Basic Concepts Tasks Bulk Insert Task—Loads data into a table by using the BULK INSERT SQL command. Data Flow Task—This is the most important task that loads and transforms data into an OLE DB Destination. Execute Package Task—Enables you to execute a package from within a package, making your SSIS packages modular. Execute Process Task—Executes a program external to your package, like one to split your extract file into many files before processing the individual files. Execute SQL Task—Executes a SQL statement or stored procedure. File System Task—This task can handle directory operations like creating, renaming, or deleting a directory. It can also manage file operations like moving, copying, or deleting files. FTP Task—Sends or receives files from an FTP site. Script Task—Runs a set of VB.NET or C# coding inside a Visual Studio environment. Send Mail Task—Sends a mail message through SMTP. Analysis Services Processing Task—This task processes a SQL Server A...
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