Director of Software Development & Integration for Sta-home Health & Hospice, Inc.Chairman of the Board of Advisors for the Dynamics GP User Group (GPUG)
10+ years with Dynamics-GP (GL, AP, HR, Payroll, Maintenance, Training, Upgrades, Security) 15+ years as a Database Administrator (Maintenance, Performance, Warehousing, Validation, Correctness, Alerts, BI & Reporting)20+ years as a Developer (Written software for IBM, BCBS, Schools, Nursing Homes, & Home Health)
You can follow my blog here or my other networks below:
Any views contained within are my personal views and not necessarily Microsoft policy or the policy of Sta-home Health & Hospice, Inc.All posts are provided "AS IS" with no warranties, and confers no rights.
Blogs Outside of the Dynamics CommunityIn Recovery by Paul RandalSQL Skills by Kimberly Tripp
Post SeriesCreating an EDW for CBI SeriesGetting Data from Point A to Point B
One of the things a DBA does is run some sort of query in Microsoft SQL Server Management Studio (SSMS) and export the results for someone else to use in Microsoft Excel. So let us look into two of the ways to do just that.
In both cases, we will be executing a select statement in SSMS, and to keep things even for both examples we will use the same simple select statement “Select * from upr00100”.
Have SSMS set to display results in Grid mode and execute the query. Here is a screen shot of the results:
Once you have this, open Microsoft Excel to a new blank worksheet, like so.
Then in SSMS first left click on the top left blank box just above the record count to select the entire grid, then right click on the same box. Notice the one that I circled in Red in the following image. Then select “Copy with Headers”, of course, I am assuming that you do want the headers in your Excel Spreadsheet, if not then just select “Copy”.
Then back over in Excel select Cell A1, right click and paste. At this point, other than a few minor Excel format options, that is it.
Common Excel Format Items
The most common of the Excel format options are the width of the cell and Date Time fields. See the following image.
Notice how column “H” does not look like a Social Security Number and Column “I” does not look like a Birth date.
To fix Column “H” all you have to do is the increase the width of the column. To fix Column “I” just format the column as a date field and you get the nice looking Social Security Number and Birth Date as seen below.
Now you are ready to send the file on to your users.
The other option is to send the results directly to a CSV file that Excel can easily open just by clicking on the file name of the CSV file. Now the easiest way for me, is to have the Output format for Text Results set to “Comma delimited”. The menu path is Query >> Query Options then click on “Text” under the Results leaf and set the output format, as shown in the following image.
Set SSMS to send results to file and then execute the query. You should have a dialog box similar to the follow show up.
On this dialog box the first thing to do is to change the “Save as type” to “All Files (*.*).
Then type in a name for the file, and be sure to use the extension of “.CSV”.
Once this is done click on the small down arrow on the right side of the “Save” button select the “Save with Encoding” option.
For “Available encodings”, the “UTF8” option works with the newer version of Excel, but I must admit that I usually select “ANSI” that way I do not have to worry about what version of Excel or whatever program the end use actually uses, as most all programs understand the ANSI format.
Then just click the “OK” Button and you are almost finished.
The only thing left to do is to open the file in Excel and do the same column format things for columns “H” and “I” that we did we did in the first example. See “Common Excel Format Items” above.
Other Microsoft Sites
I'm a Customer
I'm a Partner
Follow Microsoft Dynamics