×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Delphi/Borland program and SQL Express 2017

Delphi/Borland program and SQL Express 2017

Delphi/Borland program and SQL Express 2017

(OP)
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)

CODE --> 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.

RE: Delphi/Borland program and SQL Express 2017

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/product-learning/sql-...
and
https://docs.microsoft.com/en-us/sql/t-sql/stateme...


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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Delphi/Borland program and SQL Express 2017

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

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

Thx

RE: Delphi/Borland program and SQL Express 2017

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

RE: Delphi/Borland program and SQL Express 2017

(OP)
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

CODE --> 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:

CODE --> 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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close