Replication Woes continued

Well since I moved from merge to snapshot replication, i thought the problems were solved..But oh well

Here is the issue with snapshot replication...

Snapshot replication takes a snapshot of the database and replaces the existing database with the snapshot...as weird as it may seem, it does not carry the custom permissions set on the stored procedures and views to the new snapshot...

Hence, none of my stored procedures would work.

First instinct is to add the TSQL to the agent running the replication agent running the entire snapshot copy procedure...WRONG...Since the agent runs on the distribution machine, IAIDEV in my case...it runs the TSQL on the local machine and not on the remote machine...

Huh !! interesting...

So, I asked the google God and God said ..let there be command line...

hehehe...jokes apart...Now I am using the osql to run my script from the command line.

Its this handy little utility hidden inside the folder after folder...but once you get it working, it runs like a charm...

Here is what I am using finally -

I created a bat file with the following syntax to run the command on my machine
then used the windows scheduler to run the command 20 mins after the snapshot replication starts...this 20 odd mins, the remote machine would not have the correct permissions...but ah well..no one should be trying to get to my remote machine at 12 AM in the morning anyways...right ?
Besides, the wrong permissions are the lower permissions then what I give it eventually and so, there is no security hole here as well...
Anyways...back to the osql and bat file -

"[custom path]\Microsoft SQL Server\80\Tools\Binn\osql" -U username -P password -d master -i setPermissionsAfterReplications.sql

Replication woes

THe mistry of disappearing data rows..

On a monday morning, the last thing you would want is someone yelling abt some lost data...

Ah..may be it got deleted...may be it never got inserted...But the process has not been modified since 2004...so, why now !! what changed !!

Well as it turns out, it was the case of replication gone haywire...I had the database set for merge replication with another machine with the Live machine having 100% priority in case of a conflict so that the backup machine would only get the changes and not the other way round in case of any changes...

And guess what happened...the SQL Server conviniently decided that if the row is in a conflict its a good idea to delete it from my live machine...So, the rows of data started disappearing...

Not a good thing !!!!


Anyhu...now the database is set for snapshot replication..more time consuming but definately unidirectional...