MS Sql Server 2000 and dbExpress

rbuser

I am trying to build an end user reporting application with Sql Server and
dbExpress. The problem comes with the designer. In the Data Settings I set
the Session Type to dbExpressSession and Database Type to dtMSSQLServer and
SQLType to sqSQL2.

When I Open the report designer I go to the Query Wizard and find no tables
listed to choose from.

I went to the data settings tab and found all my settings there that I had
set at run time, except the database type. It was blank. When I tried to
Select SQL Server It weas not there in the list (strange since it was a
vailable in the designer).

Am I missing something? I know it should work with dbExpress and MSSQL (I
was told it would when I upgraded to version 10). Do I need to add
something to the uses list to get the SQL Server Option when using dbExpress
(like I need to to get the database tabl to show)?

Nigel.

nardmoseley

I researched this and have updated the DADE plug-in for DBExpress,
daDBExpress.pas for RB 10.03. Send an email request to
support@digital-metaphors.com and we can send you the updated unit.



--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Thanks. Have sent the email.

Nigel.

rbuser

Have Dowloaded 10.04 and installed it. MSSQL Server now appears in the data
base list of the Data Settings Form. However the list of tables is still
empty when I go into the Query Wizard to create a query.

What else is it that I need to do?

Nigel.

nardmoseley

I installed SQL Server and performed some testing using D2006, RB 10.04, and
dbExpress

- the query tools work fine. I encountered no issues with gettingthe list of
tables or fields. Make sure that you have DataSettings.UseDataDictionary set
to false. (Implementing the DataDictionary support should always be a
secondary step).

- the report explorer only partially works. I can create new folders and I
can create reports that have no queries. But if I create report that has a
query and preview the data or preview the report (basically do something
that opens the query), then dbExpress raises an error when I save. The error
is 'Database Server Error: Cannot create new transaction because capacity
was exceeded." It seems that the combination of dbExpress and MS SQL has
limitations that do not exists when using dbExpress with other drivers. Nor
does this limitation exist with ADO (which also uses the same underlying MS
OleDB library)


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

>>Database Server Error: Cannot create new transaction because capacity was

This occurs because the OLEDB Driver for SQL Server does not support nested
transactions. My guess is you are trying to do two updates in the database
at once within a single BeninTransaction/Commit.

I am using TClientDatasets without explicit transaction control to get
around this issue. Maybe this is the problem. The report itself may be
using Transactions.

Usually I Have TSQLQuery -> TDataSetProvider -> TClientDataSet ->
TDataSet -> TppDBPipeline which is then connected to the Designer and Report
Explorer Components.

I Call the ApplyUpdates method in the AfterPost Event of all the data sets.

If you have an Example Application you can post (I assume you are using the
Northwind sample database) I can take a look at it to see where I am going
wrong and maybe I can work out where the error message ids comming from.

I am using Delphi 7 (Sp1), the latest version of the D7 dbExpress driver
that Borland issued (in late 2003) and SqlSever 2k.

Nigel.

Nigel.