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 '18 at 6:50
  • Then, how I will make the folder as OBDC object ?? – Zubair Sultan Feb 20 '18 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 '18 at 10:43

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)
            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
            MessageBox.Show("New link has been created.")
        Catch ex As SmoException
            ls = Nothing
            If s.ConnectionContext.IsOpen = True Then
            End If
        End Try

    End Sub
End Class

I hope this help you.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

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