SQL server 2014 and INFORMATION_SCHEMA.INDEXES
Posted: 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:
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:
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!
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:
I used dbForge to trace the query and I found out that SQL Server was querried with the following SQL while opening the query:"Project ... raised exception class EMSError with message 'Invalid object name 'INFORMATION_SCHEMA.INDEXES'.'. "
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'
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 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!