Microsoft Dynamics GP DBA

SQL view for SOP sales by customer by month

2013-06-13T03:45:00Z

Victoria is at it again! Check out SQL view for SOP sales by customer by month.

Till Later,

I admit defeat.

2013-06-10T20:13:24Z

Ok, so I was not able to post at least something each day at Tech-Ed 2013.

I blame it totally on Microsoft Windows Surface. Not that there is anything wrong with the Surface, but that I was able to buy a Surface Pro and a Surface RT at unbelievable prices, and well, then I just had to play with my new Surfaces, (Surfaci) what is the plural of Surface in this context?

Anyway, give me a little more time to play with them and I should be back to posting. Hey, they are really great!

till later,

Decisions at Tech-Ed NA 2013

2013-06-05T14:29:49Z

Decisions, decisions, decisions, no matter how hard I try I still cannot figure out how to be in two places at once.

Maybe it is something that I am just doing wrong, or I am just not smart enough to figure it out.

So, what do I do, lots of excellent classes, but I cannot seem to decide between, a SharePoint Security class, a Business Intelligence class, or a Data Warehousing class, all of which are at the exact same time.

Guess, I hit one of them and then download the slides and video of the other two.

till later,

Microsoft Tech-Ed North America 2013

2013-06-04T19:40:05Z

WOW,

So much to learn, fantastic speakers, experts, and networking.

So little time to blog.

till later,

Another Day at Microsoft Tech-Ed

2013-06-03T19:27:02Z

Man, oh man, just soaking up the knowledge on SQL Server, SharePoint, Visual Studio. How much more can I pack into my head?

till later,

SQL Server Data Tools

2013-06-02T18:14:04Z

To summarize SQL Server Data Tools (SSDT) in a single word, WOW!

till later,

Pre-Con Class at Microsoft Tech-Ed

2013-06-02T15:24:20Z

Enjoying a great Pre-Con class “How to Be a Successful DBA in the Changing World of Cloud and On-Premise Data”.

Really looking forward to applying this for Microsoft Dynamics GP and my Data Warehouse!!!

Till later,

Trying to Blog while attending a Conference

2013-06-02T15:22:35Z

Starting today I will be attending Microsoft’s Technical Educational (Tech-Ed) North America 2013 in New Orleans, LA.

Now, if you haven’t noticed I am usually not very successful at blogging while I am at a conference, but I am at least going to try and make amends and post some of my experiences while I am here.

So, let us keep our fingers crossed and see how successful I am at this.

Till later,

Doug Pitcher’s OFFICIAL 100 most famous, awesome and totally influential Dynamics people for 2013

2013-05-25T19:44:37Z

Ok, so I am playing catch-up on my blogging

Be sure to read Doug Pitcher’s OFFICIAL 100 most famous, awesome and totally influential Dynamics people for 2013 on his Rose Business Solutions, Inc. Blog post!

Till Later,

Two ways to Export data from SSMS to Excel

2013-05-17T04:43:00Z

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

2012-12-31T16:01:21Z

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

2012-12-28T19:00:00Z

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

2012-12-26T19:42:16Z

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

2012-11-27T16:37:11Z

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

2012-11-27T03:14:45Z

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

2012-11-23T17:42:43Z

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

2012-10-03T18:06:18Z

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

2012-09-17T20:43:35Z

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

2012-09-12T23:33:40Z

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

2012-09-05T18:38:26Z

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.

2012-09-05T18:10:00Z

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

2012-08-22T18:52:05Z

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!

2012-08-21T15:31:35Z

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

2012-08-21T13:02:11Z

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

2012-08-20T14:18:57Z

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,