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

Python create package

Create a root folder Create a sub-folder "example_pkg" that contains the funtionallity packaging_tutorial/ example_pkg/ __init__.py In the root folder create the following structure  packaging_tutorial/ example_pkg/ __init__.py tests/ setup.py LICENSE README.md in the setup.py contains the configuration of the packages your package is found by find_packages() import setuptools with open ( "README.md" , "r" ) as fh : long_description = fh . read () setuptools . setup ( name = "example-pkg-YOUR-USERNAME-HERE" , # Replace with your own username version = "0.0.1" , author = "Example Author" , author_email = "author@example.com" , description = "A small example package" , long_description = long_description , long_description_content_type = "text/markdown" , url = "https://github.com/pypa/sam...
Publicar un comentario
Leer más

Rails - Basic Steps III

pValidations Validations are a type of ActiveRecord Validations are defined in our models Implement Validations Go to   root_app/app/models Open files  *.rb for each model Mandatory field validates_presence_of   :field Ex:   validates_presence_of    :title Classes The basic syntax is class MyClass        @global_variable                def my_method              @method_variable        end end Create an instance myInstance = MyClass.new Invoke a mehod mc.my_method class() method returns the type of the object In Ruby, last character of method define the behavior If ends with a question -> return a boolean value If ends with an exclamation -> change the state of the object Getter / Setter method def global_variable       return @global_variable end ...
Publicar un comentario
Leer más

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