Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6072
  • Last Modified:

Fully Qualified table name syntax

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, 10.0.0.120 but the periods in the IP confuse SQL( Select * from 10.0.0.120.jir.dbo.CusFil ).
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?

thanks,
0
mpdillon
Asked:
mpdillon
3 Solutions
 
ptjcbCommented:
Select * from [fs-jirsq1].jir.dbo.CusFil
0
 
ptjcbCommented:
SQL Server has a long history of not liking the hypen in a name.
0
 
LowfatspreadCommented:
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)...

;-)

0
 
mpdillonAuthor Commented:
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?
0
 
nmcdermaidCommented:
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.

2. Use OPENROWSET or OPENDATASOURCE.

SELECT a.*
FROM OPENROWSET('SQLOLEDB','10.0.0.120';'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!!

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now