SQL server linked server, Quering remote server instance with \ in it name

Question

Hi All,

I need to query a SQL server linked server with \ in the name like Server name \instancename.

when I use [linkedservername].[db].[Schemaname].tablename then receiving an error.

Error : msg 117 level 15, State 1 procedure

'DBSourceServer.SourceDWHTemp.dbo.SourceTable1' contains more than the maximum number of prefixes. The maximum is 2

If I remove square brackets then receiving another error.

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near  \

is there a way to address this issue. I cannot change the linked server name.

Please advise.

Thank you

Answer 1

Hi @Newtojob

Generally, SQL Server support four-part name in the form like this:

SELECT * FROM [LinkedServerName].[DatabaseName].[SchemaName].[TableName];

However, the error message says the maximum value is 2.

Guess that you're using the SELECT - INTO Clause, because it is not supported to create new table on a remote server.

Try this:

INSERT INTO [LinkedServerName].[DatabaseName].[SchemaName].[TableName]
SELECT * FROM [LocalServerName].[DatabaseName].[SchemaName].[TableName]

Alternatively, you can use the OPENQUERY function or the OPENDATASOURCE function in the FROM clause to specify the remote data source.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

contains more than the maximum number of prefixes. The maximum is 2

Then I guess you uses Azure SQL Database?

Azure DB don't support cross server/cross database access.