Reseting Identity Seed

O.k. here's my deal. I have a table who's items get assigned an ID based on
the identity seed. The identity seed is incremented by 1, and the seed
length is 4 . When my program is run, some of these items get moved into
another table for future use and the others are deleted. The items that get
moved are used again the next time the program is run(which is only once a
day). The one items get deleted is based on whether or not they match off t
o
another item read in by the program. Ex. If the data in my table is 1 2 3
4
5 and the program reads in 1 2 4 5, then the 3 is moved to another table and
the 1 2 4 5 get deleted b/c they were matched off. The identity seed is the
n
reset using DBCC CHECKIDENT which works fine, but I need to be able to have
the value it is reset to tied in to something like the date so that it is
unique each time it is reset. This way the next time my program reads in a
3, I need to make sure it does get the same identity seed as the previous 3
that was saved from the first time. I hope you can understand all that and
give me some insight as to how/if this can be done. Thanks.Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"CD" wrote:

> O.k. here's my deal. I have a table who's items get assigned an ID based
on
> the identity seed. The identity seed is incremented by 1, and the seed
> length is 4 . When my program is run, some of these items get moved into
> another table for future use and the others are deleted. The items that g
et
> moved are used again the next time the program is run(which is only once a
> day). The one items get deleted is based on whether or not they match off
to
> another item read in by the program. Ex. If the data in my table is 1 2
3 4
> 5 and the program reads in 1 2 4 5, then the 3 is moved to another table a
nd
> the 1 2 4 5 get deleted b/c they were matched off. The identity seed is t
hen
> reset using DBCC CHECKIDENT which works fine, but I need to be able to hav
e
> the value it is reset to tied in to something like the date so that it is
> unique each time it is reset. This way the next time my program reads in
a
> 3, I need to make sure it does get the same identity seed as the previous
3
> that was saved from the first time. I hope you can understand all that an
d
> give me some insight as to how/if this can be done. Thanks.|||Here's how I read your *requirement* as opposed to what you are
actually asking for. It seems like you have a table and you need to
determine which is the 1st, 2nd, 3rd or Nth row inserted to that table
on any particular day. So add a DATETIME column to the table:
CREATE TABLE YourTable (creation_date DATETIME NOT NULL UNIQUE DEFAULT
CURRENT_TIMESTAMP, ...)
The derive the sequence number like this:
SELECT T1.creation_date, COUNT(*) AS seq
FROM YourTable AS T1
JOIN YourTable AS T2
ON T2.creation_date >= '20050225'
AND T1.creation_date < '20050226'
AND T1.creation_date >= T2.creation_date
GROUP BY T1.creation_date
ORDER BY T1.creation_date
Resetting the seed and relying on IDENTITY to do the same thing is a
really bad idea. IDENTITY sequences can have gaps.
If I've completely misunderstood then the standard advice applies:
Please post DDL, sample data, required results to maximize your chance
of getting a good answer. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--sql

Reset permissions

Hello,
I am looking for a script that will work dynamically depending on which
database I am in. I want to run the GRANT or DENY based upon a certain group
and I would prefer not to select each table the EM. Is there a script out
there that can read the tables and generate a permission script off of it?
Thanks in advance.
Jakesp_msForeachtable 'Grant select on ? to Public'
Change What u want on the Statement but ?
"jake" <rondican@.hotmail.com> wrote in message
news:OT%23e5$%239EHA.3260@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I am looking for a script that will work dynamically depending on
which
> database I am in. I want to run the GRANT or DENY based upon a certain
group
> and I would prefer not to select each table the EM. Is there a script out
> there that can read the tables and generate a permission script off of it?
> Thanks in advance.
> Jake
>|||Hi,
I do not recommend 'sp_msForeachtable' because it is undocumented. I
strongly urge all of you to think long and hard before embedding calls to
undocumented APIs in production code. Some of these undocumented APIs are
gone in Yukon.
I recommend building some simple code generators like the one shown below.
FYI, in Yukon you can GRANT/DENY/REVOKE permissions at different scopes. So
you can do this:
-- GRANT EXECUTE on all current and future procs and scalar funcs
-- in schema
--
GRANT EXECUTE ON SCHEMA :: someschema TO someuser
-- GRANT EXECUTE on all current and future procs and scalar funcs
-- in all schemas in the current database
--
GRANT EXECUTE TO someuser
Regards,
Clifford Dibble
Program Manager
SQL Server Engine
create function make_sql(@.sqltemplate nvarchar(2000)
, @.gdr nvarchar(6)
, @.perm nvarchar(128)
, @.object nvarchar(128)
, @.user nvarchar(128)
)
returns nvarchar(4000) as
begin
declare @.sql nvarchar(4000)
select @.sql = replace(replace(replace(replace(@.sqltemp
late, '<gdr>',
@.gdr), '<perm>', @.perm), '<object>', @.object), '<user>', @.user)
return @.sql
end
go
select dbo.make_sql('<gdr> <perm> ON <object> TO <user>', 'GRANT', 'SELECT',
o.name, 'PUBLIC')
from sysobjects as o
where o.type = 'U'
go
select dbo.make_sql('<gdr> <perm> ON <object> TO <user>', 'REVOKE',
'INSERT', o.name, 'PUBLIC')
from sysobjects as o
where o.type = 'U'
go
"Melih SARICA" wrote:

> sp_msForeachtable 'Grant select on ? to Public'
> Change What u want on the Statement but ?
> "jake" <rondican@.hotmail.com> wrote in message
> news:OT%23e5$%239EHA.3260@.TK2MSFTNGP14.phx.gbl...
> which
> group
>sql

Require Solution for this SQL problem

Hi,

I have two tables TableA and TableB. I have a stored procedure which
runs every morning and based on some logic dumps rows from TableA to
TableB. In Table B there are two additional colums ID and RunID. ID is
a normal sequence applied for all rows. But the RunId should be
constant for a run of stored proc.

So for e.g. say structure of Table A and Table B

Table A Table B

col1 ID RunID col1

Now when I run stored proc I want rows copied as below

TableA TableB

col1 ID RunID col1
row1 1 1 row1
row2 2 1 row2

The next day when stored prc runs I want data as

TableA TableB

col1 ID RunID col1
row1 1 1 row1
row2 2 1 row2
row1 3 2 row1
row2 4 2 row2

So for every run of stored proc each day I want the Run ID incremented
only by one and ID is normal sequence which increments for allrows
inserted.

Please help.

Nick"Nick" <nachiket.shirwalkar@.gmail.comwrote in message
news:1189853588.129281.216870@.n39g2000hsh.googlegr oups.com...

Quote:

Originally Posted by

Hi,
>
I have two tables TableA and TableB. I have a stored procedure which
runs every morning and based on some logic dumps rows from TableA to
TableB. In Table B there are two additional colums ID and RunID. ID is
a normal sequence applied for all rows. But the RunId should be
constant for a run of stored proc.
>
So for e.g. say structure of Table A and Table B
>
Table A Table B
>
col1 ID RunID col1
>
Now when I run stored proc I want rows copied as below
>
TableA TableB
>
col1 ID RunID col1
row1 1 1 row1
row2 2 1 row2
>
The next day when stored prc runs I want data as
>
TableA TableB
>
col1 ID RunID col1
row1 1 1 row1
row2 2 1 row2
row1 3 2 row1
row2 4 2 row2
>
So for every run of stored proc each day I want the Run ID incremented
only by one and ID is normal sequence which increments for allrows
inserted.
>
Please help.
>
Nick
>

Lookup the ROW_NUMBER() function.

--
David Portas

Reqplication queues up without any errors

I have two publications (with only one article in each) based on the same
source table. Both of these publications have one pull subscription each.
Using sp_browsereplcmds and querying MSrepl_commands, I noticed that
replication queues up for these two articles. How can I check whether this
data has been replicated -- (I do not believe it has).
Also, I am not seeing any errors in replication but the queue keeps growing.
Why are no errors being thrown out?
select * from distribution.dbo.MSdistribution_status and check the number of
undelivered commands.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
news:9B45D62C-616F-4D65-A02A-54FA74E24751@.microsoft.com...
>I have two publications (with only one article in each) based on the same
> source table. Both of these publications have one pull subscription each.
> Using sp_browsereplcmds and querying MSrepl_commands, I noticed that
> replication queues up for these two articles. How can I check whether
> this
> data has been replicated -- (I do not believe it has).
> Also, I am not seeing any errors in replication but the queue keeps
> growing.
> Why are no errors being thrown out?
>
|||The number of rows in MSrepl_commands and MSrepl_transactions will grow
without limitation. They are not removed when they have been applied to
each subscriber. Instead, a clean up process runs at a periodic interval.
This cleanup process removes anything older than the retention interval that
you specified. So, if you have a retention interval of 72 hours, the
cleanup process will remove anything from MSrepl_commands and
MSrepl_transactions that have been successfully applied to all subscribers
needing that transaction which are more than 72 hours old. Just because
data exists in these two tables doesn't mean that it has not been sent to
the subscriber.
MSdistribution_status will tell you how many rows have not been sent. You
can also spot check this using sp_browsereplcmds. It will give you the
exact data for the change sent. You can then simply run a select statement
against the subscriber to verify that the change made it.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
news:9B45D62C-616F-4D65-A02A-54FA74E24751@.microsoft.com...
>I have two publications (with only one article in each) based on the same
> source table. Both of these publications have one pull subscription each.
> Using sp_browsereplcmds and querying MSrepl_commands, I noticed that
> replication queues up for these two articles. How can I check whether
> this
> data has been replicated -- (I do not believe it has).
> Also, I am not seeing any errors in replication but the queue keeps
> growing.
> Why are no errors being thrown out?
>
|||Thank you Mike and Hilary. Your answers were very helpful.
"Michael Hotek" wrote:

> The number of rows in MSrepl_commands and MSrepl_transactions will grow
> without limitation. They are not removed when they have been applied to
> each subscriber. Instead, a clean up process runs at a periodic interval.
> This cleanup process removes anything older than the retention interval that
> you specified. So, if you have a retention interval of 72 hours, the
> cleanup process will remove anything from MSrepl_commands and
> MSrepl_transactions that have been successfully applied to all subscribers
> needing that transaction which are more than 72 hours old. Just because
> data exists in these two tables doesn't mean that it has not been sent to
> the subscriber.
> MSdistribution_status will tell you how many rows have not been sent. You
> can also spot check this using sp_browsereplcmds. It will give you the
> exact data for the change sent. You can then simply run a select statement
> against the subscriber to verify that the change made it.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
> news:9B45D62C-616F-4D65-A02A-54FA74E24751@.microsoft.com...
>
>
|||Actually, one more question. How do I check which replication commands /
transactions are not yet delivered? MSdistribution_Status ?
"Michael Hotek" wrote:

> The number of rows in MSrepl_commands and MSrepl_transactions will grow
> without limitation. They are not removed when they have been applied to
> each subscriber. Instead, a clean up process runs at a periodic interval.
> This cleanup process removes anything older than the retention interval that
> you specified. So, if you have a retention interval of 72 hours, the
> cleanup process will remove anything from MSrepl_commands and
> MSrepl_transactions that have been successfully applied to all subscribers
> needing that transaction which are more than 72 hours old. Just because
> data exists in these two tables doesn't mean that it has not been sent to
> the subscriber.
> MSdistribution_status will tell you how many rows have not been sent. You
> can also spot check this using sp_browsereplcmds. It will give you the
> exact data for the change sent. You can then simply run a select statement
> against the subscriber to verify that the change made it.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
> news:9B45D62C-616F-4D65-A02A-54FA74E24751@.microsoft.com...
>
>
|||Check the subscriber and look at the value of transaction_timestamp for
your publication
select transaction_timestamp From MSreplication_subscriptions
Compare this with the xact_Seqno or xact_id in msrepl_transactions in your
publisher's distribution database. Get the xact_seqno - note that this
number will not be an exact match but rather in the range.
The number of rows remaining for that publication are the number of commands
which have to be applied there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
news:E478A229-D8A4-4511-807B-B977E8DA5E95@.microsoft.com...[vbcol=seagreen]
> Actually, one more question. How do I check which replication commands /
> transactions are not yet delivered? MSdistribution_Status ?
> "Michael Hotek" wrote: