With DBs in different countries, different servers but same structure, I have created registered server to access them when running certain queries for reports on SSMS. Thus far this is ok. However, I would like to enhance this and have the end users be able to extract the data over Excel.(already able to do this but on individual countries). How can I use MS Excel to extract data from registered servers.

All this is on SSMS 2008 r2.

  • It's not clear what users should see. All the data? Data specific to a country? How is Excel accessing the data? Is Excel unable to query a four-part name? – spaghettidba Jan 18 '16 at 20:52
  • end data which is the result from a query, all the data from all the countries. Question is how Excel can access the data. Let me read up on FourPart name. – Wheat Ni Jan 19 '16 at 14:43

If using excel to get the data , you can always depend on external data link function.

Create new excel 2013 and click data ribbon. Click "from other sources", click "sql server" After that follow the wizard. It is better that you create a specific view to pump the data to excel as the wizard only recognize table and view.

If you want to distribute the excel make sure to save the password on the excel file. Also create a user role specific to excel export is advised.

Whenever user want to get a fresh data, just click refresh on data ribbon.

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.