I am working on MS SQL server management studio 2014. Using linked Server, I can link Excel File as a Linked Server. I know how to link. But I want to know how can I link 5 files of Excel on a Linked Server or I have to create separate Linked server of each excel files?

Thanks in advance,

  • Why don't you connect the FOLDER as ODBC object so that you can use any file! – Rajesh S Feb 20 at 6:50
  • Then, how I will make the folder as OBDC object ?? – Zubair Sultan Feb 20 at 9:09
  • It's simple, make a Folder and copy all the Excel files and while create ODBC object provide the Folder name with Connection String with full path. Then after whenever you use the ODBC abject all the Excel files will be accessible. – Rajesh S Feb 20 at 10:43
up vote 2 down vote accepted

You would setup it using either OLEDB provider or the provider for ODBC drivers and create a connection using the ODBC Administrator tool on the server to the Excel file.

If you are suppose to read from Excel file on a regular basis then this method is very useful.

Also, you can use SQL Server Management Objects SMO to configure Excel data source as Linked Server, using VBA code.

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim s As Server
        Dim conn As ServerConnection
        Dim ls As LinkedServer

        conn = New ServerConnection("ServerName\InstanceName", "YourUesrName", "YourPassword")
        s = New Server(conn)
        Try
            ls = New LinkedServer(s, "XLTEST_DMO")
            With ls
                .ProviderName = "Microsoft.Jet.OLEDB.4.0"
                .ProductName = "Excel"
                .DataSource = "c:\book1.xlsx"
                .ProviderString = "Excel 9.0"
            End With
            ls.Create()
            MessageBox.Show("New link has been created.")
        Catch ex As SmoException
            MessageBox.Show(ex.Message)
        Finally
            ls = Nothing
            If s.ConnectionContext.IsOpen = True Then
                s.ConnectionContext.Disconnect()
            End If
        End Try

    End Sub
End Class

I hope this help you.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.