Compare databases between 2 SQL servers

Hello,
I wish to see if the tables from database A server A1 exist in database
A on server B1.
I setup a linked server from my first server (robertcamarda) to a
target (cognos-dev)
This works from robertcamarda:
select count(*) from [cognos-dev].ds_v6_source.dbo.stdmas

and this works:
select * from ds_v6_source.information_schema.tables order by
table_name

but this wont work:
select * from [cognos-dev].ds_v6_source.information_schema.tables order
by table_name

Error:
Msg 117, Level 15, State 1, Line 1
The object name 'cognos-dev.ds_v6_source.dbo.information_schema.tables'
contains more than the maximum number of prefixes. The maximum is 3.

I want to do something like:
select * from ds_v6_source.information_schema.tables
where table_name not in (select table_name from
[cognos-dev].ds_v6_source.information_schema.tables order by
table_name)

so I can see of the sql server (robertcamarda) has any missing tables
that exist on the server (cognos-dev)
TIA
Rob
SQL Server 2005 Enterpriseuse sqlcompare tool to compare anything and everything between two
databases

www.red-gate.com

download the trial version and see if it works for you.|||On 17 May 2006 05:30:36 -0700, "rcamarda" <robc390@.hotmail.com> wrote:

>Hello,
>I wish to see if the tables from database A server A1 exist in database
>A on server B1.
winsql has a trial that is pretty useful
hth
Jeff Kish|||rcamarda (robc390@.hotmail.com) writes:
> but this wont work:
> select * from [cognos-dev].ds_v6_source.information_schema.tables order
> by table_name
> Error:
> Msg 117, Level 15, State 1, Line 1
> The object name 'cognos-dev.ds_v6_source.dbo.information_schema.tables'
> contains more than the maximum number of prefixes. The maximum is 3.

There is an apparent inconsistency between the error message and the
command you presented.

Then again, I was not able to get it to work with the proper command
either. This is probably because the INFORMATION_SCHEMA views are
not present in the databases, only in master. (Hm, I believe they
were in SQL 7, so with if you connect to SQL 7 it might work.)

Rather than using INFORMATION_SCHEMA, use the system tables or
the catalog views depending on which version of SQL Server you
are on.

SELECT name FROM [cognos_dev].ds_v6_source.dbo.sysobjects
WHERE type = 'U'
ORDER BY name

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Company - Address

I need some help devising my tables.

I have a company table and a address table. I have created a linked table with two foreign keys from the company table and the address table respectively.

This set up allows me to apply more than one address per company which is fine. My problem is that I need a way to prevent a company from entering exactly the same address twice.

In the address table i have the following fields:

AddressKey - PK
CompanyKey - FK
Address 1 - 3
Town
County
Country
PostCode

In the company table I have the following fields.

CompanyKey - PK
FirstName
Secondname

And finally in the link table I have the following:

CompanyKey - FK
AddressKey - FK.

Now, if i entered the following into the address table Assuming that company id of 1 was already entered into the company table.

AddressKey - 1, CompanyKey = 1, Address1 = 11 Address2= Taylor

I need a way of preventing this from happening.

AddressKey - 2, CompanyKey = 1, Address1 = 11 Address2= Taylor

As can be seen the PK - FK values are unique and are correct for referential integrity, but the actual address is the same.

Any help we be mostly appreciated.

Cheers

Wayneyou do not need the linking table at all, because you apparently want to link each address to its company using the CompanyKey as a FK in the address table

the only time you need a linking table is when multiple companies share the same address

and even if this does occur, it probably does not occur all that frequently (compared to the bulk of your data)

therefore if two companies have the same address, put that address into the table twice

now, as to your question...

to prevent the same company from entering the same address twice, declare a unique constraint on ( CompanyKey, Address 1 - 3, Town, County, Country, PostCode )|||r937.

Thanks for the reply.

ok I understand why you would not use the linked table, but In my case the address table is in effect the site address of a particular company, and companies can have multiple site addresses.

If i don't use a linked table then this would not be possible.

I tried using an instead of trigger - would this not be a good idea in this case then?

Cheers

Wayne|||let's make sure we are talking about the same linked table

you can support the one-to-many model of a company having multiple addresses just with Company and Address table

the Address table CompanyKey is the FK that says which company this is an address for

the 3rd table, the link table, is not necessary

unless, like i said earlier, you wnt two different companies to "share" an address!

as far as preventing a compnay from entering the same address twice, no, you would not use a trigger for that, you would use a unique constraint for that|||r937.

Thanks for clearing that up for me. I will give it a stab once i've finished browsing ebay. Ok to give you a shout if I have any problems?

Wayne|||if you were thinking of contacting me personally through a private message or email, i would prefer that you post a followup in this thread

:cool:|||sorry that's what I meant!|||here would be a way to solve your issue by using a composite key(or Unique index)and an intersecting entity
your business rule dictates that one customer can have many addresses and that no duplicate addresses can be used

you could create a customer table

customerid PK
companyname
addressid

then create a address table

Addressid PK
StreetAddress
city
state etc..

now the issue exists that you cannot get 2 addresses out of this erd but you can by modifying the erd with an intersecting entity..

create a customeraddress table

Customerid PK
addressid PK
(any dependent data on a customer at a particular address goes in this table)
the composite key on the two columns generates uniqueness for each of them. (no one customer can have a duplicate address)

The join path would look like this
Customer <----> CustomerAddress <----->Address|||Ruprect, that's beautiful, but that's the many-to-many relationship

i have my doubts about whether that's required

i think only a one-to-many is required

and in any case, if you go right back to the original question, the situation was

AddressKey - 1, Address1 = 11 Address2= Taylor
AddressKey - 2, Address1 = 11 Address2= Taylor

and thus possible to give the "same" address to a company twice, because the "same" address is actually in the table twice! (this is a problem of surrogate keys, not one-to-many versus many-to-many)

your 3-table many-to-many does not prevent that duplication either

here's what i recommend:
create table Companies
( CompanyKey
, FirstName
, Secondname
, primary key (CompanyKey)
)
create table Addresses
( AddressKey
, CompanyKey
, Address1
, Address2
, Address3
, Town
, County
, Country
, PostCode
, primary key (AddressKey)
, foreign key (CompanyKey) references Companies (CompanyKey)
, unique (Address1, Address2, Address3, Town, County, Country, PostCode)|||Sunday Morning.

Just read your replies guys. Ruprect as r937 states, this was my original solution and does not prevent a duplicate address from existing, ok it does allow me to create a unique constrant based on the companykey and addresskey of the respective tables but this was not what I was after. - Thanks for the input though.

r937 - going to that last reply a stab.

Thanks for all your helps guys.

Wayne|||Thats worked fine.

Next question is how do I take that sever error message that is created (Server msg 2627) and produce a user friendly error message stating that they have violated key constraints.

Would I design a trigger for this?|||wait
now hold on justa cotton picken minute rabbit! ( channeling yosemite sam)

you solution limits the address process the actual # of cols (in this case 3) what if someone has 4 addresses are you going to alter the table?

my solution which is typically a many to many solution also eliminates the dupes in the customeraddresstable by creating a composite unique constraint or index on the customerid and addressid columns

here are the customers and address tables with some data
customer c Address a
c1 a
c2 b
c3 c
c4 d
c5 e

here is the customeraddress table

c1 a
c1 b
c1 c
c1 d
c1 e

if i try to add customer c1 with the address a again i will violate the constraint. no dupes and there is no limit to the possible addresses that one customer can have.

i wouldnt like to use it because of the extra joins so the previous solution might have a perf advantage. but my solutin does work.|||in the end guys i cam up with this design|||cool! good for you

Commit & Rollback Logic in VB.NET

I have several sets of code that need to delete rows from more than one database at a time. The rows are basically linked without being identified as having a foreign key. This means I issue two deletes. If one fails, especially the second one, there is no way to roll the first delete back.

Can someone either point me to some code that enables me to link the deletions, allowing me to insure that both are successful or both do not occur.

I cannot identify any fields on the secondary database table as specifically linked to the primary, as the secondary database is a storage medium for images, that may be linked to more than one different table.

TIA for any opinions, options, etc. Tom

you need to wrap a transaction around your deletes

read this:http://msdn.microsoft.com/msdnmag/issues/06/11/DataPoints/default.aspx

|||

Many thanks. That looks like the ticket.

Tom