Microsoft Dynamics GP DBA

Two ways to Export data from SSMS to Excel

John Lowther — Fri, 17 May 2013 04:43:00 GMT

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”.

Option 1

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.

Option 2

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.

Till later,

Wondering what I am up to??? Read this other post

John Lowther — Mon, 31 Dec 2012 16:01:21 GMT

So, while everyone is enjoying their New Year’s eve and New Years day. What am I up to?

Read this to find our. ==> 2012 End of Year

till later,

Deprecated Database Engine Features in SQL Server 2012

John Lowther — Fri, 28 Dec 2012 19:00:00 GMT

I do not know if everyone has seen this but you really should go take a look!

You can find it here ==> Deprecated Database Engine Features in SQL Server 2012

Now, for me there is not too many things that list that make that much of a difference to me. Things change, that is life. The only constant is change itself.

However, there are some things under the “Features Not Supported in a Future Version of SQL Server” (Deprecated) that I would like to point out so that everyone notices them and gets ready. And you know me, sometime I have got to add my two cents. (Note: In no specific order.)

Feature ID 15 -- The old FILE_ID is changing to FILE_IDEX
My 2 Cents, where is the “N” as in FILE_INDEX?

Feature ID 3 -- Three-Part and Four-Part column Reference is going away only Two-Part will be allowed.
My 2 Cents, I really hope that this does not mean what I think it does on my first glance. I will have to dig into it some more.

Feature ID <None given> but as I type this it is just under 163 -- End Transact-SQL statements with a semicolon(;)
My 2 cents, I really hope that is added to the IntelliSense to automatically do that when I hit the enter key. Otherwise I am in a lot of trouble, and that’s not mentioning all the code I will have to go back and change.

Feature ID 186 -- Use of @, @@ as Transact-SQL identifiers.
My 2 Cents, hum, I am going to have to think about that one.

Community Content Section

Now, under the Community Content section, is a comment about Feature ID 112, and while I can see that persons point completely, I have to admit for the most part I actually (and this is IMO (In My Opinion)) choose to disagree.

Please keep in mind that everyone has a right to their opinion and we can all agree to disagree.

But in my humble opinion anything added to NULL should be NULL.

Think about math for a second, and number times zero is zero. Additionally if you try to divide by zero you get an error as you should. So, with that thought in mind, any string plus NULL should be NULL unless you explicitly code something to prevent it like the ISNULL function. If you divide by some variable you should always check that the value of that variable to make sure it is not zero. This at least to me is pretty much the same.

Anyway, these are all just my 2 cents worth at the time of this posting.

I reserve the right to an opinion.

I reserve the right to be wrong. (I am only human after all).

Additionally, I reserve the right to change my opinion as I learn more.

And feel free to comment, send me an email, contact me anyway you wish, and disagree with me. I will be more than willing to listen-n-learn and possibly change my opinion. It has happened lots of times before, and I am sure that it will in the future.

Till later and have a happy holiday,

Disk and File Layout for SQL Server - SQL Server Team Blog - Site Home - TechNet Blogs

John Lowther — Wed, 26 Dec 2012 19:42:16 GMT

Here is an excellent post on Disk and File Layout for SQL Server

till later,

Finding out what the values mean in a static field in a Dynamics GP Database

John Lowther — Tue, 27 Nov 2012 16:37:11 GMT

Check out what one of my coworkers figured out! Additional Field Information

Just goes to show to never get too old to learn

Till later,

The Dynamics GP Blogster: Microsoft Dynamics GP Excel Dashboards

John Lowther — Tue, 27 Nov 2012 03:14:45 GMT

Mariano Gomez does a fantastic job of describing some of the research that Microsoft is doing with Excel Dashboards in Microsoft Dynamics. Be sure to read his post here for all the details ==> Microsoft Dynamics GP Excel Dashboards

Till later,

Reporting Resources for Dynamics GP | Dynamics GP Insights

John Lowther — Fri, 23 Nov 2012 17:42:43 GMT

Christina Phillips has a great list of Reporting Resources for Dynamics GP

So, what are you waiting for, go check it out!

Till later,

Speechless

John Lowther — Wed, 03 Oct 2012 18:06:18 GMT

For anyone that knows me, I think they will all agree that I have the “Gift of Gab”. In other words, In other words I am not afraid to talk.

However, on October 1, 2012, Microsoft found a way to make me utterly, and totally speechless!

I received an email that stated “Congratulations! We are pleased to present you with the 2012 Microsoft® MVP Award!”

I must admit I stared at the email for several minutes in utter disbelief.

No words can truly express the honor I feel to have received such a prestigious award and I will do my utmost best to live up to the ideas and standards of that award.

Thank you Microsoft,

Till later,

Blog Posts by Mark Polino

John Lowther — Mon, 17 Sep 2012 20:43:35 GMT

First, I have to admit I have no idea how one person can blog so much, but somehow he manages to out blog me by an unimaginable amount.

So, rather than create an individual post for each thing he has posted over the last few days I will just give you a list.

Leslie Vail takes us inside Installing the GP 2013 Web Client–roll up your sleeves!...(read more)

There are a ton of blog entries coming out of the technical airlift. I thought about trying to lump some of them together and then I just gave up so...buckle up and enjoy. Dynamics University starts us out with info on Supporting Microsoft Dynamics GP...(read more)

We've covered a bunch of the now free PSTL tools. When Microsoft made the PSTL tools free they also made some other tools free including POP Over Receipt Tolerance. This tool allows a receipt tolerance percentage for quantities. A setup window allows...(read more)

Mariano has coverage of Microsoft Dynamics GP Technical Airlift 2012: Day 1...(read more)

The newest GP2013 Feature of the Day:Update Back Order Item Qty in Field Service...(read more)

Ian Grieve has multiple posts on How To Install Microsoft Dynamics GP 2013 (Beta) including: Server Configuration Client Demo Company...(read more)

Charles Allen asks Are You Still Here? – How to Know Who’s Still in Dynamics GP...(read more)

News out of Fargo from Dynamics Corporate Performance Management: Web-based Report Viewer debuts ....(read more)

I've got a new SmartList resource available. This is a front and back quick reference guide to SmartLists in Dynamics GP. It’s similar to ones you've seen at office supply stores for applications like Word and Excel. The format is designed to...(read more)

It’s not unheard of for someone to make a mistake with depreciation. Maybe an asset was put into service early for example. Well if you need to undo depreciation for a single asset in Dynamics GP it’s very easy. Simply go to the Routine for Depreciate...(read more)

Now, you might think that covers several weeks of blog post, but atlas no, it is just two days for him. Maybe one day he will tell me his secret.

till later,

GP2themax - Helping users get the 'max' from Dynamics GP: Field Service Business Alerts

John Lowther — Wed, 12 Sep 2012 23:33:40 GMT

Frank Hamelly shows us how to add Field Service to Dynamics GP Business Alerts.

Check out his post here ==> GP2themax - Helping users get the 'max' from Dynamics GP: Field Service Business Alerts

till later,

GPUG DBA SIG (2012 - 09 - 20) Voting for Topics

John Lowther — Wed, 05 Sep 2012 18:38:26 GMT

All the request for our next webinar are in and the top three are ready for your votes and comments.

You can register for the webinar here.

till later,

Getting entire databases from one point to another.

John Lowther — Wed, 05 Sep 2012 18:10:00 GMT

This is an addendum to the Getting Data from Point A to Point B series.

When I first started this line of thought I was thinking more about SQL Queries, Triggers, etc.; however, as time when by I realized that there was an incredible need to get massive amounts of data from one place to another.

At that point I started looking at Replication, Mirroring, Snapshots, all kinds of things that would let me get entire databases from the application server to some place that I could then work on it and move that information into the Data warehouse. Please keep in mind that one of my goals is to NOT affect the performance of the Application Server just because I am working on building a warehouse.

So, after much testing of the various methods I somewhat fell into one that I had never realized would be the best option in my environment. Now at this point please keep in mind that this does depend upon the exact environment. But before I go into what I stumbled upon please allow me to briefly describe some (not all) of the other options that I did not go with.

This is where I started and for both of them I quickly through them out. Please do not go down this path!

SQL Queries pulling data directly out of the production databases.
- Pros - you are getting the data straight from Dynamics GP which would reflect any changes made at the time that you ran your query.
- Cons - If SQL Server is running your query then it is not serving a users request. As the number of your own queries goes up. And trust me, over time you will have a lots of them. This begins to degrade the performance of Dynamics GP. And that is not even mentioning the upgrade nightmare you can fall into if you put your own queries into the actually Dynamics System or Company databases. Note: If for some reason you do need to pull data directly out of Dynamics GP. Do yourself a favor and create another unrelated database that contains the stored procedure that then looks at the Dynamics System or Company databases. That practice could possibility save you a lot of headaches when it comes time to upgrade.
SQL Triggers - directly in the Dynamics Company databases.
- Pros - You are getting the data the moment it changes.
- Cons - As with SQL queries this rapidly leads to performance problems, and I will not mention the upgrade problems again. Just reread the cons on SQL Queries again please.

So, how do we get large amounts of data from once place to another? The overview is really quite simple. Copy the entire database or at least most of it. The trick is to do this in a manor that does not hurt performance. Now I have already mentioned Replication, Mirroring, Snapshots, not to mention a few others. Additionally as there is already a lot of documentations about each of these methods with all of there pros and cons, so I will skip that part and get right into what I came up with and my exactly environment as the environment is part of the solution.

So the winner at last, after months of testing. Log Shipping. Yes, you heard right, log shipping.

Now before I go any further think about this. All of us probably have all of our Dynamics GP databases set to Full Recovery, and we are all probably making full and transactional backups on some schedule.

Now here is my environment, as I understand it from my network admin, (hey, nobody, especially me knows everything ) and I will try to make sense out of my drawing as I translate what I learned from them.

Ok, the Application SQL Server has a set of teamed network cards that are the connection between the users and the databases.

The Application SQL Server also has another network card that connects it to a SAN. This is the connection that is used when a SQL backup is made.

Note: the SQL Backup is NOT on a local drive nor does it use the same network as the users to get out of the SQL server.

The Translation SQL Server (my name for the SQL Server that imports information from multiple sources, combines and cleans that information up for transport to the Consolidated Business Intelligence (CBI) SQL Server. (I will discuss that in a future post.) Also has multiple teamed Network cards to the CBI SQL Server, in addition to a network card to the SAN itself. Remember, that is the location of the SQL Backups. The SQL backups being on the SAN that both SQL servers can access without using the network pipes going to the users is the key to the success of this approach.

Once the Application SQL Server makes a Translation Log backup the Translation SQL Server restores that backup to it's log-shipped databases. I am then free to run SQL queries to pull what ever data from those databases that I want without having any affect on the Application users what so ever. Further more, as the SQL backups are on a SAN that uses different data paths from the users, rather than on a local drive to the Application SQL Server the act of restoring those databases has no impact on the performance of the Application SQL Server in serving up data to the users.

Once I have the entire database over to my Translation SQL Server I can start building my Stage One Database that I mentioned in this post.

So, there you have it.

Feel free to drop me a line and let me know how it goes for you.

till later,

About Dynamics, Development and Life: Data Warehouse / Business Intelligence Solution for Dynamics GP–Part 1 Overview

John Lowther — Wed, 22 Aug 2012 18:52:05 GMT

MVP Jivtesh Singh starts a series of post on building a Data Warehouse using Kimball’s approach.

Check out his first post of the series located ==> Data Warehouse / Business Intelligence Solution for Dynamics GP–Part 1 Overview

And here is his second post of the series ==> Data Warehouse / Business Intelligence Solution for Dynamics GP–Part 2 Kimball Approach

till later,

You have to Read and Never Stop Learning!

John Lowther — Tue, 21 Aug 2012 15:31:35 GMT

While I posted this on my Accidental Administrator site I wanted to make sure everyone saw it as it really applies to everybody.

So have a few giggles on me as you read my post You have to Read and Never Stop Learning!

till later,

GPUG DBA SIG (2012 - 09- 20) Topic Request

John Lowther — Tue, 21 Aug 2012 13:02:11 GMT

To enter your request for the list of topics to be voted on please take this quick three question survey located here.

After the results are in I will post the list of general topics for everyone to vote for their favorite.

You can register for the webinar here.

till later,

Cleaning up after a Payroll Error

John Lowther — Mon, 20 Aug 2012 14:18:57 GMT

If during the Payroll Process the Payroll Clerk gets an error to the effect of "an Open Operation error, a Get/Change error, or a Save Operation error "

Then give these scripts a try:

--Find let us find out where the error actually is:
-- I like to know what I am about to delete!

Select * From DYNAMICS..UPR10300
Select * From DYNAMICS..UPR10304
Select * From UPR10200
Select * From UPR10201
Select * From UPR10202
Select * From UPR10203
Select * From UPR10204
Select * From UPR10205
Select * From UPR10206
Select * From UPR10207
Select * From UPR10208
Select * From UPR10209
Select * From UPR10213
Select * From UPR19900
Select * From UPR19901

Now, that I know what is in these tables, and have the chance to back up that information up somewhere should I feel the need. Let us clear them out with this section of SQL Code:

Delete DYNAMICS..UPR10300
Delete DYNAMICS..UPR10304
Delete UPR10200
Delete UPR10201
Delete UPR10202
Delete UPR10203
Delete UPR10204
Delete UPR10205
Delete UPR10206
Delete UPR10207
Delete UPR10208
Delete UPR10209
Delete UPR10213
Delete UPR19900
Delete UPR19901

till later,

Vote for the GPUG 2012 All Star

John Lowther — Fri, 17 Aug 2012 17:41:23 GMT

Each year, GPUG recognize 3 Premium Members who represent the community well by having:
Courage – The strength to persevere, hold the talent to teach fellow GP users.
Passion – For those who share their GP and technology passion with others.
Resolve – Willing to take the time to help others with challenges they face.
Advancement – The pursuit of individual and organizational growth and development.

There are some utterly great nominees this year so let your voice be heard by voting!

Here is the link to place your vote: GPUG ALL Star Award Survey.

till later,

Jonathan Kehayias | Identifying High Compile Time Statements from the Plan Cache

John Lowther — Thu, 19 Jul 2012 22:01:23 GMT

Jonathan Kehayias digs into Identifying High Compile Time Statements from the Plan Cache.

till later

GP - Multiple Company Support in Single Database | Microsoft Connect

John Lowther — Thu, 19 Jul 2012 21:32:48 GMT

Ever wanted needed multiple companies but wanted the advantages of a single database? Go here to vote on this item in Microsoft Connect ==> GP - Multiple Company Support in Single Database | Microsoft Connect

till later,

How to use Word Mail Merge and Macros to Import Data - Developing for Dynamics GP - Site Home - MSDN Blogs

John Lowther — Thu, 19 Jul 2012 21:20:45 GMT

I was nicely surprised on my GPUG DBA SIG webinar today when someone told me about a post from my friend David Musgrave on How to use Word Mail Merge and Macros to Import Data. Check it out!

till later,

Alerts: 401k Setup

John Lowther — Wed, 18 Jul 2012 22:02:07 GMT

For years I have been writing alerts to let either myself of someone else know that there is a problem somewhere so that it can be fixed before it becomes a major issue. Now, over the years I have boiled my alerts down into a few generic types. And while I will keep you guessing as to the types of alerts themselves, I will give you the code to one of them for your review.

Now, I can just about assure you that this code will not work on your system as is. You will need to modify it to fit your needs. Things like who gets the alert, your database names, the actual name of your 401k deduction and benefit codes, etc.

I hope that most of it is understandable for most people but as always if you have any questions feel free to drop me a line, (just remember to put GPUG) in the subject line.

Here is the link to the code on the GPUG Share My Code

this later,

SQL Script for Account Level Security - Developing for Dynamics GP - Site Home - MSDN Blogs

John Lowther — Fri, 13 Jul 2012 15:19:00 GMT

Check out Sivakumar's post on SQL Script for Account Level Security

till later,

Integration Manager: Error - The 'Microsoft.ACE.OLDB.12.0' provider is not registered on the local machine

John Lowther — Thu, 12 Jul 2012 14:05:58 GMT

Frank Hamelly has not one but two resolutions. Check out his post at GP2themax 'Microsoft.ACE.OLDB.12.0' provider is not registered on the local machine

till later,

Notes on Performance for Microsoft Dynamics GP

John Lowther — Wed, 11 Jul 2012 18:49:09 GMT

When it comes to the performance of Microsoft Dynamics GP one of my favorite links is to the Optimizing and Maintaining Performance for Microsoft Dynamics GP 2010 white paper on Customer Source. But rather than just downloading the file I keep a link to it because it is updated as new things come out.

till later,

Deadlocks caused by Implicit Conversion

John Lowther — Fri, 29 Jun 2012 16:47:28 GMT

Sans SQL has a good post on how implicit Conversion can call deadlocks in SQL Server. Check out their post at Sans SQL: Deadlock - Implicit Conversion

I would also like to thank Vaidy for letting us know about this in his post Implicit Conversions Causing Deadlocks in SQL Server.

Till later,