Prblem With Inserting Data With Identity

Greetings!

I Have A Little Problem...

Is It Possible To Make The Identity In My Table Reset?
I Have Used The Table Already To Make Insert Some Sample Data.. Now I Deleted Those Data Inserted.. The Problem Now Is That The Identity Goes On With Incrementing(obviously)...

For Example:

I Have 2 Columns And 3 Rows..
1st Col Is The Identity, 2nd Is Some Data...
The Rows Has Identities Which Are 1,2,3 Respectively..
Then I Manually Deleted The 3rd Row Which Has The ' 3 ' Value In The First Col... Now When I Inserted Data, The Value Becomes ' 4 ' In The First Col, Becuase Obvioulsy It Is An Identity Column..

My Question Is, Is It Possible To Still Have The ' 3 ' Value When I Insert A New Value?

Or Maybe Just Reset The Table To Start With The Last Identity In The Table... Just Like With My Example...
(some Scripts Maybe?)Follow the steps
1. Drop Identity Column from table and Save
2. Re Create Identity Column.

Hope fully your problem will be resolve.

Regards|||[QUOTE=vinci]Greetings!

I Have A Little Problem...

Is It Possible To Make The Identity In My Table Reset?
I Have Used The Table Already To Make Insert Some Sample Data.. Now I Deleted Those Data Inserted.. The Problem Now Is That The Identity Goes On With Incrementing(obviously)...

You can use this:

SET IDENTITY_INSERT TableName ON

and then attempt to insert with an identity number that has been deleted.

Possible to disable welll-formedness checking when inserting XML data.

Hi All,

Can anyone help with the following problem? I have a database which
contains a table with a 'text' field, and the text field contains an
xml document - typically 50-100K. Now I'd like to make use of SQL
Server 2005s XMLData type. To do this I have created a new field of
the 'xmldata' datatype, and run an SQL statement to update the contents
from one field to another - hoping to end up with a complete table of
xml (based on the old text field).

The problem I have is after a minute or so, it must come across an
badly-formed xml fragment because I get the following message:

Msg 9436, Level 16, State 1, Line 1
XML parsing: line 1, character 67640, end tag does not match start tag

Can I turn off the checking during the update, or is it not possible to
add badly formed data to the xmldata field. Any help appreciated as
the table runs into tens of thousands of rows, so I can't really check
the contents of each!

Many thanks,

Duncan.(DSmith1974@.googlemail.com) writes:
> Can anyone help with the following problem? I have a database which
> contains a table with a 'text' field, and the text field contains an
> xml document - typically 50-100K. Now I'd like to make use of SQL
> Server 2005s XMLData type. To do this I have created a new field of
> the 'xmldata' datatype, and run an SQL statement to update the contents
> from one field to another - hoping to end up with a complete table of
> xml (based on the old text field).
> The problem I have is after a minute or so, it must come across an
> badly-formed xml fragment because I get the following message:
> Msg 9436, Level 16, State 1, Line 1
> XML parsing: line 1, character 67640, end tag does not match start tag
> Can I turn off the checking during the update, or is it not possible to
> add badly formed data to the xmldata field. Any help appreciated as
> the table runs into tens of thousands of rows, so I can't really check
> the contents of each!

Indeed, you can only pass valid XML fragments to the xml data type. They
don't have to be valid documents, that is have exactly one top-level tag,
but apart from that they must follow the XML syntax. The reason is that
the XML is stored an internal format, so SQL Server have no idea of what
do with the poorly formed XML.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

positive and negative signs

i have account_entries table which has the following fields:

debit_or_credit

entry_amount

and I want when inserting every field to check if the inserted row was assigned to (D)ebit and (C)redit it should change the entry_amount accoridanly.

maybe something like trigger? plz help...

Are you sure you want to change the value? It could be better to have a calculated field like "case when debit_or_credit = 'D' then entry_amount else -entry_amount end as effective_amount"

Rob|||

I think it's better to keep the credit as - and debit as + in the database otherwise i have to place check routines everwhere, all screen and reports..

|||

A trigger could be written, but I would not personally want to do it that way. I would prefer to either do what Rob suggested or have a calculated column that calculated whether it was a debit or a credit. :

create table stuff
(
value decimal(10,2),
debit_or_credit as (case when value >= 0 then 'Credit' else 'Debit' end)
)
go
insert into stuff (value)
select 1.00
union all
select 2.00
union all
select -2.00
union all
select -100.00
go
select *
from stuff
go
Returns:

value debit_or_credit
- -
1.00 Credit
2.00 Credit
-2.00 Debit
-100.00 Debit

This way NO chance of invalid data...

|||But don't you actually want:

create table stuff
(
entry_amount decimal(10,2),
debit_or_credit char(1),
effective_value as (case when debit_or_credit = 'D' then entry_amount else -entry_amount end)
);
go
insert into stuff values (50,'D');
insert into stuff values (50,'C');
select * from stuff;
/* Results */
entry_amount debit_or_credit effective_value

50.00 D 50.00
50.00 C -50.00

Then you can sum up 'effective_value' (restricting to date-ranges, or whatever) to see the net income/loss. And of course, there are times when you may have a negative entry_amount (for example, if you have an adjustment of depreciation). That should be counted as negative in entry_amount, but could translate to a positive amount in effective_value.

Either way though, a calculated field is the way to go, as I hope Louis and my examples have shown.

Rob|||

i have one more non-IT question plz..

I have assigned the visit fees as expense, visit payment as income

what about the discount? will be positive or negative? income or expense?

|||I'm hoping I've answered your question correctly - I'm not entirely sure of the situation. Here I'm assuming you have revenue which is paid to you, but some people may have a discount, which reduces how much you charge them.

So it depends on whether you count discount as an expense, or a reduction in revenue. I think you should do it as an expense, so that you can clearly see what your discount expense is - but other people might do that differently.

And yes, this is an area where you could potentially have a negative amount.

Rob

positive and negative signs

i have account_entries table which has the following fields:

debit_or_credit

entry_amount

and I want when inserting every field to check if the inserted row was assigned to (D)ebit and (C)redit it should change the entry_amount accoridanly.

maybe something like trigger? plz help...

Are you sure you want to change the value? It could be better to have a calculated field like "case when debit_or_credit = 'D' then entry_amount else -entry_amount end as effective_amount"

Rob|||

I think it's better to keep the credit as - and debit as + in the database otherwise i have to place check routines everwhere, all screen and reports..

|||

A trigger could be written, but I would not personally want to do it that way. I would prefer to either do what Rob suggested or have a calculated column that calculated whether it was a debit or a credit. :

create table stuff
(
value decimal(10,2),
debit_or_credit as (case when value >= 0 then 'Credit' else 'Debit' end)
)
go
insert into stuff (value)
select 1.00
union all
select 2.00
union all
select -2.00
union all
select -100.00
go
select *
from stuff
go
Returns:

value debit_or_credit
- -
1.00 Credit
2.00 Credit
-2.00 Debit
-100.00 Debit

This way NO chance of invalid data...

|||But don't you actually want:

create table stuff
(
entry_amount decimal(10,2),
debit_or_credit char(1),
effective_value as (case when debit_or_credit = 'D' then entry_amount else -entry_amount end)
);
go
insert into stuff values (50,'D');
insert into stuff values (50,'C');
select * from stuff;
/* Results */
entry_amount debit_or_credit effective_value

50.00 D 50.00
50.00 C -50.00

Then you can sum up 'effective_value' (restricting to date-ranges, or whatever) to see the net income/loss. And of course, there are times when you may have a negative entry_amount (for example, if you have an adjustment of depreciation). That should be counted as negative in entry_amount, but could translate to a positive amount in effective_value.

Either way though, a calculated field is the way to go, as I hope Louis and my examples have shown.

Rob|||

i have one more non-IT question plz..

I have assigned the visit fees as expense, visit payment as income

what about the discount? will be positive or negative? income or expense?

|||I'm hoping I've answered your question correctly - I'm not entirely sure of the situation. Here I'm assuming you have revenue which is paid to you, but some people may have a discount, which reduces how much you charge them.

So it depends on whether you count discount as an expense, or a reduction in revenue. I think you should do it as an expense, so that you can clearly see what your discount expense is - but other people might do that differently.

And yes, this is an area where you could potentially have a negative amount.

Rob