Law Manager User Group

[BHousel]

Recently I ran into a problem with LawManager.Pro on a machine that had the SQL Server 2005 client tools installed. It didn't work because the BDE (Borland Database Engine) relies on features that are no longer supported by SQL Server 2005 client tools.

More info here: http://pauked.com/blog/?p=83

Can Law Manager 5.3, or a future version, be built without this BDE dependency?

[genabrave]

Is this resolved? We are planning to upgrade to sql 2005 by calendar year end

[nickpuschak]

TITLE: LawManager support for MS SQL Server 2005
TYPE: Developer Tip
PRODUCT: Case Management Foundation
KEYWORDS: MS SQL Server 2005
SYMPTOM/ISSUE:
==============
There are two issues regarding LawManager support for MS SQL Server 2005 that can cause problems.

ISSUES:
======
1) Client server LM Pro uses the Borland BDE, which communicates with the SQL DB-Library, which is not included anymore with SQL 2005. This is only a problem with LM Pro, not LM Web/Server, which uses ADO to communicate with SQL Server.

2) SQL 2005 does not support the older outer join SQL syntax if you are not running in SQL 2000 (80) compatibility mode. Many LawManager systems may have views that use the older MS SQL outer join syntax and the BDE uses that syntax to manage text fields. You can run Pro or Web in 2000 compatibiliy mode and not have to change your views. If you don't want to run in compatibility mode, you need to change your views and run LM Pro three tier or switch to LM Web.

NOTE: LM Pro 5.4 may switch from BDE to ADO.

SOLUTIONS:
=========
If you don't want to upgrade much you can run MS SQL Server 2005 in a 2000 compatibility mode by doing the following:
1) For freshly installed SQL clients you will need to copy the dll "C:\WINDOWS\system32\ntwdblib.dll" from a MS SQL Server 2000 installation to your Client PCs that are running LM Pro.
2) Set the Compatibility level of the database to SQL Server 2000 (80) on the SQL Server.

For LawManager 5.3 we have done the following to allow you to run in full 2005 mode:
The views ParentAddressView and ParentPhoneView were updated.
CREATE VIEW dbo.ParentAddressView AS
SELECT Address.*, AddressType.description address_type, Office.office_name
FROM Address LEFT OUTER JOIN Office ON Address.office_key = Office.office_key, AddressType
WHERE Address.address_type_key = AddressType.address_type_key
CREATE VIEW dbo.ParentPhoneView AS
SELECT Phone.*, PhoneType.description phone_type, Office.office_name
FROM Phone LEFT OUTER JOIN Office ON Phone.office_key = Office.office_key, PhoneType
WHERE Phone.phone_type_key = PhoneType.phone_type_key

The scripts with SQL changed:
DocMergeGenerateDoc (line 191 didn't need the outer join since Merge.selection_key is required)
GetComponentCaption - outer join wasn't needed (don't think script is being used)
MergeFieldSelectCode - outer join not needed (script may not be used)
The Scripts where there were Outer Join vars, but they weren't used:
MatterBudgetFinancialSQL - outer join vars not needed
MatterFinancialFooterFindSQL - outer join vars not needed
MatterFinancialHeaderSQL - outer join vars not needed
MatterFinancialSQL - outer join vars not needed
VendorHeaderSQL - outer join vars not needed
NOTE: LM Pro Client/Server uses the BDE and blob saving will NOT work since the BDE fires a SQL with the old style Outer Join, which we can not fix. The only solution is to run LM Pro in a three-tier deployment through LM Server, which uses ADO. ADO does not have this problem.
MORE INFO:
==========
http://pauked.com/blog/?p=83
DB-Library - Embedded SQL for C
Although the SQL Server 2005 Database Engine still supports connections from existing applications using the DB-Library and Embedded SQL APIs, it does not include the files or documentation needed to do programming work on applications that use these APIs. A future version of the SQL Server Database Engine will drop support for connections from DB-Library or Embedded SQL applications. Do not use DB-Library or Embedded SQL to develop new applications. Remove any dependencies on either DB-Library or Embedded SQL when modifying existing applications. Instead of these APIs, use the SQLClient namespace or an API such as OLE DB or ODBC. SQL Server 2005 does not include the DB-Library DLL required to run these applications. To run DB-Library or Embedded SQL applications you must have available the DB-Library DLL from SQL Server version 6.5, SQL Server 7.0, or SQL Server 2000.

The query uses non-ANSI outer join operators (”*=” or “=*”). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.
_________________
Nicholas Puschak
Bridgeway Software
VP Product Management