is this possible

hey all,
i have a transaction table i need to add a record to. the primary key is a 2
field column. RecordID and then the SequenceID.
How i understand it is:
To add a new record i have to
1. Find the last sequence number used
2. Then add the new record
can i do this in one stored procedure?
thanks,
rodcharWhy not use an IDENTITY column?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> hey all,
> i have a transaction table i need to add a record to. the primary key is a
2
> field column. RecordID and then the SequenceID.
> How i understand it is:
> To add a new record i have to
> 1. Find the last sequence number used
> 2. Then add the new record
> can i do this in one stored procedure?
> thanks,
> rodchar
>|||Again provide DDL...
Create Table (RecordId Int, SequenceId Int
, Constraint Primary Key (RecordId, SequenceId))
Insert Table(RecordId, SequenceId)
Select RecordId, Max(SequenceId) + 1
From Table
Group By RecordId
BTW, this does have issues in a multi-user environment. If two people were t
o
execute this function at exactly the same time, they'll get the same answer
and
thus a collision. A better way would be to make a small table that stores th
e
last value used.
Thomas
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> hey all,
> i have a transaction table i need to add a record to. the primary key is a
2
> field column. RecordID and then the SequenceID.
> How i understand it is:
> To add a new record i have to
> 1. Find the last sequence number used
> 2. Then add the new record
> can i do this in one stored procedure?
> thanks,
> rodchar
>|||"Thomas" <thomas@.newsgroup.nospam> wrote in message
news:OuVOPfcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
> BTW, this does have issues in a multi-user environment. If two people were
to
> execute this function at exactly the same time, they'll get the same
answer and
> thus a collision. A better way would be to make a small table that stores
the
> last value used.
..which would have the same issue -- what would stop two readers from
getting the value simultaneously?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||The problem is that this is an existing table in production.
"Adam Machanic" wrote:

> Why not use an IDENTITY column?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> 2
>
>|||Just increase Isolation Leel to Repeatable Read (or Serializeable) to preven
t
this issue from arising...
"Thomas" wrote:

> Again provide DDL...
> Create Table (RecordId Int, SequenceId Int
> , Constraint Primary Key (RecordId, SequenceId))
> Insert Table(RecordId, SequenceId)
> Select RecordId, Max(SequenceId) + 1
> From Table
> Group By RecordId
> BTW, this does have issues in a multi-user environment. If two people were
to
> execute this function at exactly the same time, they'll get the same answe
r and
> thus a collision. A better way would be to make a small table that stores
the
> last value used.
>
> Thomas
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
>
>|||If you can withstand the entire table being locked during the insert process
,
this would also be a viable choice.
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> Just increase Isolation Leel to Repeatable Read (or Serializeable) to prev
ent
> this issue from arising...
> "Thomas" wrote:
>|||If you use a small table that stores the next value, you can lock the table
and
increment the "next" value. In essence, serializing the retrieval of the nex
t id
value. However, it does mean you may get gaps.
Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OI6wTkcQFHA.1884@.TK2MSFTNGP15.phx.gbl...
> "Thomas" <thomas@.newsgroup.nospam> wrote in message
> news:OuVOPfcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
> to
> answer and
> the
> ...which would have the same issue -- what would stop two readers from
> getting the value simultaneously?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> Just increase Isolation Leel to Repeatable Read (or Serializeable) to
prevent
> this issue from arising...
How would that prevent issues?
QA Window 1:
--
use tempdb
go
create table x(id int)
go
insert x values (1)
go
set transaction isolation level serializable
go
begin tran
select id
from x
go
QA Window 2:
--
use tempdb
go
set transaction isolation level serializable
go
begin tran
select id
from x
go
Serializable blocks only if writes have taken place.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
> "Thomas" wrote:
>
were to
answer and
stores the
is a 2|||Adam,
Yes with just a read, but if you use the relation from t he select as
Insert values, you are doing more than just a read, and qry window 2 will
block..
create table x(id int)
go
insert x values (1)
go
set transaction isolation level serializable
go
begin tran
Insert x (id)
select id + 1 from x
-- Wait here while you run Qry WIndow 2 --
Commit Tran
-- ******************************
--Query Window 2
--
set transaction isolation level serializable
go
begin tran
Insert x (id)
select id + 1 from x
-- Now go back and commit Query Window 1
-- ---
"Adam Machanic" wrote:

> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> prevent
> How would that prevent issues?
> QA Window 1:
> --
> use tempdb
> go
> create table x(id int)
> go
> insert x values (1)
> go
> set transaction isolation level serializable
> go
> begin tran
> select id
> from x
> go
>
> QA Window 2:
> --
> use tempdb
> go
> set transaction isolation level serializable
> go
> begin tran
> select id
> from x
> go
>
> Serializable blocks only if writes have taken place.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> were to
> answer and
> stores the
> is a 2
>
>

is this an attack ?

Last night we made the backup as usual, in the morning a table is
completely all nulls except for the elements of the key.
We thought there was some error or accident, so we copy that table from
the backup, 30 min later we saw another table with the same problem,
then checking another DB used for testing also had the same problem, is
this an attack, virus ?
We don't know what to do, please someone advise.
SQL Server 2005 Standard 9.0.1399
Thanks
Andres Sanchez
Monterrey MexicoYou could run Profiler to try to track down if someone is submitting such UP
DATE statement to your
database instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mancha" <andres.sanchez.rendon@.gmail.com> wrote in message
news:1159492282.552559.267590@.i3g2000cwc.googlegroups.com...
> Last night we made the backup as usual, in the morning a table is
> completely all nulls except for the elements of the key.
> We thought there was some error or accident, so we copy that table from
> the backup, 30 min later we saw another table with the same problem,
> then checking another DB used for testing also had the same problem, is
> this an attack, virus ?
> We don't know what to do, please someone advise.
> SQL Server 2005 Standard 9.0.1399
> Thanks
> Andres Sanchez
> Monterrey Mexico
>|||Hi
You don't say how may non-key columns there are, but you may want to review
why they are all nullable.
John
"Mancha" wrote:

> Last night we made the backup as usual, in the morning a table is
> completely all nulls except for the elements of the key.
> We thought there was some error or accident, so we copy that table from
> the backup, 30 min later we saw another table with the same problem,
> then checking another DB used for testing also had the same problem, is
> this an attack, virus ?
> We don't know what to do, please someone advise.
> SQL Server 2005 Standard 9.0.1399
> Thanks
> Andres Sanchez
> Monterrey Mexico
>sql

is this an attack ?

Last night we made the backup as usual, in the morning a table is
completely all nulls except for the elements of the key.
We thought there was some error or accident, so we copy that table from
the backup, 30 min later we saw another table with the same problem,
then checking another DB used for testing also had the same problem, is
this an attack, virus ?
We don't know what to do, please someone advise.
SQL Server 2005 Standard 9.0.1399
Thanks
Andres Sanchez
Monterrey Mexico
You could run Profiler to try to track down if someone is submitting such UPDATE statement to your
database instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mancha" <andres.sanchez.rendon@.gmail.com> wrote in message
news:1159492282.552559.267590@.i3g2000cwc.googlegro ups.com...
> Last night we made the backup as usual, in the morning a table is
> completely all nulls except for the elements of the key.
> We thought there was some error or accident, so we copy that table from
> the backup, 30 min later we saw another table with the same problem,
> then checking another DB used for testing also had the same problem, is
> this an attack, virus ?
> We don't know what to do, please someone advise.
> SQL Server 2005 Standard 9.0.1399
> Thanks
> Andres Sanchez
> Monterrey Mexico
>
|||Hi
You don't say how may non-key columns there are, but you may want to review
why they are all nullable.
John
"Mancha" wrote:

> Last night we made the backup as usual, in the morning a table is
> completely all nulls except for the elements of the key.
> We thought there was some error or accident, so we copy that table from
> the backup, 30 min later we saw another table with the same problem,
> then checking another DB used for testing also had the same problem, is
> this an attack, virus ?
> We don't know what to do, please someone advise.
> SQL Server 2005 Standard 9.0.1399
> Thanks
> Andres Sanchez
> Monterrey Mexico
>

is this an attack ?

Last night we made the backup as usual, in the morning a table is
completely all nulls except for the elements of the key.
We thought there was some error or accident, so we copy that table from
the backup, 30 min later we saw another table with the same problem,
then checking another DB used for testing also had the same problem, is
this an attack, virus ?
We don't know what to do, please someone advise.
SQL Server 2005 Standard 9.0.1399
Thanks
Andres Sanchez
Monterrey MexicoYou could run Profiler to try to track down if someone is submitting such UPDATE statement to your
database instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mancha" <andres.sanchez.rendon@.gmail.com> wrote in message
news:1159492282.552559.267590@.i3g2000cwc.googlegroups.com...
> Last night we made the backup as usual, in the morning a table is
> completely all nulls except for the elements of the key.
> We thought there was some error or accident, so we copy that table from
> the backup, 30 min later we saw another table with the same problem,
> then checking another DB used for testing also had the same problem, is
> this an attack, virus ?
> We don't know what to do, please someone advise.
> SQL Server 2005 Standard 9.0.1399
> Thanks
> Andres Sanchez
> Monterrey Mexico
>|||Hi
You don't say how may non-key columns there are, but you may want to review
why they are all nullable.
John
"Mancha" wrote:
> Last night we made the backup as usual, in the morning a table is
> completely all nulls except for the elements of the key.
> We thought there was some error or accident, so we copy that table from
> the backup, 30 min later we saw another table with the same problem,
> then checking another DB used for testing also had the same problem, is
> this an attack, virus ?
> We don't know what to do, please someone advise.
> SQL Server 2005 Standard 9.0.1399
> Thanks
> Andres Sanchez
> Monterrey Mexico
>

Is this A Valid Table Schema

GO

CREATE TABLE [dbo].[CmnLanguage]
(
[Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY,
[UniqueName] [varchar](26) NOT NULL,
[NativeName] [nvarchar](26) NOT NULL,
[DirectionType] [smallint] NOT NULL,
[IsVisible] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(),
[ModifiedDateTime] [datetime] NULL
)

GO

CREATE TABLE [dbo].[CmnLink]
(
[Id] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PkCmnLink_Id PRIMARY KEY,
[UniqueName] [varchar](52) NOT NULL,
[IsVisible] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(),
[ModifiedDateTime] [datetime] NULL
)

GO

CREATE TABLE [dbo].[CmnLinkCmnLanguage]
(
[LinkId] [int] NOT NULL CONSTRAINT FkCmnLinkCmnLanguage_LinkId FOREIGN KEY (LinkId) REFERENCES CmnLink(Id) ON DELETE CASCADE,
[LanguageId] [char](2) NOT NULL CONSTRAINT FkCmnLinkCmnLanguage_LanguageId FOREIGN KEY (LanguageId) REFERENCES CmnLanguage(Id) ON UPDATE CASCADE ON DELETE CASCADE,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(),
[ModifiedDateTime] [datetime] NULL
)

Well, thats not really a schema at all. Thats a script. Sometimes also called a Create Script or Change script. I suppose someone may refer to it as a schema since it really is a deffinition of a set of tables (3 of them)

Try it out, see if it works. Run this inside a project and see if your tables and relationships get generated correctly.

|||

I already test it. But I am asking Is it valid in respect of rules.

|||

I think what you're asking is: Does it follow best pratices. There's no way for us to know what rules you'd like it to follow, but best pratices are kind of dictated by the most elegant way of doing something.

I've run your scripts on a database, and had it create the three tables, and 2 relationships. I'm not quite sure what you're trying to accomplish. It appears to be some sort of localization mapping set of tables, but I am not sure I understand why the CmnLinkCmnLanguage table exists, it doesnt make sense to me why it's there?

Could you explain further what you're end goal is, and what you hope to accomplish with these sets of tables?

|||

Ok I will explain it from start.

I am working on a completely multilingual website.

Now at this point I am working on Database end.

As this is a multilingual website so I need the Language Table as show below.

----------

Language

----------

Id

RomanName

NativeName

Direction

IsVisible

----------

Next.

I have Book Table.

----------

Book

----------

Id

RomanName

NativeName

IsVisible

----------

Every thing is fine till here.

But I have a limited type of Books and each book is avalaible in different languages.

For Example I add a book translated in Arabic, Urdu and English.

These three books have different ID. But these are the translation of Same book.

And when I have this Book in Urdu By default. And need all the available languages for this book then problem occurs.

To resovle this issue I modify the Book Table and break it into 2 Tables.

----------

Book

----------

Id

RomanName

IsVisible

----------

----------

BookNative

----------

BookId

LanguageId

NativeName

----------

Then an ID will assign for the book and all available language editions have not the ID.

At this end the above mentioned goal will got.

But I explain it on another post

http://forums.asp.net/t/1145293.aspx

And they replied its not correct.

Then again I think it for some time.

And another solution will come in mind.

That make a single Table for Book

----------

Book

----------

Id

Name

IsVisible

BookGroupID

----------

And for the above mentioned goal make a separate BookGroup Table

----------

BookGroup

----------

Id

Name

IsVisible

----------

After this solution Book Table is alone.

That was all the story.

Hope you will pick it.

And reply me with some great idea.

Waiting for your reply.