Some useful info about changes in SQL SP4 that could affect SalesLogix, courtesy Mike Spragg of e1 Business:
5.1.16 Change to Maximum Network Packet Size Introduced in SP4
In SP4, the maximum value for the network packet size option (set using sp_configure) is 32767. This is slightly less than half the previous maximum of 65536. During upgrade, existing values larger than 32767 will automatically be adjusted to 32767. If a script attempts to use sp_configure to set a value larger than 32767 but less than or equal to 65536, the value will also be set to 32767. Setting the network packet size to a value larger than 65536 results in an error.
5.1.17 Optimization of Queries with Large IN Lists or Many OR Clauses Introduced in SP4
SP4 includes a change in the behavior of the SQL Server optimizer that affects queries containing predicates with large IN lists or many OR clauses. More specifically, this change (introduced in SQL Server 2000 hotfix 789) affects queries that contain (or that can be rewritten using an equivalent expression that contains) the following:
a. More than 10,000 elements in the IN list
b. Two IN lists, each containing over 100 elements
c. Over 10,000 disjuncts in the OR clause
d. A combination of OR clauses and IN lists such that their equivalent expression will contain more than 10,000 disjunctsBecause of this change, SQL Server uses less memory when compiling these types of statements and thereby avoids out-of-memory errors. In rare cases when these types of queries are run on systems that have very large memory and a low degree of parallelism, a query plan with inferior performance may be chosen by the optimizer. To override the change in optimizer behavior, trace flag 9060 is provided in this service pack. By default, trace flag 9060 is OFF. When the trace flag is ON, SP3 behavior prior to hotfix 789 is enabled. If error 701 (insufficient system memory) is encountered when the trace flag is ON, consider rewriting the queries using temporary tables or table variables for the values in the IN lists. For numerical ranges, use BETWEEN clauses or greater than (>) or less than (<) operators. For information on using trace flags, see “Trace Flags” in SQL Server Books Online.
According to Mike, despite what the install guide says – it does NOT reset the packet size option. You MUST set this back to 4096/8192 manually if you have changed this value BEFORE running the SalesLogix upgrade – failure to do so means your upgrade will fail with:
“error running script: replsys.sql(1)”
And in replsys.out you will see:
[DBNETLIB]General network error. Check your network documentation.
[DBNETLIB]ConnectionRead (recv()).
I was about to ask the discussion group that Mike frequents about SP4 and since I’m not a BP I don’t have access to the newsgroups (though I did have access to slxdn which is now no longer available). I suppose the document Mike sent was much bigger (5.1.16 gives it away) but this is the only thing relevant to SP4?
Now I don’t have to ask since you’ve explained what I need to know. Is this a necessary upgrade for security reasons or is there a problem with waiting?
Also I’m not fully following what Mike said. If you changed the packetsize from 4096/8192 you have to change this back before the upgrade? I thought anything under 32767 would work but I guess Monday’s affecting me a little too much.
Thanks for the heads up. I would have been asking somewhere if I didn’t come across this sooner.