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

request for sample program for fulltext search

hi...
i'm newbie here... can i have a sample program for fulltext search.
thanx!
VpUser,
Well, could you be more specific? SQL Server 2000 Full-text Search (FTS)
uses pure T-SQL such as CONTAINS or FREETEXT, for example:
select * from pub_info where CONTAINS(*,'books')
or if you want a SQL-DMO sample program, you can checkout SQL 2000 BOL title
"SQL-DMO Examples: Full-text Indexing"
More info would be helpful. What are you looking to do with the sample
program?
Regards,
John
"VpUser" <vpuser@.someone.com> wrote in message
news:OGwpRiEoEHA.2764@.TK2MSFTNGP11.phx.gbl...
> hi...
> i'm newbie here... can i have a sample program for fulltext search.
> thanx!
>
|||John,
Thanks John! It's very great example. I'm using SQL Server 2000 FTS.
1. May i know what different between CONTAINS and FREETEXT?
eg.
select * from pub_info where CONTAINS(title,'booktitle')
and
select * from pub_info where FREETEXT(title,'booktitle')
Its returns same result.
2. When i try this query:
==> select * from pub_info where CONTAINS(title,'book and title')
It's giving me the error. Error abt noisy words...
May i enter noisy word in the search? how do i know the noisy word by using
SQL statement(not from text file).
regards,
VpUser
|||You're welcome, VpUser,
1. May i know what different between CONTAINS and FREETEXT?
A. CONTAINS and CONTAINSTABLE will return rows that contain the exact word
that you are searching for while FREETEXT or FREETEXTTABLE will return the
exact word and if present, words that "match the meaning and not the exact
wording of the words in the search condition" (from SQL 2000 BOL). So while
in the small table pub_info, the results would be the same for your search
word booktitle, while in larger tables with more diverse text, FREETEXT
would often return more rows than CONTAINS when searching for the same word.
2. The correct sntax for the table pub_info than does not have a column
called "title", would be:
select * from pub_info where CONTAINS(*,'book and title')
When I execute the above query on Win2003, it returns 0 rows and no errors.
However, if I change title in the search condition, to "between" a
US_English noise word:
select * from pub_info where CONTAINS(*,'book and between')
This returns error Msg 7619 ".. A clause of the query contained only
ignored words". This can be avoided by removing "between" from the
US_English noise word file noise.enu that is located under
\FTDATA\SQLServer\Config where you have SQL Server 2000 installed. You can
open and edit this file with notepad.exe, but to save it you will need to
stop the "Microsoft Search" service and then run a Full Population.
Additionally, you can alter the query using quotes or parse the noise word
out via pre-processing before passing it to a SQL Server contains query. I'd
recommend that you review SQL Server 2000 BOL title "Full-text Search
Recommendations" as well as KB article 246800 (Q246800) "INF: Correctly
Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defaul...b;EN-US;246800
3. May i enter noisy word in the search? how do i know the noisy word by
using SQL statement(not from text file).
A. Yes, but you will need to parse the noise word into phrases or remove it
to avoid the error. You can also use BULK INSERT and import the noise word
file (noise.enu text file) into a SQL table and then use that table as a
lookup table to determine if the searcher enters a noise word.
Regards,
John
"VpUser" <vpuser@.someone.com> wrote in message
news:#JunAhFoEHA.3464@.TK2MSFTNGP14.phx.gbl...
> John,
> Thanks John! It's very great example. I'm using SQL Server 2000 FTS.
> 1. May i know what different between CONTAINS and FREETEXT?
> eg.
> select * from pub_info where CONTAINS(title,'booktitle')
> and
> select * from pub_info where FREETEXT(title,'booktitle')
> Its returns same result.
> 2. When i try this query:
> ==> select * from pub_info where CONTAINS(title,'book and title')
> It's giving me the error. Error abt noisy words...
> May i enter noisy word in the search? how do i know the noisy word by
using
> SQL statement(not from text file).
> regards,
> VpUser
>
|||John,
Thanks your clear explanation. It's very clear and "user friendly", easy for
me to pick up.
Well... i still have one question abt Contains.
I try to run this query in SQL server it giving me the error.
Error ==> select * from pub_info where CONTAINS(*,'yellow book')
it return me the error...
If I enter
OK==> select * from pub_info where CONTAINS(*,'yellow and book')
How do i the query if i want to find "yellow book" exact word in the query?
best regards,
VpUser
|||wrap your search phrase in double quotes
select * from pub_info where CONTAINS(*,'"yellow book"')
that's a single quote, followed by a double quote, followed by yellow book
followed by a double quote followed by a single quote
"VpUser" <vpuser@.someone.com> wrote in message
news:uEs%23$IIoEHA.3968@.TK2MSFTNGP11.phx.gbl...
> John,
> Thanks your clear explanation. It's very clear and "user friendly", easy
> for
> me to pick up.
> Well... i still have one question abt Contains.
> I try to run this query in SQL server it giving me the error.
> Error ==> select * from pub_info where CONTAINS(*,'yellow book')
> it return me the error...
> If I enter
> OK==> select * from pub_info where CONTAINS(*,'yellow and book')
> How do i the query if i want to find "yellow book" exact word in the
> query?
>
> best regards,
> VpUser
>
>
|||I am trying to use Bulk insert to populate a table from a text file.it works
fine on localhost but while running on network it gives the following error.
"Could not bulk insert because file 'E:\far\farextracts\DEGREE.txt' could
not be opened. Operating system error code 5(Access is denied.)."
Any Suggestions or solutions.
Thanks
"VpUser" wrote:

> John,
> Thanks John! It's very great example. I'm using SQL Server 2000 FTS.
> 1. May i know what different between CONTAINS and FREETEXT?
> eg.
> select * from pub_info where CONTAINS(title,'booktitle')
> and
> select * from pub_info where FREETEXT(title,'booktitle')
> Its returns same result.
> 2. When i try this query:
> ==> select * from pub_info where CONTAINS(title,'book and title')
> It's giving me the error. Error abt noisy words...
> May i enter noisy word in the search? how do i know the noisy word by using
> SQL statement(not from text file).
> regards,
> VpUser
>
>
|||MMSqlserver,
Yes. This is a permissions issue related to accessing the file
(E:\far\farextracts\DEGREE.txt) and the account (DOMAIN\account or "Local
System"/LocalSystem) that is use to start the SQL Server (MSSQLServer)
service most likely does not have the correct permission to access the
server and share where this file exists. You should also ensure that the
share (\far\farextracts) has the correct permissions, such as the Everyone
group with Read access.
While not directly related to uploading a file via BULK INSERT, but still
related to the access denied error, you should review the following KB
article "PRB: Unable to Back Up Database to a Network Drive Without
Permissions" at:
http://support.microsoft.com/default...b;EN-US;207187
Regards,
John
"MMSqlserver" <sqlservermn@.discussions.microsoft.com> wrote in message
news:609CDD9A-DA01-4CBE-9170-502990189403@.microsoft.com...
> I am trying to use Bulk insert to populate a table from a text file.it
works
> fine on localhost but while running on network it gives the following
error.[vbcol=seagreen]
> "Could not bulk insert because file 'E:\far\farextracts\DEGREE.txt' could
> not be opened. Operating system error code 5(Access is denied.)."
> Any Suggestions or solutions.
> Thanks
> "VpUser" wrote:
using[vbcol=seagreen]

Request for Change form

Does anyone have a sample of this form I could use?
We're moving to a custom CRM package written in .NET and I'll be handing the
maintanence of the CRM. I need to start developing a procedure for request
for changes because currently it's all informal.
The requests would be anything from something small like changing the font
of a field on a page to medium like adding a new field to high of error
messages.
Should all requests be written? in what format?
any direction is appreciated.Hi
Examples of this sort of thing might be found (say) in a book or course
notes for whatever methodology you are using, or possibly if you are using a
bug tracking system it would be a specific status type and a given report
that would produce this.
HTH
John
"ngan" wrote:
> Does anyone have a sample of this form I could use?
> We're moving to a custom CRM package written in .NET and I'll be handing the
> maintanence of the CRM. I need to start developing a procedure for request
> for changes because currently it's all informal.
> The requests would be anything from something small like changing the font
> of a field on a page to medium like adding a new field to high of error
> messages.
> Should all requests be written? in what format?
> any direction is appreciated.

Request for Change form

Does anyone have a sample of this form I could use?
We're moving to a custom CRM package written in .NET and I'll be handing the
maintanence of the CRM. I need to start developing a procedure for request
for changes because currently it's all informal.
The requests would be anything from something small like changing the font
of a field on a page to medium like adding a new field to high of error
messages.
Should all requests be written? in what format?
any direction is appreciated.Hi
Examples of this sort of thing might be found (say) in a book or course
notes for whatever methodology you are using, or possibly if you are using a
bug tracking system it would be a specific status type and a given report
that would produce this.
HTH
John
"ngan" wrote:

> Does anyone have a sample of this form I could use?
> We're moving to a custom CRM package written in .NET and I'll be handing t
he
> maintanence of the CRM. I need to start developing a procedure for reques
t
> for changes because currently it's all informal.
> The requests would be anything from something small like changing the font
> of a field on a page to medium like adding a new field to high of error
> messages.
> Should all requests be written? in what format?
> any direction is appreciated.