I work for a company that has 32 locations, each location having its own SQL server. Replication is set up from each location to a central location, and then from the central location to a main office location. The main office location also has data that is entered, which replicates from the main office to the central location, and then from the central location to the individual locations. We've recently upgraded all of our SQL servers from SQL Server 2000 to SQL Server 2005. All of our keys for our databases use a composite key that consists of an int ID and an int Location.
We're creating our first application using SQL Server 2005 throughout the company. This application can have data entered both at the individual locations as well as the main office location. We normally would set the int ID field as an identity and auto increment it whenever the user is at the individual location. This changes insert statements since you can't explicitly set the value of the ID field. We would then not auto increment the ID field at the central and main office locations since the data would be replicating to the individual locations. This means that whenever data would be inserted at the main office location, the insert statements would require that the ID field be explicitly defined.
We're now trying to use the "Not for Replication" attribute for the ID field, which would theoretically allow us to use the same insert statements across the entire company. My question is, if "Not for Replication" is set to true and the ID field is set to auto increment, am I allowed to explicitly define the ID field when inserting? I'm thinking the answer is no. Also, if anyone has experience in a similar situation, what solutions did you come up with?
Thanks in advance.