SQL Server-Side Trace…with an example using Sage 500 ERP

This topic has been written about many times. I am writing this post with a focus on Sage 500 ERP. The reason I am focusing on this as an example is that I have done quite a bit of work recently with some Sage 500 ERP customers and partners only to discover that most of them were using SQL Profiler on a system already experiencing severe pressures. Yet it needed to be done to satisfy the needs of Sage Support in order to provide more information on a support case.

SQL Profiler, also know as a Client-Side Trace, only adds to the pressure. An article by Andrew Kelly from 2008 does a nice job of explaining some the differences between the two and why you would choose over the other. The article can be found at  http://www.sqlmag.com/article/performance/sql-server-profiler-or-server-side-trace-.

We are now going to concentrate on Server-Side Trace for the rest of this post. This is still relevant with most of the Sage 500 ERP and Sage X3 ERP implementations and would most likely be a benefit to you, at least that has been my experience over the past few months. I say that because we will discuss another technology feature that was introduced in SQL Server 2008 is Extended Events. This feature has been greatly expanded in the upcoming version, SQL Server 2012. If you want to read more about this feature this is a good starting point http://msdn.microsoft.com/en-us/library/dd822788(v=SQL.100).aspx. Jonathan Kehayias has many blog posts on this subject matter and is probably the industry expert on this topic in my opinion.

We will cover some foundation related to Trace Events and Trace Columns. This will provide you with resources and knowledge you will need to work with Server-Side Trace.

One way to approach this is to use SQL Profiler to configure your trace then you can do a File->Export->Script Trace Definition to write out the SQL Script that you can use to perform a Server-Side Trace. This can be a great way to get familiar with and transition from what you have been doing for probably many years.

Complete list of events

You can also select the Trace Events with the following syntax.

   1: -- Trace Events

   2: SELECT  e.trace_event_id, 

   3:         e.category_id,

   4:         e.name as traceeventname, 

   5:         cat.name as categoryname, 

   6:         cat.type

   7:     FROM sys.trace_events e          

   8:         INNER JOIN sys.trace_categories AS cat  ON e.category_id = cat.category_id

   9:     ORDER BY e.category_id, e.trace_event_id

Complete list of columns


You can also select the Trace Columns by Trace Event with the following syntax.

   1: SELECT   e.Trace_Event_ID,

   2:          e.name AS Event_Descr,     

   3:          c.trace_column_id,

   4:          c.name AS Column_Descr,

   5:          c.type_name,

   6:          c.is_filterable

   7: FROM sys.trace_events e

   8: INNER JOIN sys.trace_event_bindings b

   9:   ON b.trace_Event_ID = e.Trace_Event_ID

  10: INNER JOIN sys.trace_columns c          

  11:   ON b.trace_column_id = c.trace_column_id

The Server-Side Trace consists, for the most part, of four components to get a trace running. The four components are:

  • sp_trace_create – http://msdn.microsoft.com/en-us/library/ms190362.aspx

    The procedure takes 5 parameters. One of which is an Output.  It is used to create the trace definition.

       TraceID         – ID of the trace, this is an Output parameter and will provide the TraceID

       Options         – several different options that can be set (refer to the link above for more details)

       TraceFile      – physical file name of the trace file you will write to

       MaxFileSize  – size of the file, once the file reaches this size it will close the file and create subsequent files

       StopTime     – used to specify a time to stop the trace

       FileCount     – used to specify the maximum number of trace files (optional0

  • sp_trace_setevent – http://msdn.microsoft.com/en-us/library/ms186265.aspx

    The procedure takes 4 parameters. It is used to specify what event and what column to capture.

       TraceID     – ID of the trace

       EventID     – ID of the event you wish to capture

       ColumnID – ID of the column you wish to capture

       On              – this will set the event on or off

  • sp_trace_setfilter – http://msdn.microsoft.com/en-us/library/ms174404.aspx

    This procedure takes 5 parameters. It is used to determine what to include and/or exclude from the collection. 
       TraceID                          – ID of the trace

       ColumnID                      – ID of the column you wish to set the filter on

       LogicalOperator           – specify the type of operation, AND or OR

       ComparisonOperator – specify the operand type (e.g. =, <>, >, <, >=, <=, like, not like)

       Value                              – value used for the comparison

  • sp_trace_setstatus – http://msdn.microsoft.com/en-us/library/ms176034.aspx

    This procedure takes 2 parameters. It is used to change the status of the trace event. The close will also delete the trace definition. This delete does not delete the trace files that were created.

       TraceID – ID of the trace

       Status    – stop(0), start(1) and close(2) trace, you must stop a trace in order to close a trace

  • So how do we use these commands. Well let’s answer some questions that I had while I was first starting out.

    How do I create a trace?

    You need to make sure that the folder exists for the tracefile and the SQL Server Service account user has permissions to write to that folder. It goes without saying but you should also make sure that you have ample disk space where the tracefile is about to write as well.

    First you need to declare your variables.

       1: -- Declare variables 

       2: DECLARE @rc INT 

       3: DECLARE @TraceID INT 

       4: DECLARE @maxFileSize bigint 

       5: DECLARE @fileName NVARCHAR(128) 

       6: DECLARE @on bit 

       7: DECLARE @stoptime DATETIME

    Then you need to provide default values for these variables. Remember TraceID is an OUTPUT during the create phase so it will be assigned later.

       1: -- Set values 

       2: SET @maxFileSize = 5 

       3: SET @fileName = N'C:\Perflogs\MyTrace' 

       4: SET @on = 1 

       5: SET @stoptime = DATEADD(hour,3,GETDATE())

    Create the trace definition and verify that it was successful.

       1: -- Create trace 

       2: EXEC @rc = sp_trace_create @TraceID output, 2, @fileName, 

       3:                @maxFileSize, @stoptime  


       5: -- If error end process 

       6: IF (@rc != 0) GOTO error 


       8: /*




      12: */



      15: -- Start the trace 

      16: EXEC sp_trace_setstatus @TraceID, 1 


      18: -- display trace id for future references  

      19: SELECT TraceID=@TraceID  

      20: GOTO finish  


      22: -- error trap 

      23: error:  

      24: SELECT ErrorCode=@rc  


      26: -- exit 

      27: finish:  

      28: GO

    Define the events and data to collect. Here are few that you would normally configure in SQL Profiler that Sage always requests. In the example below, using the first execution line from row 3, this is basically asking for the Event “RPC:Completed” which is event 10, column 1 which is “TextData” and  set the event definition to 1 meaning on. See Image 1 below for how to discover this information.


       2: -- RPC:Completed

       3: exec sp_trace_setevent @TraceID, 10, 1, @on

       4: exec sp_trace_setevent @TraceID, 10, 6, @on

       5: exec sp_trace_setevent @TraceID, 10, 9, @on

       6: exec sp_trace_setevent @TraceID, 10, 10, @on

       7: exec sp_trace_setevent @TraceID, 10, 11, @on

       8: exec sp_trace_setevent @TraceID, 10, 12, @on

       9: exec sp_trace_setevent @TraceID, 10, 13, @on

      10: exec sp_trace_setevent @TraceID, 10, 14, @on

      11: exec sp_trace_setevent @TraceID, 10, 15, @on

      12: exec sp_trace_setevent @TraceID, 10, 16, @on

      13: exec sp_trace_setevent @TraceID, 10, 17, @on

      14: exec sp_trace_setevent @TraceID, 10, 18, @on

      15: exec sp_trace_setevent @TraceID, 10, 27, @on


      17: -- SQL:BatchCompleted

      18: exec sp_trace_setevent @TraceID, 12, 1, @on

      19: exec sp_trace_setevent @TraceID, 12, 6, @on

      20: exec sp_trace_setevent @TraceID, 12, 9, @on

      21: exec sp_trace_setevent @TraceID, 12, 10, @on

      22: exec sp_trace_setevent @TraceID, 12, 11, @on

      23: exec sp_trace_setevent @TraceID, 12, 12, @on

      24: exec sp_trace_setevent @TraceID, 12, 13, @on

      25: exec sp_trace_setevent @TraceID, 12, 14, @on

      26: exec sp_trace_setevent @TraceID, 12, 15, @on

      27: exec sp_trace_setevent @TraceID, 12, 16, @on

      28: exec sp_trace_setevent @TraceID, 12, 17, @on

      29: exec sp_trace_setevent @TraceID, 12, 18, @on

      30: exec sp_trace_setevent @TraceID, 12, 27, @on


      32: -- SP:Completed

      33: exec sp_trace_setevent @TraceID, 43, 1, @on

      34: exec sp_trace_setevent @TraceID, 43, 6, @on

      35: exec sp_trace_setevent @TraceID, 43, 9, @on

      36: exec sp_trace_setevent @TraceID, 43, 10, @on

      37: exec sp_trace_setevent @TraceID, 43, 11, @on

      38: exec sp_trace_setevent @TraceID, 43, 12, @on

      39: exec sp_trace_setevent @TraceID, 43, 13, @on

      40: exec sp_trace_setevent @TraceID, 43, 14, @on

      41: exec sp_trace_setevent @TraceID, 43, 15, @on

      42: exec sp_trace_setevent @TraceID, 43, 16, @on

      43: exec sp_trace_setevent @TraceID, 43, 17, @on

      44: exec sp_trace_setevent @TraceID, 43, 18, @on

      45: exec sp_trace_setevent @TraceID, 43, 27, @on

      46: exec sp_trace_setevent @TraceID, 43, 34, @on

      47: exec sp_trace_setevent @TraceID, 43, 35, @on


      49: -- SP:StmtStarting

      50: exec sp_trace_setevent @TraceID, 44, 1, @on

      51: exec sp_trace_setevent @TraceID, 44, 3, @on

      52: exec sp_trace_setevent @TraceID, 44, 12, @on

      53: exec sp_trace_setevent @TraceID, 44, 14, @on

      54: exec sp_trace_setevent @TraceID, 44, 22, @on

      55: exec sp_trace_setevent @TraceID, 44, 26, @on

      56: exec sp_trace_setevent @TraceID, 44, 27, @on

      57: exec sp_trace_setevent @TraceID, 44, 34, @on

      58: exec sp_trace_setevent @TraceID, 44, 35, @on


      60: -- SQL:BatchStarting

      61: exec sp_trace_setevent @TraceID, 13, 1, @on

      62: exec sp_trace_setevent @TraceID, 13, 3, @on

      63: exec sp_trace_setevent @TraceID, 13, 12, @on

      64: exec sp_trace_setevent @TraceID, 13, 14, @on

      65: exec sp_trace_setevent @TraceID, 13, 26, @on

      66: exec sp_trace_setevent @TraceID, 13, 27, @on

      67: exec sp_trace_setevent @TraceID, 13, 35, @on


      69: -- Exec Prepared SQL

      70: exec sp_trace_setevent @TraceID, 72, 1, @on

      71: exec sp_trace_setevent @TraceID, 72, 6, @on

      72: exec sp_trace_setevent @TraceID, 72, 9, @on

      73: exec sp_trace_setevent @TraceID, 72, 10, @on

      74: exec sp_trace_setevent @TraceID, 72, 11, @on

      75: exec sp_trace_setevent @TraceID, 72, 12, @on

      76: exec sp_trace_setevent @TraceID, 72, 13, @on

      77: exec sp_trace_setevent @TraceID, 72, 14, @on

      78: exec sp_trace_setevent @TraceID, 72, 15, @on

      79: exec sp_trace_setevent @TraceID, 72, 16, @on

      80: exec sp_trace_setevent @TraceID, 72, 17, @on

      81: exec sp_trace_setevent @TraceID, 72, 18, @on

      82: exec sp_trace_setevent @TraceID, 72, 27, @on

      83: exec sp_trace_setevent @TraceID, 72, 34, @on

      84: exec sp_trace_setevent @TraceID, 72, 35, @on



      87: -- Execution Warnings 

      88: exec sp_trace_setevent @TraceID, 67, 1, @on

      89: exec sp_trace_setevent @TraceID, 67, 8, @on

      90: exec sp_trace_setevent @TraceID, 67, 10, @on

      91: exec sp_trace_setevent @TraceID, 67, 11, @on

      92: exec sp_trace_setevent @TraceID, 67, 12, @on

      93: exec sp_trace_setevent @TraceID, 67, 14, @on

      94: exec sp_trace_setevent @TraceID, 67, 27, @on

      95: exec sp_trace_setevent @TraceID, 67, 31, @on

      96: exec sp_trace_setevent @TraceID, 67, 35, @on



    Image 1 – This shows you how to select information for the event and column.

       1: -- Get trace Event and Column details for event 10 RPC:Completed

       2: SELECT   e.Trace_Event_ID,

       3:          e.name AS Event_Descr,     

       4:          c.trace_column_id,

       5:          c.name AS Column_Descr,

       6:          c.type_name,

       7:          c.is_filterable

       8: FROM sys.trace_events e

       9: INNER JOIN sys.trace_event_bindings b

      10:   ON b.trace_Event_ID = e.Trace_Event_ID

      11: INNER JOIN sys.trace_columns c          

      12:   ON b.trace_column_id = c.trace_column_id

      13: WHERE e.Trace_Event_ID = 10

    Now if you wish to set filters on the information you wish to collect would do that next. I have some specific filters created plus there are a few commented out just for examples. Make sure to visit the setfilter link above to get a more detailed understanding on this subject as you may not get the results you were hoping just because of how this is configured.

       1: -- Set Filters 

       2: DECLARE @DB_ID int

       3: SET @DB_ID = DB_ID('MAS500_app')

       4: -- Creates a filter for only the database MAS500_app

       5: EXEC sp_trace_setfilter @TraceID, 3, 0, 0, @DB_ID                           -- 3 : DatabaseID

       6: -- Creates a filter to include only if column TextData contains tsoSalesOrder

       7: EXEC sp_trace_setfilter @TraceID, 1, 0, 6, N'%tsoSalesOrder%'                -- 1 : TextData, The 6 means LIKE

       8: -- Creates a filter to exclude application SQL Profiler and SQL Agent

       9: EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler%'                -- The 7 means NOT LIKE

      10: EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Agent%'                    -- The 7 means NOT LIKE


      12: -- Some addtional filter examples that might be useful

      13: -- To set a filter at the Server level instead of or in addtition to the database

      14: --EXEC sp_trace_setfilter @TraceID, 8, 0, 0, 'SERVERNAME'                   -- 8 : HostName

      15: -- This means to include if the ObjectName is equal to the passed in value

      16: --EXEC sp_trace_setfilter @TraceID, 34, 0, 0, 'spsoCreateSalesOrder'        -- 34 : ObjectName

      17: -- This means that TextData will = the value passed in

      18: --EXEC sp_trace_setfilter @TraceID, 1, 0, 0, 'Timeout getting Lock'         -- 1 : TextData

    Now start the Trace.

       1: -- Start the trace 

       2: EXEC sp_trace_setstatus @TraceID, 1 

    I already covered the error trap and exit logic in the Create Trace segment above but I will provide you with this code again just for clarity sake.

       1: -- display trace id for future references  

       2: SELECT TraceID=@TraceID  

       3: GOTO finish  


       5: -- error trap 

       6: error:  

       7: SELECT ErrorCode=@rc  


       9: -- exit 

      10: finish:  

      11: GO

    How do I find out about my traces(s)?

    To get a list of all the running traces on the server use the following statement. This will also show the SQL Server Default trace which is always traceid 1.

       1: SELECT * FROM :: fn_trace_getinfo(default)

    To look at the filters that are applied to a trace.

       1: SELECT * FROM :: fn_trace_getfilterinfo(default)

    To look at information about the events being traced by a trace use the following select.

       1: SELECT * FROM :: fn_trace_geteventinfo(default)

    We covered this already but I am providing these commands again just for clarity sake and a simple point of reference for you related to the setstatus procedure.

       1: -- To start a trace

       2: sp_trace_setstatus traceid, 1                -- TraceId would be the value of the trace

       3: -- To stop a trace 

       4: sp_trace_setstatus traceid, 0                -- TraceId would be the value of the trace

       5: -- To close and delete a trace

       6: sp_trace_setstatus traceid, 2                -- TraceId would be the value of the trace

    What do I do with my trace now that I am done?

    Well you have several options available to you. You can simply open the trace file with SQL Profiler and scroll through it like you might have done in the past or you can load it into a SQL table so you can run queries against it.

    The following code snippets show you how you can load data into a new table or an existing table from a trace file.

       1: -- Load into a new table (need to type in the name created above)

       2: SELECT * INTO master..TraceTable_RKL 

       3: FROM ::fn_trace_gettable('c:\Perflogs\MyTrace.trc', DEFAULT) 


       5: -- Load into an existing table  (need to type in the name created above)

       6: INSERT INTO master..TraceTable_RKL 

       7: SELECT * FROM ::fn_trace_gettable('C:\Perflogs\MyTrace.trc', DEFAULT) 

    Once you have the data loaded into a table you can look at it just like it is any other table.

       1: -- Look at data in table

       2: SELECT * FROM master..TraceTable_RKL

    Don’t forget to cleanup the table when you are finished.

       1: -- cleanup the table

       2: drop table master..TraceTable_RKL

    Can I look at the details/contents of the Trace File?

    Well yes you can.

       1: -- View the contents of the trace file without loading into a table

       2: -- or using SQL Profiler

       3: SELECT * FROM ::fn_trace_gettable('C:\Perflogs\MyTrace.trc', DEFAULT) 

    I hope you found this useful. Thanks for stopping by. Until next time…

    Windows Server 2008 High Performance vs Balanced Setting

    You can see in the Specification area below that the system contains a 2.70 GHz processor yet the Core Speed area shows that the system is actually running at 798.3 MHz. How can that be you may ask? Well that is what I continue to see repeatedly when logging in to systems when I am asked to review some performance issues.

    I am not saying this is the only issue but fixing this issue alone has solved quite a few of the reported issues, especially when I hear that my system is fast for a while then it slows down after several days or so. The tool shown above is called CPU-Z from CPUID. There are other tools that you can use. I am just a fan of this one. It is free and very small.

    So now let’s go look at where this is configured. I am doing the changes below in Windows Server 2008. This is in Windows Server Power Options.

    Go to Control Panel-System and Security-Power Options

    Make sure the setting has High Performance selected

    If the above setting was balanced it will adjust the Core Speed down based on average use to save on power and energy. Once it settles in on a number for Balanced it will not speed up again to full speed until you do a reboot. This is true in a virtual server as well.

    In addition to the Windows Server 2008 your physical systems also contain BIOS settings as well to allow you to configure the system to run balanced or optimized as well. Please consult your hardware documentation for more information on CPU Throttling.

    These terms are a little misleading in my mind as I think this means efficiently in terms of power consumption. I understand why these settings are important to save energy but if the purpose of your system is to be a high performance system then this setting defeats the purpose.

    X3 – Inquiry on Invoice Lines from Customer Search

    Sage ERP X3 has a lot of functionality and flexibility but the one thing that I am not fond of is the lack of support for shortcut keys. So that means I try to get as much information on the Window as possible to limit the amount of mouse movements and mouse clicks. The cosmetic enhancement that I am about to describe in this post is just make users more efficient.

    The issue at hand is that a user in Customer Service needs flexible customer search capabilities along with the need to look at the customer’s previous purchases then quickly go into create a Quote or Order. The Customer Search window is a great multipurpose customer search tool, as shown in figure 1.

    Figure 1: Customer Search window


    Yes I am aware that once you have searched and found a Customer on this window that you can click on one of the results rows, then click on the menu Inquiries > Invoices > Lines. This will also deliver the desired result that I am trying to accomplish. However I personally feel that is way too much mouse activity to be productive.

    Another way to use the Customer Search is if you bring back a list of customers you can then point your mouse to a cell in the Customer column and double-click this enables all the buttons available on the window as shown in Figure 2. From this form you can click on Order which would take you to the Orders window where you can select a previous order to view or copy, you can also just create a new order right there then return to the Customer Search window.

    Figure 2: Enabled buttons after doing a Customer Search


    This is what gave me the idea to add a button for Invoice Lines Inquiry as well to this form. So we need navigate to the Development > Processes > Windows task and select XCUSSCH as shown in figure 3. This is the form we wish to enhance.

    Figure 3: Development mode Customer Search window


    Then we need to go to the Buttons/menus tab. You can just add the new Button to the bottom of the list. So scroll down the list to the next open row and enter the information as shown in figure 4 row 51.

    Figure 4: Add a new Button to the Customer Search window


    The one thing to keep in mind is that the Code column must be unique. I assigned an arbitrary ‘z’ because I knew it was not in use by any of the other 50 rows.

    The one often overlooked but important step is to click on the Save button and then click on the Validation button. Without this you will not see your changes.

    Your new window should contain the Invoice Lines button as shown in figure 5. The Invoice Lines button is highlighted in yellow.

    Figure 5: Customer Search window with Invoice Lines button.


    I hope you found this helpful. As always feel free to reach out to me with comments.


    14 Days of Sage ERP X3 – Day 14 of 14 – X3 Connection Settings Configuration

    Well I am sure if you have been following these that you are ready for me to finish. Let me assure you that I am happy to be coming to an end as well. I promise I will continue to add to my ramblings for the last 14 days in the future. I want to thank you for stopping by and I hope you enjoyed what I have shared. It was my pleasure to finally put down on paper what I have been compiling in my head.

    The connection Settings for available folders on the login screen must be configured for each profile that logs into a workstation with the client software installed. We use this simple process to copy the connection settings across all profiles on a workstation by executing a windows batch file. Here are the steps involved:

    1. Log on to a workstation as a local administrator and launch the X3 client
    2. Click the screwdriver icon to create new connection settings

    3. Set up all the pertinent folders you wish to connect to
    4. Log into X3 via each connection to verify it is configured correctly
    5. Navigate through Windows Explorer to the profile you are logged in as:
      1. C:\Users\[username]\AppData\Roaming\Sage\Safe X3 Client\V1
    6. The contents of the AdxCnx.cfg file in this directory contain all the connection settings you just configured. Here is a sample file with 4 folders configured:
      1. X3#[sqlserver]#1806#########1#X3 (1806)#admin##DOMAIN\sagert#vqrgrQmsxuTrrw3uvatvauvaWdo#[sqlserver]#1#[sqlserver]#0#0#####BRY#1#X3V6##Sage ERP X3#V6.2#TRAINV6#[sqlserver]#1806#########1#TRAINV6 (1806)#admin##DOMAIN\sagert#vqrgrQmsxuTrrw3uvatvauvaWdo#[sqlserver]#1#[sqlserver]#0#0#####BRY#1#X3V6##Sage ERP X3#V6.2#PILOT#[sqlserver]#1806#########1#PILOT (1806)#admin#vmvvaTo2vccUm#DOMAIN\sagert#vqrgrQmsxuTrrw3uvatvauvaWdo#[sqlserver]#1#[sqlserver]#0#0#####BRY#1#X3V6##Sage ERP X3#V6.2#PROD#[sqlserver]#1806#########1#PROD (1806)#admin#ukrgrTevccTkrqweor#DOMAIN\sagert#vqrgrQmsxuTrrw3uvatvauvaWdo#[sqlserver]#1#[sqlserver]#0#0#####BRY#1#X3V6##Sage ERP X3 #V6.2#
    7. Copy the AdxCnx.cfg file to the X3 SQL server to a shared folder that is accessible to the X3 workstations
    8. In the same shared folder, create a windows batch file with the following command. This will copy the AdxCnx.cfg file from the server to the computer the bat file is executed on, search through the existing profiles for the existing AdxCnx.cfg file, and replace it with the configured one from the server
      1. replace “\\[servername]\[sharename]\AdxCnx.cfg” “C:\Users” /S/P
    9. Log into an X3 workstation and navigate to the shared directory and execute the bat file you just created (as an administrator). This will execute the copy to each profile.

    I want to thank Cliff Horst from our team for his assistance with this posting.

    I will be offline for you few days spending time with my wife…talk to you when I get back!

    14 Days of Sage ERP X3 – Day 12 of 14 – How to Deploy Sage ERP X3 Web Services

    Sage ERP X3 Web Services

    You have to install the necessary Patches for the version of Sage ERP X3 you are on to get the Web Services functionality. Once you these installed it will require configuration in the following areas.

    • X3 Console
    • X3 Client
    • X3 Web Server

    The order of these steps is important. The purpose of the steps is to publish the Web Services to the various X3 Folders. I am assuming that the Apache servers are already configured properly.


    Installing the Patch containing Web Services (this may not be necessary depending on what version of Sage ERP X3 you are starting with)

    As of this writing the Patch necessary for Sage ERP X3 v6.1 is A41. (NAV6-XA41.dat)

    Login into the Client of Sage ERP X3 under Folder ‘X3’ and navigate to the Development -> Utilities -> Patches -> Patch Integration function.

    Locate the patch file directory where you placed the dat file. Make sure you select Patch Integration and Comment Overwrite then click OK.



    Publish the Web Services

    Create an X3 User for the Web Services –

    Parameters -> Users -> Users by copying from the User WEB.

    Make sure to create a password for this new User. In order for the connection to work properly it needs a Password, plus it is just good practice to have a password on this user. Make sure this user has the checkbox for Web Services connection as shown above.

    You need to logout of the current user and login as the User you are using above then go to Usage -> Users -> Password Change.



    On the Server running the X3 ConsoleLaunch the SAGE X3 V1 Management Console and click on Web Servers section.

    You need to select the X3 Web Server from the Left List, then Click on the Solution Identifier in the Right Window. The Folders will be displayed in the bottom window pane. Check the Folders you wish to publish to. Then click the Apply to publish the Web Services to the selected Folders.

    NOTE: If you are unable to open adxinstalls.xml verify that the Windows Service for ADXADMIN is running.

    You can see the process running under the Details button as shown below during the Publication process.


    Now select the Folder you wish to configure the Web Services for. You will need to do this for each Folder individually if you want to enable Web Services for more than one Folder.



    You need to provide the following information for each Web Service pool.

    Pool Alias: This must be unique. This is how you will call the Web Service for this Folder.

    Pool Language: This relates to the

    Pool Userid: This is the X3 user you configured in the previous step.

    Pool Password: This is the password for the X3 user.

    Operating System Userid: This is a valid user for the OS.

    Operating System Password: This is the password for the OS user.

    MaxSize: Yo

    InitSize: Yo

    Autostart: This states if this Web Service pool should be auto-started.

    Reserved: t


    See the following screen as a sample of a completed configuration.

    Once you have this configured then click Apply. In the picture above once this is applied you will see the (0) switch to a (1) in the upper window pane, this being the first instance.

    Then in the X3 client you should see you valid Web Services Pool in Development-> Utilities -> Miscellaneous -> Web Services Pool.


    You can also verify that the Sage ERP X3 Web Service patch is installed at Development->Utilities->NA Utilities->System Inquiry.

    Now to configure and test the Sage ERP X3 Webservice Server (this will provide the Website ID for the next steps)

    NOTE: If you get this message it means that the latest web service is not published. You can try reinstalling the XA41 patch. If the patch is installed and you see the Web Services in Sage ERP X3 then you can just perform validation on the Web Services to republish them.

    The pool tried to invoke subprogram XTDSOH but the XML service description file for XTDSOH did not match the program’s expectations. In .net’s world this would be like an out of date wsdl file.

    If you need to verify and validate the Object go to Development->Processes->Object select the Object and click on Validation.


    If you need to generate the Web Services go to Development->Processes->Processes->Web Services. The XA41 Patch contains the Web Services that begin with ‘X’.


    If everything appears to be installed correctly to this point you may just need to publish the Web Services. You can do this by selecting the Web Service and click on Validation.


    Additional Web Service Configuration

    In order for the Web Services to process orders into Sage ERP X3 you will need to configure the following information for each Folder you wish to use.
    This information at Website Web Services->Web Service Parameters is used to determine certain default values when submitting orders.

    It is important that you fill in the Defaults values on the General tab. If you do not fill in these values you will receive errors when trying to process orders, especially the Sales Site and Shipping Site fields.

    The Websites tab above should show the published Website ID for you to associate to this Folder.

    Then create login id’s to be used by your Web Commerce solution. These login id’s will be linked with a Customer in Sage ERP X3.

    For the Credit Card Customer field if you select No then this customer can create orders based on the Terms on their Customer account or they can use a Credit Card. If you choose Yes then they are only able to place orders with a Credit Card.

    One additional area to verify that Web Services license is activated.


    To Verify the Web Service using the WebService tester

    Go to the Web Server that is hosting the Web Services. Click on the WebService tester link. You need to fill in the following information. After you have entered the information then click the Save Context button.

    Pool entry Group: This is the Web Service Pool name you created earlier.

    User id: The Sage ERP X3 user you created.

    Password: The password for the user.

    Language: Select the language you wish to use for this Web Service.


    Click on the Sub program link, key in one of the Web Services to consume. I have entered XTDSOH below, click on the “WebService Description” button. This should contain valid information in the Reply as shown below.


    The Sage ERP X3 Web Services are more like a mapping interface API. They are not what you might be used to in traditional SOAP development. They are a wrapper around the base Sage ERP X3 logic. I only point this out so you are aware of this before you start looking for traditional WSDL files.

    14 Days of Sage ERP X3 – Day 11 of 14 – How to Import Data from Sage ERP X3

    This process describes importing of transactions from two different systems. The first example is a Point of Sale (POS) system. The second example is an eCommerce system. They are both disparate systems. The assumption in this is example is that the transactions are being exported on a daily basis from the respected source system. The import jobs in these examples are used to bring the transactions into Sage ERP X3 on a daily basis.

    In the steps described below I also realize that once you are logged into Sage ERP X3 that you would just be switching functions and it would not require you to log in each time. I just did that to avoid any confusion if you were beginning at just that step. I have also not supplied any of the SSIS scripts to massage the example files. Your instance may not require any massaging of files.

    POS Import:

    Create the Files to import: (this is just for this example and your unique situation may differ)

    1. Generate a data file from POS for invoices to import called “LS POS.txt” and place it on the X3 server at \\servername\X3V6DOC
    2. Generate a data file from POS for payments to import called “Payments_(To_Import).iif” and place it on the X3 server at \\ servername \X3V6DOC
    3. Run the following jobs in SQL Server to manipulate the files for import into Sage ERP X3:
      1. POS Import Stock Issues: Creates file for Miscellaneous Stock Issues in Sage ERP X3 (all line items with a positive quantity on the invoice)
      2. POS Import Stock Receipts: Creates file for Miscellaneous Stock Receipts in Sage ERP X3 (all line items with a negative quantity on the invoice)
      3. POS Import GL: Creates file for a summarized GL Posting for everything in the LS POS.txt source
      4. POS Import GL Payments: Creates file for a summarized GL Posting for everything in the Payments_(To_Import).iif source

      NOTE: The 4 SQL Jobs outlined above are SSIS scripts created to manipulate the file format to from the POS system to the layout of the Sage ERP X3 import format. This step may not be necessary if you have the files already in the format for the import routines. These jobs can be manually executed from SQL Server Management Studio, or scheduled to automatically run on a set schedule.


    Import the data into Sage ERP X3:

    Template “SMO” – Imports the Miscellaneous Stock Issues:

    1. Log into Sage ERP X3 and go to Usage -> Imports /Exports -> Imports
      1. Key in “SMO” in the template field
      2. Choose Server as the location and Tab to enter the path to the data located at C:\Sage\SageX3V6\X3V6DOC\LSPOSX3.txt

    1. Click OK to import the data into Sage ERP X3

    Template “SMR” – Imports the Miscellaneous Stock Receipts:

    1. Log into Sage ERP X3 and go to Usage -> Imports/Exports -> Imports
      1. Key in “SMR” in the template field
      2. Choose Server as the location and Tab to enter the path to the data located at C:\Sage\SageX3V6\X3V6DOC\LSPOSX3Ret.txt


    Template “GASRKL” – Imports the Summarized GL for the POS Invoices and Payments:

    1. Log into Sage ERP X3 and go to Usage -> Imports/Exports -> Imports
      1. Key in “GASRKL” in the template field
      2. Choose Server as the location and Tab to enter the path to the data located at C:\Sage\SageX3V6\X3V6DOC\LSPOSGLX3.txt


    1. Log into Sage ERP X3 and go to Usage -> Imports/Exports -> Imports
      1. Key in “GASRKL” in the template field
      2. Choose Server as the location and Tab to enter the path to the data located at C:\Sage\SageX3V6\X3V6DOC\LSPOSGLPMTX3.txt


    eCommerce Site Import:

    Create the File to import:

    1. Generate data files from the eCommerce site for sales orders to import called “OrderHeader.txt” and “OrderDetail.txt” and place it on the X3 server at \\servername\X3V6DOC
    2. Run the following job in SQL Server to manipulate the files for import into Sage ERP X3:
      1. BW Import: Create file for Sales Orders in Sage ERP X3


      NOTE: The 1 SQL Job1 outlined above are SSIS scripts created to manipulate the file format to from the eCommerce site to the layout of the Sage ERP X3 import format. This step may not be necessary if you have the files already in the format for the import routines. These jobs can be manually executed from SQL Server Management Studio, or scheduled to automatically run on a set schedule.


    Import the data into Sage ERP X3:

    1. Log into Sage ERP X3 and go to Usage -> Imports/Exports -> Imports
      1. Key in “SOHFL” in the template field
      2. Choose Server as the location and Tab to enter the path to the data located at C:\Sage\SageX3V6\X3V6DOC\BWOrderX3.txt

    1. Click OK to import the data into Sage ERP X3

    14 Days of Sage ERP X3 – Day 10 of 14 – How to Export Data from Sage ERP X3

    I picked this to set up my next blog which is going to focus on Importing Data. These two topics are always highly requested tasks. People want to know how to get data out and get data into Sage ERP X3. The other thing that is not so obvious is where are these tasks located both in terms of how to run existing import/exports as well has creating new templates or customizing existing ones.

    I picked the Products table because they want to do cleanup of things related to the Product master or they want to export it to provide it to their customers or vendors.

    1. Go to Usage -> Imports-Exports -> Exports
    2. Choose a template (ITM for example)
      1. Uncheck the Chrono Management box.
      2. Choose whether to create the file on the server or the client. This selection will determine where the Data file path is located on the Sage ERP X3 Application Server or on the client that is running the Export.
      3. Click OK.
      4. Choose the location of the export file you are creating.


    The above was simple running an existing Export Template. The creation of Import/Export templates and other related tasks is located under Parameters -> Usage -> Import/Exports. I am not going to go through all of the tasks or even the Window fields. You can find this information in the Help system.

    One thing to remember if you create anything new or modify an existing Sage ERP X3 function to use the Activity Code field to help you identify your changes as well as prevent them from being overwritten by a future patch.



    14 Days of Sage ERP X3 – Day 8 of 14 – Additional Learning Resources from the Sage U ERP Team

    While I have been working through this process I have received some emails from the Sage U ERP Learning Team. The ‘U’ stands for University. I was going to discuss 2 of these topics but they have done an outstanding job, it would be a better resource for you than my writing.

    Their online home is located at www.SageU.com. However they have continued to publish mini overview videos and post them on YouTube. Here are the latest videos released this week.

    Sage ERP X3 Enterprise Webtop

    The Sage Enterprise Webtop allows you to connect to Sage ERP X3 through widgets using the Netvibes publishing platform. You have secure access to the Sage ERP X3 system on any mobile device in a personalized dashboard environment.

    Sage Search Utility

    Sage Search is a powerful search engine that allows you to search all the data within your ERP system. It provides a single point of access to information regardless of source, format, or location.

    New Dashboard Layout Options

    Learn about the new layout options in the Sage ERP X3 that allow you to easily mix and match gadgets of information to personalize your dashboard.

    Sales Signature Workflows

    New features regarding sales workflows allow for the definition of signatures for quotes, sales orders, and sales open orders.

    New Inquiry Functions (version 6.2)

    A new task, List of WOs, allows you to view a list of work orders according to criteria entered. This new task can be accessed from many areas in Sage ERP X3.

    There are more videos under the SageUERPLearning category on YouTube. Make sure you check those out as well. Randy Bacchus and the rest of the SageU team do a great job.

    14 Days of Sage ERP X3 – Day 7 of 14 – How to Configure Order Entry to Default to Ship-To tab, etc…

    Well we are half way through this crazy adventure I decided to take on. I hope you are finding it useful. Today we will discuss several enhancements you can make to Sales Order. Some of these changes are done to the Customer screen because the values from the Customer Ship-To carry through to the creation of a new order for the said Customer. I have broken this down into 3 enhancements in the blog just for clarity.

    Enhancement #1: Default to Ship-To Customer tab to have the Pick Ticket and Pack Ticket and Work Days automatically checked on new customer

    The purpose of this change is so that when you create a Sales Order for this Customer and corresponding Ship-To the Pick Ticket and Pack Ticket will automatically be checked on the new order. The first thing you need to know is what form and what fields. One of the easiest ways to determine this, we have already covered, the use of the F6 hot-key. So go to the Customer window, Common Data -> BPs -> Customers, then the Ship-To Customer tab. Click on the Picking Tick or Packing Slip under the Reports section. Now press F6. I pressed F6 while on the Picking Ticket field in this screen shot. Now I know the Screen name (BCP4) and the Field Name (XNPRFLG).

    Picking Ticket

    Packing Slip

    Working Days (here is Monday, you get the idea for the rest of the Work Days)


    All of these changes can be made using Development -> Processes -> Screens -> Screens.

    Select the BPC4 screen and click on the Fields tab. You want to change the Default Value column to 2 for the Picking Tick, Packing Ticket and Days of Week (Monday – Friday). First locate the Field. It will have the same name without the ‘X’. Scroll to the right to the column Default Value. Save your changes. Then you need to perform Validation on the screen to publish the change.

    NOTE: The reason this Default Value is 2 is that this is 1 for unchecked and 2 for checked.


    Enhancement #2: When entering sales order lines, product and quantity are the only fields to which we need to tab, how to limit the columns of entry on Order Lines…

    Navigate to Development -> Processes -> Screens -> Screens.

    Select Screen SOH4 and go to the Fields Tab. Scroll to the right to the Input column. Change the Input values to meet your requirements. In the case I was trying to satisfy, I would change the Input value to Display for all of the Fields except the ITMREF and QTY. Save your changes. Then you need to perform Validation on the screen to publish the change.



    Enhancement #3: Can we default a sales quantity of 1 to each order line that we add?

    Navigate to Development -> Processes -> Screens -> Screens.

    Select Screen SOH4 and go to the Fields Tab. Select the QTY Field and scroll to the right. Enter the Default Value, in this case we were making it 1. Save your changes. Then you need to perform Validation on the screen to publish the change.

    I hope you found this assortment of enhancements valuable. While you may not need these exact changes, I was also trying to cover a wide range of changes so that you could see how the SAFE X3 framework can be used to enhance your installation.

    14 Days of Sage ERP X3 – Day 6 of 14 – How Can I Track Changes I Make to My System

    Let me start by saying there are a lot of moving parts inside of Sage ERP X3. This makes it extremely important for you to do all you can to track any changes that you make to the system. This allows you to do all you can to protect yourself from overwriting changes. This blog is not all inclusive and is to server as a general overview. If you want more information on this topic please refer to the Sage ERP X3 help. You can also take various Sage ERP X3 training. This is covered in System Administration as well as the Developer Track in more detail.

    Activity Codes
    can and are used for a variety of reasons in Sage ERP X3. I am outlining here how they can protect your changes from being overwritten. All changes should be marked with an activity code because patching will not overwrite a component that has an activity code that is not included in the patch.


    • When the folder is revalidated, the object dictionary (tables, screens, etc.) is copied from the reference folder, overwriting existing objects. Only objects or object elements (field, indexes, etc.) that are flagged with a specific activity code are protected. Header level is the General Tab, do this on your own custom tables.
    • Do not put a value in the Activity Code at the header level of a base X3 table. This will prevent any table alterations with a patch from being deployed which can cause errors with code.
    • You can also put an Activity Code at the field level which will protect the field level from patches.
    • You can put an Activity Code on Screens, Objects, Windows, as well as Fields within these components.

    Activity Code that we would create should start with X, Y or Z. This allows you to inquiry on the enhancements. These rules are loosely enforced meaning they are for guidance and best practices recommendations. They are not strictly enforced rules by the system.

    • X is used for US created and vertical enhancements
    • Y is used for what you create for a customer as a Partner (Sage recommends to use one activity code per customer no matter how many mods)
    • Z is used for a customer to use for anything they may do by themselves

    Activity Code in relation to Database Tables

    • The Activity Code field is used in conjunction with the Module field to determine whether or not the table will be created in the database of the folder.
    • A table starting with X, Y, or Z is considered to be specific/custom and is not affected by a version change. These activity codes can be placed at the line level.


    How to create an Activity Code in your implementation?

    How to use the Activity Code in your implementation?

    How to identify an Activity Code used within your implementation?

    • Searching: Development -> Utilities -> Searches -> Activity Code


    • Mass Validation of all screens, objects, windows, etc by Activity Code. Development -> Utilities -> Dictionary -> Validation


    • Patches: This can be to test a patch install or even to create a patch from your customized enhancement. Development -> Utilities -> Patches ->

    Patch Creation

    Test a Patch

    Custom modifications are protected by an activity code and Patch Test will display the Sage ERP X3 element (screen, table, window, etc.), along with the activity code when a potential conflict is present.


    Configuring an Activity Code


    You can create a new Activity Code at Development -> Data and Parameters -> Development Setup -> Activity Codes. Once you have an Activity Code Created you can use this for all of your changes. Here are 3 examples of how to use this Activity within the system for tracking. For the examples I have created an Activity Code ‘RES’ for tracking my changes.


    • Create a Table – You can put an Activity Code at the Table, Fields, Index, or View levels.












    • Create a Screen – If you create a new screen or just want to flag an existing screen as changed, you can attach an Activity Code like ‘RES’ at the Screen level. This is true for Object and Window as well.



    • Modify a Field on a Screen – You can attach an Activity Code like ‘RES’ to any Field you add to a screen.





    It might have been better for me to do this sooner based on the fact that we made some changes and I could have included how to track these changes during those discussions. By doing it this way I am able to use those prior examples along with the best practices outlined above. I will use these practices in future blogs for repetition sake. Thanks again for taking the time to stop by my blog.

    %d bloggers like this: