SQL server 2014 and INFORMATION_SCHEMA.INDEXES

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for SQL Server in Delphi and C++Builder
Post Reply
Goran
Posts: 16
Joined: Tue 03 May 2005 15:38
Location: Ljubljana, Slovenija

SQL server 2014 and INFORMATION_SCHEMA.INDEXES

Post by Goran » Fri 03 Mar 2017 11:19

Hello,
we are in process of migrating our VCL code from Delphi XE and dbXpress driver 4 to Delphi Berlin. I downloaded the trial version of latest dbXpress driver 7.3.5 to convert one of our projects to the new Delphi. So far I found out that I should use "dbo" in SchemaName where Delphi leaved it blank or used username instead "dbo". Or use parameter schema override. Okay, this seems to be known problem.
The more interesting thing is that I have a problem when opening a ClientDataset with custom SQL and get an error:
"Project ... raised exception class EMSError with message 'Invalid object name 'INFORMATION_SCHEMA.INDEXES'.'. "
I used dbForge to trace the query and I found out that SQL Server was querried with the following SQL while opening the query:

Code: Select all

exec sp_executesql N'SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, CASE [UNIQUE] WHEN 1 THEN INDEX_NAME ELSE NULL END, CONVERT(BIT, [PRIMARY_KEY]), CONVERT(BIT, [UNIQUE]), CONVERT(BIT, 1) FROM INFORMATION_SCHEMA.INDEXES WHERE   (TABLE_NAME = @P1 OR (1=2)) ORDER BY INDEX_NAME, ORDINAL_POSITION',N'@P1 nvarchar(16)',N'NastavitveKlient'
I tried to execute this statement in SQL server and as expected it didn't work, SQL server also complained that schema INFORMATION_SCHEMA does not exist.

In Delphi, I have on datamodule TSQLConnection, some TSQLDatasets, TDatasetProviders, TClientDatasets... The problematic CDS is connected to dedicated TDatasetProvider with Options [poAllowCommandText, poUseQuoteChar] and UpdateMode upWhereKeyOnly. DatasetProvider is connected to dedicated TSQLDataset which has CommandType ctQuery, SchemaName dbo.
In ClientDataset connected to this chain the following code should be executed:

Code: Select all

with cdsNastKlient do
  begin
    DisableControls;
    try
      if Active then Close;
      CommandText := 'SELECT * FROM dbo.NastavitveKlient' + ^m +
                     'WHERE Upper(Klient) = ' + QuotedStr(UpperCase(FClientName)) + ^m +
                     'AND Upper(Koda) = ' + QuotedStr(UpperCase(FKoda));
      try
        Open;
        First;
....
The error is raised on "Open". In our environment we use SQL Server 2014 while on field our clients use everything from SQL Server 2005 express till SQL Server 2014.
The exactly same code in Delphi XE and old (your) dbXpress driver (version 4.80.0.29) works normally, no problems there on same database, same server. Any ideas on how to resolve the problem?

Thank You in advance!

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: SQL server 2014 and INFORMATION_SCHEMA.INDEXES

Post by azyk » Mon 06 Mar 2017 12:41

We tried to reproduce the problem according your recommendations, but the problem was not reproduced. Please compose a small test project to reproduce and send it to us using the contact form on our website: https://www.devart.com/company/contactform.html. Also include in the project test DB backup(.bak).

Goran
Posts: 16
Joined: Tue 03 May 2005 15:38
Location: Ljubljana, Slovenija

Re: SQL server 2014 and INFORMATION_SCHEMA.INDEXES

Post by Goran » Fri 17 Mar 2017 08:43

Hi,

sorry for the delay, I had some other problems to solve in old Delphi XE. I tried also with a bit older version 7.2.4.0 which we got as the latest availble when we upgraded driver 2 years ago.
Test is actually fairly simple.
1. Create a new application with one datamodule. Put there:
TSQLConnection
TSQLQuery
TSQLDataSet
Connect later two with TSQLConnection

2. Connect to some database using sqlConnection.

3. in TSQL Query under SQL\Text write "SELECT * FROM dbo.Table". If You change Active to true, it is no problem. Also if You have some schema defined with one table and use "SELECT * FROM MySchema.Table" there is no problem as well.

4. try the same with TSQLDataset. CommandType = ctQuery, CommandText = SELECT * FROM Table.
If You activate the TSQLDataset now You will get the error. If You use schema name in commandtext or separately, again, You shall get this error (Invalid object name 'INFORMATION_SCHEMA.INDEXES'). There was no problem in Delphi XE and old dbx driver 4.80.0.29.

If You need params for TSQLConnection, here they are:

Code: Select all

DriverName=DevartSQLServer
SchemaOverride=%.dbo
DriverUnit=DBXDevartSQLServer
DriverPackageLoader=TDBXDynalinkDriverLoader,DBXCommonDriver240.bpl
MetaDataPackageLoader=TDBXDevartMSSQLMetaDataCommandFactory,DbxDevartSQLServerDriver240.bpl
ProductName=DevartSQLServer
LibraryName=dbexpsda40.dll
VendorLib=sqloledb.dll
LocaleCode=0000
IsolationLevel=ReadCommitted
MaxBlobSize=-1
LongStrings=True
EnableBCD=False
FetchAll=True
ParamPrefix=False
UseUnicode=True
IPVersion=IPv4
BlobSize=-1
HostName=<server+instance name>
Database=TestDatabase
User_Name=<username>
Password=<password>
The question is, is this a problem with driver or Delphi's dbX implementation?

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: SQL server 2014 and INFORMATION_SCHEMA.INDEXES

Post by azyk » Fri 17 Mar 2017 15:00

We still cannot reproduce the specified problem. Please create a test database, including DB objects needed to reproduce the problem (table, scheme, role, user, etc.). Backup test DB and send it to us.

Goran
Posts: 16
Joined: Tue 03 May 2005 15:38
Location: Ljubljana, Slovenija

Re: SQL server 2014 and INFORMATION_SCHEMA.INDEXES

Post by Goran » Mon 20 Mar 2017 10:48

Hi azyk,
I have prepared test project and a backup database with one table only and sent the ZIP to Your email directly.

Best regards,
Goran

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: SQL server 2014 and INFORMATION_SCHEMA.INDEXES

Post by azyk » Tue 21 Mar 2017 10:00

We cannot reproduce the problem. We ran the sent sample, clicked the Connect button and got the error message

Code: Select all

Not connected: SQL State: 1, SQL Error Code: 4060, Level 11, Procedure: , Line: 1
Cannot open database "Test" requested by the login. The login failed.

In the application we use user dentuser who in the sent backup is created using the WITHOUT LOGIN option.

Please specify whether the problem is reproduced when using a user with the sysadmin role, for example sa.

Goran
Posts: 16
Joined: Tue 03 May 2005 15:38
Location: Ljubljana, Slovenija

Re: SQL server 2014 and INFORMATION_SCHEMA.INDEXES

Post by Goran » Tue 21 Mar 2017 10:33

Hi Azyk,
the user dentuser is sysadmin on the database/server, at least in our case. Also, since there are no settings in the EXE, the expected server is GORANWM/SQL2008. We don't use "sa" nor other admin user anywhere in our software(s).

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: SQL server 2014 and INFORMATION_SCHEMA.INDEXES

Post by azyk » Wed 22 Mar 2017 14:28

In the provided Test.bak dentuser is not sysadmin - it was created by the following T-SQL command:

Code: Select all

CREATE USER [dentuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
Therefore when we run the sample, then when trying to connect we get the error
'Not connected: SQL State: 1, SQL Error Code: 4060, Level 11, Procedure: , Line: 1
Cannot open database "Test" requested by the login. The login failed.'

If we delete dentuser and create it with the sysadmin role:

Code: Select all

CREATE LOGIN [dentuser] WITH PASSWORD= '*****'
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [dentuser]
then the connection is successful, but the specified error is not reproduced.

Please check in your environment that the error 'Invalid object name 'INFORMATION_SCHEMA.INDEXES'.'. " is reproduced using your sample.

Goran
Posts: 16
Joined: Tue 03 May 2005 15:38
Location: Ljubljana, Slovenija

Re: SQL server 2014 and INFORMATION_SCHEMA.INDEXES

Post by Goran » Thu 23 Mar 2017 09:38

Hi Azyk,
I have to apologize. On the same computer I have old dbxpress driver for Delph XE (4.0...) and the new one for Delphi Berlin. If I add the correct DLL to exe, then error disappears.

I apologize for the problems.

Best regards,
Goran

azyk
Devart Team
Posts: 1119
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: SQL server 2014 and INFORMATION_SCHEMA.INDEXES

Post by azyk » Mon 27 Mar 2017 09:58

Glad to see you have found the reason of the problem. If any other questions come up, please contact us.

Post Reply