Delphi/Borland program and SQL Express 2017

[JoPaBC]

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)

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.

 

[fredericofonseca]

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

https://www.red-gate.com/hub/produc...-using-sql-prompt-old-style-join-syntax-st001

and

https://docs.microsoft.com/en-us/sq...sql-compatibility-level?view=sql-server-ver15

to confirm on the old server execute the following 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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[JoPaBC]

I tried that and got an error too (Incorrect syntax near '*='.)

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

Thx

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[JoPaBC]

Well, there both are 2017 (one with security update installed)

https://buildnumbers.wordpress.com/sqlserver/

 

[gmmastros]

*= 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:

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

 

[JoPaBC]

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

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:

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