Re: Copy SQL Server Objects Fails for certain views

Hello Joseph,

I cannot help but think that if you are using the Transfer Objects task to move a whole DB everytime that it will be a lot slower and error prone than a simple BACKUP/RESTORE.

You can then run scripts on the restored database to add/remove users/logins etc if you need to

Is this an option?


Allan

Redefine the package.  Copy objects just uses an alphabetical order.
If you define this to explicitly define which objects are moving,

Easier said than done. Our database is a constantly evolving
conglomeration of hundreds of objects. We don't relish having to
constantly evolve the DTS package as well. That's why we are using the
more generic transfer methodology to transfer the entire database,
rather than identifying each object specifically.

Also, it does not seem that objects are copied in alphabetic order, it
seems to have more to do with creation / last modification date. By
making an innocuous change to the object we are now able to complete
the transfer. We did not rename the object, yet we managed to change
its transfer sequence.

Nice going Jonathan! :-)

- Joe Geretz -

"Michael Hotek" <mike@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:OJOVDuNDGHA.3876@xxxxxxxxxxxxxxxxxxxxxxx

Redefine the package.  Copy objects just uses an alphabetical order.
If you define this to explicitly define which objects are moving, you
can specify the order which will move the dependent objects first.

--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Jonathan Orgel" <Jonathan@xxxxxxxxxxx> wrote in message
news:ee1MIoJDGHA.3920@xxxxxxxxxxxxxxxxxxxxxxx

We have been using the 'Copy SQL Server Objects' with success for
some
time to copy an entire database to another server. Recent changes to
our
database infrastructure cause DTS to fail:
1) We have a check constraint which uses a user function (which
refers to
the table on the check constraint
2) We have a view which refers to another view
DTS fails on both. DTS fails on the check constraint and stops. if I
remove the check constraint it will fail on the view. See errors
below.

Any hints how to solve this would be appreciated.

Jonathan Orgel

Errors:
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'dbo.SRS_NumberRWSubjects'
[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name
'VMSGRECIPIENTS2'



.

Relevant Pages

  • Re: Key attributes with list values was Re: What are the differences ...KEY
    ... Jane Harper is married. ... And a constraint that states that single people cannot become divorced. ... database, or users, for that matter, to distinguish between them. ... That's the whole point of keys. ...
    (comp.databases.theory)
  • Re: New to SQL server
    ... [CONSTRAINT constraint_name] ... | [FOREIGN KEY] ... Is the name of the database in which the table is created. ... REFERENCES permission on the type is ...
    (microsoft.public.access.adp.sqlserver)
  • Re: A real world example
    ... Isn't NULL/NOT NULL a multiplicity constraint? ... different in successive database states, ... different in successive universe states, ... surrogate key with inconsistent natural keys. ...
    (comp.databases.theory)
  • Re: How do you mulitply in a field?
    ... asked how one places a unique constraint on a combination of fields ... This will work for Jet in ANSI-92 Query Mode Jet SQL. ... I apply security so that the database can't be hacked. ... If I am selecting only one name, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: A real world example
    ... Brian believes tuples in seperate relation values should correspond to ... Isn't NULL/NOT NULL a multiplicity constraint? ... different in successive database states, ... surrogate key with inconsistent natural keys. ...
    (comp.databases.theory)