0

I have got this problem with FireDAC -> FDQuery component when it tries to select data from a database with '.' (dot) in its name.

The database name is TEST_2.0 and the error on Opening the dataset says:

Could not find server 'TEST_2' in sys.servers [...]

I have tried {TEST_2.0} (curly brackets) and [TEST_2.0] (square brackets). Also setting QuotedIdentifiers (Format Opetions) property to True does not seem to fix the problem. In SQL query I can add 'SET QUOTED_IDENTIFIER ON;' but this breaks inserts to the dataset.

The FDConnection component can connect to that server and that database using MSSQL driver without problems. It seems it is the dataset that dosn't handle it. UniDAC seems to handle everything without any problems.

I am using RadStudio 10.2.

Has anyone found any solution to this? Thanks in advance for any replies

Victoria
  • 7,822
  • 2
  • 21
  • 44
Michal B
  • 21
  • 4
  • Could you share the SQL command that you've executed, please? I was able to reproduce this issue with the command `SELECT * FROM [TEST_2.0].INFORMATION_SCHEMA.TABLES` and the source was the metadata command, specifically `SQLPrimaryKeys` ODBC function. I'd like to know to know your exact case before I'll be able to be sure with the answer. – Victoria Apr 10 '18 at 20:15
  • @Victoria - I have tried multiple SQLs. The base one in the dataset SQL string is `SELECT * FROM CRM_USERS ` (CRM_USERS being the table name). Later on I have tried `SELECT * FROM [TEST_2.0].[dbo].[CRM_USERS]`. But the only think that really helped was `SET QUOTED_IDENTIFIER ON; SELECT * FROM CRM_USERS` which breaks the Insert/Append method for the dataset – Michal B Apr 11 '18 at 07:17
  • @Victoria the FDQuery data set is connected to FDConnection component with parameters: Server: (localhost); Database: Test_2.0 – Michal B Apr 11 '18 at 07:29

2 Answers2

2

I got a response from Emarcadero and it works for me:

"The problem is not in FireDAC, but in SQL Server ODBC driver SQLPrimaryKeys function. It fails to work with a catalog name containing a dot. FireDAC uses this function to get primary key fields for a result set, when fiMeta is included into FetchOptions.Items. So, as a workaround / solution, please exclude fiMeta from FetchOptions.Items."

Victoria
  • 7,822
  • 2
  • 21
  • 44
Michal B
  • 21
  • 4
  • Funny enough. I've reached the same conclusion (in a different scenario). The answer I've posted right now was prepared yesterday :) Of course the workaround might not be sufficient, but the only thing we can do is report this to Microsoft. – Victoria Apr 11 '18 at 11:51
2

What is wrong?

I was able to reproduce what you've described here. I've ended up on metainformation command, specifically the SQLPrimaryKeys ODBC function call. I have used SQL Server Native Client 11.0 driver connected to Microsoft SQL Server Express 12.0.2000.8, local database server instance.

When I tried to execute the following SQL command (with TEST_2.0 database created) through a TFDQuery component instance with default settings (linked connection object was left with empty database connection parameter) in Delphi Tokyo application:

SELECT * FROM [TEST_2.0].INFORMATION_SCHEMA.TABLES

I got this exception raised when the SQLPrimaryKeys function was called with the CatalogName parameter set to TEST_2.0 (from within the metainformation statement method Execute):

[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find server 'TEST_2' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.'.

My next attempt was naturally modifying that CatalogName parameter value to [TEST_2.0] whilst debugging, but even that failed with similar reason (just failed for the name [TEST_2), so for me it seems that the SQLPrimaryKeys ODBC function implementation with the driver I've used cannot properly handle dotted CatalogName parameter values (it seems to ignore everything after dot).

What can I do?

The only solution seems to be just fixing ODBC drivers. Workaround I would suggest is not using dots in database names (as discussed e.g. in this thread). Another might be preventing FireDAC from getting dataset object metadata (by excluding fiMeta option from the Items option set). That will bring you the responsability of supplying dataset object metadata by yourself (at this time only primary key definition).

Victoria
  • 7,822
  • 2
  • 21
  • 44