0

I have a query on a database table like this select * from dbo.MyTable which works fine even though for reason I don't know the table MyTable has been created with trailing spaces in the identifier.

That should not let me worry as described here since SQL Server just ignores trailing spaces. Unfortunately, I have a case where I need to select the data from that table using a linked server like this:

select * 
from linkedserver.targetdb.dbo.[MyTable   ]

I need to use the quotename including the spaces otherwise I get the error:

An invalid schema or catalog was specified for the provider "SQLNCLI11" for linked server "linkedserver"

Unfortunately the queries are generated dynamically and don't account for the trailing space as a comparison of the object name with the information_schema table works (correctly ignoring the trailing spaces).

Could this be related to the provider SQLNCLI11 or a configuration of the linked server?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
casenonsensitive
  • 955
  • 2
  • 9
  • 18
  • 1
    Why not just remove the trailing spaces from the table name? Seems like a much simpler, and cleaner approach rather than trying to debug the internals of cross server name resolution. – GarethD Aug 14 '20 at 11:47
  • 1
    Fixing the table's name seems to be the solution here. – Thom A Aug 14 '20 at 11:49
  • Fixing the table name is definitely what I will discuss. It is not my object and I want to be sure that I've looked at the problem from a few perspectives. I don't know if there's some other code using selects on that table via the mentioned linked server. Changing the name might break it. – casenonsensitive Aug 14 '20 at 12:03

1 Answers1

3

Considering that, as you stated, SQL Server is "happy" to reference a table called MyTable as MyTable, apart from when you used a linked server query, the obvious solution is to fix the name of the table.

You can do this using sp_rename. For example:

USE Sandbox;
GO

CREATE TABLE dbo.[YourTable   ] (SomeCol int);
GO

DECLARE @SQL nvarchar(100) = N'SELECT * FROM dbo.YourTable;'; --Just to prove the same statement is run

EXEC sys.sp_executesql @SQL;

SELECT DATALENGTH([name]) --returns 20, so spaces are tehre
FROM sys.tables 
WHERE [name] LIKE N'MyTabl%';

EXEC sys.sp_rename N'dbo.YourTable   ',N'YourTable';

EXEC sys.sp_executesql @SQL;

SELECT DATALENGTH([name]) --Returns 14, so spaces are gone.
FROM sys.tables 
WHERE [name] LIKE N'MyTabl%';
GO

DROP TABLE dbo.YourTable;

Additional note: if you do have some code that references the object as dbo.[YourTable ] it will still work if the trailing spaces from the table's actual name are removed as well.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • 1
    Perhaps your table is already being used in several other places and you cannot rename it, so you can create `SYNONYM` https://learn.microsoft.com/pt-br/sql/t-sql/statements/create-synonym-transact-sql?view=sql-server-ver15 – Eliseu Marcos Aug 14 '20 at 12:23
  • @EliseuMarcos notice my comments about how trailing spaces are handled by the database. – Thom A Aug 14 '20 at 12:25