Column name in SQL Server fully qualified name

Question

I read in SQL Server documentation that I can specify column names in FROM clause of a simple SELECT query. But when I try to run this query:

select * from my_db.dbo.test_table.test;

I get the following error:

Msg 7202, Level 11, State 2, Line 1 Could not find server 'my_db' 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.

I know this is a T-SQL page, and I'm trying to run .

Why does this happen?

I'm using SQL Server version 2017 via Microsoft SQL Server Management Studio version 2017 as well.

Answer 1

try rewriting from this

select * from my_db.dbo.test_table.test;

to this

select test_table.test,* from my_db.dbo.test_table;

the way it was written with that many Periods it assume you are trying to fully qualify the table so in what you had tried to the server is treating it as follows

my db = linked server (a server other than the Server you are working on)
dbo = Schema (which is correct)
test_table = Table (Also correct)
test = just plain erroror

the fields you want to show should directly follow the Keyword Select so if you only wanted 2 fields you could write

select test,test2 from my_db.dbo.test_table;

or simpler if you only have the one server

select test,test2 from dbo.test_table;

or if you only have the defailt Schema dbo (Database Base Owner)

select test,test2 from test_table;

I hope thaelps

Answer 2

Please run sp_addlinkedserver and sp_addlinkedsrvlogin SPs:

EXEC sp_addlinkedserver @server='MyLinkedServer'
EXEC sp_addlinkedsrvlogin 'MyLinkedServer', 'false', NULL, 'MyUserName', 'MyPassword'

After that, you can try to run: select * from my_db.dbo.test_table

Ref: https://blog.sqlauthority.com/2014/08/26/sql-server-fix-error-7202-could-not-find-server-in-sys-servers-verify-that-the-correct-server-name-was-specified/

Also, even though it is SQL Server 2014 related maybe helps, please see: Could not find server 'server name' in sys.servers. SQL Server 2014

Answer 3

You cannot specify columns in the FROM clause, but you can specify columns in the SELECT clause.

select test from my_db.dbo.test_table