Firedac and SQL Server DB with "." in name

[Martyn Spencer 0]

I am using Delphi 10.2 and I have been given a database to connect to and I have no control over its name. It is in the format xxxx.yyyy and when I create the connection definition, all is good and I can connect. I am specifying the database as xxxx.yyyy and setting the other various parameters appropriately (the same as I do for other databases). I can use the SQL tool in the connection editor to query the tables just fine. I create a simple TFDQuery consisting of a basic "select * from table" and when I attempt to run it, I am told that database xxxx is not in the list of valid servers and it should be added to sys.servers using sp_addlinkedserver. Clearly, what Firedac is passing to SQL server leads SQL server to interpret the xxxx as a server name. I have tried "select * from tablename", "select * from dbo.tablename", "select * from [xxxx.yyyy].tablename", "select * from [xxxx.yyyy].dbo.tablename" and other variations and still it refuses to work.

 

I am setting no specific values for the components - I create the definition in the data explorer, test that it connects, test that I can use the "SQL script" tab to run various SQL statements. I then drag one of the tables onto the form designer (a quick test) and try to run the query and it fails with the message:

 

"Could not find server 'xxxx' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinked server to add the server to sys.servers".

 

I have tried removing the database name from the connection definition, in the hope that the default database has been set - this does not work.

 

Any suggestions, please? I am unable to request that the database name be changed.

Edited February 6, 2019 by Martyn Spencer
Minor re-wording.

[Eli M. 38]

My only suggestion would be try various forms of quoting it with double quotes and  brackets. You tried some but maybe not using double quotes?

 

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-2017

 

Also you could copy the FireDAC source file where it throws the error from to your project and modify it as a workaround.

[Martyn Spencer 0]

Thanks Eli. I'll give that a go. My understanding was that when [ ] brackets were used, almost anything could be contained within them. I did try some basic playing around with quotes and brackets but none of my attempts proved successful. I'll monitor the connection to the DB and see what FireDAC is submitting to the DB, if I can.

[Dmitry Arefiev 106]

https://quality.embarcadero.com/browse/RSP-20349

[Martyn Spencer 0]

Brilliant. Thanks Dmitry.