how to copy data from SQL Server table to excel Spreadsheet thru Linked Servers

Question

I try to copy data from a table in SQL Server 2005 into a excel spreadsheet. Since that sheet is linked to some external charts, so I have to overwrite the existing data with the updated data from SQL Server table. I want to do it through linked servers. But failed several times. Do someone knows how to do it?

Thanks in advance

• Mike Walsh: Can you give more details on the failure? Error message? Symptoms? What provider are you using for the linked server? 64 Bit or 32 Bit?
• ErikE: Yes, more data please!
• ConcernedOfTunbridgeWells: The question isn't clear. Are you trying to load the data into excel through MSQuery - with a query that joins something in the local server against a table in the remote server? If that's the case and MSQuery's spitting the dummy on the syntax you could try putting a view over it and pointing MSQuery at the view.
• Mr.Brownstone: Why must you use a linked server?

Answer 1

I think you are looking at this from the wrong point of view. You don't want SQL to upate your spreadsheet, you want your spreadsheet to connect to your database and get the latest data.

Create a local DSN -- using "setup data sources (ODBC) -- then connect to that data source from excel.

Answer 2

You can use SSIS (SQL Server Integration Service) for convert data from each source such as MSSQL to each distination such as excel. create package is ssis for convert data and run it each time you want copy data to excel file.

Answer 3

You can connect directly from Excel to SQL Server using the "Get External Data" option under the Data menu in Excel - you should see a SQL Server option. The only pre-requisites are that you have an account with permission to query the database and that you have the appropriate drivers installed on your machine.

I hope this helps you.