Home Forums SQL Server 7,2000 Replication Getting Login failed for user 'sa' for Distribution agent RE: Getting Login failed for user 'sa' for Distribution agent

  • Presumably, this is the distribution agent applying a snapshot? At my subscribers I don't create the foreign keys. I'm happy that if all is well at the publisher then all will be well at the subscriber. Consequently, I'm not a snapshot expert. In the publication properties on the articles tab if you click on one of the edit buttons next to one of the tables a dialog box appears with a snapshot tab. If you click on that you will see a list of option under "Copy objects to destination". By default most are switched off but there is one for referential integrity.

    If you do want foreign keys at your subscriber you can create them with the "NOT FOR REPLICATION" option which means that the foreign keys are ignored when data is inserted etc. But this won't apply in your case as I'm guessing you are using Snapshot Replication.

    So to fix your distribution agent problem you could temporarily drop the foreign keys if that's not too big a job. Otherwise, it's a drop subscription and recreate the publication so that foreign keys don't get created at the subscriber if that's how they got there in the first place. But as I say, I'm not a snapshot expert so I might be misinforming you.

    Mike