Changing the identity range values with database name keeping the same.

Hello,
What are the steps for changing the identity range values?
We have backup the database that contained initial publication with
identity range set to 1000. Now when I delete the existing database with
replication, and then create a new DB with same name and restore the
database it maintains the publication and does not allow me to change
the identity range values. Also constraints are not modified. If I
create a new publication two identity range constraints are generated.
However when I create a new DB with a new name, restore the database and
then create another publication it correctly modifies the identity range
constraints with new value say 20000.
I want database with same name to be created. Please help.
Neha Magia
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Replication does not remove the constraints it uses to maintain the identity
ranges on the subscriber tables. You will have to remove these manually.
It may be possible that these constraints are also in place on the
publisher. You will have to drop the publication, remove the constraints is
they still exists, and then rebuild it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Neha Magia" <nehamagia9@.yahoo.com> wrote in message
news:ussPRbZ7EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Hello,
> What are the steps for changing the identity range values?
> We have backup the database that contained initial publication with
> identity range set to 1000. Now when I delete the existing database with
> replication, and then create a new DB with same name and restore the
> database it maintains the publication and does not allow me to change
> the identity range values. Also constraints are not modified. If I
> create a new publication two identity range constraints are generated.
> However when I create a new DB with a new name, restore the database and
> then create another publication it correctly modifies the identity range
> constraints with new value say 20000.
> I want database with same name to be created. Please help.
> Neha Magia
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Changing date and keeping time

Hi! I'm using MS SQL server 2000. How can I change date in date field keepin
g
time the same (datetime field)? I'm using SQL Enterprise Manager for that.
Please help with syntax. More thanks, Alar. PS! Can You suggest some book or
other source I can find hints about MS SQL syntax?
> Hi! I'm using MS SQL server 2000. How can I change date in date field
> keeping
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
That wont work, if you edit the date via EM you always will (implicit)
issue a command like:
UPDATE Sometable SET Somecolumn '01/01/2004 00:00:00' Where ...
even if you type in '01/01/2004'. The function DATEADD or if you prior cut
out the time and put it on the changed column (time 00:00:00) would work
fine.
PS! Can You suggest some book or
> other source I can find hints about MS SQL syntax?
Did you try BOL ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||> Hi! I'm using MS SQL server 2000. How can I change date in date field
keeping
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax.
See function CONVERT in BOL.
Example:
update table1
set c1 = '2005-05-09' + right(convert(varchar(25), c1, 126), 13)
where c1 >= '20050501' and c1 < '20050508'
AMB

> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?
MS SQL Server 2000's Books Online
AMB
"Alar Pandis" wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?|||The DateAdd() function can add arbitrary number of days, ws, months, or
whatever, to a given date. If you choose any time increment greater than a
day, the time portion of the value will remain the same...
Otherwise, you need to update the column to a new date with the same time as
the the datetime that's in there...
Update TablleName Set
DTColumn = 'NewDate as CCYYMMDD ' +
convert(VarCHar(12), DTColumn, 14)
"Alar Pandis" wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?|||I don't recommend using Enterprise Manager for this, and
I suggest you run the update query in Query Analyzer
instead.
If @.newDate is the new date, T is your table, myDate is the
column you want to change, and rowKey = @.rowKey identifies
the row you want to change, this will work (not tested - watch
for typos)
update T set
myDate = dateadd(day, datediff(day, myDate, @.newDate), myDate)
where rowKey = @.rowKey
This will add a whole number of days to myDate, the number
it adds being exactly the number of whole days from myDate
to @.newDate.
Steve Kass
Drew University
Alar Pandis wrote:

> Hi! I'm using MS SQL server 2000. How can I change date in date field keep
ing
> time the same (datetime field)? I'm using SQL Enterprise Manager for that.
> Please help with syntax. More thanks, Alar. PS! Can You suggest some book
or
> other source I can find hints about MS SQL syntax?