Fully Qualified table name syntax

Posted on 2006-06-08
Last Modified: 2012-06-21
I would like to write a query accessing two tables using ADO in VB6. The syntax for fully qualified tables names is [server].[databasename].[owner].[table name].

When the server name is SQL2000, the fully qualified name works( Select * from SQL2000.jir.dbo.CusFil ). However, when the server name is fs-jirsql, I receive an error becasue of the hyphen( Select * from fs-jirsq1.jir.dbo.CusFil ). Alternately, I tried the IP address, but the periods in the IP confuse SQL( Select * from ).
How do I correctly format the fully qualified table name in a select statement when either the server name contains a hyphen or when I only have the IP address and not the server name?

Question by:mpdillon
    LVL 27

    Assisted Solution

    Select * from [fs-jirsq1].jir.dbo.CusFil
    LVL 27

    Expert Comment

    SQL Server has a long history of not liking the hypen in a name.
    LVL 50

    Assisted Solution

    you could also have spaces  in a name   the best way is to not use them in names but
    to always code within enclosed square brackets

    [server].[databasename].[owner].[table name]

    the square brackets are there for a purpose (in your example)...



    Author Comment

    Thanks very much. The square brackets work great with a name that contains a hyphen. However, I tried the IP address in the square brackets and it did not work. I received a message saying to add the server using sp_addlinkedserver. I read a little about this stored procedure but I don't think it is what I want. Is there a way to use the IP address in a fully qualified name?
    LVL 30

    Accepted Solution

    That server name is a 'logical' name

    That is to say that the server name must match a linked server.

    If you want to use the servers physical IP address in your query, there are two options:

    1. Create a linked server whose logical name matches the IP address. Basically this is an option but I can't see any practical reason for doing it. If your physical server changes IP addresses, your linked server name will not change.


    SELECT a.*
    FROM OPENROWSET('SQLOLEDB','';'sa';'MyPass',
       'SELECT * FROM jir.dbo.CusFil') AS a

    But there is rarely a good reason to refer to server IP addresses - thats what DNS is for!!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Suggested Solutions

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now