|
|
You're in the
sql server dts
group:Copy SQL Server Objects Fails for certain views
sql server dts:
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'
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 [quoted text, click to view] "Jonathan Orgel" <Jonathan@srssoft.com> wrote in message news:ee1MIoJDGHA.3920@tk2msftngp13.phx.gbl... > 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' > > >
[quoted text, click to view]
> 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 - [quoted text, click to view] "Michael Hotek" <mike@solidqualitylearning.com> wrote in message news:OJOVDuNDGHA.3876@tk2msftngp13.phx.gbl... > 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@srssoft.com> wrote in message > news:ee1MIoJDGHA.3920@tk2msftngp13.phx.gbl... >> 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' >> >> >> > >
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 [quoted text, click to view] >> 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@solidqualitylearning.com> wrote in message > news:OJOVDuNDGHA.3876@tk2msftngp13.phx.gbl... > >> 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@srssoft.com> wrote in message >> news:ee1MIoJDGHA.3920@tk2msftngp13.phx.gbl... >> >>> 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' >>>
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 [quoted text, click to view] > 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@no-spam.sqldts.com> wrote in message > news:d4c9a65237e998c7decb051d4258@msnews.microsoft.com... > >> 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@solidqualitylearning.com> wrote in message >>> news:OJOVDuNDGHA.3876@tk2msftngp13.phx.gbl... >>> >>>> 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@srssoft.com> wrote in message >>>> news:ee1MIoJDGHA.3920@tk2msftngp13.phx.gbl... >>>>> 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'
Hi Allan,
[quoted text, click to view] > 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 - [quoted text, click to view] "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:d4c9a65237e998c7decb051d4258@msnews.microsoft.com... > 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@solidqualitylearning.com> wrote in message >> news:OJOVDuNDGHA.3876@tk2msftngp13.phx.gbl... >> >>> 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@srssoft.com> wrote in message >>> news:ee1MIoJDGHA.3920@tk2msftngp13.phx.gbl... >>> >>>> 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' >>>> > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |