Archive for the ‘SQL Server’ Category

Rollback your SQL script w/o restoring the database

Have you ever had to update a table in GP and made a mistake. No! Never right? Then had to restore the database to bring it back before you ran the statement.  Dang!! I forgot to make a backup! How many times have you had to restore before getting it right?

ptg02205064

Well there is in SQL the Rollback and Commit Statements to help rollback your changes in case of the OOPS!! or in case you forgot to back-up the database. Then you Commit in case you are perfect. For a few seconds more, you can save a time and heartache.

 

Example :

  – How to not commit changes in your database until you are sure it works and can roll back the changes without doing  a restore of the database.

–Below is an example of an Update Set command that can be rolled back in case of OOPS!!

– Uses a Specific Database

– Step One

Select * RM00101 where ChekBKID=’Main’

–check the number of rows for transactions

 

– Step Two

Begin Tran

Update RM00101 Set ChekBkId=’New’ where ChekBkId=’Main’

– check the number of rows for transactions to make sure it matches your first select statement

– you can run the select statement again by just highlighting it and make sure it is ’0′ rows and no data.

 

– Step Three or Four

Commit

–run this if everything works great

 

Rollback

– run this if you did something wrong OOPS

 

 

Why my GP is too slow?

Hello all, I would like to provide you with some information on what to look when 312339_117542088417759_1094173370_nDynamics GP seems to be working slow. As you may already know Dynamics GP is a process driven application and you may experience slow performance when specific processes are performed in GP please take a look:

 

· While posting might be due to the PJOURNAL table as you know checks post too or remittance is being printed separately

· Client workstations should have a default printer setup and online, remove invalid printers

· While opening windows, the autocomplete feature may cause performance issues and if not used can be turned off

· While login into Dynamics GP or utilizing 3rd party dictionaries as well if Menu Master table (SY07110) became too large.

· The location of the modified dictionaries other than local workstation

· Certain Smartlist reminders might interfere with login into Dynamics GP

· You may have shortcuts to network locations that are no longer mapped or available

· Printing to file directly into the client/remote computer instead of the hosted server user folders

· OLE Notes path in Dex.ini

· SQL AutoClose and AutoShrink options not set to false

· Virus scanner setup not excluding the following extensions (CNK, DIC, CHM, SET, INI, DAT, IDX, VBA, LOG, LDF, MDF)

· The Dynamics GP homepage smartlist favorites

· The Dynamics GP homepage outlook integration

· Enabling tracing options in DEX.ini

· Bad user defined triggers in SQL

· Bad configuration of SQL server memory allocation

· SQL server or Dynamics GP server available disk space

· SQL server log file is full and is not set to Autogrow

· TNT*.* files, your %TEMP% folder has not been cleaned

· Non-compliant SQL server/GP Server/Client hardware

· Different DB owner than DYNSA

· Little or no SQL server maintenance (Table Fragmentation)

· You might be missing table indexes or statistics

· When exporting a budget, thru the budget wizard it seems locked (if you are using the excel wizard to export, make sure the “save as” dialog is not on the background, Alt-Tab to it as it must have been opened and its behind your main GP windows.

· Too much history (You can archive historical years, specially if you have large tables like Item Master, Customers, Vendors) Believe me I ran reconcile one time and it took 6.5 days on a company with more than half a million SKUs and 5 years of sales.

 

I have witnessed how few administrators that in order to preserve enough disk space they have a tendency of running SHRINK on the SQL server, this obviously will fragment tables affecting performance. I have a post that covers that here.

 

If you want us to take a look at your environment don’t forget to contact us, and as always when troubleshooting record answers for the following questions:

1.- Can you replicate the issue? write down steps that let you reproduce the issue.

2.- If its related to posting, please note the module( s ), how many transactions are in the batch, how long does the process last? how long did the process last before?

3.- On a Server/Client install, can you replicate on the server?

4.- Can you reproduce on all or other clients?

5.- Are there any 3rd party products running on the same SQL/GP server or together with Dynamics GP?

6.- Are there any customizations in GP?

 

Until my next post and let us know if we can help!!

Francisco G. Hillyer

It’s here again: Year End Processes 2012

So just like every year we go thru similar steps, I am providing some help along the way, 598846_4784214609951_774409899_nmany of the processes described here belong to a published KB from Microsoft as well links to posts from other resources but I wanted you to have them all in one place.

Please note that some of the links require you to have access to Customersource/Partnersource.

 

Difference between Year End Update and Payroll Tax Table Updates

The Year End Update download contains software changes to allow you to comply with 2012 filing requirements as well as the most recent fixes for your Dynamics application.

The 2013 Payroll Tax Table Updates contains updated rates and tax changes as well it might contain FICA/Medicare changes to be applied when processing 2013 payrolls.

Here is the link for the 2012 US Payroll Year End Update: Click Here

Here is the link for the US Payroll Tax Updates: Click Here

It is important to mention that you can install the Year end update prior to your last payroll for 2012, however do not install the Tax table updates before your last 2012 payroll as you might end up with a wrong calculation.

Always backup your data, your .DIC files as well.

 

Setting up / Adjusting Fiscal Periods in GL

Microsoft provides a KB article number 871679 KB871679

 

Closing Dynamics GP GL

STEP 1: CLOSE ALL OTHER MODULES

Complete the posting/closing procedures for the modules in the suggested order prior to closing GL, please note that if you are not using a particular module, just skip it:

Follow the instructions provided in each KB

Inventory: KB 872713

Receivables Management: KB 857444

Payables Management: KB 875169

Fixed Asset Management: KB 865653

Analytical Accounting:

For Microsoft Dynamics GP 10.0 Service Pack 2 and greater, functionality was added to consolidate balances for dimensions in Analytical Accounting. Please review KB 960356 to make sure you have properly marked the dimensions that you want to be consolidated  during the year-end process. Please note that there is no separate year-end process that needs to be run in the Analytical Accounting module. When the year-end close process is run for General Ledger, it will automatically consolidate the balances and move the transactions in Analytical Accounting for dimensions that were properly marked.

PAYROLL year end procedures are independent of the procedures in other modules and are usually performed at the end of the calendar year. Please see this KB 850663

STEP 2: POST FINAL ADJUSTING ENTRIES

Adjusting entries are considered most of the time as entries that allow you to correct errors that were made when transactions were recorded as well they might be utilized to assign revenues or expenses to period or periods in which revenues were earned or expenses incurred.

If you need to in setup or adjust your fiscal periods in GP, please review the section “Setting up / Adjusting Fiscal Periods in GL” at the beginning of this article.

STEP 3: VERIFY ACCOUNTS POSTING TYPE

The posting type helps Dynamics GP determine whether an account will be closed to the retained earnings account or if the account balance will be brought forward to the next fiscal year.

Follow these steps to print an account list:

  1. On the Reports menu, point to Financial, and then click Account.
  2. In the Reports list, select All Accounts, and then click New.
  3. In the Option box, type all accounts.
  4. Click to select the Inactive Accounts check box. (if you want to delete inactive accounts)
  5. Click Destination to specify a report destination, and then click OK.
  6. Click Print.
STEP 4: CLOSE FISCAL PERIODS FOR 2012 (OPTIONAL)

Use the fiscal periods setup window to close all fiscal periods open for the 2012 year. This will prevent transactions from posting to the wrong period or the wrong year.

NOTE: If you still use Microsoft FRx, keep one period in the most recent historical year open to prevent the error: “FRX Print Engine Failed to Load the Company Calendar” see this KB 874932

STEP 5: PERFORM MAINTENANCE ON FINANCIAL SERIES (OPTIONAL)

Run the check links procedure on the financial series group of modules.

Make sure that you always have a backup and that you can confirm you can restore from it.

STEP 6: VERIFY SETTINGS IN GL SETUP WINDOW

If you are like me and always want to keep historical records, you must enable the checkbox next to Accounts and Transactions in the Maintain History area of the General Ledger Setup Window.

NOTE: If for some reason you have the checkbox enabled for “Close to Divisional Segments” and you are no longer closing to Divisional Segments or by mistake someone else enabled it and not using it pay attention:

I have discovered in the past a BUG in dynamics GP that is still there as I reported in this blog in August 14, 2012, if you would like to read more about this important step please click here: YEAR END CLOSE BUG 64711

STEP 7: MAKE ANOTHER BACKUP (OR BACKUP IF YOU HAVE NOT)

Make sure all users are out of the system

Remove stranded user sessions, you can follow this post Removing Stranded Sessions

Backup DYNAMICS database

Backup all company databases if you are unsure which databases to backup you can query the company master table (SY01500) and retrieve the names, the following script can help you determine that information.

select INTERID, CMPNYNAM from dynamics..sy01500

Make sure to backup as well the Dynamics GP code folder

STEP 8: PRINT A TRIAL BALANCE REPORT (OPTIONAL)

Use the Trial Balance report window to print a year end detailed trial balance report. Even when this step is optional it is highly recommended to be followed.

STEP 9: PRINT YEAR END FINANCIAL STATEMENTS (MR, FRx, Other)

Print any year end financial statements that are required. The most common are:

  • Balance Sheet
  • Profit and Loss Statement
  • Statement of Cash Flows
  • Statement of Retained Earnings
STEP 10: SETUP A NEW FISCAL YEAR

Before you can perform the year end closing routine, you must setup a new fiscal year, please follow the instructions on the section at the top of this article to Setup or Adjust Fiscal Periods.

STEP 11: CLOSE THE YEAR
  • Click on the Microsoft Dynamics GP Menu > Tools > Routines > Financial > Year End Closing.
  • If its not specified, you can key in the Retained Earnings Account.
  • Optional: Specify the Starting Journal Entry
  • Click on Close Year
STEP 12: BACKUP YOUR SYSTEM

Make another backup of the Dynamics DB and all the company Databases.

 

Thank you for reading I would like to mention that this post enhances Microsoft KB 888003 

 

Until my next post

Francisco G. Hillyer

DYNSA and SQL Maintenance for Dynamics GP

Hello all, it has been quite some time since my last post, kind of missing all of you, specially with the holidays approaching etc.598846_4784214609951_774409899_n

This time I would like to share with you something that I recently learned “the hard way” obviously on a support case.

First of all, I want to express the importance of validating your information and that the engineers/partners that are/were involved in your company setup of Dynamics GP were bound to the best practices established by Microsoft and supported by many of my colleagues.

In my case, a customer did a side by side upgrade of SQL, with this came the issue of not having the DYNSA login in the new SQL instance, we followed certain processes to make sure DYNSA was the owner of the Databases Dynamics GP is using.

But you may ask who or what is DYNSA? my friend Mariano Gomez has a post very complete about this subject and you can find it here: Mariano’s DYNSA Info since I am not reinventing the wheel take a look at Mariano’s blog its packed with information for all audiences (GP related !!).

So when I was working on this customer DYNSA setup, I suddenly remembered another case where I was having issues with a third party, I jumped into their environment (literally) and started investigating this DB configuration, to my surprise the owner of the databases was an AD account not DYNSA. I proceeded to replace the owner and then certain SQL reports started working and producing results. I am still intrigued on why, but I will do a full research on spare time.

So back on the game for this customer issue I was having while trying to archive data, just imagine a SOP30300 table with 11 million records and a huge base of customers.

The queries running were taking countless hours to execute not even mentioning the impact on the processor. memory and of course user experience.

I learned that the customer had a “Maintenance Plan” where they executed the Shrink process on SQL, as you may know I am a SQL enthusiast and I recalled an important blog post from another noted resource Mr. Pinal Dave aka “The SQL Authority” here is his post about why is BAD to shrink a DB Shrink is Bad for you… there is one section in the article that explains that Shrinking a DB to obtain disk space will actually fragment your tables, obviously to reduce fragmentation you rebuild indexes. So this maintenance plan was being executed to reduce disk space, then to improve performance, and the disk space was gone again. Wise words from a mentor that prefers to be in the shadows once said “with current prices on storage why waste time shrinking when you can focus on performance”.

I ended up tweaking some SQL scripts to automate a SQL job on finding fragmented tables in the DB and executing that as part of DB maintenance, as I said “go buy another disk drive and add it to your server, move the logs to this new disk and keep data apart from the logs and you will be better than now”.

If you need help, let us know, our team at RoseASP and RBS we have experience in solving this type of issues.

I hope my experience helps you for a better community.

 

Until my next post

Francisco G. Hillyer

Why my integration is changed? Sort-wise

Its not new for support dealing with Integration Manager crazy requirements, transformations, translations, scripts and many things that are often missed.282962_4217420760459_350721136_n

Even customers are truly surprised when I show them how Integration Manager can pull information from one company or even better from another SQL server into Dynamics GP as a source.

Yes I know you are surprised too that I am telling you that your source can be a SQL Query Script…….

 

We have many ways of sorting data in integration manager, we even have filters and sort fields on the source definition, but nothing beats the customer frustration when they have a sorted file and Integration Manager scrambles the file, then open up the distributions for example on a JE import and see that the order is not even close to what the source file have.

 

One thing important is that there is a setting in the IntegrationManager.ini file. This setting is called UseOptimizedFilter normally I see this option with the value True. 

When this option is enabled, Integration Manager uses a series of ninja techniques to optimize the data upload but that has an impact on the sort order.

 

If you want to have the distributions ordered or your transactions in the order you have them in your source file, close your Integration Manager, go to this file located on the Integration Manager code folder and set to false this setting.

Try your integration once again and voila!!!! Its just like magic, everything is the way you had it on your source file.

 

I am hoping to save techs from headaches here, and if this tip helps you, let us know, we want to know if our work can actually influence a better Dynamics community.

 

Francisco.

How to remove the Connect section on the HomePage in Dynamics GP (via SQL)

Hello fellow readers, a few of us work in environments where we support many 282962_4217420760459_350721136_nusers or even better many instances of GP at a time.

 

Lately for many of our customers the Connect section has not been working as expected, for a few its just an ugly Error 404: File or directory not Found.

Error 1

 

Or simply it has been getting in the way of Integration Manager. Even when many colleagues like Mariano Gomez, Mark Polino, David Musgrave, Leslie Vail and others have provided help to the community on this issues. One question remained unanswered: How to massively remove it without the administrator login into each account?.

Famous engineer Tami Jones from the RoseASP team came up to my beach front office with a wonderful view of the Pacific ocean (ok it’s a 3 wall cubicle with no view) and asked me how can we automate this process as you know we team up with myGPcloud as well.

 

So I decided to take a look at the homepage behavior and came up with the following descriptions:

 

SectionID Value 1 is the ToDo Section
SectionID Value 2 is the Outlook Section
SectionID Value 3 is the Metrics Section
SectionID Value 4 is the My Reports Section
SectionID Value 5 is the Quick Links Section
SectionID Value 6 is the Connect Section

 

The table responsible for storing the values to display or hide those sections is SY08100 in Dynamics DB. So here is the script that will help you remove the connect section:

 

Code 1

With a little tweak you can limit the update statement to work only on 1 user or a set of users.

Always remember to backup your database and try statements in a test DB.

 

Have a great day!!!

Francisco

How Do You Back Up Your GP System?

These are the results of our January Poll:

How do you back up your GP system

Removing stranded user sessions in Dynamics GP via SQL Server

In today’s post I want to share a SQL script that I considered very important, lately I worked in  cases  were  I  have  been  witness  on  how  other  partners/customers  deal in SQL to remove stranded users. 

A few do this task from inside GP, others run the following code: 

Delete from Activity where userid = 'XXXX"

But  lets  analyze,  they  are  not  really  removing  a  stranded  user  and  all  of  the  activity associated  with  the  user, what  they are really doing is just removing the record from the activity table so that Dynamics GP does not see the user, but what happens if they remove via  SQL  or GP the wrong user id? if the user is logged into 2 or more different companies at the same time?. 

Messages like “The selected document is being edited by another user” sounds familiar? 

Let  me  explain  to  you how this SQL script works and then I’ll provide it to you remember with this script you don’t need the users to log off from Dynamics GP. 

SQL server maintains a record of active user sessions in the table SYSPROCESSES from the  MASTER  database, inside sysprocesses there is a column named “login name” and we  base  our  script  on  sysprocesses  to  clear  the  ACTIVITY  table from the DYNAMICS database. 

Once ACTIVITY table has  been  cleaned out we are ready to clean 2 tables from the TEMP database  first  we  clean  DEX_SESSION and then DEX_LOCK in order to eliminate locks and processes in temp tables. 

The next step is to clean batch activity (SY00800) and resource activity (SY00801) in order to have a valid session clean up. 

Here is the code: 

delete from DYNAMICS..ACTIVITY
  where USERID not in (select loginame from master..sysprocesses)
delete from tempdb..DEX_SESSION
  where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)
delete from tempdb..DEX_LOCK
  where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00800
  where USERID not in (select USERID from DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00801
  where USERID not in (select USERID from DYNAMICS..ACTIVITY)

Note: Make  sure  you  have  a  backup  of  your  databases  when  running  scripts  that can modify your data. 

Also  I am including the KB’s published that show how to remove records from DEX_LOCK and DEX_SESSION that working together make the script I just provided. 

A batch is held in the Posting, Receiving, Busy, Marked, Locked, or Edited status in Microsoft Dynamics GP (KB 850289)

How to remove all the inactive sessions from the DEX_LOCK table in the TempDB database when you use Microsoft Dynamics GP together with Microsoft SQL Server (KB 864411)  

How to remove all the inactive sessions from the DEX_SESSION table in the TempDB database when you use Microsoft Dynamics GP together with Microsoft SQL Server (KB 864413)

Blog >> SQL View >> Dynamics GP >> Excel

Retrieving information from Dynamics GP for reporting purposes is so easy to do because there are several great tools available and there are many sources of information on how to use the tools and specific code to make and build reports.

Victoria Yudin writes a blog in which she generously shares SQL code that allows you to easily create SQL views to retrieve information from Dynamics GP.  Victoria wrote this article this week, and in it includes code for creating a SQL View that retrieves information about payables invoices and associated purchase order line details.  This is fantastic.

You can easily copy the code from her blog and paste it into a new query using SQL Server Management Studio.  Execute the query and it will create the view.  The SQL view can be used in Excel and SmartList Builder to create SmartLists that all users can have access to.

I created this short video that shows how easy it is to do.

Creating SmartLists using SmartList Builder and SQL Views

There are a number of GP bloggers that often include SQL Query Language as part of their posts.  This is a rich source of specific problem solvers.

SmartList Builder allows you to easily take advantage of these resources to provide data inquiry and reports.

These are the steps to use once you’ve found a SQL Query that would be useful:

  • Create the new SQL View using the SQL Query
  • Grant access to the new view
  • Create the SmartList using the SQL View

I created this video to show you a simple example of how to do it.

Victoria Yudin is one GP Blogger that regularly includes SQL Queries in her posts.  Check it out.

This is the specific post I referenced in the video.

Subscription Options:
Subscribe via RSS
Articles Categories