Upgrading Infor CRM (Saleslogix) LAN

I have a client who’s on the verge of upgrading from SLX LAN 7.5 to 8.2, with extensive customizations to their forms and scripts. So I finally wrote down the steps involved in doing that.

A customizations upgrade starts with running the Customized Plugins Query against your current database, to get a list of your customized plugins. Then you ideally want to have two other SLX deployments, to compare your current scripts against, and find the differences: An OOTB version of 7.5 (or whatever version you’re currently on), and an OOTB deployment of the version you’re upgrading to (presumably 8.2). The rationale is that any OOTB plugins you haven’t customized won’t show up in that query, and will correspondingly go through any version upgrade without their (not-present) customizations being overwritten with the new release. Likewise, any fully customized plugins (e.g., anything prefaced with “C_”) can’t, in principle, get overwritten by anything in the new SLX. So you (and that SQL query) only care about plugins that began as OOTB, and were later customized by yourself and/or your BPs.

Then, for each of the form/script/mainview plugins returned by that query, you copy the entire current-version code out of the customized form/script into Word (or Notepad). Do the same with the corresponding 7.5 OOTB and 8.2 OOTB versions of the plugin, into separate Word or text files. You then run up to three text comparisons between those three files in Word, as explained in any of the SLX upgrade documents, using the Review tab > Compare > Compare feature. That will highlight (in red) the differences between the Original (OOTB) and Revised files. (In Word, a red strikethrough in the output file means the line has been removed from the Revised file, compared to the Original; while red code text in that output file without a strikethrough means the line has been added to the Revised file):

i. 7.5 OOTB code [Original file] vs. current code [Revised file]
ii. 8.2 OOTB code vs. current code
iii. 7.5 OOTB code vs. 8.2 OOTB code

For each plugin, you then do a human evaluation of the “changed lines of code” highlighted by Word, to determine which way of doing things (out of the three below) would be the least effort, to wind up with your custom code merged with the 8.2 OOTB code:

i. If only a small amount of custom code has been added to the 7.5 OOTB, then copying and pasting the same code into the 8.2 plugin is usually the easiest.

ii. If only a small amount of new SLX code is present in the 8.2 OOTB plugin (vs the 7.5 OOTB), esp. compared to the quantity of custom code in your current customizations, then copying and pasting the new (differential/red Infor) 8.2 OOTB code into your current 7.5 plugin is usually the easiest.

iii. If Sage/Infor have made a lot of changes between their 7.5 OOTB and 8.2 OOTB, you’d usually start with the 8.2 OOTB plugin, and copy-and-paste your current custom code into that.

A lot of those evaluations typically just come down to how much “red” is in each of those plugin comparisons, and choosing the path with the fewest changes. If Sage/Swiftpage/Infor has made changes to the form layout/controls since 7.5, based on the (not particularly reliable, in the details) change logs in the upgrade PDFs, that of course also factors into your evaluation of the “fewest changes.” Also factoring into your decision is whether Infor’s code changes “step on” your customizations, or vice versa—i.e., whether it’s easier to merge your customizations into the Infor changes, or to merge the Infor changes into your customizations. There will be some plugins where the decision is obvious, and others where it’s a tough judgment call.

For non-scripted plugins (e.g., reports, picklists, navbars, menus, toolbars, and groups), you have to rely on the upgrade PDFs, to find which of those have changed. Even there, though, SLX again can/will only update the OOTB plugins. So unless you’ve modified those without changing the names, there’s no effect on them during the upgrade. (If you have modified them, then you’ll need to do manual comparisons in Architect, to see what pieces Infor [and you] have added, and what is the easiest way of arriving at an 8.2 version with your changes merged into it.) That is half the reason why custom plugins (e.g., new menus and toolbars) are prefixed with “C_”, rather than making the changes directly in the OOTB menu plugins.

Before actually making the above changes in your plugins, upgrade a copy of your current database to 8.2, so you can make all of the above-determined changes in there. (SLX sometimes changes ActiveX control versions in upgrades, so you can’t just make the changes in your current 7.5 and know for certain that they’ll work in 8.2 … even though I’ve never yet seen them break a control interface in an upgrade. The latter is why it works to start with the current 7.5 forms, and add the differential 8.2 code to them [in “ii”, above], when that’s determined to be the easiest approach. Your current 7.5 customized-from-OOTB forms, of course, will still exist in that upgraded database, they’ll just have been unreleased by the upgrade. So for plugins where you go with the “ii” approach above, you’d obviously need to manually re-release them.)

Then create a bundle of all the plugins you’ve just changed in your upgraded 7.5-to-8.2 database, to apply to your (copy of the current 7.5) testing environment. (You can get that list just by querying the Plugin table, and ordering by descending ModifyDate or the like.)

There are no other special tools or knowledge that any BPs have, for doing upgrades.

SalesLogix Eval Licenses

Looking for the latest SLX Eval Licenses?

They’re under the “Sales Tools” folder on the new partner site (https://partners.sagenorthamerica.com).

Where can you find that Sales Tools folder?

Right where you’d expect it:

Knowledgebase -> Browse Documents -> For Partners -> Sage SalesLogix -> Sales Tools -> 11-19 / 19 -> Sage_SalesLogix_Evaluation_Keys.doc

Crystal Timeout‏

I’ve got a Crystal XI report query that’s going to take a long time to run, so I was wondering how you can set/disable something like a CommandTimeout property in Crystal.

Searched through the first 100 Google results for “Crystal Reports Timeout,” and the closest I found to an answer was a person asking the same question …

I’m using Crystal Reports XI. My report contains SQL statement that may take very long time to run. Should I worry about increasing the command timeout? If yes, how can I do it in Crystal Reports XI ? Is it something that is to be done in the Crystal Reports Server level (Central Management Console) or in the reports level?

… which no one had responded to!

Finally found this, in the Crystal HTML Help:

The time out interval determines (in minutes) when inactive report sources are disposed. By default, the timeout interval is 10 minutes. You can configure the Java Reporting Component to have no timeout by setting the value to 0.

The timeout interval only applies to inactive reports—reports that are being processed are not timed out as a result of exceeding this value. [I assume that a report is “inactive” if it’s waiting for the query result set from the server, and that “being processed” would happen after the result set is returned to the report from the server.] Each time a report source request is successfully completed, the timeout timer is reset. If a report source is not used within the timeout interval, it is disposed and its resources are made available to other processes.

For Crystal Reports XI R2, the CRConfig.xml file is installed by default in the \Program Files\Business Objects\common\3.5\java directory.

Query Timeout Expired

I was getting “Query timeout expired” errors in the code for doing an import from a back-end ERP into SalesLogix.

So I logged into the database server, right-clicked on the connection, Properties, and then opened the Connections items on the Server Properties form, and changed the Remote query timeout from its default of 600 seconds to 0, to disable that.

Re-ran the code … but it was still giving the same (unpredictable, intermittent) error.

Turns out that the objConn in code has a CommandTimeout property, too … which defaults to 30 seconds. So if you set objConn.CommandTimeout = 0 (or just to a really high number), you can disable that, too.

Those timeouts are of course affected by the speed of the server, so they wouldn’t be occurring on a faster machine.

SData Core Specification

Documentation for the Sage SData Core Specification.

SQLOLEDB, SQLNCLI connection pooling issue‏

This is exactly the crazy problem I was running into yesterday, in processing a text-file import from a back-end ERP into SLX:

We have some VB6 code accessing MS SQL Server with ADODB using the SQLOLEDB provider which gives Timeout Expired errors when a large number of executes are performed against the same ADODB connection and when there is a recordset open on the same connection. The application opens a single ADODB connection at startup and closes it on shut down. I have determined the timeout error is based on the ConnectionTimeout value (set it low and it happens more quickly/often).

I tried using one ADODB connection for all cursors and another ADODB connection for all executes. This solved the timeouts however we use begintrans/open a recordset/run an execute based on the recordset contents/committrans sequence in our code so that doesn’t work.

I discovered the MS articles 258697 and 271128 which suggest that the problem is when a firehose cursor is used. Yes, our default cursor was a firehose, so I tried all combinations of the CursorType and LockType properties when opening the recordsets but no combination stopped the subsequent executes from using the same explicit connection.

I then discovered the MS article 907264 which suggests using the new (with SQL Server 2005) SQLNCLI provider. This provider supports multiple active result sets and so its behaviour is supposedly different firehose cursors. Initial testing with this provider suggested it did indeed solve the problem but performing a much larger number of executes caused varying failures. All recovered on retry but??? The errors seen were ‘Named Pipes Provider: No process is on the other end of the pipe.’ and ‘Failed to read prelogin response.’ Named pipes are turned off at the server and weren’t being used by the client app. It was almost as if the SQL Server had run out of TCP ports and so decided to fall back to named pipes. This was certainly a better result than that we were getting with SQLOLEDB but still not bulletproof.

When you’re iterating through a recordset with thousands (in my case, over 100,000) of values in it (e.g., PK ID’s, used to grab the rest of the info for each record, in turn), one of the things you can do is to throw the recordset into an array (e.g., via objRs.GetRows, or with a Do While … Loop) as soon as you get it back from the server, and then immediately close the objRs. For me, in addition to using the SQL Native client rather than the SLX OLEDB Provider, that appears to have fixed the issue.

VMWare

I wanted to be able to access my VMWare (7.0) websites from my host machine (and ultimately, from the outside world)—for testing SLX Mobile 2011 customizations, for one.

I found this old (2006) article: Allow Access to a VMware Virtual Machine(NAT) From Another Computer. But things have changed a little since then. So here are the updated steps:

1. On the machine with the website you want to be able to access, open a Command Prompt, and type in ipconfig. Note the IP Address of the machine.

2. On the same active machine, in the VMWare console go to VM -> Settings…, and on the Hardware tab, select Network Adapter. In the lower right-hand side of the page, change the Network connection setting to Custom: specific virtual network, and select VMnet8 (NAT) from the drop-down. Click OK.

3. In the VMWare console, go to Edit -> Virtual Network Editor….

4. Select the VMnet8 network, and click the NAT Settings… button.

5. Under Port Forwarding, click the Add button, and enter the Host port (e.g., 8080), Virtual machine IP address (from #1), and Virtual machine port (e.g., 80).

6. OK out of all of the boxes, to set the info and automatically restart the DHCP and NAT Services.

If you now type in http://localhost:8080 into the address bar of a browser on your host machine, you should be able to see the (default, port 80) website on the VMWare virtual machine.

SSRS

I’ve been creating SQL Server Reporting Services (2005) reports for an independent client of mine for several years, after spending six months writing them for a Microsoft Certified Partner back in 2006.

If you ever need to work with multi-valued parameters in SSRS 2005, you need to know about the following, life-saving page:

Passing multi-value parameter in stored procedure (SSRS report).

In case of sql query as well as stored procedure, all selected values of the multi-value parameter will be passed as ‘,’ (comma) separated. So we should use IN clause in WHERE to impose the desired condition.

Problem: If we use stored procedure passing an input parameter which is multi-value parameter, SSRS will take all those selected multiple values as different-2 parameters (as they all are ‘,’ separated). And thus if we do multiple selection (or SelectAll) error would be there, though it will work fine if we select only one value.

error(s): Must Declare Scalar variable @parameter OR number of parameters being passed are more than expected….

Now if preview the report it will work fine for single value of color, but if we select multiple colors from colors parameters, it will give error. To pass the complete list of selected values of @color parameter as a single value/string we should use a function JOIN().

In data set properties of AdventureWorks dataset, click on parameters tab and use JOIN() funtion as:

=JOIN(Parameters!Color.Value, “,”)

This function will join all the color values and will a give a comma separated values thus a single value as a whole will be passed to the SP….

To use this list of comma separated values in the stored procedure we need to split them in a proper way as they are as : ‘value1, value2, value3′, but we need them as ‘value1′, ‘value2′, ‘value3′ to use them in IN clause. So we need to have a UDF function which will take this string of values and will return all them splitted. And then use them in SP as:WHERE @Color IS NULL OR Color IN (SELECT * FROM SplitList(@Color, ‘,’))

where SplitList is a UDF which takes the list and the de-limiter as input parameters and splits all the values.

The function I always use for splitting is one I found in a book years ago:

CREATE FUNCTION [dbo].[fn_MVParam](@RepParam nvarchar(4000),
   @Delim char(1)=',')
RETURNS @Values TABLE (Param nvarchar(4000)) AS
   BEGIN
   DECLARE @chrind INT
   DECLARE @Piece nvarchar(4000)
   SELECT @chrind = 1
   WHILE @chrind > 0
      BEGIN
         SELECT @chrind = CHARINDEX(@Delim, @RepParam)
         IF @chrind > 0
            SELECT @Piece = LEFT(@RepParam, @chrind - 1)
         ELSE
            SELECT @Piece = @RepParam
         INSERT @Values(Param) VALUES (@Piece)
         SELECT @RepParam = RIGHT(@RepParam, LEN(@RepParam) - @chrind)
         IF LEN(@RepParam) = 0 BREAK
      END
   RETURN
END

SLX Web FAQ

I’ve been working on my first SLX Web project, going through the extreme learning curve for that over the past two weeks—adapting an existing LAN Tickets customization for the Web client.

Today, I stumbled on the following valuable document: Sage_SalesLogix_72_Web_Client_Programming_FAQ.

Checkbox Columns

If you need to add a checkbox column to an SLX LAN datagrid, here are the steps (after you’ve already set up the grid’s SQL/Layout properties):

1. On the grid’s Properties page, clear the ReadOnly and RowSelect properties
2. Go into the Columns property of the grid (i.e., click on the ellipses button for the property)
3. Right-click on the column you want to set to a checkbox, and select Change Column Type…
4. From the combo box, select Check, and OK
5. Right-click on the column again, and select Properties…
6. Make sure the Read-Only box is cleared
7. For each of the other grid columns in turn, set the Read-Only property box to be checked (assuming you want the checkbox column to be the only editable column on the grid)

This is documented somewhere, right?

Yeah: Right here. 🙂