Search code examples
sqlsql-serverfully-qualified-naming

Column name in SQL Server fully qualified name

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.

Solution

  • 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