reseeding temporary tables or table data types with identity column

Hi,

I have a indexing problem. I have a sequence that needs to has a index number. I want to use a table data type and have a working sample BUT I cannot reseed the table when needed. How do I do this.

This works only for the first ExitCoilID then I need to RESEED.

Here is my code:

DECLARE

@.EntryCoilCnt AS INT,

@.ExitCoilID AS INT,

@.SubtractedFromEntyCoilCnt AS INT

DECLARE

@.ExitCoilID_NotProcessed TABLE

(ExitCoilID int)

INSERT INTO @.ExitCoilID_NotProcessed

SELECT DISTINCT ExitCoilID

FROM

dbo.TrendEXIT

where

ExitCoilID is not null and

ExitCnt is null

order by

ExitCoilID

DECLARE

@.ExitCoilID_Cnt_Index TABLE

(ExitCoilID int, ExitCnt int IDENTITY (1,1))

IF @.@.ROWCOUNT > 0

BEGIN

DECLARE ExitCoilID_cursor CURSOR FOR

SELECT ExitCoilID FROM @.ExitCoilID_NotProcessed

ORDER BY ExitCoilID

OPEN ExitCoilID_cursor

FETCH NEXT FROM ExitCoilID_cursor

INTO @.ExitCoilID

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT INTO @.ExitCoilID_Cnt_Index

SELECT ExitCoilID

FROM dbo.TrendEXIT

WHERE

ExitCoilID = @.ExitCoilID

ORDER BY

EntryCoilID, Cnt

select * from @.ExitCoilID_Cnt_Index

--truncate @.ExitCoilID_Cnt_Index

--DBCC CHECKIDENT ('@.ExitCoilID_Cnt_Index', RESEED, 1)

FETCH NEXT FROM ExitCoilID_cursor

INTO @.ExitCoilID

END

CLOSE ExitCoilID_cursor

DEALLOCATE ExitCoilID_cursor

select * from @.ExitCoilID_Cnt_Index

END --IF @.@.ROWCOUNT <> 0

Is this SQL 2005? Try using ROW_NUMBER() instead of identity.|||

Hi,

Yes I'm using SQL 2005 and I'm new to it. How is ROW_NUMBER() Used? I don't have my onlinbooks with me.

Thanks

|||you can search for it on www.msdn.com.|||

Hi,

This is good when you have 2005. Is there a way to reseed a memory table under 2K ?

Thanks.

|||

Hi,

Look up DBCC CHECKIDENT in the help

DBCC CHECKIDENT ('TableName', RESEED, ReseedValue);

Don

|||

No, there is not method to reseed the local variable table identity value. If you need this functionality, use a local temporary table instead. They are not as convienient but they give you everything you need, and have very favorable scoping that you should be able to get what you need from them.

set nocount on
create table #table (tableId int identity)
insert into #table default values
insert into #table default values
insert into #table default values
insert into #table default values

select *
from #table

truncate table #table

insert into #table default values
insert into #table default values
insert into #table default values
insert into #table default values

select *
from #table

drop table #table

tableId
--
1
2
3
4

tableId
--
1
2
3
4

Otherwise, if variable based tables give you some incredible value, stop using identities and manage number creation manually, like using a table of numbers, or a loop, or just hard coding values if you aren't doing too many (I expect you are, but this is the easiest to code:

set nocount on
declare @.table table (tableId int )
insert into @.table select 1
insert into @.table select 2
insert into @.table select 3
insert into @.table select 4

select *
from @.table

delete from @.table

insert into @.table select 1
insert into @.table select 2
insert into @.table select 3
insert into @.table select 4

select *
from @.table

reseeding temporary tables or table data types with identity column

Hi,

I have a indexing problem. I have a sequence that needs to has a index number. I want to use a table data type and have a working sample BUT I cannot reseed the table when needed. How do I do this.

This works only for the first ExitCoilID then I need to RESEED.

Here is my code:

DECLARE

@.EntryCoilCnt AS INT,

@.ExitCoilID AS INT,

@.SubtractedFromEntyCoilCnt AS INT

DECLARE

@.ExitCoilID_NotProcessed TABLE

(ExitCoilID int)

INSERT INTO @.ExitCoilID_NotProcessed

SELECT DISTINCT ExitCoilID

FROM

dbo.TrendEXIT

where

ExitCoilID is not null and

ExitCnt is null

order by

ExitCoilID

DECLARE

@.ExitCoilID_Cnt_Index TABLE

(ExitCoilID int, ExitCnt int IDENTITY (1,1))

IF @.@.ROWCOUNT > 0

BEGIN

DECLARE ExitCoilID_cursor CURSOR FOR

SELECT ExitCoilID FROM @.ExitCoilID_NotProcessed

ORDER BY ExitCoilID

OPEN ExitCoilID_cursor

FETCH NEXT FROM ExitCoilID_cursor

INTO @.ExitCoilID

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT INTO @.ExitCoilID_Cnt_Index

SELECT ExitCoilID

FROM dbo.TrendEXIT

WHERE

ExitCoilID = @.ExitCoilID

ORDER BY

EntryCoilID, Cnt

select * from @.ExitCoilID_Cnt_Index

--truncate @.ExitCoilID_Cnt_Index

--DBCC CHECKIDENT ('@.ExitCoilID_Cnt_Index', RESEED, 1)

FETCH NEXT FROM ExitCoilID_cursor

INTO @.ExitCoilID

END

CLOSE ExitCoilID_cursor

DEALLOCATE ExitCoilID_cursor

select * from @.ExitCoilID_Cnt_Index

END --IF @.@.ROWCOUNT <> 0

Is this SQL 2005? Try using ROW_NUMBER() instead of identity.|||

Hi,

Yes I'm using SQL 2005 and I'm new to it. How is ROW_NUMBER() Used? I don't have my onlinbooks with me.

Thanks

|||you can search for it on www.msdn.com.|||

Hi,

This is good when you have 2005. Is there a way to reseed a memory table under 2K ?

Thanks.

|||

Hi,

Look up DBCC CHECKIDENT in the help

DBCC CHECKIDENT ('TableName', RESEED, ReseedValue);

Don

|||

No, there is not method to reseed the local variable table identity value. If you need this functionality, use a local temporary table instead. They are not as convienient but they give you everything you need, and have very favorable scoping that you should be able to get what you need from them.

set nocount on
create table #table (tableId int identity)
insert into #table default values
insert into #table default values
insert into #table default values
insert into #table default values

select *
from #table

truncate table #table

insert into #table default values
insert into #table default values
insert into #table default values
insert into #table default values

select *
from #table

drop table #table

tableId
--
1
2
3
4

tableId
--
1
2
3
4

Otherwise, if variable based tables give you some incredible value, stop using identities and manage number creation manually, like using a table of numbers, or a loop, or just hard coding values if you aren't doing too many (I expect you are, but this is the easiest to code:

set nocount on
declare @.table table (tableId int )
insert into @.table select 1
insert into @.table select 2
insert into @.table select 3
insert into @.table select 4

select *
from @.table

delete from @.table

insert into @.table select 1
insert into @.table select 2
insert into @.table select 3
insert into @.table select 4

select *
from @.table

sql

Rerun only certain containers ?

Hi There

I have a package that has many containers that execute in sequence.

If any container fails the package does not fail as the subsequent containers must run.

However if containers fail i do not want to rerun the entire package. Checkpoint restartibility only allows you to start from where your package failed, however my package will not fail and i do not want to start from where it failed but only rerun the containers that failed.

Is this possible ? Can one maybe run only certain containers in a package through dtsexec or another command line tool?

Thanx

Hi,

As for as I know, there is no direct method to achieve this. But, as always, there are some indirect methods. What you can do is, whenever a container completes successfully, in its 'onPostExecute' event, write the container name to a text file. (Note even for a failed job this event will be fired. So make sure you are writing the name only for successful containers'). In the 'onPostExecute' event of your package, if the whole job is successful, clear the contents of the text file.

Whenever you are executing the package, read the contents of the data to a variable. In your package, before each container, put a script task, which should check whether the container name is there in the variable. If it is there, then don't execute it. Otherwise run that package.

|||

Interesting solution, Thiru. I have done something similar in the past, but without using script tasks before each task.

You can create a variable which hold a bitmask as an integer. Each container when it succeeds should add a power of 2 to the variable.

Use a script task at the end of the package to write out the variable to, say, an environment variable. (If you use an environment variable you can easily set the value of the variable again using a configuration.)

Each task then needs a property expression on its Disable property, using a bitwise AND (&) to check if its power of 2 has been added to the variable, for example: (@.MyVariable & 64) == 64

In this way, you only need to manage one variable, and no need to use scripts.

Donald

|||

Wow. That's simple and easy. That's why you are guru and I am a learner.

Thx for your solution.

|||

Hi Donald

Very interesting, i really like this solution.

For now i simply disable all successful contrainers in my config file before i re-run it , but this solution is much more dynamic, i will implement it in the future.

Thanx a million

|||

Donald Farmer wrote:

Interesting solution, Thiru. I have done something similar in the past, but without using script tasks before each task.

You can create a variable which hold a bitmask as an integer. Each container when it succeeds should add a power of 2 to the variable.

Use a script task at the end of the package to write out the variable to, say, an environment variable. (If you use an environment variable you can easily set the value of the variable again using a configuration.)

Each task then needs a property expression on its Disable property, using a bitwise AND (&) to check if its power of 2 has been added to the variable, for example: (@.MyVariable & 64) == 64

In this way, you only need to manage one variable, and no need to use scripts.

Donald

Donald,

I don't know if you're still watching this thread but if you are, perhaps you could explain why your solution here will work in spite of the problem I have reported here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1875701&SiteID=1

[Note that at the time of writing there has been no reply from Microsoft to this thread. By the time you come to read it there may have been]

-Jamie

|||It appears that the configuration is loaded first, then the Disable property is evaluated, then the package is run. In your test package, if you set the variable to 1 at design time, the expression does evaluate correctly, and the task doesn't run. If you do the same thing via a configuration on your test package, you get the same result. I believe the expression is being evaluated, but only once.

Rerun only certain containers ?

Hi There

I have a package that has many containers that execute in sequence.

If any container fails the package does not fail as the subsequent containers must run.

However if containers fail i do not want to rerun the entire package. Checkpoint restartibility only allows you to start from where your package failed, however my package will not fail and i do not want to start from where it failed but only rerun the containers that failed.

Is this possible ? Can one maybe run only certain containers in a package through dtsexec or another command line tool?

Thanx

Hi,

As for as I know, there is no direct method to achieve this. But, as always, there are some indirect methods. What you can do is, whenever a container completes successfully, in its 'onPostExecute' event, write the container name to a text file. (Note even for a failed job this event will be fired. So make sure you are writing the name only for successful containers'). In the 'onPostExecute' event of your package, if the whole job is successful, clear the contents of the text file.

Whenever you are executing the package, read the contents of the data to a variable. In your package, before each container, put a script task, which should check whether the container name is there in the variable. If it is there, then don't execute it. Otherwise run that package.

|||

Interesting solution, Thiru. I have done something similar in the past, but without using script tasks before each task.

You can create a variable which hold a bitmask as an integer. Each container when it succeeds should add a power of 2 to the variable.

Use a script task at the end of the package to write out the variable to, say, an environment variable. (If you use an environment variable you can easily set the value of the variable again using a configuration.)

Each task then needs a property expression on its Disable property, using a bitwise AND (&) to check if its power of 2 has been added to the variable, for example: (@.MyVariable & 64) == 64

In this way, you only need to manage one variable, and no need to use scripts.

Donald

|||

Wow. That's simple and easy. That's why you are guru and I am a learner.

Thx for your solution.

|||

Hi Donald

Very interesting, i really like this solution.

For now i simply disable all successful contrainers in my config file before i re-run it , but this solution is much more dynamic, i will implement it in the future.

Thanx a million

|||

Donald Farmer wrote:

Interesting solution, Thiru. I have done something similar in the past, but without using script tasks before each task.

You can create a variable which hold a bitmask as an integer. Each container when it succeeds should add a power of 2 to the variable.

Use a script task at the end of the package to write out the variable to, say, an environment variable. (If you use an environment variable you can easily set the value of the variable again using a configuration.)

Each task then needs a property expression on its Disable property, using a bitwise AND (&) to check if its power of 2 has been added to the variable, for example: (@.MyVariable & 64) == 64

In this way, you only need to manage one variable, and no need to use scripts.

Donald

Donald,

I don't know if you're still watching this thread but if you are, perhaps you could explain why your solution here will work in spite of the problem I have reported here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1875701&SiteID=1

[Note that at the time of writing there has been no reply from Microsoft to this thread. By the time you come to read it there may have been]

-Jamie

|||It appears that the configuration is loaded first, then the Disable property is evaluated, then the package is run. In your test package, if you set the variable to 1 at design time, the expression does evaluate correctly, and the task doesn't run. If you do the same thing via a configuration on your test package, you get the same result. I believe the expression is being evaluated, but only once.