Query on Linked Server 4 Part Naming Convention

[mamartin]

I am trying to join tables from 2 separate customer databases (both SQL Server 7.0) with the same type of table structure. I have added one as a linked server, but I am having problems with determining the correct syntax for the four part naming convention. Any suggestions?

Thank you,

mamartin

 

[clapag22]

SERVERNAME.DBNAME.OWNER.TBNAME

I find on 7.0 that it needs to be in all caps for it to work consistently.

 

[tlbroadbent]

Be careful about all CAPS if any servers have a case sensitive collation. Our servers are not case sensitive and I've not had to use all caps. Case does not matter. Some non-SQL Server linked servers may require the use of all CAPS.

You can also use Openquery to access data on a linked server. Using Openquery is often faster than performing the same query using the four-part object name because the query is executed on the linked server.

Select *
From OpenRowset(linkedserver,'Select col1, col2, col3 From database.owner,table Where col1=''abc''') Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[clapag22]

My 7.0 servers were not case sensitive, either. But I still found that I had to put it in all caps. I don't find that to be true with SQL2K.

 

[mamartin]

I tried both options (all CAPS and Openquery), but each time I get a syntax error. My linked server name is HTS-BDC-1. Could the - cause a problem?

Thanks for you assistance.

 

[tlbroadbent]

Can you post your query?

I made a mistake in the previous post. The example uses OpenRowset rather than OpenQuery. This is the Correct syntax for OpenQuery.

Select *
From OpenQuery(linkedserver,'Select col1, col2, col3 From database.owner,table Where col1=''abc''') Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[mamartin]

My query is "select * from HTS-BDC-1.HOMES.DBO.INSTALLATION"

I get a syntax error pointing to "-".

 

[tlbroadbent]

Try this. Enclose the server name in brackets.

select * from [HTS-BDC-1].HOMES.DBO.INSTALLATION Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[skicamel]

Try select * from [HTS-BDC-1].database.owner.table

 

[skicamel]

Terry just beat me to it (like always).

 

[mamartin]

yes, the brackets worked. Thanks. Now, I am getting a login failed for user error message. Any ideas? This is regardless of the type of security I check in the security tab.

 

[tlbroadbent]

Verify that the remote login is setup properly. Review the security tab of the linked-server properties. You can use a local login if the same login exists on the remote server or you can provide a remote login ad password. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[mamartin]

OK, select "they will be impersonated" seems to work. Thanks. Can you tell me what the various options within the security tab for linked servers really mean?

 

[tlbroadbent]

I can but I'd just be copying SQL BOL. Search for "Linked Server Properties (Security Tab)" in SQL BOL or at the MSDN website. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[BigRetina]

Hi
I have an ALMOST similar problem
I have a FoxPro linked Server..i can see the tables in the enterprise manager..I can query them using OPENROWSET but I can not query them directly as :
SELECT * FROM MyLink...MyTable

it gives the following error:
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.

what could be it?
and what is the catalog or schema for a dbf file?..i think there is NOT!