How To Detect SQL Server 2005 Instances Installed on a Single Machine

Have you ever wondered if there is a programmatic way to detect all the SQL Server instances on a single machine? Well, no worries, copy the following code given below and save it as SQLInfo.Vbs and run this script using C:\CScript SQLInfo.Vbs or just dobule click on the script.

Contents of SQLInfo.Vbs:
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")
Set colItems = objWMIService.ExecQuery( "SELECT * FROM SqlService",,48)
For Each objItem in colItems
      Wscript.Echo "-----------------------------------"
      Wscript.Echo "SqlService instance"
      Wscript.Echo "-----------------------------------"
      Wscript.Echo "DisplayName: " & objItem.DisplayName
      Wscript.Echo "ServiceName: " & objItem.ServiceName
      Wscript.Echo "SQLServiceType: " & objItem.SQLServiceType
Next

Is your Microsoft Dynamics GP Database to Large?

Company Data Archive (CDA) is a 3rd party tool for Microsoft Dynamics GP by Professional Advantage that provides an easy way to move historical information from your 'live' company to another 'archive' company. Archiving your data will reduce the size of your live company database and improve system performance while posting, running reports, performing file maintenance, and day-to-day use of Dynamics GP. Company Data Archive allows you to enter in a cutoff date for historical data and all transactions on or before that date will be moved to the archive company. The archived transactions will still be available to inquire and report on by simply logging into the archive company as you would any other Dynamics GP company or take advantage of powerful cross company inquiries to view transactions from your live and historical companies at the same time. The archive can also be performed periodically - for example on a quarterly or annual basis. 

CDA will archive data from:

• General Ledger
• Bank Reconciliation
• Receivables Management
• Payables Management
• Inventory
• Purchase Order Processing
• Sales Order Processing
• Payroll

Check out the product in this quick demonstration:  http://www.profad.com/files/CDA/Company Data Archive Demonstration.wmv 

Dynamics GP on Windows 7 and Windows Server 2008 R2

With the release of Microsoft Windows 7 and Server 2008 R2 on the horizon (slated for an October 22, 2009) many companies will once again be faced with the decision whether to upgrade or not. You can rest assured that if you are running Dynamics GP 10 on one of the latest service packs (SP3 or SP4) that it will already be compatible with Windows 7 and Server 2008 R2 from the time you install your new operating system. The caveat to this is that Microsoft has no plans to make releases prior to Dynamics GP 10 (versions 9.0 and prior) compatible with the new operating systems.

Please take note, however, that Integration Manager is not yet supported in Windows 7. This could simply mean that Microsoft has not completed testing or that there will be some sort of Hotfix or Service Pack requirement for Integration Manager to be supported.

SmartSort for Dynamics GP

Ever wanted to change the default sort method for a lookup in Dynamics GP? Ever wanted to customize a lookup for a specific user or group of users? And SmartSort is the free utility you’re looking for.

Demonstration

Download for SmartSort

Micorosft Dynamics GP Version Retirement Dates By Microsoft

Microsoft Dynamics GP Version	Retirement Date
GP Version 8	Retired (no longer supported by Microsoft)
GP Version 9	10/12/2010
GP Version 10	No Retirement Date Set At This Time

Microsoft usually only supports the two most recent version of Microsoft Dynamics GP. Microsoft will typically support a product for the following 24 months after the release of a subsequent version or service pack.

Keeping current with service packs and version releases provides Clients with the newest patches and newest features. It also ensures that you can get on going support from your local support provider or directly from Microsoft.

How to Gain Financial Stability

People always complaint about their financial instability, but they don’t do anything by themselves to make their bad financial state good. If they take care of only few little things, then they can get financial stability. Though, it’s not easy to gain a stable financial state, as it takes years to reach financial stability. But with only a little wise planning and consistency, people can become financially strong. Following are 8 effective ways by which you can become financial strong, let’s have a look on them.

Spend Less – Save More

Always try to less than what you actually earn. That’s because the less you spend the more you can save, and the more you save the more you become financially strong. Financial problems arise from the point, where people spend more than they earn. So try to avoid it as much as you can.

Follow a Budget

This should be included in your financial routine in any way. Prepare a budget that suits you and then follow this budget. Budget also helps you to keep a record of your spending. A settled budget will not allow you to spend more and it will restrict you to spend on unnecessary things.

Cut Off Luxuries

Check your earnings and then decide that do you really need to spend on luxuries even if you can live without them. For instance, if you can live with small 22” TV, then why do you want to purchase 52” TV? Etc.

Save Something for Rainy Day

Wow! What a true old saying. Even if you save little money in a separate account, it will work for you after retirement. So let the time work for you and save so9mething for rainy days.

Do You Have Extra Income?

Try to save your bonuses, tax refunds, or any other saving into a saving account separately. Try not to spend this income and forget it when the itch of spending this money rises in your hand.

Open a Roth IRA

Open a Roth IRA and contribute to it. You can take out the principle amount without any penalty if you have a backup fund for this. The amount that you have contributed to this account is after tax and the interest that you’ve earned on it is not tax deductible if you take it out after the age of 50 ½.

Prepare a Back up Fund

Keep this fund reserve for emergencies only. For building up this back up fund, start saving little amount every month or as per your convenient. Don’t touch this fund without any emergency.

Save Money for Major Purchases

If you want to purchase big items, then you should have a separate saving coffin for them. When you saved sufficient amount to purchase big items, then go to purchase them otherwise make your life habitual to live without them as long as you can.

WMI configuration error in SQL Server System Configuration Check

Select following text from "@echo on" to ":End" and copy into notepad. Then Save As "fixwmi.cmd" or "fixwmi.bat" in "C:\" and then from run command, type "CMD" then press Enter, from command line type "C:\fixwmi.cmd" or "C:\fixwmi.bat" and press Enter.

It takes several minutes to complete and at points it appears that it is not running but it is. After it is complete, you see the ":END" statement then start the SQL server installation again. Also, don't forget to add yourself as follower of this blog, thanks.

Contents of FIXWMI.CMD

@echo on
cd /d c:\temp
if not exist %windir%\system32\wbem goto TryInstall
cd /d %windir%\system32\wbem
net stop winmgmt
winmgmt /kill
if exist Rep_bak rd Rep_bak /s /q
rename Repository Rep_bak
for %%i in (*.dll) do RegSvr32 -s %%i
for %%i in (*.exe) do call :FixSrv %%i
for %%i in (*.mof,*.mfl) do Mofcomp %%i
net start winmgmt
goto End

:FixSrv
if /I (%1) == (wbemcntl.exe) goto SkipSrv
if /I (%1) == (wbemtest.exe) goto SkipSrv
if /I (%1) == (mofcomp.exe) goto SkipSrv
%1 /RegServer

:SkipSrv
goto End

:TryInstall
if not exist wmicore.exe goto End
wmicore /s
net start winmgmt
:End

NOTE: Administrator privileges might be required to run this script.

Whats Hidden in Microsoft Word

This should work in any version of Microsoft Word. Just open up a new document, type =rand() and hit Enter. Three paragraphs of text will be instantly added.

You can add more than 3 paragraphs or more than 5 sentences per paragraph by typing =rand(paragraphs, sentences), for 6 paragraphs of 20 sentences each, type =rand(6, 20).

You can also use =lorem() instead of rand(), with the same syntax if you wanted to insert the Lorem Ipsum text instead of completely random text.

Hidden Games in Microsoft Excel 2000

Follow the instructions to launch Trigger the egg game:

• Open a new workbook Press F5, type X97:L97 and press Enter.
• Press Tab Press Ctrl+Shift and Click on the Chart Wizard button from the toolbar.

You will be greeted with a full-screen animated image.  Use the mouse to “fly” over the landscape (the mouse changes direction, left button moves forward and right button moves backwards).  Fly around a bit and you will see a grey stone pyramid.  Fly to the side of the pyramid that’s black and you’ll see the credits scroll by for the developers.

Now, follow the instructions to launch a car racing/shooting game:

• Open a new workbook in Excel 2000
• Save as a web-page. Give any file name you wish. In the save as Window, select Selection:Sheet, add Interactivity and Publish as web-page. Click Publish
• Now, open your saved web-page.
• Reach Row 2000, column WC.
• Use “Shift+Space bar” to select whole row.
• Press ALT key+ Shift + Ctrl and click on the Microsoft Logo on the top left handed side of the sheet
• Enjoy the hidden game. The game is car chase/driving game. It gives you control of a car and you get to shoot other cars. Also has unlimited lives.

Tricks for working more efficiently in Microsoft Outlook

1. Save a few keystrokes when entering dates
When entering the start and due dates for a new task, you don’t have to type the entire date. If a date is in the current month, just enter the day and Outlook will enter the rest of the date for you. For instance, if the current month is March and you enter 14, Outlook assumes you mean March 14 of the current year and fills in that date. When a date isn’t in the current month, you can still save a few keystrokes by entering the month and day. Outlook will fill in the year as follows:

1. If the month and day haven’t occurred in the current year, Outlook uses the current year.
2. If the month and day have passed, Outlook uses the next year.

2. Control how you print notes
If you use the Notes feature to jot down questions, ideas, or quick reminders, you might also want to print them occasionally. By default, Outlook prints each note on a separate page, which might or might not be what you want. You can force Outlook to fill each page, to save paper or to keep related notes together. Select the notes you want to print and then choose Print from the File menu. In the resulting Print dialog box, deselect the Start Each Item On A New Page check box in the Print Options and click OK.

If the option is disabled, you’re using HTML format. You must switch to plain or text format to enable this option. To do so, choose Options from the Tools menu. Click on the Mail Format tab and choose Plain Text from the Compose In This Message Format option’s drop-down list. Then, click Apply and OK. Repeat the print instructions, and you’ll find the Start Each Item On A New Page option is enabled. After printing the notes, just retrace your steps to reset your format setting.

3. Print e-mail when it arrives
For a variety of reasons, some of us end up printing e-mail messages. If you print most of your messages, or all of your messages from a specific source, opening each message to print it manually interrupts your work. Setting a rule to print the desired incoming mail might be more efficient. To do so:

1. From the Tools menu, choose Rules And Alerts.
2. Click New Rule on the E-mail Rules tab.
3. Click Start From A Blank Rule at the top of the resulting Rules Wizard dialog box.
4. In the Step 1 box, highlight the Check Messages When They Arrive option (it should be the default) and then click Next.
5. In the Step 1 box, select the Where My Name Is In The To Box check box (or whatever option applies, if you don’t want to print all you messages) and click Next.
6. In the Step 1 box, click the Print It option and click Next.

At this point, you can identify exceptions to the rule, but we won’t do that here. Just click Next, and then Finish, Apply, and OK to return to Outlook.

After setting up the new print rule, Outlook will print every incoming message that meets your requirements. Now, the chances are that you won’t want to print every message. So make good use of the conditions offered in steps 5 and 7 to limit the messages Outlook prints.

4. Store sent mail efficiently
When you reply to an e-mail, Outlook stores a copy of that message in the Sent folder. If you’re like me, your Sent folder has thousands of messages. If you need to find a specific message later, you must sort through all those messages, and that takes time. Instead, store your replies with the original message. For instance, suppose you automatically route all your messages from your boss into a folder named, appropriately enough, MyBoss. If you want Outlook to store your replies in MyBoss with the original messages, do the following:

1. From the Tools menu, choose Options.
2. On the Preferences tab (which should be selected by default), click E-mail Options in the E-mail section.
3. Click Advanced E-mail Options at the bottom of the Message Handling section.
4. In the Save Messages section, select the In Folders Other Than The Inbox, Save Replies With The Original Message check box and then click OK three times to return to Outlook.

Outlook will apply this setting to all of your personal folders. Just remember that Outlook saves replies with the original message and not in the Sent folder only when the original message is in a folder other than the Inbox. Outlook continues to save all replies sent from messages in the Inbox in the Sent folder.

5. Create a Flags toolbar
Quick Flags help you categorize your messages, usually by some level of importance or by task. For instance, you might use a red flag to mark messages that need a quick response and a blue flag to mark messages on which you’ve acted and are waiting for a response. The problem with Quick Flags is that there’s no way to customize their descriptions. Outlook identifies them only by color. You can’t change the name of Red Flag to Critical. Remembering what each color represents can
become burdensome.

1. An easy way to remember what each flag represents is to create a custom toolbar that displays each flag with text that means something to you. Fortunately, the process is easy:
2. From the Tools menu, choose Customize.
3. On the Toolbars tab, click New and name the new toolbar appropriately (for instance, you might name it “Flags”) and click OK.
4. In the Customize dialog box, click the Commands tab.
5. Select Actions from the Categories list box.
6. Next, drag the appropriate flag color buttons from the Commands list to the custom toolbar. (If you can’t find the toolbar, look behind the dialog box.)
7. After adding all the flags you want, change the text for each flag button. Right-click a flag button and replace the Name setting, e.g., &Red Flag, with something more helpful, such as “Critical.” Select the Image And Text option so that Outlook will display the button’s name on the toolbar.
8. Complete steps 6 and 7 for each flag button in your new toolbar.

6. Modify the scope of your Calendar work week
By default, the Work Week calendar view displays the five days of the traditional business week, Monday through Friday. To include Saturday and Sunday in that view, choose Options from the Tools menu. In the resulting Options dialog box, click Calendar Options in the Calendar section. In the Calendar Options dialog box, check Sat and Sun in the Calendar Work Week section. Then, click OK twice to return to the Calendar.

You don’t have to view a seven- or traditional five-day work week. Check the days of the week that apply to you for a custom work week view. For example, if you work Wednesday through Sunday, you can make those selections to build a view that reflects your schedule.

7. Use color to identify messages from specific senders
Expecting important mail? Identify it as soon as it comes in by displaying it in a distinctive color. Start by select an existing message from the sender in question, if you have one. If you don't, that's okay; you can enter the sender’s name manually. Now follow these steps:

1. In Mail, choose Organize from the Tools menu.
2. In the Ways To Organize Mail pane, click the Colors link on the left side.
3. In the first condition statement (we won’t use the second), choose From in the first drop-down list.
4. If you chose a message before starting, the sender’s name will appear in the text box to the right. If it’s the
5. wrong name, enter the right name or the person’s e-mail address.
6. Choose a color from the second drop-down list.

Click Apply Color and close the pane. Afterward, Outlook will display all messages, existing and new, from the person you specified in step 4 in the color you selected in step 5.
8. Distinguish incoming mail from existing mail
By default, Outlook displays incoming mail as bold text until you read it. If you need a bit more help, consider displaying unread mail in a bright color. To do so, you’ll work in a view, not a rule:

1. In Mail, choose Inbox.
2. Choose Arrange By from the View menu.
3. Select Current View and then Define View from the subsequent submenus.
4. In the Custom View Organizer dialog box, check the option and click Modify.
5. Click Automatic Formatting.
   In the Automatic Formatting dialog box, click Font.
6. In the Font dialog box, choose a color from the Color drop down list, and click OK three times. Then, click Apply View.
7. Outlook will display all unread mail in the Inbox in the color you choose in step 7. Doing so won’t display unread messages automatically routed to other folders. Since this is a view, it works only on the current folder. However, you can set up a similar view for any folder you like. In addition, the unread mail formatting takes precedent over colors used to identify mail from a specific sender (see Tip #7).

9. Force replies to reach multiple recipients
A problem arises when you need recipients to reply to everyone receiving a message. The responding recipient must remember to click Reply All instead of Reply. If the recipient forgets to click Reply All, you’re the only one who will see the reply, forcing you to forward it to everyone else, which is inefficient. Before you send the message, you can configure it to reply to everyone:

When composing the message, click the Options button on the Formatting toolbar.

1. Choose Options from the resulting drop-down list.
2. In the Message Options dialog box, check the Have Replies Sent To option in the Delivery Options section, which will automatically enter your e-mail address.
3. Next, click the Select Names button to the right and select all the appropriate recipients.
4. Click Close to return to your message.
5. When any recipient responds to the message, regardless of which reply choice they click, the reply will go to everyone you specified in the Have Replies Sent To option.

10. Create a temporary work week in the Calendar
The Work Week calendar view displays a week view, and you define what constitutes a work week (see Tip #6). If you need to see more or less than the usual work week, you can temporarily change that view. In the Date Navigation pane, click the first date you want to view. Then, hold down the [Shift] key and press the last date in the period. Outlook automatically adjusts the Calendar Work Week to include all of the dates in the selected time period. Alternately, you can view a group of non-contiguous dates by holding down [Ctrl] instead of [Shift] as you click dates. Outlook will display the days in order, regardless of how you select them.

Adding Extra Information in Active Directory

Adding extra information against domain users in Active Directory for creation of link between Intranet website or any other external software. Just suppose, we want to add "Attendance ID" and "Payroll ID" fields in Active Directory (AD). Before starting this process, please make sure that an successful system State Backup of Domain and Active Directory has been done. First, you install the Windows Server Support Tools from the Windows Server CD-ROM (Support/Tools), then perform the following steps:

• Install the Schema snap-in (Start, Run, regsvr32 schmmgmt.dll)
• To add the employeeID and employeeNumber attributes to the person class, open the Schema console, run MMC then from Add/Remove Snap-in, select Active Directory Schema, and from Schema console navigate to Classes, Person. Right-click Person and select Properties, select Attributes, select admincontextmenu, click Edit, click Add, then locate and click employeeID. Click OK. Click Add again. Locate and click employeeNumber, then click OK twice to close both dialog boxes.
• Open the Active Directory Service Interfaces (ADSI) Edit utility, then navigate to Configuration Container, CN=Configuration, CN=DisplaySpecifiers, CN=409.
• In the right-pane, locate and right-click CN=user-display, and select Properties.
• In the Edit Attribute box, type &Attendnace ID, attend_id.vbs (Please find "attend_id.vbs" contents, bottom of this post) again click Add, and type &Payroll ID, payroll_id.vbs (Please find "payroll_id.vbs" contents, bottom of this post)
• These steps configure the options Attendance ID and Payroll ID on the context menu for a user in the Microsoft Management Console (MMC) Active Directory Users and Computers snap-in.
• Please copy these two VB Script files on any shared location for accessing from PDC and ADC.

After performing these steps, we will able to define Attendance ID's from Attendance System and Payroll ID's from Payroll System against all the users of Active Directory.

Contents of ATTEND_ID.VBS:
Dim oVar
Dim oUsr
Dim tmp
Set oVar = Wscript.Arguments
Set oUsr = GetObject(oVar(0))
tmp = InputBox("The Attendance ID of the user is: " & oUsr.employeeID & vbCRLF & vbCRLF & "If you would like enter a new ID or modify the existing ID, enter the new ID in the textbox below","Attendance ID")
if tmp <> "" then oUsr.Put "employeeID",tmp
oUsr.SetInfo
Set oUsr = Nothing
WScript.Quit

Contents of PAYROLL_ID.VBS:
Dim oVar
Dim oUsr
Dim tmp
Set oVar = Wscript.Arguments
Set oUsr = GetObject(oVar(0))
tmp = InputBox("The Payroll ID for this user is: " & oUsr.EmployeeNumber & vbCRLF & vbCRLF & "If you would like enter a new ID or modify the existing ID, enter the new ID in the textbox below","Payroll ID")
if tmp <> "" then oUsr.Put "employeeNumber",tmp
oUsr.SetInfo
Set oUsr = Nothing
WScript.Quit

Copy these contents and save as "ATTEND_ID.VBS" and "PAYROLL_ID.VBS".

Setting up Client Workstations for Microsoft Business Portal, GP

In order to required smoothly running of Business Portal on client workstations, perform following procedures on client workstations for smoothly running of Business Portal:

• Set browser settings for a workstation

Be sure that the Internet Explorer options for client workstations are set so content is refreshed. Also, Internet Explorer security should be set up so that ActiveX controls are allowed to run on the Business Portal web site. If ActiveX controls are not allowed to run, users won’t be able to use result viewer web parts. Internet Explorer options are set for each user on a single computer as follows:

1. Start Internet Explorer and open the Internet Options window from Tools > Internet Options.
2. Click the General tab. Under Browsing history, click Settings.
3. The Temporary Internet Files and History Settings window is displayed. Be sure the Check for newer versions of stored pages option is not set to Never.
4. Click OK to close the settings window.
5. Click the Security tab, highlight Trusted Sites, and click Sites to open the Trusted Sites window and also clear the “Require server verification (https) for all sites in this zone” check box. If you can’t clear the check box, you must change the security settings for the Local Intranet zone instead of adding Business Portal to your Trusted Sites list.
6. In Add this Web site to the zone, enter the Business Portal URL and click Add.
7. Click OK to close the Trusted Sites window.
8. In the Internet Options window, be sure Trusted Sites is still selected and click Custom Level to open the Security Settings window.
9. In the ActiveX controls and plug-ins section, select Enable for the following settings:
• Download signed ActiveX controls
• Initialize and script ActiveX controls not marked as safe for scripting
• Run ActiveX controls and plug-ins
10. Click OK an alert message will be displayed, asking if you’re sure that you want to change the security settings for the zone, Click Yes.
11. Click OK to close the Internet Options window.

• Pop-up blockers

Pop-up blocker software on client workstations may interfere with some Business Portal pages. It is recommended that configure pop-up blockers to allow pop-ups within the Business Portal URL. If your pop-up blocker doesn’t provide this option, please remove or disable pop-up blocker software on Business Portal client workstations.

• Install Microsoft Office XP Web Components

In order to export information to Excel from a Business Portal client workstation, Microsoft Office XP Web Components must be installed, regardless of the version of Office you're using. The Office XP Web Components can be downloaded from the Microsoft Download Center.

Four Ways To Manage Your Cash With eBanking in Microsoft Dynamics GP

Looking for ways to increase your cash flow, make sure your money gets there on time, monitor cash balances, and reduce check fraud?  We have many customers looking at these exact issues and finding the suite of eBanking products available to Business Ready Advanced Management customers a perfect fit.

Safe Pay
After each check run, you can send a file to your bank with the correct check number and amount and the file is loaded into its system.  The banks call this process “positive pay”.  When the check is presented for payment, only the amount you sent will be debited from your account.  Any other check showing a different amount will be refused.  Every company needs to use this to prevent check fraud.

EFT for Payables
You pay vendors online via your bank’s wire transfer or ACH system now.  Why not transmit this information directly from Dynamics GP to your bank and take out several steps and opportunities for error?  Set up the NACHA format used by most US banks and you can generate a file to upload to the bank’s web or FTP site right after the check run is done with no process changes for payables staff.  The simple setup involves the file verification and pre-noting the participating vendors to verify the accounts and ABA numbers.

EFT for Receivables
An even better way of managing your cash flow is to receive your cash receipts via ACH.  Set up the required format used by your bank and you can download a file to upload the day’s cash receipts from your customers.  There is some setup involving the file verification and verifying the participating customer’s accounts and ABA numbers.

Electronic Reconcile
You don’t want to spend more time that necessary validating your cash in the bank.  Download daily, weekly, or monthly your cleared checks directly into the Bank Reconciliation window and you will always know the amount of cash available that day.  Use the time spent to click off the cleared checks for more productive analysis.

Virtual PC for Microsoft Dynamics AX

Microsoft Dynamics AX 2009 Virtual PC - Refresh 1, The Virtual PC image of Microsoft Dynamics® AX 2009 (Refresh 1) enables you to demonstrate the features of Microsoft Dynamics AX 2009 using a single PC or laptop computer.  This VPC image contains an installation of Microsoft Dynamics AX 2009 with Service Pack 1. The demonstration VPCs also include a range of integrations to Microsoft servers and application software.

Microsoft Dynamics GP 10.0 Product Download

You can download the Microsoft Dynamics GP 10.0 CD images in U.S. English or International English. The CD images contain the installation of the Microsoft Dynamics GP business application, and also contain separate installations for customization and integration tools such as the following:

Tool	Location
Extender	Feature option for the standard Microsoft Dynamics GP client installation from CD1
Modifier with Visual Basic for Applications (VBA)	Installed as part of the standard Microsoft Dynamics GP client installation from CD1
Integration Manager	Install from Additional Products on CD2
eConnect Runtime	Install from Additional Products on CD2
Web Services Runtime	Install from Additional Products on CD2
Dexterity	Install from the \Tools\Dex\ folder on CD2
Continuum APIs	Install from \Tools\Continuum\ folder on CD2
Microsoft Dynamics GP SDK	Install from \Tools\SDK\ folder on CD2

• Download using PartnerSource.
• Download using MSDN. On the MSDN Subscriptions Downloads page, choose Microsoft Dynamics > GP > GP 10.0 to view the available downloads.

The Closer for Microsoft Dynamics GP

Remove the tedious and time consuming process of ticking and tying your ledgers in Dynamics GP.  Completely balance your General Ledger with your Inventory, Sales Order, Accrued Purchases, AR, AR, COGS and Cash Sub-Ledgers. Fully reconciled and balanced subledgers and transaction details enables you to have the ultimate base for reporting and making informed decisions.

Benefits Of The Closer

• Explains the cause of the problem
• Suggests corrective actions
• Ensures GL and Sub-Ledgers tie to the penny
• Builds trust with Microsoft Dynamics GP information
• Eliminates manual ticking and tying
• Reduces month end closing time
• Eliminates write-offs
• Ensures financial reports and operational reports agree
• Reduces audit and compliance risks
• Identifies problem transactions

By: www.reporting-central.com/closer.htm

Custom Link in Microsoft Dynamics GP

Dynamics GP provides a great feature to tie web page links to specific values in Dynamics GP. For example, when a bank account is selected a link is made available to that bank's website. The link is contextual, meaning that it is tied to the value in the field. In this recipe, we'll look at setting up and using the Custom Link feature.

Before users can benefit from this recipe, an administrator needs to set up the custom links. To set up the links:

• Select Administration from the Navigation Pane on the left. Then click on Custom Link under the Company heading.
• Click on New on the bottom left.
• In the Prompt field select Checkbook. In the Custom Link Labelfield type Website.
• In the Field Value box click on the lookup button (indicated by a magnifying glass) and select the FIRST BANK checkbook.
• Type www.firstbank.com into the Internet Address field as the bank's website and then click on Save to save the record.

Congratulations, that's all there is to setting up a custom link.

Now we'll see how to use a custom link by completing the following steps:

• To demonstrate how the custom link works for users, click onFinancial on the Navigation Pane. Select Bank Deposits from theFinancial Area Page under Transactions.
• In the Checkbook ID field use the lookup button (indicated by a magnifying glass) to select the checkbook used in the previous setup. Once the checkbook shows in the Checkbook IDfield click on the Checkbook ID label.
• A field will drop down with two options. One, marked Checkbook Maintenance, leads back to the Checkbook Maintenance window. This is the typical behavior if Custom Links have not been set up:

• Select the second link, Website, to open up a web browser and navigate to the website set up in the link.

This process associates website links with values in specific fields. This allows contextual drill through to web pages for more information. Custom links can be created for Checkbooks, Credit cards, Currency, Customers, Employees, Exchange Rates, Items, Salespeople, Tracking Numbers, and Vendors providing plenty of options to link to more information on the Internet.

A single site can be made available for all of the choices or multiple choices can be applied to a single value. All field values Selecting the All field values checkbox means that the website entered will be used for all values in this field. For example, if a single currency website is used for all currencies, selecting the All field values checkbox points all currency values to that one website. This option is available for prompts named:

• Checkbook
• Credit Card
• Currency
• Exchange Rate
• Tracking Number
• Multiple values

For Custom Links the Prompt field is the key identifier. There can be multiple prompts for a single value, creating more than one link for a value. For example, a bank might have a website used to check balances and a completely different one for processing ACH transactions. By creating two links, one named Balances and one named ACH, both links can be available for a single checkbook.

Speeding Up Lookups with Advanced Lookups in Microsoft Dynamics GP

Dynamics GP provides a very robust functionality in lookup windows for finding data such as accounts, vendors, customers, items, and more. Various fields can be used for sorting or searching and some additional fields are always provided by default. However, if all of that is not enough, Dynamics GP provides an option for administrators to add additional fields to lookups. This recipe demonstrates how to accomplish that.

Before using Advanced Lookups they need to be set up. Up to four custom lookups can be created for each type in the system. We will do this as follows:

• Select Administration on the Navigation Pane, and under the Company heading select Advanced Lookups.
• On the Advanced Lookups Setup window use the drop-down menu to select a Lookup Name.
• For our example, select Customers. In the first Sort by Field drop-down menu scroll down and select Zip.
• Change the Description to Zip Code.
• Click on Save to save the lookup and close the window.

These setup steps add a lookup based on zip code to any place where Customers are selected in the system:

Once an advanced lookup has been set up, let's look at how to use it by completing the following steps:

Select Sales from the Navigation Pane on the left. Select Customer on the Sales Area Page.
In the Customer ID field click on the lookup button (indicated by a magnifying glass) and click on the arrow next to Additional Sorts. A predefined set of lookups is shown at the top of the drop-down menu and customer lookups are at the bottom. In the middle is the Zip Code lookup created previously.
Click on Zip Code and the Zip codes will appear on the right-hand side of the window. The search box at the top also changes to allow searching by selected lookup. In this case, will be Zip code:

Administrators get the chance to set up four extra lookups for each of the lookup options.  Lookup options include:

• Accounts
• Addresses
• Customers
• Employees
• Items
• Open documents
• Open Payables documents
• Prospects
• Purchase orders
• Sales Document Numbers
• Sales documents
• Vendor addresses
• Vendors
• Vouchers

This provides users with more opportunities to ensure that they are selecting the right information with a minimum amount of work.

Improving Financial Reporting Clarity by Splitting Purchasing Accounts in Microsoft Dynamics GP

By default, in Dynamics GP, when a payables invoice is fully or partially paid, the payment portion of the transaction doesn'tfl ow through the payables account. This can make it more difficult to trace a transaction as the transaction could skip the payables account altogether by crediting cash and debiting an expense.

GP provides an optional setting to force transactions tofl ow through the payables account and that's what we'll look at in this recipe.

Setting up GP to pass voucher payments through payables is as easy as following these steps:
Select the Administration button from the Navigation Pane on the left. In the Administration Area Page click on Company under the Setup and Company headers.
Click on the Options button. In the Company Setup Options window scroll down to the
setting marked Separate Payment Distributions and select the checkbox next to it:

Click on OK to close the window and accept the changes.

Let's assume a $100 purchase transaction where $20 is paid in cash and the remaining $80 would go to accounts payable. By default, GP will create a transaction distribution that looks similar to the following screenshot:

After the Separate Payment Distributions checkbox is selected, GP will create a transaction that looks similar to the following screenshot:

Notice that the full $100 is credited to payables and then the $20 payment is debited to reduce accounts payable to the amount due.

Remembering Processes with an Ad hoc Workflow in Microsoft Dynamics GP

Dynamics GP provides options for robust workfl ow functionality integrated with Microsoft Office SharePoint Server(MOSS) or Windows SharePoint Services(WSS). However, for many users this is more functionality than they need. Additionally, many organizations don't feel they are ready for the cost and complexity of MOSS. For users who only need a simple workfl ow to ensure that they remember the steps for a particular task, a basic workfl ow can be built using shortcuts and folders.

This process works well for irregular tasks such as month-end or quarter-end processes where tasks are performed infrequently enough, thus making it easy to forget the steps. For this recipe, we'll look at setting up a basic month-end workflow.

The basic steps of this task are to create a folder to hold the workfl ow and then to add the steps in order to that folder. For our example, we will assume that a month-end financial closing workfl ow includes posting a Quick Journal, processing a Clearing Entry, and closing the month.

Here are the steps to create a basic Ad hoc workflow:

• Click on the Home button from the Navigation Pane on the left. This makes the Shortcut Bar available on the top left.
• Right-click on the Shortcut Bar and select Add | Folder to add a folder to the Shortcut Bar that can be used to organize entries. Name the folder Month End and press the Enter key. Now, there is a folder to hold month-end entries.
• The next step is to add our three sample entries. Select the Month End folder on the Shortcut Bar. Right-click on the folder and select Add | Add Window:

• Click on the plus sign (+) next to Microsoft Dynamics GP. Click on the plus sign (+) next to Financial and select the window named Quick Journal Entry. Change the name at the top to 1) Quick Journal Entry. Click on Add. Putting a number in front of the text prevents this shortcut from interfering if the same shortcut appears somewhere else on the Shortcut Bar:

• Next, select Clearing Entry also under Microsoft Dynamics GP and Financial. Rename it to 2) Clearing Entry in the Name field and click on Add.
• Finally, click on the plus sign (+) next to Company under Microsoft Dynamics GP and select Fiscal Periods Setup. Rename this to 3) Close Period and click on Add. Select Done to finish.
• These items will now appear on the Shortcut Bar on the left under the Month End folder. Selecting an item with the left mouse button will allow moving of these items around to adjust the order if necessary.

Ad hoc workflows provide an option to group a set of steps together and make these all available in one place. Clicking on the arrow to the left of the folder closes it up and keeps the steps out of the way until these are needed.  Clicking on the arrow again re-opens the folder to run the steps.  Some common uses include creating a basic set of steps for new users, month-end and quarter-end processes, and any other process where it is important to ensure that all of the steps are followed.

Account Rollups in Microsoft Dynamics GP

Microsoft Dynamics GP provides great functionality for analyzing and reviewing individual accounts and sequential groups of accounts. Many users don't know that it also provides impressive functionality for analyzing non-sequential groups of accounts via a feature known as Account Rollup.

Account Rollups are inquiries built to allow users to see different GP accounts rolled up together and to provide drill back capability to the details. Additionally, these queries can include calculations for things such as budget versus actual comparisons and calculations.

FRx Reporter provides similar functionality and Account Rollup allows users to access this functionality without the wait time of starting up FRx. Let's see how to mix up some account rollups in this recipe.

Before using Account Rollups it's important to understand how to set them up.

• To set up Account Rollups, select Financial from the Navigation Pane. Then select Account Rollup in the Inquiry section to open the Account Rollup Inquiry Options window.
• In the Option ID field enter the name Actual vs. Budget and press Tab. Select Yes to add the option. On the right, set the number of columns to 3.
• In the first row type Actual in the Column Heading field and set the Type to Actuals.
• In the second row type Budget in the Column Heading field and set the Type to Budget. In the Selection column click on the lookup button (indicated by a magnifying glass) and select BUDGET 2008.
• In the third row type Difference in the Column Heading field. Set the Type to Calculated. Click on the blue arrow next to Selection to open up the Account Rollup
• Inquiry Calculated Column window.
• In the Column field select Actual and click on the double arrow (>>). Then click on
• the minus (-) button. Back in the Column field select Budget and click on the double arrow (>>). Click on OK.
• Back on the Account Rollup Inquiry Options window, select the Segment field, and then select Segment2. Use the lookup buttons (indicated by a magnifying glass) in the From and To fields to add account 4130 and click on Insert. Repeat this process to insert 4120 and then 4100 into the Restrictions box below. Click on Save and close the window:

• Selecting a line and clicking on Balance from the Account Rollup Detail Inquiry Zoom window drills back to the detailed transactions behind the balance.

Account Rollups combine the account totals from disparate accounts for reporting. This is great for tying back multiple accounts that roll up into a single line on the financial statements. Account Rollups also work well for analyzing a single segment, such as a department, across multiple accounts. In the past, I've used this for easy comparisons of Fixed Asset general ledger accounts to the subledger and for rolling up full-time equivalent of unit accounts to get the number of employees across the company with drill back to the employees in each department.

Internet User Defined fields in Microsoft Dynamics GP

Dynamics GP provides a built-in set of Internet fields for users to enter information such as web pages, e-mail addresses, and FTP sites. What many people don't know is that these are actually user defined fields and can be changed by an administrator. This allows afi rm to add a second e-mail address or remove the FTP link if they want to. In this recipe, we'll look at how to customize these fields.

It is important to keep in mind when setting up Internet User Defined fields that these settings affect all of the Internet User Defined field names attached to address IDs assigned to a Company, Customers, Employees, Items, Salespeople, and Vendors.

Customizing the Internet User Defined fields is easy. Let's look at how it is done. For our example, we'll add the social networking service Twitter as a new label:

• Select Administration from the Navigation Pane. Under the Setup and Company headers in the Administration Area Page select Company.
• Click on the Internet User Defined button and change the description in the Label 4 field to Twitter. Click on OK:

• Back on the Company Setup screen click on the blue italic letter (I) to the right of the Address ID to open the Internet Information window. In the Twitter field type http://www.twitter.com/user1.
• Click on the link associated with the Twitter field on the left. This opens a web browser and navigates to my Twitter account so that you can follow me. Click on Save to update the record:

The secret to Internet User Defined fields is how the data is entered. Internet items use a prefix in the field to identify the type of Internet transaction to be used with the link. http:// is used for web pages, mailto:// for e-mail, and ftp:// for FTP sites. These prefixes tell Dynamics GP what to do when a link is clicked on. If no prefix is entered, GP will try to figure out what to do and may or may not succeed.

If http://www.microsoft.com is entered in the Home Page field, clicking on the link to the left will start the default browser and open the Microsoft web page. If http:// is not included but www is, GP figures out that it should open a web page. Just putting in microsoft.com isn't enough for GP to understand that the link corresponds to a web page. Similarly, if a user enters mailto://user1@gmail.com in the E-mail field and clicks on the corresponding link the default e-mail client opens up ready to send an e-mail to me. If no prefix is used on an e-mail address, GP will respond with a "File Not Found" error when the link is clicked on. It's not smart enough to know that the @ symbol means that this is an e-mail account. Using a prefix in the Internet User Defined fields explicitly defines how this link should work and provides the most consistency to users.

Some Internet User Defined fields look special but aren't, and some really are special.

Login and password
By default Label 5 is set to Login and Label 6 is set to Password. These fields are supposed to represent the login and password for one of the associated web pages or FTP sites. However, these fields are not encrypted and there is limited security control. So, it may not be appropriate to leave these fields named Login and Password if a company doesn't want users entering that information here.

Labels 7 and 8
Label 7and Label 8 in the Internet User Defined Setup window are special fields that allow a user to look up and attach links to files located on the computer or the network. Clicking on the label name on the left opens the associatedfile. Any of the user defined fields can hold a filename, not just text. However, the special ability of labels 7 and 8 to allow users to look up filenames means that administrators should reserve these fields for file attachments.

Widening Segments width for better visibility in Microsoft Dynamics GP

When companies use alphanumeric characters in their chart of accounts wide letters, such as M or W, are often cut off. Horizontal scroll arrows don't help because the problem is that the segment field is too narrow, not the entire account field. To resolve this problem Dynamics GP provides an option to widen the segment fields as well.

On the Navigation Pane click on Administration, select Account Format. For each segment that needs to be wider, select the field under the Display Width column and change it from Standard to Expansion 1, Expansion 2, or Expansion 3 to widen the field. Expansion 3 represents the widest option.

Companies using only numbers in their chart of accounts won't need to widen the segment field. However, firms that include letters as part of their chart will need to increase the width. Following is a list of the expansion options and the letters these are designed to accommodate:

• Expansion 1: A,B,E,K,P,S,V,X, and Y
• Expansion 2: C,D,G,H,M,N,O, Q,R, and U
• Expansion 3: W

Activating Horizontal Scroll Arrows for all users in Microsoft Dynamics GP

Horizontal Scroll Arrows are activated by the user. However, an administrator can turn this feature on for all users in all companies by running the following SQL script against the Dynamics database:

UpDate SY01400 Set HSCRLARW=1

Speeding Up Account Entry with Account Aliases in Microsoft Dynamics GP

As organizations grow the chart of accounts tends to grow larger and more complex as well.   Companies want to segment their business by departments, locations, or divisions. All of this means that more and more accounts get added to the chart. As the chart of accounts grows it gets more difficult to select the right account. Dynamics GP provides the Account Alias feature as a way to quickly select the right account. The account aliases provide a way to create shortcuts to specific accounts. This can dramatically speed up the process of selecting the correct account. We'll look at how this works in this recipe.

Getting ready
Setting up Account Aliases requires a user with access to the Account Maintenance Window.

To get to this window:

• Select Financial from the Navigation Pane on the left. In the Cards section of the Financial Area Page click on Accounts. This will open the Account Maintenance window.
• Click on the lookup button (indicated by a magnifying glass) next to the Account field.
• Find and select Account No.000-2100-00.
• Enter AP in the Alias field, which is in the middle of the Account Maintenance window. This associates the letters AP with the Accounts Payable account selected.
• This means that the user now only has to enter AP instead of the full account number
• to use the Accounts Payable account:

• Once aliases have been set up, let's see how the user can quickly select an account using the alias

• To demonstrate how this works, click on Financial from the Navigation Pane on the left. Select Transaction Entry from the Financial Area Page under Transactions.
• In the Transaction Entry window select the top line in the grid area on the lower half of the window.
• Click on the blue arrow next to the Account heading to open the Account Entry window.
• In the Alias field type AP and press Enter:

The Account Entry window will close and the account represented by the alias will appear in the Transaction Entry window:

Account Aliases provide quick shortcuts for account entry. Keeping them short and obvious makes them easy to use. Aliases are less useful if users have to think about them. Limiting them to the most commonly used accounts make these more useful. Most users don't mind occasionally looking up the odd account. However, they wouldn't want to memorize long account strings for regularly used account numbers.

It's counter-productive to put an alias on every account as that would make finding the right alias as difficult as finding the right account number. The setup process should be performed on the most commonly used accounts to provide easy access.

How To Remove/Avoid "Microsoft_Dynamics_GP.vba project reference" Message Box in Microsoft Dynamics GP

If you are getting "Microsoft_Dynamics_GP.vba project reference" Message Box at start-up then do the followings to ignore this.

 

Create DEXVBA.ini file, this file is not created by default. You will need to open Notepad (or similar text editor) and create it. The file should be created in the root Windows folder, not in the GP folder.

Step 1. Create a file named DEXVBA.ini in the root Windows folder.
Step 2. Add the following line to the top of the file: [General]
Step 3. Add the selected .ini setting beneath [General].

LogObjects=TRUE
This will create a text file that will include all of the objects in a VBA project. The text file will be the same name as the product dictionary with a ‘.txt’ extension.

NoUnresolvedDialog=TRUE
This will suppress the following error message when you launch Dynamics GP.
“The product_name.vba project references some objects that cannot be found.
These objects are listed in the file: C:\Program Files\Microsoft Business Solutions\GP\ product_name.txt”
The warning will be suppressed for all VBA projects loaded. It doesn’t solve the problem regarding missing objects, but it suppresses the message.

Contents of DEXVBA.ini

[General]
LogObjects=TRUE
NoUnresolvedDialog=TRUE

SAP R/3 Interface

As you learned in Lesson 1, "Accessing SAP R/3," when you launch SAP R/3, the SAP logon screen appears. Figure 2.1 shows the logon screen and points out the main elements of the user interface.

Plain English
User Interface

The controls and displays you use to operate something. In your car, for example, the user interface would consist of the steering wheel, the pedals, and the dashboard.

The title bar in this figure reads SAP R/3. This changes according to which screen you are looking at. The title bar also can help you confirm that you are where you need to be.

The menu bar contains a number of menus from which you select commands to perform your tasks. The available menus change depending on which screen you are in. Two selections available from all screens are System and Help.

Three standard Windows controls appear in the upper-right corner of the title bar:

• The Window Minimize control minimizes the SAP R/3 window to a button on your taskbar (where it remains active and you can get to it easily). You can bring it back to full size by clicking it or by pressing Alt+Tab.

• The Restore control changes your SAP R/3 session from occupying only a window on your screen to taking up the full screen. You might want to use this to check information in another system (to check your email, for example) while using SAP R/3. When your session is occupying only a window, the Restore control is replaced with a Maximize button, which you can click to make SAP R/3 take up the full window again.
  
• The Close control (×) shuts down your SAP R/3 session, after you confirm that this is really what you want to do.

The tool buttons across the top of the screen function as shortcuts you can use to perform common tasks. SAP R/3 displays active tool buttons in color; shadowed tool buttons don't apply to the active screen.

Figure 2.1 shows a Quick Info box labeled New Password F5. (These are also known as ToolTips in Windows 95/98.) These boxes appear when you position the mouse pointer over a button. This one, in particular, indicates that you're pointing to the New Password button, which performs the same function as the F5 key—both open the New Password dialog box.

SAP R/3 uses fields to accept and display information. Some things to consider when dealing with fields include the following:

• The length of a field shows you how many characters you can type in that field.
  
• The cursor (a flashing line or block) shows the field you are now in (the active field); anything you type appears in this field.
  
• SAP R/3 generally shows a field name for each field onscreen.
  
• When SAP R/3 displays a question mark in a field, you must enter something into the field before you can go any further. In the logon screen shown in Figure 2.1, for example, a user name is required. If you try to go on without filling in all the required fields, SAP R/3 gives you an error message.

SAP R/3 and Dialog Boxes

Sometimes SAP R/3 uses dialog boxes to display or request information. When a dialog box appears, it becomes the active window, and its title bar is highlighted.

Plain English

Dialog Box  
A box that SAP R/3 displays to communicate with you. Dialog boxes are smaller than the full SAP R/3 window.

Figure 2.2 shows the Change Password dialog box. Notice that its title bar is highlighted, and the main screen's title bar is no longer highlighted to show that it's not active. This means that only the dialog box is active. You can't access anything on the main screen behind it until you deal with the dialog box. You must click Copy or × (Cancel) to close this dialog box and return to the main screen.

Sometimes SAP R/3 presents you with several layers of dialog boxes. You must deal with those boxes to get back to your original screen.

The SAP R/3 Toolbar

The SAP R/3 toolbar is the row of tool buttons across the top of the screen. Some buttons apply to all screens; others apply only to some screens. SAP R/3 tells you which are active by showing them in color. Shadowed tool buttons don't apply to the displayed screen. Table 2.1 shows you the tool buttons and describes each one.

TIP

Back and Exit  
New users sometimes find this confusing. If you are at the first screen in a series, the Back and Exit buttons will do the same thing. If you are at the third screen in a process, Back takes you back to the second screen, and Exit takes you right out of the process.

The Status Bar

The status bar is usually displayed at the bottom of the SAP R/3 screen (see Figure 2.3).

SAP R/3 uses the status bar to pass along information. In particular, the message area part of the status bar contains a message preceded by one of the following codes:

Code	Meaning
I	Information
W	Warning
E	Error
A	Abnormal end

In Figure 2.3, the status bar message E: Required Entry not made is an error notice telling you that you need to fill in a field before you can proceed.

TIP

Status Bar Error Messages  
New users sometimes don't notice error messages on the status bar and don't know why they can't go on. The first thing you should look at when you have a problem is the status bar.

On the right end of the status bar, you'll find the following information:

• Server name
  This is different from the one you typed in to gain access to the system, which may seem a little awkward at first. In Figure 2.3, the name is HRS(1)(000); HRS is the name of a demonstration system in the SAP Calgary office. We used the client code of 000 to access this system.
  
• Session number
  You can have more than one SAP R/3 session open at once.
  
• Insert/overtype indicator 
  This indicates which typing mode you are in. You switch between insert and overtype modes when you press the Insert key.
  
• Clock
  SAP R/3 provides a clock in the lower right corner.
  

In this lesson, you learned the basics of the SAP R/3 user interface. In the next lesson, you see how to use the SAP R/3 screen elements to move between screens.

How to Kill Processes That Have Open Connection in a SQL Server

You may frequently need in especially development and test environments instead of the production environments to kill all the open connections to a specific database in order to process maintainance task over the SQL Server database.
In such situations when you need to kill or close all the active or open connections to the SQL Server database, you may manage this task by using the Microsoft SQL Server Management Studio or by running t-sql commands or codes. Actually, this task can be thought as a batch task to kill sql process running on a SQL Server.

If you open the SQL Server Management Studio and connect to a SQL Server instance you will see the Activity Monitor object in the Object Explorer screen of the related database instance. You can double click the Activity Monitor object or right click to view the context menu and then select a desired item to display the activities to be monitored on the Activity Monitor screen.

 As seen on below you can monitor and view process id's and process details on the list of prcesses running on the database instance. If you want you can filter processes based on specific values like user, database or status.

Note that default view when displayed the screen is first opened is filtered only for non-system processes which means system processes which own the first 50 reserved processid's are not listed in the view by default. You can view system processes by removing the filter on "Show System Processes" criteria in the filter settings screen.

 SQL Server 2005 SQL Server Management Studio Activity Monitor screen

You can kill a process by a right click on the process in the grid and selecting the Kill Process menu item. You will be asked for a confirmation to kill the related process and then will kill the open connection to the database over this process. This action is just like running to kill sql process t-sql command for a single process.

A second method which I do not recommend but can be used in some situations may be using the Detach Database screen to drop connections and detaching the database and then re-attaching the database.

By: http://www.kodyaz.com/

How To Find Out Who Entered a Transaction in Microsoft Dynamics GP

Short of purchasing and using the Audit Trails package, there is some information concerning the user that last worked on transactions in MS Dynamics GP.

Most of the transaction tables have a field for user ID. Typically, this is the last user to touch the record. If the transaction is posted, then the user that posted will have their id there, overwriting the ID of the person that entered the transaction.

The Sales Order Processing is different. There is a field called USER2ENT that records the ID of the user that entered the transaction and a field called PTDUSRID that shows the id of the user that posted the final invoice. This is in table SOP10100.

How To Void Partially Applied AP Payment in Microsoft Dynamics GP

When an AP payment has been partially applied, it cannot be voided in the normal manner. Here is what needs to be done:

1. Create a dummy invoice for the balance of the payment.
2. Apply the payment balance to the dummy invoice, fully applying the payment and fully paying the dummy invoice.
3. Use Void Historical to void the payment (reinstating the invoices that it paid) then void the dummy invoice.

How to Unlock a User in Microsoft Dynamics GP

This is useful in solving Dynamics GP error: "User is already logged-in" when you are trying to log into Dynamics version 7.5 and below.
For version 8 and above, a better way is to remove them in the User Activity window. Go to Setup - System - User Activity, then choose the user to delete.

SQL Command:

DELETE FROM ACTIVITY WHERE userid='username'

A quick way to duplicate or copy of PRICELEVEL SET in Microsoft Dynamics GP

Use this command to easily duplicate a pricelevel in GP DYNAMICS. You just need to specify the pricelevel code you want to copy, and the new pricelevelcode and its description. In the example below, 'RETAIL' is the code of the existing pricelevel. The script will copy that into a new pricelevel whose pricelevel code is 'WHOLESALE', and description as 'WHOLESALE CUSTOME PRICING'. Note: in the code, any text preceeded with a "--" is a comment.

SQL Command:

declare
  @source_pricelevelcode varchar(250),
  @new_pricelevelcode varchar(250),
  @new_priceleveldesc varchar(250)

SET @source_pricelevel = 'RETAIL'  -- SET YOUR SOURCE PRICELEVEL CODE
SET @new_pricelevel = 'WHOLESALE'  -- SET YOUR NEW PRICELEVEL CODE
SET @new_priceleveldesc = 'WHOLESALE CUSTOME PRICING' -- SET YOUR NEW PRICELEVEL DESCRIPTION


-- PROCEED TO COPY PRICELEVEL SET INTO ANOTHER, THERE ARE 3 TABLES INVOLVED

INSERT INTO iv00108(ITEMNMBR, CURNCYID, PRCLEVEL, UOFM, TOQTY, FROMQTY, UOMPRICE, QTYBSUOM)
SELECT ITEMNMBR, CURNCYID, @new_pricelevelcode, UOFM, TOQTY, FROMQTY, UOMPRICE, QTYBSUOM
FROM iv00108
where prclevel = @source_pricelevelcode

INSERT INTO iv00107 (ITEMNMBR, CURNCYID, PRCLEVEL, UOFM, RNDGAMNT, ROUNDHOW, ROUNDTO, UMSLSOPT, QTYBSUOM)
select ITEMNMBR, CURNCYID, @new_pricelevelcode, UOFM, RNDGAMNT, ROUNDHOW, ROUNDTO, UMSLSOPT, QTYBSUOM
from iv00107
where prclevel = @source_pricelevelcode

INSERT iv40800 (PRCLEVEL, DSCRIPTN)
VALUES (@new_pricelevelcode, @new_priceleveldesc)

How to remove the Customer Experience Improvement Program (CEIP) task from Microsoft Dynamics GP

The CEIP task appears after you install Microsoft Dynamics GP. CEIP collects information about how a customer uses Microsoft products and about any problems that customers experience.

SQL Command:

USE DYNAMICS
set nocount on
declare @Userid char(15)
declare cCEIP cursor for 
        select A.USERID
        from SY01400 A left join SY01402 B on A.USERID = B.USERID and B.syDefaultType = 48
        where B.USERID is null or B.SYUSERDFSTR not like '1:%'
open cCEIP
while 1 = 1
begin
    fetch next from cCEIP into @Userid
    if @@FETCH_STATUS <> 0 begin
        close cCEIP
        deallocate cCEIP
        break
    end

    if exists (select syDefaultType from DYNAMICS.dbo.SY01402 where USERID = @Userid and syDefaultType = 48)
    begin
        print 'adjusting ' + @Userid
        update DYNAMICS.dbo.SY01402
        set SYUSERDFSTR = '1:'
        where USERID = @Userid and syDefaultType = 48
    end
    else begin
        print 'adding ' + @Userid
        insert DYNAMICS.dbo.SY01402 ( USERID, syDefaultType, SYUSERDFSTR )
        values ( @Userid, 48 , '1:' )
    end
end /* while */
set nocount off

Find Field Value in Database in Microsoft SQL Server

The following is a SQL Script that can be run in a database to return all tables and columns where a particular value is present. This can be used for strings or values with a small modification.
This type of thing is great when moving applications/products between servers. This is certainly a good script to include in your master table to be used over and over.


SQL Command:

DECLARE @value VARCHAR(64)
DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)

SET @value = 'valuehere'

CREATE TABLE #t (
    tablename VARCHAR(64),
    columnname VARCHAR(64)
)

DECLARE TABLES CURSOR
FOR

    SELECT o.name, c.name
    FROM syscolumns c
    INNER JOIN sysobjects o ON c.id = o.id
    WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)
    ORDER BY o.name, c.name

OPEN TABLES

FETCH NEXT FROM TABLES
INTO @table, @column

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] '
    --SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) = ''' + @value + ''') '
    SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') '
    SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
    SET @sql = @sql + @column + ''')'

    EXEC(@sql)

    FETCH NEXT FROM TABLES
    INTO @table, @column
END

CLOSE TABLES
DEALLOCATE TABLES

SELECT *
FROM #t

DROP TABLE #t

Find any Column from any Table of any Database in Microsoft SQL Server

There may be some instance where in you need to know in how many table the column exist, this is useful query for the DBA to find the specified Column in a given database.

SQL Command:

SELECT name as Table_Name,
case when xtype = 'U' then 'Table'
      else 'View'
      end Type
FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'ITEMNMBR')
and xtype = 'U'
order by name

Find Table Size of the Database in Microsoft SQL Server

Very useful script for DBA to know the each table size of a specified database.

SQL Command: 

DECLARE
@id int,
@pages int,
@objname varchar(750)

SET NOCOUNT ON

CREATE TABLE #tblSize
(
Name varchar (100),
Rows varchar (100),
Reserved varchar (100),
Data varchar (100),
Index_Size varchar (100),
Unused varchar (100)
)

CREATE TABLE #spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

-- declare main cursor to get first user table name from sysobjects
DECLARE TabNameCur CURSOR FOR
SELECT id, name
FROM dbo.sysobjects
WHERE xtype = 'u'
ORDER BY name


OPEN TabNameCur
FETCH TabNameCur INTO @id, @objname

WHILE @@FETCH_STATUS = 0
BEGIN

TRUNCATE TABLE #spt_space

INSERT INTO #spt_space (reserved)
SELECT sum(reserved)
FROM sysindexes
WHERE indid in (0, 1, 255)
AND id = @id

SELECT @pages = sum(dpages)
FROM sysindexes
WHERE indid < 2
AND id = @id

SELECT @pages = @pages + isnull(sum(used), 0)
FROM sysindexes
WHERE indid = 255
AND id = @id

UPDATE #spt_space
SET data = @pages

UPDATE #spt_space
SET indexp = (SELECT sum(used)
FROM sysindexes
WHERE indid in (0, 1, 255)
AND id = @id) - data

UPDATE #spt_space
SET unused = reserved
- (SELECT sum(used)
FROM sysindexes
WHERE indid in (0, 1, 255)
AND id = @id)

UPDATE #spt_space
SET rows = i.rows
FROM sysindexes i
WHERE i.indid < 2
AND i.id = @id

--This step required as 'convert.../1000' cannot be used with varchars
INSERT INTO #tblSize
SELECT name = object_name(@id),
rows, --= convert(char(11), rows),
reserved = convert(decimal (8,2), (reserved * d.low / 1024.)/1000),
data = convert(decimal (8,2), (data * d.low / 1024.)/1000),
index_size = convert(decimal (8,2), (indexp * d.low / 1024.)/1000),
unused = convert(decimal (8,2), (unused * d.low / 1024.)/1000)
FROM #spt_space, master.dbo.spt_values d
WHERE d.number = 1
AND d.type = 'E'

FETCH NEXT FROM TabNameCur INTO @id, @objname
END

-- close & deallocate main cursor
CLOSE TabNameCur
DEALLOCATE TabNameCur


SELECT Name, Rows,
Reserved + ' MB' as Reserved,
Data + ' MB' as Data,
index_size + ' MB' as Index_Size,
unused + ' MB' as Unused
FROM #tblSize

How To Reset System Password in Microsoft Dynamics GP

The GP Administrator probably forget the system password, here is a hidden technique to resetting password. When the user set a system password, it saved in DYNAMICS..SY02400, it is not recommended to remove or reinsert any record in this table, the following script will reset system password and user can redefine password.

SQL Command:

UPDATE DYNAMICS..SY02400 SET DMYPWDID=1,PASSWORD=0x00202020202020202020202020202020

How to remove "Report yields no data" message in Microsoft FRx

Removal of "Report yeilds no data" from any report or view report with complete zero balance, do the following steps:

1. In column layout, insert new column as "CALC".
2. In "Calc Formula:" type any figure i.e "2000+1000".
3. In "Print Control" define "NP"

After done all changes in column layout, generate report.  All zero balance accounts will be appeared in report.

NOTE: Make sure "Display rows with no amounts" must be checked under "Catalog>Report Options>Formating" tab.

How to print "-" for Zero Amounts or assign "Dr" / "Cr" for Amounts in Microsoft FRx

In the Column Layout, there is a drop down containing default formats to select from in the Special Format Mask field. These can be modified as necessary.  Amount formatting has three separate sections separated by semi-colons.

#,##0.00; (#,##0.00); 0.00

Change the display of zero amounts to print either a line "-" or the text Zero, within the Column Layout select the Special Format Mask field for the column desired.  Use either the drop down to select a pre-defined positive or negative number format or use the edit bar from the top and type in the positive or negative number format.  Finally, type a semicolon and then type "-" if dash are desired, if word Zero are desired then type "Zero". Repeat steps for each column in the layout requiring the special format.

Ex:

For "-" type the following mask:

                 #,##0.00; (#,##0.00); -

For "Zero" type the following mask:

                 #,##0.00; (#,##0.00); Zero

NOTE: If you desire to suppress printing of amounts, enter semicolons with nothing between them. The missing format prevents that type of amount from being displayed. Ex. #,##0.00;;Zero

NOTE: If you desire DR next to positive amounts and CR to print next to negative amounts, you can do so by placing these in quotes anywhere in the format. Ex. #,##0.00DR; #,##0.00CR; Zero As in this example, there is no need for the parentheses for the negative amounts.

Simplify Account Reconciliation with SmartList Builder

While it might not be the first use that comes to mind, you can use SmartList Builder to more easily reconcile payables to the general ledger in Microsoft Dynamics GP.
To find out how, follow this guide from John Ellis, a consultant at Tribridge, a Microsoft Gold Certified consulting firm.  This SmartList displays the Payables Batch ID as a column alongside the General Ledger Journal Entry column. According to Ellis, you access Microsoft Dynamics GP SmartList Builder in Tools > SmartList Builder > SmartList Builder, and take the following steps:

1. Click the plus sign (+) next to Tables.
2. Choose Microsoft Dynamics GP Table.
3. Choose Microsoft Dynamics GP for Product.
4. Choose Financial for Series.
5. Choose Year-To-Date Transaction Open for Table.
6. Click Save.
7. Click the plus sign (+) next to Tables.
8. Choose Microsoft Dynamics GP Table.
9. Choose Microsoft Dynamics GP for Product.
10. Choose Financial for Series.
11. Choose Account Index Master for Table.
12. Choose Year-To-Date Transaction Open for Link To Table.
13. Choose Equals for Link Method.
14. Click the plus sign (+) next to Link Fields.
15. Choose Account Index in the From and To fields.
16. Click Save.
17. Highlight Year-to-Date Transaction Open.
18. Click the plus sign (+) next to Tables.
19. Choose Microsoft Dynamics GP Table.
20. Choose Microsoft Dynamics GP for Product.
21. Choose Purchasing for Series.
22. Choose PM Transaction Open File for Table.
23. Choose Year-to-Date Transaction Open for Link To Table.
24. Choose Left Outer for Link Method.
25. Click the plus sign (+) next to Link Fields.
26. Choose Originating Master ID in the From field.
27. Choose Vendor ID in the To field.
28. Click Save.
29. Click the plus sign (+) next to Link Fields.
30. Choose TRX Date in the From field.
31. Choose Posting Date in the To field.
32. Click Save.
33. Click the plus sign (+) next to Link Fields.
34. Choose Originating Control Number in the From field.
35. Choose Voucher Number in the To field.
36. Click Save.

Create a restriction in Microsoft Dynamics GP SmartList Builder to only pull in the PMTRX source document, limiting to transactions posted from Payables Transaction Entry and not from other modules. To do so, follow these steps:

1. Click the Restrictions button.
2. Click the plus sign (+) next to Restrictions.
3. Choose Year-to-Date Transaction Open for Table.
4. Choose Source Document for Field.
5. Choose Is Equal to One of List for Restriction.
6. Enter GJ for Value.
7. Click Add.
8. Enter PMTRX for Value.
9. Click Add.
10. Enter CMTRX for Value.
11. Click Add.
12. Click Save.
13. Click OK.

Create a calculated field that will display a column indicating if the batch in the SmartList was created as a result of posting a transaction in Bank Reconciliation. Do so as follows:

1. Click the Calculations button.
2. Hit the plus sign (+) next to Calculated Fields.
3. Enter Bank Rec Batch for Field Name.
4. Choose String for Field Type.
5. Type the following formula in the Calculation field:
    CASE {Year-to-Date Transaction Open:Source Document} WHEN 'CMTRX'
       THEN 'Bank Rec'
       ELSE 'Not Bank Rec'
    END
6. Click Save.
7. Click OK.

If your client does not pay its posted payables transactions within a regular monthly cycle, these transactions will reside in the PM Transaction OPEN File table. So, instead of using the PM Paid Transactions File table, you would use the PM Transaction OPEN File table.