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

C# Using tabs

To use tabs in C# use the TabContainer element from AjaxControlToolkit Include AjaxControlToolkit  Include in the Web.config file, inside the tag <system.web> the following code  <pages>       <controls>         <add tagPrefix="ajaxCTK" namespace="AjaxControlToolkit" assembly="AjaxControlToolkit"/>       </controls>     </pages>   Include TabContainer element First  include TabContainer element that is the section where all the tabs will be displayed. <ajaxCTK:TabContainer ID="TabContainerUpdate" runat="server"                 Height="800"                 CssClass="ajax__tab_style"> </ajaxCTK:TabContainer> Second per each tab include the following code corresponding to each ...
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

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