SQLOLEDB, SQLNCLI connection pooling issue‏

April 1, 2011

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.


SSRS

March 27, 2011

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 Remote Users and MSXML 6.0 SP2

June 25, 2010

If you’re upgrading SalesLogix for Remote users, and doing that on SQL Server Express Edition 2005, and having to reinstall SSEE because they’ve lost the sa password (etc.), guess what?

Before you do that, if Microsoft MSXML 6.0 SP2 exists on the system (in Control Panel, Add/Remove Programs), you have to uninstall that, or the SSEE installation will fail, with an error message (displayed via a hyperlink to the setup log file, not via a dialog box) that DOES NOT give you much help in figuring out what actually went wrong.

And you can’t uninstall MSXML 6.0 SP2 from Control Panel (Add/Remove Programs), either. Instead, you have to download the Windows Installer CleanUp utility, and run that separately.