Monday, February 28, 2011

Sending Remittance Emails Through Great Plains

Recently, I was asked to come up with a solution to start sending remittance emails when payments were electronically wired to vendors. The information is currently held in a Great Plains (GP) accounting system and anyone who has ever worked at the table / view level in GP knows it isn’t the easiest system to decipher. But thanks to several blog posts which give some real-world names to the cryptic 7/8 character table and column names, I was able to muddle through and create a workable solution.

Thanks to:
GP Tables from Victoria Yudin:

Basically the script flows as follows:

1.       Declare and initialize variables to be used in procedure
2.       Select Vendor ID, Vendor Name, Email Address, Bank Information, Transfer Number, Invoice Number, Invoice Date, and Amount Paid Toward Invoice into temp table. That information can be found in the following tables:
a.       PM00200 – Vendor Master
b.      SY01200 – Email Address Table
c.       SY06000 – EFT Master
d.      PM30200 – Transactions
e.      PM30300 – Apply To History
f.        PM00400 – Master File
3.       Create and open header cursor that spins through all the distinct vendors in the temp table
4.       Since the email will be formatted in HTML and I want the output of the email to look structured, I start a HTML table element for the vendor. This contains the beginning paragraph and the table column headers for the detail lines that will follow
5.       Declare and open detail cursor that will spin through and output invoice payment details into the HTML table that was created.
6.       Finish the email and close the detail cursor.
7.       Send the email to the vendor.
8.       Move to the next value in the header cursor, create email and send. This is done for all the distinct vendors in the temp table.

This is then scheduled to run once daily and will pick up any electronic payments scheduled to be made to vendors that day.

If you only want to send emails to vendors who have an email address setup in the system, change the left outer join to an inner join. I specifically put in left outer join in there so that when a vendor had been setup without an email address, the process would send emails for that vendor to a mailbox that someone would monitor and then input an email for that vendor into GP.
NOTE: This script comes as is with no guarantees. Please test on a non production system before using this to send any emails inside or outside of your company.

This code is as follows:


Declare Variables
Declare @VendorID          varchar(256)
Declare @VendorName        varchar(256)
Declare @VendorEmail       varchar(256)
Declare @VendorBankName    varchar(256)
Declare @VendorBankAcct    varchar(256)
Declare @VendorInvoice     varchar(256)
Declare @VendorPayment     Money --Using Money type to be able to add commas
Declare @VendorTotal       Money --Using Money type to be able to add commas
Declare @TableHTML         nvarchar(MAX)
Declare @VendorTransferNo  varchar(256)
Declare @VendorInvoiceDate varchar(256)

Thursday, February 24, 2011

A Quick Way to Change the Owner on All User Databases

We like to keep the SA account as the owner on all of our user databases, but when we started restoring backups to a new server we were testing, we noticed that the owner of the restored database was the DBA who did the restore. When a database is being created for the first time on a server, SQL sets the owner of the database as the person who restored the database. If someone changes the owner, and restores a backup over the existing database, the change will be retained.

So I needed a quick way to set the owner on all user databases and this is what I came up with:

exec sp_msforeachdb 'use ? if (select db_name()) not in (''master'',''msdb'',''model'',''tempdb'')
   Alter Authorization on Database::? to <login>

How to determine if you code is supported in SQL 2008 when moving from SQL 2005 / 2000

How to determine if you code is supported in SQL 2008 when moving from SQL 2005 / 2000.

I know I’m a little late to the party, but if you are upgrading a server from SQL 2005 / 2000 to SQL 2008 and need to determine what features you are using that are not supported or are deprecated in SQL 2008, the following tools can give you starting point as to what you need to change and where.

1.       Microsoft SQL Server 2008 Upgrade Advisor: This tool will analyze the different features and instances that you currently have running on SQL 2005 / 2000 and identify problems or changes that will have to be made before your upgrade. Learn about it here:

2.       SQL Profiler: You can use SQL’s built in profiler to capture statements and procedures that trigger the Deprecation Announcement or Deprecation Final Support events. To see these events, you must check the Show All Events check box and look under Deprecation. Run this against the server you want to upgrade and it will return the application and, depending on what other events you are capturing, the offending code.

Wednesday, February 23, 2011

Changing the Default Backup Path in SQL Server 2008 / 2005

One of the issues that I’ve found with SSMS is that if your default backup path is a UNC path and you use the GUI to restore a database, you will receive the following nasty error message when you try to point the GUI to that default UNC path.

“Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists. 
If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.”

Turns out that the SSMS Locate Backup File window does not support browsing out to a UNC path, but the path can be entered manually in the Selected Path edit box like so: \\server\path and the .bak backup file can be specified in the File Name edit box.

To get rid of this annoying error message, our answer was to change the default backup path for SQL server. While this will not change where we place our current backups, since their backup locations are specified in a script, it will change where SSMS wants to place ad hoc backups in the future. So if you are using the SSMS GUI to do a backup, be aware of where your backup is being placed.

To change the default backup path in SQL Server, you must change the BackupDirectory value in the registry. The key can be found here:

SQL 2008:
HKEY_Local_Machine\Software\Microsoft\Microsoft SQL Server\MSSQL10.<instance>\MSSQLServer

SQL 2005:
HKey_Local_Machine\Software\Microsoft\Microsoft SQL Server\MSSQL.<instancenumber>\MSSQLServer

Note: Modify the registry at your own risk. Always backup your registry before making any changes.

Tuesday, February 22, 2011

SAN Disk Setup and SQL

Today, I had an opportunity to voice my opinion on a subject that I had virtually no experience in, SAN setup in regards to SQL Server. This has always been something that has been abstracted from me by the SAN admin. In the old times, we would get a new server, ask for some disks (we determine the RAID level, if needed), and presto, they would appear a bit later. But this is the new times and with the new times comes a new SAN admin.

Since I had no experience in storage other than basic knowledge of RAID setup, I decided I should first acquaint myself with some of the terms like:

·         Partition_Offset: Starting place on the disk where user data will be written to. (Windows Server 2008 defaults to 1 MB)
·         Stripe_Unit_Size: This is the block size that striped across your RAID arrays. Typical values are 8KB, 16KB, 32KB, 64KB.
·         File_Allocation_Unit_Size: Bytes Per Cluster

Trying to go from Zero to Good Enough To Get By was a little tougher that I thought it was going to be. But thanks to the information provided on the internet by Paul Randal, Microsoft, and Dell, I was able to craft a workable solution.

In the end, we created the following disks for SQL:
·         Data: RAID 5
o   Partition_Offset: 1024 KB (Windows Server 2008 Default)
o   Stript_Unit_Size: 64 KB (SAN Default for RAID 5)
o   File_Allocation_Unit_Size: 64 KB (Recommended size for SQL)
·         Logs: RAID 1
o   File_Allocation_Unit_Size: 64 KB (Recommended size for SQL)
·         TempDB: RAID 1
o   File_Allocation_Unit_Size: 64 KB (Recommended size for SQL)

Any comments or suggestions are welcome. Perhaps someone who has more experience with these types of SAN configurations can shed some light on some areas I might have overlooked.