How can i reset the Primary key field numbers to zero before uploading my site to the prod

I have been testing my database application for a while now. As a result i have a lot of records already with their respective identity field values. What i want to do is to reset this field to 0 (zero) before i deploy the aplication. I have tried deleting all the records and entered one to see if the id will be 1 but it still starts from where it was before i deleted the records. Any ideas.DBCC CHECKIDENT (tableName, RESEED, 0)|||

usingtruncate tablewill help you.

thanks,

satish.

sql

How can I reset a database surrogate PK using SSIS?

I have a database surrogate key that increments so rapidly (+5000 every 30 mins). I need my SSIS package to reset this database surrogate key to avoid reaching an upper limit value for that field.

How can I do that using SSIS package?

thanks,

Aref

A quick Google revelas that you can use DBCC CHECKIDENT to do this

http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm

You can issue the command using the Execute SQL Task.

-Jamie

How can i output XQuery's key words such like "<,>" when using XQuery in SQL Se

in SQL Server 2005, I have a codes like this:

declare @.zz varchar(1000)
declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select @.x.query('
for $bi in //billID/text()
return ">"
')

it runs, but it prints "&gt;" not ">", I know maybe "<,>" are keys words in XQuery, but how can i output them?

someone told me to use "\", but it doesn't work.

I used Stylus Studio, it prints ">", very well http://www.stylusstudio.com/xml_download.html

but why Microsoft can't ?

Help please

Do you want it to be in an XML document that can be reparsed? then the serialization will most likely generate &gt; (as you noticed we do). If you load the XML document that contains &gt; into the IE renderer, you will notice that it will show > instead (but you could not reparse the document).

If you want to get a string value for further string processing, you need to cast the XML value to a string type... for example:

declare @.x xml

set @.x = '<root><billID>1122</billID></root>'

select (@.x.query('for $bi in //billID/text()
return ">"
')).value('text()[1]', 'nvarchar(10)')

I hope this helps
Michael

|||

hi, thanks buddy, it's so helpful, thanks so much

But I have already found another way to solve my problem, to use replace in select.. like this:

declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select replace(convert(varchar(1000),@.x.query('
for $bi in //billID/text()
return ">"
')),'&gt;','>')

I used replace ... convert ... , thus replace all the "&gt;" as ">",, nit very smart , but it works

anyway, ur way is the best one, I like it...

Thanks

sql

How can i output XQuery's key words such like "<,>" when using XQuery in SQ

in SQL Server 2005, I have a codes like this:

declare @.zz varchar(1000)
declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select @.x.query('
for $bi in //billID/text()
return ">"
')

it runs, but it prints "&gt;" not ">", I know maybe "<,>" are keys words in XQuery, but how can i output them?

someone told me to use "\", but it doesn't work.

I used Stylus Studio, it prints ">", very well http://www.stylusstudio.com/xml_download.html

but why Microsoft can't ?

Help please

Do you want it to be in an XML document that can be reparsed? then the serialization will most likely generate &gt; (as you noticed we do). If you load the XML document that contains &gt; into the IE renderer, you will notice that it will show > instead (but you could not reparse the document).

If you want to get a string value for further string processing, you need to cast the XML value to a string type... for example:

declare @.x xml

set @.x = '<root><billID>1122</billID></root>'

select (@.x.query('for $bi in //billID/text()
return ">"
')).value('text()[1]', 'nvarchar(10)')

I hope this helps
Michael

|||

hi, thanks buddy, it's so helpful, thanks so much

But I have already found another way to solve my problem, to use replace in select.. like this:

declare @.x xml
set @.x = '
<root>
<billID>1122</billID>
</root>'

select replace(convert(varchar(1000),@.x.query('
for $bi in //billID/text()
return ">"
')),'&gt;','>')

I used replace ... convert ... , thus replace all the "&gt;" as ">",, nit very smart , but it works

anyway, ur way is the best one, I like it...

Thanks

How can I handle an error

Is it possible to catch and error and then keep the process going in a stored procedure?

So if an update encounters a primary key violation on a row, is it possible to skip that row and keep the process going?

You can use a try block in a stored procedure

http://msdn2.microsoft.com/en-us/library/ms175976.aspx

|||

Ken,

I have looked at the try block, but I'm not sure how to set it up. Here is what I'm doing, can you giving me any help on adding the try block so it will keep processing after it catches a primary key violation on a row?

 @.KeepClientIDint, @.RemoveClientIDintASBEGIN UPDATE tbOrgCodesSET tblOrgCodes.Client_ID = @.KeepClientIDWHERE tblOrgCodes.Client_ID = @.RemoveClientIDEND

|||

I added the try/catch and it stopped the error, but it did not continue the process. What do I need to do to get it to continue?

@.KeepClientIDint, @.RemoveClientIDintASBEGINBEGIN TRYUPDATE tbOrgCodesSET tblOrgCodes.Client_ID = @.KeepClientIDWHERE tblOrgCodes.Client_ID = @.RemoveClientIDEND TRYBEGIN CATCH--?END CATCHEND

|||

Hi Jack,

We can catch the exception that is thrown within this single update. If the update affects serveral rows, we cannot only ignore one row and proceed with other rows.

However, if you're calling the DataAdapter.Update method to update rows. When one row throws exception, and you need to go on with other rows, you can set DataAdapter.ContinueUpdateOnError to true, so that it will continue with the next row when error generates.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

How can I get the keyword list indexed by SQL Server 2005

Full Text search must keep a list of key words or items somewhere.

How can I get access to this list to present to a user.

For example: show me all indexed words starting with letter a* and then ac*.

I'm trying to find this information without success.

Full Text uses only predicates so what you are asking are actually noise word and you can find the noise words for supported languages in the location below in your C drive. Hope this helps.

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData

|||

As far as I know there is no way built in way to get the list from fulltext service as the data is stored natively by FTS.

Jens K. Suessmeyer

http://www.sqlserver2005.de

How can I get or set a foreign key's 'on update cascade' or 'on insert cascade' by SQLDMO?

Hello,everyone!
I have a problem with sqldmo's KEY object,please help me!
When creating a foreign key constraint with 'on update cascade ' or 'on delete no action ' by T-SQL,we can do like this:
ALTER TABLE [dbo].[myForeignTableName] with nocheck
ADD CONSTRAINT [FK_mykeyname] FOREIGN KEY
([myForeignColumn])
REFERENCES [dbo].[myPrimaryTableName]
([myPrimaryKeyColumn])
on delete no action on update cascade

But,how can I set or get ' on delete no action on update cascade ' by SQLDMO's KEY object?

Thank you very much!
Toly.

DMO does not support cascaded delete. SMO however, does support it.|||

A thousand thanks for Michiel Wories's help! Your answer is very very heplful to me!

Koly.