Invalid Objects Error

Whenever I use the DTS Wizzard to copy my database, I get an "Invalid Object" error and the transfer aborts. The error message says one of my views is invalid. If I delete this view and rerun, the copy is successful. When I recreate the view the error returns. The application that uses this database & view runs successfully everyday. I just can't seem to get the DTS package to do my backup / copy.

Help needed ASAP

Thanks in advance.Please post the DDL for the VIEW and the underlying TABLEs.

Hugh Scott

Originally posted by James Aiello
Whenever I use the DTS Wizzard to copy my database, I get an "Invalid Object" error and the transfer aborts. The error message says one of my views is invalid. If I delete this view and rerun, the copy is successful. When I recreate the view the error returns. The application that uses this database & view runs successfully everyday. I just can't seem to get the DTS package to do my backup / copy.

Help needed ASAP

Thanks in advance.|||Thanks for the help. This condition has existed for over a month. It seemed to have been created when I updated the definition of the view.

The database was originally upsized from MS-Access a year ago so I don't have the DDL for all of the tables. I attached a zip file with the DDL for the view, and current PDF's of the tables generated from an MS-ACCESS project file attached to the database.

Thanks in advance.

Jim Aiello|||I have to confess that diagnosing SQL code is not my strong suit. Some may wonder if I even have a strong suit ;-). It's a fair question.

In looking at your code, I am troubled by the white space between various concatenations. There's nothing wrong with it per se, but I just wonder if one of them is slightly off.

The other thing that came to my mind was the possibility that one or more records in the view might exceed 8000 (?) bytes -- the max allowed by SQL (the precise number is in BOL, and I don't have it in front of me). Looking at the underlying tables, that did not appear to be a problem, since the address fields appeared to be nicely limited to nvarchar(40). Still, if some of the white space were improperly delimited and it were combined with some extra-long addresses, I suppose it might conceivably result in a record with too many characters. Not sure how to check for and identify the problem record.

Some additional to do's for you:

1. Right click on the tables in EM and find Generate SQL Scripts (it's at the bottom of the pop-up, I think). Generate theSQL scripts and save them to a file. Then, post those scripts to the forum. That way others who are much smarter than me can help you work on the problem.

2. Populate a new database with the views and the underlying tables (from the DDLs you just created). Do your DTS Wizard while there is no data in the database (if it succeeds, then I might possibly be on to something). If it fails, then you haven't wasted much time and you can focus in on the syntax of the view.

3. If the DTS Wizard does work, then start populating the tables with data from your production machine. Take it in increments and see if you can re-create the error.

I hope this helps.

Hugh Scott

Originally posted by James Aiello
Thanks for the help. This condition has existed for over a month. It seemed to have been created when I updated the definition of the view.

The database was originally upsized from MS-Access a year ago so I don't have the DDL for all of the tables. I attached a zip file with the DDL for the view, and current PDF's of the tables generated from an MS-ACCESS project file attached to the database.

Thanks in advance.

Jim Aiello