SQL Server connection

[Leoric]

Hi

I have a problem generating new connections to the execution of SQL queries.
Sometimes, when I run queries, it does not use the connection created normally, it created a new connection with the default settings.

Example 1: Use of the normal connection

Code: Select all

RPC:Completed	exec sp_executesql N'Select SocValue from ParamSoc where SocParam = @P1 AND SocUser = @P2',N'@P1 nvarchar(26),@P2 nvarchar(5)',N'ShowOrderLineTotalCurPrice',N'Admin'
SQL:BatchCompleted	SET NO_BROWSETABLE ON
SQL:BatchCompleted	SELECT ORDERS.*, Cus.CusName, Cus.CusFirstName, Cus.CusAddress1, Cus.CusAddress2, Cus.CusZipCode, Cus.CusCity, Cus.CusCategoryCode, Cus.CusForbidden, Cus.CusCurrency, Cus.CusInvAccount, Cus.CusTypeFiscal, Cus.CusTypeCusFisc, Cus.CusNoSocAux, Cus.CUSPORT, Cus.CUSCOUNTRY, Cus.CUSSCALEPORTCODE, Cus.CusFrancoValue, Cus.CusFrancoTypeValue, Cus.CusBaseTransport, Cus.CusDepot, Cus.CusULHeterogen, Cus.CUSDELAIEXPEDATE, Cus.CUSEXPOFFSET,Cus.CusDepotTransfer,Cus.CusNature,Cus.CusComment3,Cus.CusCostNumber,Cus.CusDestinationDepot,Cus.CusCeilling, Cus.CusFidelityActive, Cus.CusDepositInAccount, Cus.CusCriteria18, CusInv.CusName InvCusName, CusInv.CusFirstName InvCusFirstName, CusInv.CusForbidden InvCusForbidden, CusInv.CusCeilling InvCusCeilling, DEOZONE, DEOCARRIER, DEONAME, DEOFIRSTNAME FROM ORDERS INNER JOIN CUSTOMER Cus ON ORDNOCUSTOMER=Cus.CUSNOCUSTOMER LEFT OUTER JOIN CUSTOMER CusInv ON ORDINVOICENOCUS=CusInv.CUSNOCUSTOMER LEFT OUTER JOIN DELIVERYORDER ON ORDNOORDER=DEONOORDER  WHERE (OrdStatus2 or OrdStatus is NULL) and OrdExpeDate>='01/01/2011' and OrdExpeDate0 and OrdType not in ('E','R') ORDER BY ORDNOORDER DESC
SQL:BatchCompleted	SET NO_BROWSETABLE OFF
SQL:BatchCompleted	SELECT ACSCODE, ACSDESIGNATION FROM ACCOUNTSTYLE ORDER BY ACSCODE

Example 2: Using a new connection

Code: Select all

Audit Login	-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language Français
set dateformat dmy
set datefirst 1
set transaction isolation level read committed
RPC:Completed	exec sp_executesql N'Select SocValue from ParamSoc where SocParam = @P1 AND SocUser = @P2',N'@P1 nvarchar(26),@P2 nvarchar(5)',N'ShowOrderLineTotalCurPrice',N'Admin'
Audit Logout
Audit Login	-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language Français
set dateformat dmy
set datefirst 1
set transaction isolation level read committed
SQL:BatchCompleted	SET NO_BROWSETABLE ON
SQL:BatchCompleted	SELECT ORDERS.*, Cus.CusName, Cus.CusFirstName, Cus.CusAddress1, Cus.CusAddress2, Cus.CusZipCode, Cus.CusCity, Cus.CusCategoryCode, Cus.CusForbidden, Cus.CusCurrency, Cus.CusInvAccount, Cus.CusTypeFiscal, Cus.CusTypeCusFisc, Cus.CusNoSocAux, Cus.CUSPORT, Cus.CUSCOUNTRY, Cus.CUSSCALEPORTCODE, Cus.CusFrancoValue, Cus.CusFrancoTypeValue, Cus.CusBaseTransport, Cus.CusDepot, Cus.CusULHeterogen, Cus.CUSDELAIEXPEDATE, Cus.CUSEXPOFFSET,Cus.CusDepotTransfer,Cus.CusNature,Cus.CusComment3,Cus.CusCostNumber,Cus.CusDestinationDepot,Cus.CusCeilling, Cus.CusFidelityActive, Cus.CusDepositInAccount, Cus.CusCriteria18, CusInv.CusName InvCusName, CusInv.CusFirstName InvCusFirstName, CusInv.CusForbidden InvCusForbidden, CusInv.CusCeilling InvCusCeilling, DEOZONE, DEOCARRIER, DEONAME, DEOFIRSTNAME FROM ORDERS INNER JOIN CUSTOMER Cus ON ORDNOCUSTOMER=Cus.CUSNOCUSTOMER LEFT OUTER JOIN CUSTOMER CusInv ON ORDINVOICENOCUS=CusInv.CUSNOCUSTOMER LEFT OUTER JOIN DELIVERYORDER ON ORDNOORDER=DEONOORDER  WHERE (OrdStatus2 or OrdStatus is NULL) and OrdExpeDate>='08/01/2011' and OrdExpeDate0 and OrdType not in ('E','R') ORDER BY ORDNOORDER DESC

In Example 2, we see that it uses one connection for each request and send it to the default settings each time.

How to use only one connection?

I try the MultipleActiveResultSets parameter, but without success.

Regards

Léonard.

[AndreyZ]

The MultipleActiveResultSets option works only when SQL Native Client is used. Please set the OLEDBProvider specific option to prNativeClient and check if the problem persists. Here is an example:

Code: Select all

UniConnection.SpecificOptions.Values['OLEDBProvider'] := 'prNativeClient';

If the problem persists, please try creating a small sample that demonstrates this problem and send it to andreyz*devart*com.