On 14.03.2008 11:48, Martin Evans wrote:
Alexander Foken wrote:
On 14.03.2008 09:46, Martin Evans wrote:
Alexander Foken wrote:
There is one annoyance with SQL server: You can't have more than
one "active" statement, i.e. a statement that is executing but not
yet finished, per connection. This is a limitation of the SQL
server protocol, not a DBI limitation. Other databases, like Oracle
and the free PostgreSQL, can handle at least a sufficiently large
number of parallel active statements. For the MS SQL Server, you
have to use several distinct connections if you need parallel
active statements.
That is no longer true Alexander:
MARS. Multiple Active Result Sets (MARS), enables your applications
to have more than one pending request per connection, and, in
particular to have more than one default result set open per
connection. The MARS feature removes the restriction present in
earlier versions of SQL Server in which an open default result set
blocks the driver from sending requests to the server until the
entire result set is consumed.
Glad to see that this particular restriction is gone. I once had to
port one of my projects from Oracle to SQL Server 8, when that was
the most recent version. SQL Server 8 did not have that feature or it
was so well hidden that I did not found it in days of research. That
project is also used with newer SQL Server versions, SQL Server 2003
is the latest version used in a production environment I know of. For
all SQL Server versions, the project uses the multi-connection hack.
Switching to a single connection with multiple parallel statements
would be a great improvement.
Is it possible to use that feature with DBD::ODBC? What are the
minimum required versions of DBI, DBD::ODBC, SQL Server? Where and
how is MARS enabled?
Alexander
I believe this came in around SQL Server 2005. You can set it in some
registry setting! or in the connection string with
"MARS_Connection=Yes". You can find detailed info about MARS on the MS
web site including some things to watch out for when using MARS and
transactions. You will need a 2005 ODBC Driver and the best one to use
is the new "SQL Native Client" (mine is version 2005.90.1399.00).
As you can set it in the connection string you can use it from
DBD::ODBC using something like:
set DBI_DSN=dbi:ODBC:DSN=mydsn;MARS_Connection=Yes
If you do this and run the 20SQLServer test you'll find the multiple
active statement tests fail because they check you cannot use MAS and
you can now.
Martin
Thanks a lot, I'll see if my ex-collegues can get it working with
DBD::ODBC 1.13 + my Unicode patch. They won't run the DBD::ODBC build
tests, they will just check if the application works with the
multi-connection hack disabled.
Alexander
--
Alexander Foken
mailto:[EMAIL PROTECTED] http://www.foken.de/alexander/
