0

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!

sth
  • 222,467
  • 53
  • 283
  • 367
Amy
  • 23
  • 4
  • possible duplicate of [Moving from 32-bit to 64-bit machine, how to make linked server work](http://stackoverflow.com/questions/3389510/moving-from-32-bit-to-64-bit-machine-how-to-make-linked-server-work) – Remus Rusanu Aug 02 '10 at 18:56
  • Hi, Yes this is a duplicate, I didn't know how to delete the previous one! I want to know if there's a way to execute the sp_addlinkedserver system procedure in 64-bit machine to work, passing the above mentioned parameters. Am trying to get Excel data (sourceFile) and place results in a table, say 'TableTest' I'm quite new to this, so probably my questions are vague, hope this comment makes it a little clearer. Thanks for any help! – Amy Aug 02 '10 at 19:43

1 Answers1

0

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

Craig
  • 1,327
  • 1
  • 13
  • 15