Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 25th, 2003, 02:45 AM
Authorized User
 
Join Date: Jun 2003
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Relation

Hi there,

I have two problems with the SQL 2000 database.
The reason that is because we got our database transfer by the hosting company from one server to another.
Since then, nothing is the same as:

1. If I import a new table (that I created locally, checked to see if working), using DTS Import/Export, I get my table as Products(my name).

Before the server changes all my tables showed as Products, MainCategory, etc., but now the tables name are as Products(dbo), MainCategory(dbo), etc.

I checked to see if I have Database Owner permission and is there but I can not explain what's after happening.

2. I have a diagram for few table, and before hand I used to be able to open the database with Microsoft Access and Open the main table then had a + sing on each record so I could scroll to the next table by ID, but all that now is gone.

I deleted the relation between the tables and created again but is still the same.

Can anybody give us some advice.


L Ion
__________________
L Ion
 
Old October 25th, 2003, 01:40 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The reason you are getting the dbo is because you are not the creator of the tables on the other server.
Where you the dbo / sa on the other server?
One way to fix it is to change the database owner from whoever created them on the new server (the actual person who moved it) to your login (that is of course if you are the rightfull owner)

Who was the owner of the tables/databse on the old server?

Let me know if this works.



Sal
 
Old October 25th, 2003, 02:18 PM
Authorized User
 
Join Date: Jun 2003
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I was the owner of the database on the old server.
When my hosting company created a new SQL server thay moved my database to this one. That's when I discover that my table are not the same anymore, for example it used to show in Access as Products, but now when I connect to the new server it shows Products (dbo)...etc.

If I create a new table using design view or SQL Query Analizer the new table will have the (dbo) at the end, but if I import a table using DTS Import I get the tables showing my user name at the end - TableName(username).

I checked the permission and I gave myself db_owner and db_ddladmin roles, but the same thing happens again. I didn't have that problem before.

I checked the permission for the Database and there is two Users:
DBO & myUsername.

Could that cause the problem?

I had a look at the owner of the SQL2000 server and his the creater of the database itself but the table were uploded by me using DTS Import.

One thing I noticed, isn't hte owner of the SQL2000 server ment to be the SA login?



I sorted out the relation as when I open the main table I choosed Insert --> SubDatasheet, so that's OK now.



L Ion
 
Old October 25th, 2003, 03:31 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

the problem goes back to who is the owner of the tables now. The person who moved it in is the dbo. You need to be made the dbo if you were the dbo before.
This happenned to me before. Some fools at a company I did a contract with would not make me the dbo for the database that i was creating. instead they created a username with my full first name and last name (that is 19 characters long) this was such a pain when i got my own server and I was the dbo.

It was painful to move the database over to the new server, because i also had to re-create that user-name with 19 characters, iomport the stuff and then re-assigning ownership to dbo. (backwards from what happend to you)

In Access you will see the tables you create as Table1. the ones someone else create will show as Table2(user2). Anoying. This is particularly a pain whan you use stored procedures. They show some _ something in front.

Make sure your security is ok, this can could cause some problems for any users attempting to use your objects.

Sal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Relation update ajaidass ADO.NET 1 January 11th, 2007 01:25 PM
constraint for 1-to-n relation j_yan MySQL 0 December 10th, 2004 11:03 PM
find relation in dataset with c# kobystud C# 2 April 21st, 2004 01:04 AM
how can i best implement a many to many relation? Haroldd Access 1 July 2nd, 2003 03:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.