Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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 '12 at 16:41
    
Yes, more data please! –  ErikE Feb 11 '12 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 '12 at 16:22
    
Why must you use a linked server? –  Mr.Brownstone Sep 5 '12 at 19:55
add comment

3 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.