We recently had a superintendent come back to work here after being laid-off for sometime. We never removed him from the users and groups section of the database, but we had turned off his replication. We recently turned everything back on and attempted to extract a new remote database for him.
During the extraction, the following message is displayed in the personal server console of the database that is being created...
Performance warning: View "view_rep_users_projects" in database "birchfieldd" was invalidated due to DDL operations on one of the referened objects.
Here is that view...
ALTER VIEW "pa"."view_rep_user_projects" as select su.description, p.project_num, p.project_name, su.user_name, p.project_seq, sru.time_received, su.sec_userseq, su.com_con_userseq from pa.rep_user_to_project_join as r key join(pa.projects as p,pa.security_users as su), pa.security_users as su natural join sys.sysremoteusers as sru
Since we are using the command line for our extractions and using dbisqlc with it, the following error popped up after the performance warning while it was running the call
Error in file "c:\rjb\repl\birchfieldd\reload-12.sql" at line 183995 RAISEERROR executed: Unable to recompile view "view_file_numbers"
I started recompiling the views and testing them (everything worked). All it did was continue to give the same performance warning with the same view, but the RAISEERROR would just go to another view. I ended up recompiling all the views and validated the database. All worked and the validation finished with no errors. Still, during the extraction, it gave this error on each extraction attempt.
I finally gave up and decided to see what would happen if I re-extracted some of the other remote users in the database. Wasn't I stunned to see that those extractions went off without a hitch. No errors, nothing. Flawless extraction.
Does anybody have any ideas why a single users extraction would run into errors like this? He is under the public group like all the other remote users, and, his remote permissions are identical to the other users in the database. Also, for the record, I tried to extract the remote using Sybase Central. Got the same error there too.
Any thoughts on this would really be appreciated. We are running SA12.0.1 Build 3554.
asked 15 May '12, 18:39
You need to check that all the objects the view depends on exist and if one of the objects is a view that it exists and so on. I can't see how this problem should happen if you can extract another user with the same publication. I assume you are using the -an option which we never use because it expects all views to function on the remote when some may not be used and there is no option to select anything other than tables in a subscription. Try doing this with a manual reload and skipping the recompile views section then manually review the extracted database and tell us what happens.
answered 18 May '12, 17:59
I would like to take this time to profusely apologize to all of you that helped me look into this issue and thank Jim for the idea of skipping the recompile views section. Once I did that I opened the database and saw that NONE of the tables were in my database.
That's when I started looking through the database and noticed that I had NOT set ANY subscriptions on the remote user.
I'm am looking to see what type of penance I need follow through with for missing such a basic step at setting the SUBSCRIPTION!!! Any suggestions Breck????
Thanks again everyone. I hope somebody else could learn from my mistake in the future!! :)
answered 21 May '12, 16:17