?
Solved

Confused by .DBO table names after upsizing

Posted on 2003-02-24
7
Medium Priority
?
393 Views
Last Modified: 2008-02-01
I'm new to SQL

I have upsized an access database and converted it to an access project. The tables and queries migrated to SQL 2000  - No problems.

When I open the Access project on a client PC all of the tables and queries have (dbo) after their names.

I have a problem when I attempt to run reports - they fail to find the source table/query. If I edit the report and change the source from table to dbo.table the report works.

Is this the right thing to do, or should I sort out the table names.

Please be gentle....
0
Comment
Question by:greenwatch
[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
7 Comments
 
LVL 1

Expert Comment

by:Divaqs
ID: 8012118
Hi greenwatch,

Within SQL there is what is called a fully qualified name, which includes Server.DB.owner.table.column

"dbo" is an owner, meaning "database owner".

It wouldn't hurt anything to use more of the qualified object name then just the table name. This reduces chances of the wrong object being found and can slightly increase performance.
0
 

Author Comment

by:greenwatch
ID: 8012663
So then is the right thing to do to go through all the forms/reports and change the source from "query" to "dbo.query/table" ? Or is there a better way? And will the tables/queries always be known as "dbo.query/table" ?

Sorry for all the questions but I want to get this right.
0
 
LVL 1

Accepted Solution

by:
Divaqs earned 150 total points
ID: 8012724
Yes, I think it would be good to do both, especially since it involves Access.
0
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!

 

Expert Comment

by:mohit108
ID: 8014375
Hi greenwatch

The problem u are facing is directly related to ur connection parameters.

If the tables u have imported are with specific user name, then try to connect ur report tables with same username.

e.g.

Table Name
someuser.table1

now u have connection in reports using some other user (someotheruser)

Now if u change this  (someotheruser) to the real table owner (someuser), then ur problem will go away and u have to change a single line of code (The connection parameter nothing else).

The fully qualified name is Good, but here u have to change a lot in ur code. So try to use this scenerio.

Bye
0
 

Author Comment

by:greenwatch
ID: 8021537
mohit108 you appear to be going along an interesting line, but i am not totally clear. I am connecting to the database using the id which linked the tables (in your example someuser). I will increase to 250 points if anybody can give me a clear understanding of how to resolve the issue with a one line change.
0
 

Author Comment

by:greenwatch
ID: 8076784
i started with 100 points and upped it to 250 in the hope of getting a definitive answer. That didn't happen. I'd like to split the original 100 points evenly between Divaqs and mohit108
0
 

Expert Comment

by:modulo
ID: 8329677
Dear greenwatch

I've refunded 200 points to enable you to accept the comment for one expert and to post a
"Points for <expertname>" Q for the other expert in this topic area.

Please:
1) Post the link to the original Q in the "Points for <expertname>" and
2) Add in the original Q a comment with the link to the "Points for <expertname>", thus the email notif will warn the expert.

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

752 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