Moving from 32-bit to 64-bit, how to make linked server (sp_addlinkedserver) work

Question

Here is some sample code that worked fine in a 32-bit machine (SQL server 2005) and needs to be updated for a 64-bit... I know this is a common problem but have not been able to find how to fix it!

    DECLARE @sourceFile varchar(255), @testRows int

  SELECT @sourceFile = @xmlInfo.value('(/SelectFile/DataSource)[1]', 'VARCHAR(100)')
  EXEC sp_addlinkedserver 'SomeData', 'Excel', 'Microsoft.Jet.OLEDB.4.0', @sourceFile, '', 'Excel 8.0', ''

   IF @xmlInfo.exist('/SelectFile/DataSource') = 1
   BEGIN
    EXEC(' INSERT INTO TableTest
      SELECT col1_Id, col2, Replace(col3, '' '', '''')
      FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
          ''Excel 8.0;HDR=Yes;Database='+@sourceFile+''', [Sheet1$])')
     SELECT @testRows = @@ROWCOUNT, @totalRows = @totalRows + @@ROWCOUNT
   END

Another thing I'm trying out is to create a .fmt file from a table, using the bcp utility, so I can define the format of the file being read.

Any help would be greatly appreciated!

Answer 1

I think there is still only a 32bit driver for Excel. So i don't know if you'll be able to use a Linked Server using that driver.

Your best bet would be to import the data using the Import Data wizard and save the package (one of the final steps in the wizard) then you can just re-run the package to refresh the data. The client tools are all 32bit so there is no problems using the 32bit driver there