4

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

Improve this question
4
  • 2
    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?
    – Mike Walsh
    Feb 10, 2012 at 16:41
  • Yes, more data please!
    – ErikE
    Feb 11, 2012 at 2:49
  • 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.
    – ConcernedOfTunbridgeWells
    Feb 15, 2012 at 16:22
  • Why must you use a linked server?
    – World Wide DBA
    Sep 5, 2012 at 19:55

3 Answers 3

Reset to default
3

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.

Improve this answer
0

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.

Improve this answer
0

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.

Improve this answer

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.