How to Assign Identity Key values in Replication

This is a very basic question on replication.

I'm having a central Server with SQL Server 2005 Standard Edition and Other sites with Sql Express Server 2005.

Other sites will also be adding New records and data will be replicated to Central server and from there it will be distributed to all sites.

Question is that if Other sites are also adding Records how i can assing Identity values in those databases. There are few restricitons on this :-

1. I don't want to use GUID.

2. Numbers should be sequential that is after 1000, 1001, 1002 etc. should come.

i thought of adding Negative Values in the primary key on other sites and then when data is replicated on central server then replace it with sequential key but i'm not clear on how to accomplish this.

any help will be highly appreciable.

You can specify identity ranges. See books online topic "Replicating Identity Columns". You can also search books online for "replication identity" for a range of topics.|||

thanks for your reply.

i saw the topics which you have mentioned. As per my requirement i/o specify individual ranges i have to keep this Identity column in Sequence for all sites. so i will have to do this manually..

is it possible to point towards some code which does that as i'm sure this is a very common requirement and lot of people must have already written generic code to accomplish this.

How to add time dependent calculed mesure

Is there a way to add time dependent calculed mesure for ex (Delta% of a standard mesure which represent its evolution within the time dimension)

Regards

I guess you're looking for something like this (from Foodmart 2000):

with member [Measures].[Delta] as '[Measures].[Unit Sales]-([Measures].[Unit Sales],[Time].currentmember.lag(1))'

select {[Measures].[Unit Sales],[Measures].[Delta]} on 0,
{[Time].members} on 1
from sales

How to add Full Text Search to SQL2005

Hi,
We have a server with SQL2005 Standard Edition installed. We did not install
Full Text Search and want to add it to the existing default instance. The
attempt to install it says it is already installed when we know that it is
not. Is this a known bug?
Thanks
Chris
Seems you have to give the actual instance name, MSSQL for the default,
before the install will happen.
Chris
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%23TtGy7FIGHA.3000@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a server with SQL2005 Standard Edition installed. We did not
> install Full Text Search and want to add it to the existing default
> instance. The attempt to install it says it is already installed when we
> know that it is not. Is this a known bug?
> Thanks
> Chris
>

How to add Full Text Search to SQL2005

Hi,
We have a server with SQL2005 Standard Edition installed. We did not install
Full Text Search and want to add it to the existing default instance. The
attempt to install it says it is already installed when we know that it is
not. Is this a known bug?
Thanks
ChrisSeems you have to give the actual instance name, MSSQL for the default,
before the install will happen.
Chris
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%23TtGy7FIGHA.3000@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a server with SQL2005 Standard Edition installed. We did not
> install Full Text Search and want to add it to the existing default
> instance. The attempt to install it says it is already installed when we
> know that it is not. Is this a known bug?
> Thanks
> Chris
>

How to add Full Text Search to SQL2005

Hi,
We have a server with SQL2005 Standard Edition installed. We did not install
Full Text Search and want to add it to the existing default instance. The
attempt to install it says it is already installed when we know that it is
not. Is this a known bug?
Thanks
ChrisSeems you have to give the actual instance name, MSSQL for the default,
before the install will happen.
Chris
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%23TtGy7FIGHA.3000@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a server with SQL2005 Standard Edition installed. We did not
> install Full Text Search and want to add it to the existing default
> instance. The attempt to install it says it is already installed when we
> know that it is not. Is this a known bug?
> Thanks
> Chris
>

How to add a new database with a new name by copying an existing datafile?

I want to save a standard SQL Server 2005 Database as a datafile to disk
which I want to add to several other SQL Server at other servers with
another name of the database. What's the procedure to do this? I've been
experimenting with copying the datafiles but SQL Server reponses that the
file names or database name don't match.
regards,
OscarBACKUP the database then use RESTORE WITH MOVE.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Oscar" <oku@.xs4all.nl> wrote in message
news:Oz0vMUBvHHA.4784@.TK2MSFTNGP06.phx.gbl...
>I want to save a standard SQL Server 2005 Database as a datafile to disk
>which I want to add to several other SQL Server at other servers with
>another name of the database. What's the procedure to do this? I've been
>experimenting with copying the datafiles but SQL Server reponses that the
>file names or database name don't match.
> regards,
> Oscar
>|||You can also use detach / attach.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> pe v diskus
nm
pspvku news:O6Kj8aBvHHA.3376@.TK2MSFTNGP04.phx.gbl...
> BACKUP the database then use RESTORE WITH MOVE.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:Oz0vMUBvHHA.4784@.TK2MSFTNGP06.phx.gbl...
>|||> You can also use detach / attach.
Only if it is acceptable for the primary to be offline briefly.|||Hi Aaron,
I want to restore the database within SQL Server Management Studio. I can't
find the 'WITH MOVE' option. It only shows the following settings :
RESTORE WITH RECOVERY
RESTORE WITH NORECOVERY
RESTORE WITH STANDBY
I've tried first to have this done at the same server before I move to
another server :
-made a BACKUP of a resource database
-added a new empty database called TESTCOMPANY
-right click at the database TESTCOMPANY and choose 'RESTORE'
-choose to add from file and select the BACKUP file in the first step
-choose 'OVERWRITE THE EXISTING DATABASE'
Now, in case I set one the 3 listed recovery state, it shows error messages
right in the beginning.
What should I do?
Oscar
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schreef in
bericht news:O6Kj8aBvHHA.3376@.TK2MSFTNGP04.phx.gbl...
> BACKUP the database then use RESTORE WITH MOVE.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:Oz0vMUBvHHA.4784@.TK2MSFTNGP06.phx.gbl...
>|||> I want to restore the database within SQL Server Management Studio.
Sorry, this is going to be like trying to swap out RAM through a USB port --
USB is a great technology but it does not cover all the bases, and the same
is true for SSMS. If the paths on the new machine and old machine are not
identical (and they can't be identical on the same machine), you will likely
have to get your hands dirty and learn/use the RESTORE DATABASE command.
A

How to add a new database with a new name by copying an existing datafile?

I want to save a standard SQL Server 2005 Database as a datafile to disk
which I want to add to several other SQL Server at other servers with
another name of the database. What's the procedure to do this? I've been
experimenting with copying the datafiles but SQL Server reponses that the
file names or database name don't match.
regards,
Oscar
BACKUP the database then use RESTORE WITH MOVE.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Oscar" <oku@.xs4all.nl> wrote in message
news:Oz0vMUBvHHA.4784@.TK2MSFTNGP06.phx.gbl...
>I want to save a standard SQL Server 2005 Database as a datafile to disk
>which I want to add to several other SQL Server at other servers with
>another name of the database. What's the procedure to do this? I've been
>experimenting with copying the datafiles but SQL Server reponses that the
>file names or database name don't match.
> regards,
> Oscar
>
|||You can also use detach / attach.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> pe v diskusnm
pspvku news:O6Kj8aBvHHA.3376@.TK2MSFTNGP04.phx.gbl...
> BACKUP the database then use RESTORE WITH MOVE.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:Oz0vMUBvHHA.4784@.TK2MSFTNGP06.phx.gbl...
>
|||> You can also use detach / attach.
Only if it is acceptable for the primary to be offline briefly.
|||Hi Aaron,
I want to restore the database within SQL Server Management Studio. I can't
find the 'WITH MOVE' option. It only shows the following settings :
RESTORE WITH RECOVERY
RESTORE WITH NORECOVERY
RESTORE WITH STANDBY
I've tried first to have this done at the same server before I move to
another server :
-made a BACKUP of a resource database
-added a new empty database called TESTCOMPANY
-right click at the database TESTCOMPANY and choose 'RESTORE'
-choose to add from file and select the BACKUP file in the first step
-choose 'OVERWRITE THE EXISTING DATABASE'
Now, in case I set one the 3 listed recovery state, it shows error messages
right in the beginning.
What should I do?
Oscar
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schreef in
bericht news:O6Kj8aBvHHA.3376@.TK2MSFTNGP04.phx.gbl...
> BACKUP the database then use RESTORE WITH MOVE.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:Oz0vMUBvHHA.4784@.TK2MSFTNGP06.phx.gbl...
>
|||> I want to restore the database within SQL Server Management Studio.
Sorry, this is going to be like trying to swap out RAM through a USB port --
USB is a great technology but it does not cover all the bases, and the same
is true for SSMS. If the paths on the new machine and old machine are not
identical (and they can't be identical on the same machine), you will likely
have to get your hands dirty and learn/use the RESTORE DATABASE command.
A

How to add a new database with a new name by copying an existing datafile?

I want to save a standard SQL Server 2005 Database as a datafile to disk
which I want to add to several other SQL Server at other servers with
another name of the database. What's the procedure to do this? I've been
experimenting with copying the datafiles but SQL Server reponses that the
file names or database name don't match.
regards,
OscarBACKUP the database then use RESTORE WITH MOVE.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Oscar" <oku@.xs4all.nl> wrote in message
news:Oz0vMUBvHHA.4784@.TK2MSFTNGP06.phx.gbl...
>I want to save a standard SQL Server 2005 Database as a datafile to disk
>which I want to add to several other SQL Server at other servers with
>another name of the database. What's the procedure to do this? I've been
>experimenting with copying the datafiles but SQL Server reponses that the
>file names or database name don't match.
> regards,
> Oscar
>|||You can also use detach / attach.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> pí¹e v diskusním
pøíspìvku news:O6Kj8aBvHHA.3376@.TK2MSFTNGP04.phx.gbl...
> BACKUP the database then use RESTORE WITH MOVE.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:Oz0vMUBvHHA.4784@.TK2MSFTNGP06.phx.gbl...
>>I want to save a standard SQL Server 2005 Database as a datafile to disk
>>which I want to add to several other SQL Server at other servers with
>>another name of the database. What's the procedure to do this? I've been
>>experimenting with copying the datafiles but SQL Server reponses that the
>>file names or database name don't match.
>> regards,
>> Oscar
>|||> You can also use detach / attach.
Only if it is acceptable for the primary to be offline briefly.|||Hi Aaron,
I want to restore the database within SQL Server Management Studio. I can't
find the 'WITH MOVE' option. It only shows the following settings :
RESTORE WITH RECOVERY
RESTORE WITH NORECOVERY
RESTORE WITH STANDBY
I've tried first to have this done at the same server before I move to
another server :
-made a BACKUP of a resource database
-added a new empty database called TESTCOMPANY
-right click at the database TESTCOMPANY and choose 'RESTORE'
-choose to add from file and select the BACKUP file in the first step
-choose 'OVERWRITE THE EXISTING DATABASE'
Now, in case I set one the 3 listed recovery state, it shows error messages
right in the beginning.
What should I do?
Oscar
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schreef in
bericht news:O6Kj8aBvHHA.3376@.TK2MSFTNGP04.phx.gbl...
> BACKUP the database then use RESTORE WITH MOVE.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Oscar" <oku@.xs4all.nl> wrote in message
> news:Oz0vMUBvHHA.4784@.TK2MSFTNGP06.phx.gbl...
>>I want to save a standard SQL Server 2005 Database as a datafile to disk
>>which I want to add to several other SQL Server at other servers with
>>another name of the database. What's the procedure to do this? I've been
>>experimenting with copying the datafiles but SQL Server reponses that the
>>file names or database name don't match.
>> regards,
>> Oscar
>|||> I want to restore the database within SQL Server Management Studio.
Sorry, this is going to be like trying to swap out RAM through a USB port --
USB is a great technology but it does not cover all the bases, and the same
is true for SSMS. If the paths on the new machine and old machine are not
identical (and they can't be identical on the same machine), you will likely
have to get your hands dirty and learn/use the RESTORE DATABASE command.
A