relation schema SQL Server 2005

Hi Group,

(I am just starting with SQL Server 2005.)
On SQL Server 7 I used often the nice relation schema, where I used to
draw out the whole database, especially the Foreign Keys constraints.

I found these relational schemas very handy to study an old database I
build a few years earlier that needs some updating.

I tried to find such an utility in SQL Server 2005, but cannot find it.
I did found the FK-constraints, but it is just an popup where I can
define them.

The overview such a visual schema gave was really great and I miss it.

Question: Is it gone in SQL Sevrer 2005, or do I just not know where to
look? If the latter, please guide me. :)

Thanks for your time.

Regards,
Erwin MollerErwin Moller wrote:

Quote:

Originally Posted by

Hi Group,
>
(I am just starting with SQL Server 2005.)
On SQL Server 7 I used often the nice relation schema, where I used to
draw out the whole database, especially the Foreign Keys constraints.
>
I found these relational schemas very handy to study an old database I
build a few years earlier that needs some updating.
>
I tried to find such an utility in SQL Server 2005, but cannot find it.
I did found the FK-constraints, but it is just an popup where I can
define them.
>
The overview such a visual schema gave was really great and I miss it.
>
Question: Is it gone in SQL Sevrer 2005, or do I just not know where to
look? If the latter, please guide me. :)
>
Thanks for your time.
>
Regards,
Erwin Moller

Forget my posting. :-)
I found it.

I had to make an owner of the database before I could proceed.
Did that, and now I have my nice Database Diagram.
(Note to myself: It is named 'Database Diagram', not 'Schema'.)

Sorry for the noise. :P

Regards,
Erwin Mollersql

Reinitializing transactional subscribers

In SQL2000, SP3, We want to periodically refresh our read-only
subscribers with the latest schema and data (including FK's, indexes,
etc) whenever we role out a new software release. The database is
relatively small and we can currently disable publishing\remove
subscribers, and add them back in about 15 minutes. As the database
grows, I'm not sure if this would be the best method of synchronizing
the subscribers, but management likes the idea of a complete snapshot.
Is there a typical approach to updating subscribers with all
differences that makes more sense? The subscribers are primarily for
Disaster Recovery, but are also used to reduce the need for our
European customers to come to the US for reads. When I tried the
"start synchronizing" option in the EM gui, it has difficulty
dropping/replacing tables because it can't drop tables w FK's.
Also, can anyone explain the difference between reinitializing and
synchronizing?
thx much!
Gary,
if you have a need to drop FKs then it is possible to apply a prescript
before the snapshot is applied (botom of snapshot tab on publication
properties).
Some schema changes can be carried out using the 'plumbing' of replication,
eg sp_repladdcolumn and sp_repldropcolumn. Some other changes eg addition of
an index, can be done using sp_addscriptexec.
As for the difference between initialization and synchronization, right at
the start when the snapshot is sent to the subscriber there isn't really
one. Once the snapshot has been applied, sending down transactions in the
case of transactional replication is referred to as synchronization, and
reinitialization would refer to a new snapshot being created and propagated.
HTH,
Paul Ibison
|||Thanks Paul... But rather than running a pre-script, and individual
system sp's, do you know if this methodology - dropping/re-establishing
replication as new releases are rolled out is a common approach to
keeping things in-sync?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I have seen it used, but sp_droparticle and sp_addarticle, along with
sp_repladdcolumn and sp_repldropcolumn are more common for small changes.
Regards,
Paul

Reinitializing subscriptions not working

Hi,

I'm doing merge sync between SQL Compact on mobile devices and SQL Server 2005. I recently made a schema change on the server and noticed that it caused an error on the subscribers when syncing. I fixed the schema problem, and did a reinitialize all subscriptions with a new snapshot. On the mobile devices I forced a reinitialisation (upload changes first). I'm still getting the same error message, which relates to a schema change which is no longer relevant to the server db and snapshot. For some reason the subscriber dbs are trying to reapply the original problem schema change even though they're supposed to be reinitializing to a new snapshot. I've even tried dropping the affected tables from the publication, and reinitializing all subscriptions with a new snapshot, but still the same error on the subscribers. Does anyone know why the subscriber dbs are still trying to apply a now defunct schema change (on tables which no longer exist in the publication)? Is there any way to flush this from the subscribers so they correctly pick up the new snapshot?

Regards,

Greg McNamara

Some additional information on the above:

The "rogue" schema change appears to be coming from the publisher, and not cached on the subscriber. The basic problem is that I'm trying to reinitialize subscribers, but it's trying to make a schema change on the subscribers which was made before the current snapshot was created. My understanding of reinitialization was that it would upload subscriber changes and then basically rebuild the subscriber db from the snapshot. Instead it seems to be applying incremental, historical schema changes on the subscriber.

Hope someone can help me with this.

Regards,

Greg McNamara

|||

And more info:

The schema change causing the original problem was the addition of a foreign key constraint. Records in a table were deleted on the server db before adding the constraint but the subscriber dbs still contain records. The sync is failing because it's trying to apply a constraint against existing records and failing. Reinitializing the subscribers is not fixing the problem (as detailed above). I decided to try deleting the records on the subscriber db before re-syncing. I now get a different error message on sync:

"Either the cursor is not on a row or there are no rows left"

Apparently this is a SQL Compact engine internal error. The malfunctioning reinitialization function seems to have caused this. I tried a repair/compact on the db but still get the same error message on syncing.

Back to the reinitialize not using the current snapshot, is anyone aware of places I should look to see if an old snapshot is being cached and somehow used by the merge sync agent?

Regards,

Greg McNamara