Board index » delphi » About General SQL error while using MSSQL 7 with BDE

Kevin Frevert

Delphi Developer
Tue, 27 May 2003 21:07:44 GMT

About General SQL error while using MSSQL 7 with BDE

cms,

   Wow, that is a big (wide) table.  I don't know why you would be getting
that error, but #1, never use TTables against a SQL server.  Always use
TQuerys.  I suggest only selecting the fields/records the user wants to see.

Good luck,
krf

 
Jeff Alexande

Delphi Developer
Wed, 28 May 2003 01:05:21 GMT

Re:About General SQL error while using MSSQL 7 with BDE


I've seen the same error occur when opening a lot of tables at once and it
usually happens on tables that are too wide (in my opionion).  If you figure
this one out could you please post your solution.  My best guess is that it is
what Kevin Frevent alluded to.  The table is much wider than normal and the
error is related to this.  I am also using TTables to SQL Server7 w/ D5.  (I
know, don't use TTables, but I have to since I don't have any say in the data
access method).  Just a little more information to think about.
Quote
cms wrote:
> Dear Sir:
> I use MSSQL7 and BDE native driver in D5 for read a table, but if I active
> the TTable, the system display a error information "General SQL error" and
> display all of fileds name in the TDBGrid.
> And I use  SQL Explorer, when I open the Data sheet, this phenomenon is
> displayed too. Why ? And how can I do ?
> The table structure is:
> CREATE TABLE dbo.Hist_EndShiftRecord2000 (
>   fldRecordID char(51) DEFAULT '
> ',
>   fldLoginDate char(10) DEFAULT '          ',
>   fldLoginTime char(8) DEFAULT '        ',
>   fldLogoutDate char(10) DEFAULT '          ',
>   fldLogoutTime char(8) DEFAULT '        ',
>   fldStaAddress char(19) DEFAULT '                   ',
>   fldLaneDirect varchar(5) DEFAULT '     ',
>   fldOperatorID varchar(6) DEFAULT '          ',
>   fldGroupName smallint DEFAULT 6,
>   fldShiftNo smallint DEFAULT (-1),
>   fldNormalCards int DEFAULT 0,
>   fldBadCards int DEFAULT 0,
>   fldReceptNum int DEFAULT 0,
>   fldTotalNoneCardVehicles int DEFAULT 0,
>   fldTotalFeeVehicles int DEFAULT 0,
>   fldVehicleType01FeeVehicles int DEFAULT 0,
>   fldVehicleType02FeeVehicles int DEFAULT 0,
>   fldVehicleType03FeeVehicles int DEFAULT 0,
>   fldVehicleType04FeeVehicles int DEFAULT 0,
>   fldVehicleType05FeeVehicles int DEFAULT 0,
>   fldVehicleType06FeeVehicles int DEFAULT 0,
>   fldVehicleType07FeeVehicles int DEFAULT 0,
>   fldVehicleType08FeeVehicles int DEFAULT 0,
>   fldVehicleType09FeeVehicles int DEFAULT 0,
>   fldTotalOfficialVehicles int DEFAULT 0,
>   fldVehicleType01OfficialVehicles int DEFAULT 0,
>   fldVehicleType02OfficialVehicles int DEFAULT 0,
>   fldVehicleType03OfficialVehicles int DEFAULT 0,
>   fldVehicleType04OfficialVehicles int DEFAULT 0,
>   fldVehicleType05OfficialVehicles int DEFAULT 0,
>   fldVehicleType06OfficialVehicles int DEFAULT 0,
>   fldVehicleType07OfficialVehicles int DEFAULT 0,
>   fldVehicleType08OfficialVehicles int DEFAULT 0,
>   fldVehicleType09OfficialVehicles int DEFAULT 0,
>   fldTotalMilitarylVehicles int DEFAULT 0,
>   fldVehicleType01MilitaryVehicles int DEFAULT 0,
>   fldVehicleType02MilitaryVehicles int DEFAULT 0,
>   fldVehicleType03MilitaryVehicles int DEFAULT 0,
>   fldVehicleType04MilitaryVehicles int DEFAULT 0,
>   fldVehicleType05MilitaryVehicles int DEFAULT 0,
>   fldVehicleType06MilitaryVehicles int DEFAULT 0,
>   fldVehicleType07MilitaryVehicles int DEFAULT 0,
>   fldVehicleType08MilitaryVehicles int DEFAULT 0,
>   fldVehicleType09MilitaryVehicles int DEFAULT 0,
>   fldTotalEmergencyVehicles int DEFAULT 0,
>   fldVehicleType01EmergencyVehicles int DEFAULT 0,
>   fldVehicleType02EmergencyVehicles int DEFAULT 0,
>   fldVehicleType03EmergencyVehicles int DEFAULT 0,
>   fldVehicleType04EmergencyVehicles int DEFAULT 0,
>   fldVehicleType05EmergencyVehicles int DEFAULT 0,
>   fldVehicleType06EmergencyVehicles int DEFAULT 0,
>   fldVehicleType07EmergencyVehicles int DEFAULT 0,
>   fldVehicleType08EmergencyVehicles int DEFAULT 0,
>   fldVehicleType09EmergencyVehicles int DEFAULT 0,
>   fldTotalTrailerVehicles int DEFAULT 0,
>   fldVehicleType01TrailerVehicles int DEFAULT 0,
>   fldVehicleType02TrailerVehicles int DEFAULT 0,
>   fldVehicleType03TrailerVehicles int DEFAULT 0,
>   fldVehicleType04TrailerVehicles int DEFAULT 0,
>   fldVehicleType05TrailerVehicles int DEFAULT 0,
>   fldVehicleType06TrailerVehicles int DEFAULT 0,
>   fldVehicleType07TrailerVehicles int DEFAULT 0,
>   fldVehicleType08TrailerVehicles int DEFAULT 0,
>   fldVehicleType09TrailerVehicles int DEFAULT 0,
>   fldTotalMotorcadeVehicles int DEFAULT 0,
>   fldTotalViolationVehicles int DEFAULT 0,
>   fldFreeNum int DEFAULT 0,
>   fldTotalFreeFee int DEFAULT 0,
>   fldVehicleType01FreeFee int DEFAULT 0,
>   fldVehicleType02FreeFee int DEFAULT 0,
>   fldVehicleType03FreeFee int DEFAULT 0,
>   fldVehicleType04FreeFee int DEFAULT 0,
>   fldVehicleType05FreeFee int DEFAULT 0,
>   fldVehicleType06FreeFee int DEFAULT 0,
>   fldVehicleType07FreeFee int DEFAULT 0,
>   fldVehicleType08FreeFee int DEFAULT 0,
>   fldVehicleType09FreeFee int DEFAULT 0,
>   fldTotalCashFeeHanded int DEFAULT 0,
>   fldTotalCashNum int DEFAULT 0,
>   fldVehicleType01CashFeeHanded int DEFAULT 0,
>   fldVehicleType02CashFeeHanded int DEFAULT 0,
>   fldVehicleType03CashFeeHanded int DEFAULT 0,
>   fldVehicleType04CashFeeHanded int DEFAULT 0,
>   fldVehicleType05CashFeeHanded int DEFAULT 0,
>   fldVehicleType06CashFeeHanded int DEFAULT 0,
>   fldVehicleType07CashFeeHanded int DEFAULT 0,
>   fldVehicleType08CashFeeHanded int DEFAULT 0,
>   fldVehicleType09CashFeeHanded int DEFAULT 0,
>   fldTotalSVCFeeHanded int DEFAULT 0,
>   fldTotalSVCNum int DEFAULT 0,
>   fldVehicleType01SVCFeeHanded int DEFAULT 0,
>   fldVehicleType02SVCFeeHanded int DEFAULT 0,
>   fldVehicleType03SVCFeeHanded int DEFAULT 0,
>   fldVehicleType04SVCFeeHanded int DEFAULT 0,
>   fldVehicleType05SVCFeeHanded int DEFAULT 0,
>   fldVehicleType06SVCFeeHanded int DEFAULT 0,
>   fldVehicleType07SVCFeeHanded int DEFAULT 0,
>   fldVehicleType08SVCFeeHanded int DEFAULT 0,
>   fldVehicleType09SVCFeeHanded int DEFAULT 0,
>   fldTotalCreditFeeHanded int DEFAULT 0,
>   fldTotalCreditNum int DEFAULT 0,
>   fldVehicleType01CreditFeeHanded int DEFAULT 0,
>   fldVehicleType02CreditFeeHanded int DEFAULT 0,
>   fldVehicleType03CreditFeeHanded int DEFAULT 0,
>   fldVehicleType04CreditFeeHanded int DEFAULT 0,
>   fldVehicleType05CreditFeeHanded int DEFAULT 0,
>   fldVehicleType06CreditFeeHanded int DEFAULT 0,
>   fldVehicleType07CreditFeeHanded int DEFAULT 0,
>   fldVehicleType08CreditFeeHanded int DEFAULT 0,
>   fldVehicleType09CreditFeeHanded int DEFAULT 0,
>   fldTotalLoan int DEFAULT 0,
>   fldTotalLoanCashHanded int DEFAULT 0,
>   fldTotalLoanSVCHanded int DEFAULT 0,
>   fldTotalLoanCreditHanded int DEFAULT 0,
>   fldTotalLoanNum int DEFAULT 0,
>   fldTotalForefeit int DEFAULT 0,
>   fldTotalForefeitCashHanded int DEFAULT 0,
>   fldTotalForefeitSVCHanded int DEFAULT 0,
>   fldTotalForefeitCreditHanded int DEFAULT 0,
>   fldTotalForefeitNum int DEFAULT 0,
>   fldTotalArrearage int DEFAULT 0,
>   fldTotalArrearageNum int DEFAULT 0,
>   fldSent smallint DEFAULT 0
> )

> Very thanks
> cms
> 2000-12-8

Redd

Delphi Developer
Wed, 28 May 2003 05:00:32 GMT

Re:About General SQL error while using MSSQL 7 with BDE


"cms" <xqlmzlh...@sina.com.cn> wrote

Hello CMS,

Your table is not even in first normal form. Are you going to use it in a
real-application? ;)
Anyway, There is certain BDE limit on the number of fields (I guess it is
128). But I don't think you hit it. It is not good to use such a wide table
in an application -- at least not for displaying the whole table. Normalize
it first.

Also, in order to do anything useful at all, you will need a Prmary key, and
some indices.

Just to see what's there in the table, you can write a select statement like
  select
     fldVehicleType01FeeVehicles ,
     fldVehicleType02FeeVehicles ,
     fldVehicleType03FeeVehicles
from
    dbo.Hist_EndShiftRecord2000
where
    fldRecordID = 1

If it displays results, then keep on adding the fields until you see"General
SQL error".

Goodluck,
-- Reddy Palle.

Quote
> CREATE TABLE dbo.Hist_EndShiftRecord2000 (
>   fldRecordID char(51) DEFAULT '
> ',
>   fldVehicleType01FeeVehicles int DEFAULT 0,
>   fldVehicleType02FeeVehicles int DEFAULT 0,
>   fldTotalOfficialVehicles int DEFAULT 0,
>   fldVehicleType01OfficialVehicles int DEFAULT 0,
>   fldVehicleType02OfficialVehicles int DEFAULT 0,
>   fldVehicleType03OfficialVehicles int DEFAULT 0,
>   fldVehicleType04OfficialVehicles int DEFAULT 0,
>   fldTotalMilitarylVehicles int DEFAULT 0,
>   fldVehicleType01MilitaryVehicles int DEFAULT 0,
>   fldVehicleType02MilitaryVehicles int DEFAULT 0,
>   fldVehicleType03MilitaryVehicles int DEFAULT 0,
>   fldVehicleType04MilitaryVehicles int DEFAULT 0,
>   fldVehicleType01EmergencyVehicles int DEFAULT 0,
>   fldVehicleType02EmergencyVehicles int DEFAULT 0,
>   fldVehicleType03EmergencyVehicles int DEFAULT 0,

Kevin Frever

Delphi Developer
Wed, 28 May 2003 04:06:16 GMT

Re:About General SQL error while using MSSQL 7 with BDE


You would be surprised what some consider a "real-application".  It's not
uncommon for desktop dbs to have tables larger (wider) than that.  Our
company's legacy system (15+ years old, before my time) had tables with
1,500 fields.

krf

Quote
Reddy wrote in message <3a31214d_1@dnews>...

>"cms" <xqlmzlh...@sina.com.cn> wrote

>Hello CMS,

>Your table is not even in first normal form. Are you going to use it in a
>real-application? ;)
>Anyway, There is certain BDE limit on the number of fields (I guess it is
>128). But I don't think you hit it. It is not good to use such a wide table
>in an application -- at least not for displaying the whole table. Normalize
>it first.

Redd

Delphi Developer
Wed, 28 May 2003 08:13:25 GMT

Re:About General SQL error while using MSSQL 7 with BDE


Quote
"Kevin Frevert" <kfrev...@midwayusa.com> wrote in message

news:3a31406d$1_2@dnews...
Quote
> You would be surprised what some consider a "real-application".  It's not
> uncommon for desktop dbs to have tables larger (wider) than that.  Our
> company's legacy system (15+ years old, before my time) had tables with
> 1,500 fields.

Hello Kevin,
I thought I have seen some night-marish DB Designs -- obviously I have not
;). I worked on a system sometime back, which had fields like sign (+/-) as
a seperate column. I guess, most of these databases are migrated from
legacy/mainframe dbs/files (where they might be stored in a columnwise may
be, instead of row-wise). But, it is always good to use a normalized db when
using modern Relational DB Servers.

Regards,
-- Reddy Palle.

Mark L

Delphi Developer
Thu, 29 May 2003 07:00:05 GMT

Re:About General SQL error while using MSSQL 7 with BDE


Switch to ADO. BDE may not be compatible with some of the new functions
implemented in MS/SQL 7. In fact, BDE is still using the old DBLibrary that
MS had stopped supporting it. Under MS/SQL 7 and greater, the record size is
allowed to be  much much bigger than under MS/SQL 6.5. So, your record is OK
under SQL7, it is just that you are still using BDE that may produce
unexpected results.

Mark

Quote
"cms" <xqlmzlh...@sina.com.cn> wrote in message

news:90q0as$6h51@bornews.inprise.com...
Quote
> Dear Sir:
> I use MSSQL7 and BDE native driver in D5 for read a table, but if I active
> the TTable, the system display a error information "General SQL error" and
> display all of fileds name in the TDBGrid.
> And I use  SQL Explorer, when I open the Data sheet, this phenomenon is
> displayed too. Why ? And how can I do ?
> The table structure is:
> CREATE TABLE dbo.Hist_EndShiftRecord2000 (
>   fldRecordID char(51) DEFAULT '
> ',
>   fldLoginDate char(10) DEFAULT '          ',
>   fldLoginTime char(8) DEFAULT '        ',
>   fldLogoutDate char(10) DEFAULT '          ',
>   fldLogoutTime char(8) DEFAULT '        ',
>   fldStaAddress char(19) DEFAULT '                   ',
>   fldLaneDirect varchar(5) DEFAULT '     ',
>   fldOperatorID varchar(6) DEFAULT '          ',
>   fldGroupName smallint DEFAULT 6,
>   fldShiftNo smallint DEFAULT (-1),
>   fldNormalCards int DEFAULT 0,
>   fldBadCards int DEFAULT 0,
>   fldReceptNum int DEFAULT 0,
>   fldTotalNoneCardVehicles int DEFAULT 0,
>   fldTotalFeeVehicles int DEFAULT 0,
>   fldVehicleType01FeeVehicles int DEFAULT 0,
>   fldVehicleType02FeeVehicles int DEFAULT 0,
>   fldVehicleType03FeeVehicles int DEFAULT 0,
>   fldVehicleType04FeeVehicles int DEFAULT 0,
>   fldVehicleType05FeeVehicles int DEFAULT 0,
>   fldVehicleType06FeeVehicles int DEFAULT 0,
>   fldVehicleType07FeeVehicles int DEFAULT 0,
>   fldVehicleType08FeeVehicles int DEFAULT 0,
>   fldVehicleType09FeeVehicles int DEFAULT 0,
>   fldTotalOfficialVehicles int DEFAULT 0,
>   fldVehicleType01OfficialVehicles int DEFAULT 0,
>   fldVehicleType02OfficialVehicles int DEFAULT 0,
>   fldVehicleType03OfficialVehicles int DEFAULT 0,
>   fldVehicleType04OfficialVehicles int DEFAULT 0,
>   fldVehicleType05OfficialVehicles int DEFAULT 0,
>   fldVehicleType06OfficialVehicles int DEFAULT 0,
>   fldVehicleType07OfficialVehicles int DEFAULT 0,
>   fldVehicleType08OfficialVehicles int DEFAULT 0,
>   fldVehicleType09OfficialVehicles int DEFAULT 0,
>   fldTotalMilitarylVehicles int DEFAULT 0,
>   fldVehicleType01MilitaryVehicles int DEFAULT 0,
>   fldVehicleType02MilitaryVehicles int DEFAULT 0,
>   fldVehicleType03MilitaryVehicles int DEFAULT 0,
>   fldVehicleType04MilitaryVehicles int DEFAULT 0,
>   fldVehicleType05MilitaryVehicles int DEFAULT 0,
>   fldVehicleType06MilitaryVehicles int DEFAULT 0,
>   fldVehicleType07MilitaryVehicles int DEFAULT 0,
>   fldVehicleType08MilitaryVehicles int DEFAULT 0,
>   fldVehicleType09MilitaryVehicles int DEFAULT 0,
>   fldTotalEmergencyVehicles int DEFAULT 0,
>   fldVehicleType01EmergencyVehicles int DEFAULT 0,
>   fldVehicleType02EmergencyVehicles int DEFAULT 0,
>   fldVehicleType03EmergencyVehicles int DEFAULT 0,
>   fldVehicleType04EmergencyVehicles int DEFAULT 0,
>   fldVehicleType05EmergencyVehicles int DEFAULT 0,
>   fldVehicleType06EmergencyVehicles int DEFAULT 0,
>   fldVehicleType07EmergencyVehicles int DEFAULT 0,
>   fldVehicleType08EmergencyVehicles int DEFAULT 0,
>   fldVehicleType09EmergencyVehicles int DEFAULT 0,
>   fldTotalTrailerVehicles int DEFAULT 0,
>   fldVehicleType01TrailerVehicles int DEFAULT 0,
>   fldVehicleType02TrailerVehicles int DEFAULT 0,
>   fldVehicleType03TrailerVehicles int DEFAULT 0,
>   fldVehicleType04TrailerVehicles int DEFAULT 0,
>   fldVehicleType05TrailerVehicles int DEFAULT 0,
>   fldVehicleType06TrailerVehicles int DEFAULT 0,
>   fldVehicleType07TrailerVehicles int DEFAULT 0,
>   fldVehicleType08TrailerVehicles int DEFAULT 0,
>   fldVehicleType09TrailerVehicles int DEFAULT 0,
>   fldTotalMotorcadeVehicles int DEFAULT 0,
>   fldTotalViolationVehicles int DEFAULT 0,
>   fldFreeNum int DEFAULT 0,
>   fldTotalFreeFee int DEFAULT 0,
>   fldVehicleType01FreeFee int DEFAULT 0,
>   fldVehicleType02FreeFee int DEFAULT 0,
>   fldVehicleType03FreeFee int DEFAULT 0,
>   fldVehicleType04FreeFee int DEFAULT 0,
>   fldVehicleType05FreeFee int DEFAULT 0,
>   fldVehicleType06FreeFee int DEFAULT 0,
>   fldVehicleType07FreeFee int DEFAULT 0,
>   fldVehicleType08FreeFee int DEFAULT 0,
>   fldVehicleType09FreeFee int DEFAULT 0,
>   fldTotalCashFeeHanded int DEFAULT 0,
>   fldTotalCashNum int DEFAULT 0,
>   fldVehicleType01CashFeeHanded int DEFAULT 0,
>   fldVehicleType02CashFeeHanded int DEFAULT 0,
>   fldVehicleType03CashFeeHanded int DEFAULT 0,
>   fldVehicleType04CashFeeHanded int DEFAULT 0,
>   fldVehicleType05CashFeeHanded int DEFAULT 0,
>   fldVehicleType06CashFeeHanded int DEFAULT 0,
>   fldVehicleType07CashFeeHanded int DEFAULT 0,
>   fldVehicleType08CashFeeHanded int DEFAULT 0,
>   fldVehicleType09CashFeeHanded int DEFAULT 0,
>   fldTotalSVCFeeHanded int DEFAULT 0,
>   fldTotalSVCNum int DEFAULT 0,
>   fldVehicleType01SVCFeeHanded int DEFAULT 0,
>   fldVehicleType02SVCFeeHanded int DEFAULT 0,
>   fldVehicleType03SVCFeeHanded int DEFAULT 0,
>   fldVehicleType04SVCFeeHanded int DEFAULT 0,
>   fldVehicleType05SVCFeeHanded int DEFAULT 0,
>   fldVehicleType06SVCFeeHanded int DEFAULT 0,
>   fldVehicleType07SVCFeeHanded int DEFAULT 0,
>   fldVehicleType08SVCFeeHanded int DEFAULT 0,
>   fldVehicleType09SVCFeeHanded int DEFAULT 0,
>   fldTotalCreditFeeHanded int DEFAULT 0,
>   fldTotalCreditNum int DEFAULT 0,
>   fldVehicleType01CreditFeeHanded int DEFAULT 0,
>   fldVehicleType02CreditFeeHanded int DEFAULT 0,
>   fldVehicleType03CreditFeeHanded int DEFAULT 0,
>   fldVehicleType04CreditFeeHanded int DEFAULT 0,
>   fldVehicleType05CreditFeeHanded int DEFAULT 0,
>   fldVehicleType06CreditFeeHanded int DEFAULT 0,
>   fldVehicleType07CreditFeeHanded int DEFAULT 0,
>   fldVehicleType08CreditFeeHanded int DEFAULT 0,
>   fldVehicleType09CreditFeeHanded int DEFAULT 0,
>   fldTotalLoan int DEFAULT 0,
>   fldTotalLoanCashHanded int DEFAULT 0,
>   fldTotalLoanSVCHanded int DEFAULT 0,
>   fldTotalLoanCreditHanded int DEFAULT 0,
>   fldTotalLoanNum int DEFAULT 0,
>   fldTotalForefeit int DEFAULT 0,
>   fldTotalForefeitCashHanded int DEFAULT 0,
>   fldTotalForefeitSVCHanded int DEFAULT 0,
>   fldTotalForefeitCreditHanded int DEFAULT 0,
>   fldTotalForefeitNum int DEFAULT 0,
>   fldTotalArrearage int DEFAULT 0,
>   fldTotalArrearageNum int DEFAULT 0,
>   fldSent smallint DEFAULT 0
> )

> Very thanks
> cms
> 2000-12-8

Paul Lineh

Delphi Developer
Fri, 30 May 2003 00:13:59 GMT

Re:About General SQL error while using MSSQL 7 with BDE


 "Kevin Frevert" <kfrev...@midwayusa.com> wrote:

Quote
> You would be surprised what some consider a "real-application".  It's not
> uncommon for desktop dbs to have tables larger (wider) than that.  Our
> company's legacy system (15+ years old, before my time) had tables with
> 1,500 fields.

I've seen one which had 35,000 (yes, that's not a typo - that's thirty
five thousand fields).

It was a legacy system which allowed Pascal records as a data type, so
when you compound a few of those, your "record count" goes through the
ceiling.

When I was writing code to extract data from this thing and put it
into a normalised (and normal) format, I had to write a programme
which generated the code - a procedure with 35000 lines.

Paul...

Quote
> krf

Other Threads

1. About General SQL error while using MSSQL 7 with BDE

2. General SQL error with image BLObs, MSSQL+D4+BDE 5.01

3. General SQL Error, MSSQL 6.5, SQL Links 4.51

4. Native MSSQL Driver cause General SQL Error with TTable and TDBMemo

5. HELP! D1/MSSQL 6.5 General SQL Error

6. MSSQL 6.5 "General SQL Error "

7. BDE error: 13059 (General SQL error)

8. BDE error: 13059 (General SQL error)

9. BDE error 13059 General SQL error

10. SOS: General SQL Error: Network error with MS-SQL