Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Delphi/Borland program and SQL Express 2017

Status
Not open for further replies.
JoPaBC

JoPaBC

Technical User
Joined
Sep 26, 2017
Messages
85
Location
CA
Hello,
We are using a Delphi/Borland program which uses a database on SQL Express 2017 server and it works without any issues.
We tried to move it to another computer so we installed the program, SQL Express 2017 and move (export/import) the database to the new computer.
The program starts normally, it connects to the server, but when trying to read data it gives us an error "General SQL error. Incorrect syntax near '*='."
When monitoring the database, we see it tried to execute commands like (generated by the program)
SQL: 
select c.name, c.status, o.name from syscolumns c, sysobjects o where c.id = object_id ('dbo.SETTINGS_T') and c.cdefault *= o.id order by colid ASC

Both, old and new computer are using exactly the same program, the same database, the same SQL Express server version with the same settings (at least to our knowledge, like compatibility level 100, etc) but what runs on the old computer it is giving error messages on the new one.

Is there any way to fix this problem? We are not able to change the Delphi program (yet), only Borland and SQL Express Server settings/parameters.

Thx

PS: I apologize if not the proper forum.
 
Sort by date Sort by votes
I think that the older machine may not be using 2017 at all but rather 2012 which is the latest version where it was possible to set the db compatibility level to 90

see
and


to confirm on the old server execute the following code
Code: 
select c.name
     , c.status
     , o.name
     , @@Version as SQLVersion
from syscolumns c
   , sysobjects o
where c.id = object_id('dbo.SETTINGS_T')
    and c.cdefault *= o.id
order by colid asc

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
I tried that and got an error too (Incorrect syntax near '*='.)

I will try to compare XEvent Profiler logs to find any clue.

Thx
 
Upvote 0 Downvote
soo.... if you run that code on the old server and if it is the same code running on the application I would say you are connecting to the wrong "old server instance"

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Well, there both are 2017 (one with security update installed)

old_sql_peblrs.png


sql_new_dwsdxg.png
 
Upvote 0 Downvote
*= is very old syntax for a left join. I would encourage you to modify the query to replace that syntax with a left join instead.

This old syntax has been deprecated for many years. The reason you are still able to use it is because SQL Server allows you to set the compatibility level of a database. I would be interested to know what the compatibility levels are for the database on each server.

Please run this and let us know what the results are:

Code: 
SELECT compatibility_level  
FROM sys.databases WHERE name = 'Your-Database-Name-Here';

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks everybody for your input.
Compatibility level is 100 for both databases.
I cannot change '*=' to a left join; I don't have a source code of the Delphi program.

When comparing event logs:

working database
SQL: 
select user_name (uid), object_name (id), type, crdate from sysobjects where type in ('U', 'V', 'S') and id = object_id ('PAYADMIN.AC_SCTN_DEFS_T')  
select user_name (uid), object_name (id), type, crdate from sysobjects where type in ('U', 'V', 'S') and id = object_id ('PAYADMIN.AC_SCTN_DEFS_T')  
-- network protocol: TCP/IP  set quoted_identifier off  set arithabort off  set numeric_roundabort off  set ansi_warnings off  set ansi_padding off  set ansi_nulls off  set concat_null_yields_null off  set cursor_close_on_commit off  set implicit_transact

failing one:
SQL: 
select user_name (uid), object_name (id), type, crdate from sysobjects where type in ('U', 'V', 'S') and id = object_id ('dbo.AC_SCTN_DEFS_T')  
select user_name (uid), object_name (id), type, crdate from sysobjects where type in ('U', 'V', 'S') and id = object_id ('dbo.AC_SCTN_DEFS_T')  
select c.name, t.type, c.length, c.status, t.name, c.prec, c.scale from syscolumns c, systypes t where c.id = object_id ('dbo.AC_SCTN_DEFS_T') and c.usertype = t.usertype order by colid ASC 
select c.name, t.type, c.length, c.status, t.name, c.prec, c.scale from syscolumns c, systypes t where c.id = object_id ('dbo.AC_SCTN_DEFS_T') and c.usertype = t.usertype order by colid ASC 
select x.name, x.indid, x.status, INDEX_COL('dbo.AC_SCTN_DEFS_T', x.indid, c.colid) from sysindexes x, syscolumns c where x.id = object_id ('dbo.AC_SCTN_DEFS_T') and indid between 1 and 254 and x.id = c.id  and c.colid <= x.keycnt order by x.indid, c.colid ASC 
select x.name, x.indid, x.status, INDEX_COL('dbo.AC_SCTN_DEFS_T', x.indid, c.colid) from sysindexes x, syscolumns c where x.id = object_id ('dbo.AC_SCTN_DEFS_T') and indid between 1 and 254 and x.id = c.id  and c.colid <= x.keycnt order by x.indid, c.colid ASC 
select c.name, c.status, o.name from syscolumns c, sysobjects o where c.id = object_id ('dbo.AC_SCTN_DEFS_T') and c.cdefault *= o.id order by colid ASC 
select c.name, c.status, o.name from syscolumns c, sysobjects o where c.id = object_id ('dbo.AC_SCTN_DEFS_T') and c.cdefault *= o.id order by colid ASC 
select c.name, c.status, o.name from syscolumns c, sysobjects o where c.id = object_id ('dbo.AC_SCTN_DEFS_T') and c.cdefault *= o.id order by colid ASC 
select c.name, c.status, o.name from syscolumns c, sysobjects o where c.id = object_id ('dbo.AC_SCTN_DEFS_T') and c.cdefault *= o.id order by colid ASC
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question Question
SQL Server in Windows
Replies
1
Views
1K
pjw001
pjw001
CodeWell
  • Locked
  • Question Question
SQL Server 2017 Install Issue - Database Not in Configuration Manager
Replies
0
Views
889
CodeWell
CodeWell
JoPaBC
  • Locked
  • Question Question
Schedule and automate backups of SQL Express databases in SQL Server Express
Replies
5
Views
1K
JoPaBC
JoPaBC
dgillz
  • Locked
  • Question Question
SQL View not available to Excel Power Query
Replies
1
Views
774
dgillz
dgillz
bethabernathy
  • Locked
  • Question Question
Front End Access 365 Database dsn Connection Fails when attempting to link to 2016 SQL server
Replies
1
Views
747
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top