Remote Extraction Failing for one user

[former_SQLA_member1694871]

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 dbo.sa_recompile_views(0)...

dbisqlc

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.

TIA

Jeff Gibson
Intercept Solutions - Sybase SQL Anywhere OEM Partner
Nashville, TN

[former_SQLA_member1694871]

Reg??? Chris??? Anyone??? 🙂

[Former Member]

No answer here - just commenting to say that I have seen that happen before, too - maybe once or twice - no rhyme or reason.

[VolkerBarth]

No hints from me this time - though we're using views in our remotes, too, I have never seen this issue. - Yes, that won't help you, sorry:(

[chris_keating]

Are you able to send a copy of unload file for review? I will only need the schema. If so, my address is last_name@sybase.com.

[former_SQLA_member1694871]

Just sent the reload file to your email address. Please advise if you need any additional information from me. Thanks!!

[JimDiaz · Accepted solution]

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.

Jim

[former_SQLA_member1694871 · Accepted solution]

Yeah. Already checked the objects. All tables (other then system tables) are granted permissions to public (which all users are a part of). All users are under the same publication.

Yes, all remote databases are more or less full databases with security restrictions. It allows us to turn jobs off or on without having to re-extract when a user is half way across the country.

That's a good idea Jim. I've got the reload file. I'll see where that takes me.

[former_SQLA_member1694871]

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!! 🙂

[Breck_Carter]

OK, here goes... open this page http://sqlanywhere.blogspot.ca/2012/03/tales-from-doc-face.html and read each and every article that talks about SQL Remote 🙂

[VolkerBarth]

Here's an even greater challenge - Breck might remember writing that grand introduction in the ol' days:):

Tip 78: Replication Step By Step A Detailed Demonstration Of Publish Subscribe Replication Using Syb...

[VolkerBarth]

@Breck: Yep, my backlinks are working:)

[former_SQLA_member1694871]

That's like the bible of replication!!