Re: Copy SQL Server Objects Fails for certain views

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hello Joseph,

You can still script the whole process and you could even put the steps into a DTS package if it helps to visualise the process. I would also look at sp_attach_db and sp_detach_db

Allan



Hi Allan,

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're definitely on target as far as reliability is concerned.
However, to make this work for us in our application context we'd need
to make Backup/Restore as transparent to the customer as our DTS
package currently is. Our customers don't typically know a database
from a monkey wrench. The advantage to DTS is we set it up for them
and it just runs (well, usually :-) for them silently in the
background. It seems to me that logistically, Backup / Restore might
be a bit more difficult to set up in this manner, but if you have any
suggestion on how to do this, I'm eager to hear.

Thanks!

- Joe Geretz -

"Allan Mitchell" <allan@xxxxxxxxxxxxxxxxxx> wrote in message
news:d4c9a65237e998c7decb051d4258@xxxxxxxxxxxxxxxxxxxxxxx

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: Executing DTS C#
    ... That DTS Package works ... >> public string serverName; ... >> private void InitializeComponent() ...
    (microsoft.public.sqlserver.dts)
  • Re: Executing DTS C#
    ... Looping through a global variable Rowset is the best way. ... DTS process. ... dts package if the package doesen't run. ...
    (microsoft.public.sqlserver.dts)
  • Re: Executing a DTS package from ASP.NET
    ... Hey, I have company. ... See the thread 'DTS from ... > file in my own machine, only SQL Server in another machine. ... > approach to run this DTS package triggered by web? ...
    (microsoft.public.sqlserver.dts)
  • Re: Scheduling DTS to run every hour Until text file is loaded
    ... trigger to auto-schedule a job. ... TargetServersRole has been denied execute permissions on the sproc. ... > existence of a file in DTS, and if not exit the package. ... > tweak the job schedule from inside the DTS package after the file has been ...
    (microsoft.public.sqlserver.dts)
  • Re: SQLEM: Package Error: "The system cannot find the file specified." on open of DTS Pack
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... www.SQLDTS.com - The site for all your DTS needs. ... > In trying to open a DTS package from SQL Enterprise Manager, ... > the error message: "Error Source: Microsoft Data Transformation ...
    (microsoft.public.sqlserver.dts)