How to Change AutoNumber Not Start From 1

I have a table with structure below :
Table_A
{
PGradeID int IDENTITY(1,1),
PName varchar(20)
}

If I delete all records on Table_A, and then fill some records,
PGradeID will no longer start from 1 anymore, everybody knows that. How
to change it so PGradeID will start from 1 after all records was
deleted?

Thanks b4

ResantLookup the DBCC CHECKIDENT command. Also, if you TRUNCATE rather than DELETE
then the IDENTITY will be reset to seed.

Why do you care what the IDENTITY value is? It's usually fatal to attach any
significance to the value assigned by IDENTITY. If you care about the value
then IDENTITY isn't the right solution.

--
David Portas
SQL Server MVP
--|||Reset identity value will not cause problem in my case, but I
appreciate your warning.
Thanks a lot, it's works!

How to catch sql exceptions gracefully when deleting some records

I use the following function (in the BLL) to delete some records:

PublicFunction DeleteStep4Dashboards()AsBoolean

Try

adpDashboards.DeleteStep4Dashboards()

Catch exAs Exception

ReturnFalse

EndTry

ReturnTrue

EndFunction

How can I catch the sql database errors when deleting the records goes wrong.

You can add a Catch for a SqlException e.g.

Catch sqlExAs SqlException

|||

You can specify the type of the exception that you want to catch, e.g. Catch ex as SqlException (rather than as Exception).

However, catching exceptions and returning a value indicating success or failure is in general bad coding style. An exception means that something has gone wrong with what you are trying to do, and you either need to take some remedial action such as correcting parameters and retrying, or report it to the user and/or administrator.

Returning false or true puts the onus of detecting and handling errorconditions on all your method's callers, and there could be several ofthose scattered through your code, making it difficult to maintain. On the other hand, throwing an exception means that your callers only need to write code to handle the error condition if they are actually able to do something about it. Otherwise they need to be allowed to bubble up to the top layer so that they can be logged and/or the user can be notified that something went wrong.

|||

Another option is to put the delete into a stored procedure and handle it there -- in fact, you may be able to prevent any errors by checking the state of your data first to make sure the delete will work before actually trying it (eg, if a foreign key might prevent the delete)

|||

Is it really best practise to bubble up the error.

Can't I show an general error message to the user.

And handle the error (logging, e-mail to administrator) in the BLL?

|||

JohanNL:

Is it really best practise to bubble up the error.

Without meaning to answer for James, I would say that you be misundestanding what he is suggesting. As he wrote, the rule of thumb is to catch an exception only if you intend to do something about it, and that may mean logging the error and showing the user a general message. I would say that it's an application specific decision about where you want to log the errors, but if you think about it, it's not worth the effort to put try...catch everywhere if all you're going to do is log it and move on. OTOH, updates often cry out for special handling in the event of a sql error, especially since it's often helpful to catch an error right where it happens.

The best thing to do, of course, is to anticipate every possible error and code in such a way that you prevent them from happening. If you think you might get a divide by zero exception, for instance, do the check before attempting the division and give the user a message. I do this in stored procedures all the time. However, if you could think of everything you probably wouldn't need an exception log anyway<g>

How to capture changes in a table

Hi Freinds,
SQL 2000
I would like to capture all the changes ( updates. insert, delete) in my
tables. this is a type of audit and capturing the changes of data.
we can't do this in GUI section, so I need to develop smt to capture the
changes.
Does anybody did smt like this before? any help? hint?
Thank in advance,
Pathttp://www.aspfaq.com/2496
http://www.aspfaq.com/2448
"Patrick" <patriarck@.gmail.com> wrote in message
news:eK9IhW0VGHA.5468@.TK2MSFTNGP14.phx.gbl...
> Hi Freinds,
> SQL 2000
> I would like to capture all the changes ( updates. insert, delete) in my
> tables. this is a type of audit and capturing the changes of data.
> we can't do this in GUI section, so I need to develop smt to capture the
> changes.
> Does anybody did smt like this before? any help? hint?
> Thank in advance,
> Pat
>|||Based on what you are describing, you can try using a trigger, the option FO
R
INSERT, DELETE, UPDATE. Check Books Online for details. You can also capture
the username of anyone who performs an INSERT, DELETE or UPDATE. You may nee
d
to create another table to store all the changes captured by the trigger.
Please be aware that depending on how often this table is being accessed,
you may experience a impact in performance, since every operation will fire
the trigger.
"Patrick" wrote:

> Hi Freinds,
> SQL 2000
> I would like to capture all the changes ( updates. insert, delete) in my
> tables. this is a type of audit and capturing the changes of data.
> we can't do this in GUI section, so I need to develop smt to capture the
> changes.
> Does anybody did smt like this before? any help? hint?
> Thank in advance,
> Pat
>
>|||This is exacly what I want to do.
I am trying to develope a function or sp to use it in my trigger for same
exact purpose.
does anybody have done this before?
Thanks again,
Pat
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:A4AC389F-D7BA-4D6A-BEFB-769F2131A625@.microsoft.com...
> Based on what you are describing, you can try using a trigger, the option
> FOR
> INSERT, DELETE, UPDATE. Check Books Online for details. You can also
> capture
> the username of anyone who performs an INSERT, DELETE or UPDATE. You may
> need
> to create another table to store all the changes captured by the trigger.
> Please be aware that depending on how often this table is being accessed,
> you may experience a impact in performance, since every operation will
> fire
> the trigger.
> "Patrick" wrote:
>|||> I am trying to develope a function or sp to use it in my trigger for same
> exact purpose.
> does anybody have done this before?
YES! Did you see the links I posted?|||there were no links in your message !!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OrD0wz0VGHA.4740@.TK2MSFTNGP14.phx.gbl...
> YES! Did you see the links I posted?
>|||Yes, there were !!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ud7qqb0VGHA.4336@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/2496
> http://www.aspfaq.com/2448
"Patrick" <patriarck@.gmail.com> wrote in message
news:Omyf$70VGHA.4424@.TK2MSFTNGP15.phx.gbl...
> there were no links in your message !!|||ok , I got it and thanks a lot :)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23WiTIA1VGHA.5664@.TK2MSFTNGP15.phx.gbl...
> Yes, there were !!
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:ud7qqb0VGHA.4336@.TK2MSFTNGP14.phx.gbl...
>
>
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:Omyf$70VGHA.4424@.TK2MSFTNGP15.phx.gbl...
>

How to brute force delete subscriptions ?

Hello,

I restored a huge ReportServer backup on a dev machine and I do not want the subscriptions and their jobs.
I already changed the SMTP server address so emails will go to nowhere.

Now What is the best sequence to get a rid of all subscriptions?

Delete records (or truncate) from the ReportServer.Subscriptions table then delete all the subscriptions jobs or the other way around?

BTY, a centralized management of subscriptions as well as stuffing all these jobs into a folder would be a welcome addition.

Thanks,

Philippe

Safest way would be using a simple script for deleting all subscriptions via DeleteSubscription() SOAP API and rs.exe.|||

Igor Taranov -- MSFT wrote:

Safest way would be using a simple script for deleting all subscriptions via DeleteSubscription() SOAP API and rs.exe.

Thank you.

It is going to take me a little time to figure out how to do this.

BOL is not very user friendly about rs.exe.

Philippe