Solved

How do I join two databases when there is a "." in the table name.

Posted on 2009-05-19
8
187 Views
Last Modified: 2012-05-07
I am attempting to join two databases on the same sql server. I am able to access the databases like so for most of the tables:

SELECT s.item1,b.item2 FROM db1.dbo.t1 s join db2.dbo.t1 b on s.id1 = b.uid1

But when i need to access a view from db1 that contains a "." in the name of the actual table I get a sys.servers prompting me to add the server name. Example below:

SELECT s.item1,b.item2 FROM db1.dbo.name.v1 s join db2.dbo.t1 b on s.id1 = b.uid1

Unfortunately I cant change the name of the view "name.v1". Any help would be appreciated.
0
Comment
Question by:sbsnewbie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24428963
Hope this helps:

SELECT s.item1,b.item2 FROM db1.dbo.[name.v1] s join db2.dbo.t1 b on s.id1 = b.uid1
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24428970
You can either use [name.v1] or "name.v1" to tell Server that it is a single word.
0
 

Author Comment

by:sbsnewbie
ID: 24429031
I have tried both of these methods and I now get "invalid object name 'db1.dbo.name.v1'

If i insert a use db1 at the top of the query it works without the db1.dbo...but this wont work within the view I am trying to create in the db2.

Any thoughts?

0
Industry Leaders: 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!

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24429112
You can obtain an Invalid object name error in SQL Server due to the following reasons.

* the user you are connecting as does not have SELECT, UPDATE, INSERT, DELETE, EXEC permissions on the object;
* you aren't logging in / connecting as the user you expect;
* you are referencing the object without an owner name prefix (or with the wrong owner name);
* you are connected to the wrong database;
* you are, in fact, spelling the object's name incorrectly.

I suspect that you dont have SELECT permission on that view. Can you confirm whether you have SELECT permission on that view.
0
 

Accepted Solution

by:
sbsnewbie earned 0 total points
ID: 24429128
Yes. When i operate this query everything is returned as expected:

SELECT item1 FROM name.v1

Its only when I attempt to join that table across two databases and I am forced to reference it as:

db1.dbo.name.v1 that it errors out.

Its OK, I just went to the source table of the view 'name.v1' and got the data I needed. The source table does not have a "." in the name of the table so this view was created successfully. Thanks for the feedback, still puzzled as to how I can access this correctly.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24429142
In your First Database db1, issue the below and check whether it works or not.

select * from db1.dbo.name.v1

If that fails, then issue the below

select * from db1.name.v1

If below one works, then from db2 issue the below statement which should work fine

select * from db1.name.v1
0
 

Author Comment

by:sbsnewbie
ID: 24429167
First two queries did not work. I have tried all combinations without success.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24429178
Ok. Now provide error messages for each and every execution:

use db1
go

select * from db1.dbo.name.v1

If that fails, then issue the below

use db1
go

select * from db1.dbo.[name.v1]

If that fails, then issue the below

use db1
go

select * from db1.name.v1

If below one works, then from db2 issue the below statement which should work fine

use db2
go
select * from db1.name.v1
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 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