Unrecognised format Linked Server Excel 2007

Karl_mark
Karl_mark used Ask the Experts™
on
I've setup a linked server to an Excel 2007 spreadsheet using the following:

sp_addlinkedserver 'Products', 'ACE 12.0', 'Microsoft.ACE.OLEDB.12.0', 'C:\Visit\Thales\ProductList.xlsx', 'Excel 12.0; HDR=Yes'

Open in new window


sp_addlinkedsrvlogin 'Products'

Open in new window


Both commands execute successfully and I can see the linked server in SQL server when I run:

select * from master.sys.servers where is_linked = 1

Open in new window


When I try and query the linked server using:

select * from openquery(Products, 'select * from [Products$]')

Open in new window


I get the following message:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Products" returned message "Unrecognized database format 'C:\ProductList.xlsx'.".

I have installed the Microsoft package for connecting to Office 2007, so I don't think that is the cause. Any other reason for this error, or is there something wrong with the way the server is being linked?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I assume that one of the sheets in the file is named Products.

 How about if you just try something like

select * from Products...Products$

 That's how I normally use a linked server to an Excel file.  I'd only use OpenQuery if I was trying to read the Excel file directly and not via a linked server.  I'm not saying that your OpenQuery won't work.  I've never tried to do anything like that (and don't really see the point).

 James

Author

Commented:
I still get the same error if I use:
select * from products...products$
There may be something wrong with your sp_addlinkedserver options.  I'm not familiar with those specific options, so I can't say for sure.  The fact that it's an Excel 2007 file might also be a factor.  I've only used older .XLS files.  You might try saving your .XLSX file as an .XLS file, just to see if that works.  Even if you ultimately need to use an .XLSX file, trying an .XLS file might help identify the problem.

 James
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks James, I switched track a little and set up a DTS script to import the data (which has to be saved as .XLS as it's SQL 2005).
This solves the problem of accessing the data, but the issue is that I want, ideally, to push data back to the spreadsheet. I had problems on my development laptop before where once I installed the ACE.12 stuff I could no longer get JET to connect to older office files. If I can get ACE to work, can I use that to connect to XLS, or is it better to use JET (Which I think I read somewhere is quicker anyway)?
I really can't say, because I've just always used Jet.  That was what I used first and it seemed to work well enough for my limited purposes, so I never looked for an alternative.  I don't think I've ever used ACE.

 If you want to try to get some other opinions in here (maybe someone who can answer your Jet vs ACE questions), you can try the Request Attention link and see if you can get a mod to send up a flare.  :-)

 James

Author

Commented:
OK. I'll post the current connection code and then ask for attention....
It is setup as shown in the attached code. I've excluded the part where it sets up the linked server login as this makes no difference to the error message anyway. THe code successfuly creates the linked server as I can see it when I query the SQL Server. However, trying to query the data gives me the error message shown in the original question.


 Private Sub btnImportProducts_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportProducts.Click
        Dim ObjOpenFileDialog As New OpenFileDialog
        Dim strImportType As String, strFilePath As String
        Dim sql1 As SqlCommand
        Dim intRecordsUpdated As Integer
        Dim prm As SqlParameter


        ObjOpenFileDialog.Title = "Please specify path for file"
        ObjOpenFileDialog.InitialDirectory = "C:\"
        ObjOpenFileDialog.Filter = "Excel Workbooks| *.xlsx"
        ObjOpenFileDialog.RestoreDirectory = True
        ObjOpenFileDialog.ShowDialog()
        strFilePath = ObjOpenFileDialog.FileName.ToString

        strImportType = "Products"
        If CheckLinkedServer(strImportType) = True Then
            If DeleteLinkedServerLogin(strImportType) = True Then
                DropLinkedServer(strImportType)
            End If
        End If

        sql1 = New SqlCommand

        'Add Serrver parameter
        prm = sql1.CreateParameter()
        prm.ParameterName = "Server"
        prm.Direction = ParameterDirection.Input
        prm.SqlDbType = SqlDbType.VarChar
        prm.Size = 128
        prm.Value = strImportType
        sql1.Parameters.Add(prm)

        'Add ProductName parameter
        prm = sql1.CreateParameter()
        prm.ParameterName = "srvproduct"
        prm.Direction = ParameterDirection.Input
        prm.SqlDbType = SqlDbType.VarChar
        prm.Size = 128
        prm.Value = "ACE 12.0"
        sql1.Parameters.Add(prm)

        'Add Provider Parameter
        prm = Nothing
        prm = sql1.CreateParameter()
        prm.ParameterName = "Provider"
        prm.Direction = ParameterDirection.Input
        prm.SqlDbType = SqlDbType.VarChar
        prm.Size = 128
        prm.Value = "Microsoft.ACE.OLEDB.12.0"
        sql1.Parameters.Add(prm)

        'Add datasource parameter



        prm = Nothing
        prm = sql1.CreateParameter()
        prm.ParameterName = "DataSrc"
        prm.Direction = ParameterDirection.Input
        prm.SqlDbType = SqlDbType.VarChar
        prm.Size = 128
        prm.Value = strFilePath
        sql1.Parameters.Add(prm)

        'Add Provider Parameter
        prm = Nothing
        prm = sql1.CreateParameter()
        prm.ParameterName = "Provstr"
        prm.Direction = ParameterDirection.Input
        prm.SqlDbType = SqlDbType.VarChar
        prm.Size = 128
        prm.Value = "Excel 12.0; HDR=Yes"

        sql1.CommandText = "sys.sp_addlinkedserver"
        sql1.CommandType = CommandType.StoredProcedure
        Dim strConnectionString As String = ConfigurationManager.ConnectionStrings("WindowsApplication1.My.MySettings.ConnectionString").ConnectionString()
        Dim objConnection As New SqlConnection(strConnectionString)

        Try

            objConnection.Open()
            sql1.Connection = objConnection
            sql1.ExecuteNonQuery()
            objConnection.Close()
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try



    End Sub

Open in new window

The addlinkedserver string specified C:\Visit\Thales\ProductList.xlsx , but the error message was about C:\ProductList.xlsx . Is this how it really was, or you mistyped when posting?

Author

Commented:
Not the actual solution, but made me check sys.servers!

Author

Commented:
The entry in sys.servers was wrong. The value which should have been in provider_string was in product, and provider_string was empty. By running the code below I got it working.
EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'G:\Development\ProductList.xls',
   NULL,
   'Excel 8.0'
GO

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial