GP Power Tools has the ability to capture SQL Profile Traces as one of the five different types of logs it can generate from Microsoft Dynamics GP.
To achieve this, GP Power Tools needs to change some permissions on the SQL Server so that it can both generate the Trace programmatically and also copy the resulting files to the shared Logging Folder.
If there is a problem with these permissions, on login you can receive the following error dialog:
You do not have permission to run ‘FN_TRACE_GETINFO’.
The Problem
The error is generated when GP Power Tools looks for possible stranded Traces when it starts up. A stranded Trace can happen if GP Power Tools starts a trace on the SQL Server and then GP does not exit gracefully (for example: crashes or is stopped by Task Manager or Terminal Server disconnect timeout). If GP Power Tools detects a stranded Trace is will open a dialog and allow it to be stopped and removed.
You can open this dialog manually from the GP Power Tools Logging Control window.
It will show any active traces and allow them to be stopped.
The Solution
Problems with the permissions can occur when changing SQL Servers as the new server will not have the changes already made on the previous server. It can also happen if the password or permissions for the SQL Trace User have been changed.
You will need to redo the SQL Profile Trace setup to fix the issue. This will restore all the settings needed for the functionality to work without any errors.
Open the Logging Settings window and click on the Edit SQL Profile Trace Settings button at the bottom of the window.
If you have moved to a new SQL Server, you will need to follow the first four steps in the step by step instructions in SQL Profile Tracing Configuration section of Chapter 2 of the GP Power Tools User Guide Manual (GPPTools.pdf). You can Press F1 or select Help from the menus while on any of the GP Power Tools windows to open the Manual. The same steps are also in the article in the More Information section (below). These steps will create the shared folder on the SQL Server (eg. C:\SQLTrace\) where the Trace files are stored as they generated, before they are moved to the shared Logging Folder, and creates the local administrator (eg. SQLTraceUser) that is used by impersonation to change the file permissions to allow the files to be copied.
Note: If recreating the shared folder and/or the Administrator User, either match the previously used naming conventions (as shown on the SQL Profile Trace Settings window) or change the window to match the new settings.
To apply all the settings to the SQL Server, click on the Process Single User Mode SQL Server Action button and select Enable Single User Mode (runs 1st set of 7 actions below).
You will be asked for the password for the Administrator User previously created.
If you do not get this password correct, GP Power Tools will not be able to copy the SQL Profile Trace files from the shared Trace folder to the shared Logging folder. It will show the following error in the GPPTools*.log file for the user and company when manual logging is stopped. The Trace files will be left in Trace folder on the SQL Server.
SQL_Tracing Failed to Stop (Return Code: 15121) ** An error occurred during the execution of xp_cmdshell. A call to 'icacls' failed. Check password for local administrator user on SQL Server. ** SQL_Tracing Failed to move \\MusgraveSB2\SQLTrace\Trace_sa_TWO24_*.trc to C:\Dyn2400\Data\Trace_sa_*.trc
If the steps have been completed correctly with the correct password entered, GP Power Tools should be able to generate a SQL Profile Trace file and move it to the logging folder.
More Information
For more information, please see the article below from when this functionality was first added to the Support Debugging Tool:
Hope this information is helpful.
David
This article was originally posted on http://www.winthropdc.com/blog.
Winthrop Development Consultants Blog 