Composite primary key autoincrement from 1

Hi,

I'm currently writing a small application that is using SQL Server as a back-end database. A part of my database looks something lie the following:

Tables:
Orders(OrderId[int, PK], OrderDescription[varchar] etc...)
OrderLines(OrderId[int, PK, FK], OrderLineId[int, PK], etc...)

What I need to achieve is - everytime that a new line is inserted into an orderlines table part of the primary key will be the OrderId and the OrderLineId should be auto-incremented from 1 for each OrderId in the OrderLines table.

I know i can do this manually in my program, but i'm just wondering if theres a way to achive this in SQL Server?

Thanks,

Nick Goloborodko

Hi BushGates :-)

The two options are:

-Doing it manually within your application (like you already netioned)

-Doing it through a trigger which would evaluate the next ids for your inserted data.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens :)

Thanks for your reply - i juess i'll stick with generating the line IDs in my code :)

Nick

Complex SQL Query

Hi all,

I am developing an application using SQL Server as Back-end. I am facing a problem in creating a SQL Query. The details are as follows:

There are three tables in the Database, Data Type of all Columns is Numeric in all three tables:

1. T1

(column names and sample data)

en
==

1
2
3

2) T2

(column names and sample data)

en gn
== ==

1 10
1 11
2 10
2 12
2 13

3) T3

(column names and sample data)

en pn
== ==

1 20
1 21
1 22
2 20

Now I have to create a SQL Query, whereby I can get the following result:

en gn pn
== == ==
1 10 20
1 11 21
1 NULL 22
2 10 20
2 12 NULL
2 13 NULL

I have tried various combination of Joins, but unable to get the desired result as the tables have many-to-many relationships, therefore I get many duplicate rows in the result. UNION will not solve the problem, as that will add the additional rows for the third table. Although I can achieve this by writing few lines of code, but I have to create a SQL Query for getting this result. Kindly tell me the way for creating the required Query for this. Many Thanks for your help.there does not seem to be any join criterion

how do you know gn=10 matches pn=20?

try stating the join criterion on english, and i don't think you can do it

you may have to do your "matching" with application code|||I agree with rudy, tried playing with the query but could not come up with anything.|||I think this is what you search for:

create table ##tmp1 (en Int,pn Int,ref Int);
create table ##tmp2 (en Int,gn Int,ref Int);

insert into ##tmp1
select
a.en,
a.pn,
count(b.en) ref
from t3 a,t3 b
where a.en=b.en and a.pn>=b.pn
group by a.en,a.pn;

insert into ##tmp2
select
a.en,
a.gn,
count(b.en) ref
from t2 a,t2 b
where a.en=b.en and a.gn>=b.gn
group by a.en,a.gn;

select
coalesce(##tmp1.en,##tmp2.en) en,
##tmp1.pn,
##tmp2.gn
from ##tmp1
full join ##tmp2 on ##tmp1.en=##tmp2.en and ##tmp1.ref=##tmp2.ref
order by en

Compiling ideas about security

Dear gurus,
We have got front-end and back-end app write in VB6 which at the beginning
retrieve information of paramount importance through XP registry, info such
as: login, password, strategic folders and so on. Well, I have been thinking
in change this and maybe storing that information in Sql tables help us to
display better our hindrances as well as holes security.
-Storing these data in Sql tables and encrypting the data there (how?)
-Storing these data in XML ??
Any help will be greatly welcomed.
Thanks in advance,
EnricHi Enric,
I see a small problem with storing your login info in SQL tables.
What would happen if your SQL password were to change? How would the app
retrieve the changed password if it can't login to the DB to begin with? :)
In terms of storing it in XML files or any config files for that matter,
what we did was we wrote a custom encryption/decryption function to handle
the read and write. To be more specific, we implemented the triple-DES
algorithm.
Hope this helps.
EK
"Enric" wrote:

> Dear gurus,
> We have got front-end and back-end app write in VB6 which at the beginning
> retrieve information of paramount importance through XP registry, info suc
h
> as: login, password, strategic folders and so on. Well, I have been thinki
ng
> in change this and maybe storing that information in Sql tables help us to
> display better our hindrances as well as holes security.
> -Storing these data in Sql tables and encrypting the data there (how?)
> -Storing these data in XML ??
> Any help will be greatly welcomed.
> Thanks in advance,
> Enric
>|||Why not use Windows Domain level security rather than create your own
security layer?
Password recovery mechanisms are inherent security weaknesses, so don't
store the password at all. Instead, store a secure hash of the password with
salt. The MS Crypto API provides the tools to do this.
David Portas
SQL Server MVP
--|||It sounds like you are basically wanting to query sensitive information from
it's designated location (Active Directory) and save it off where it is more
accessable. But accessable to whom? There are probably developers in your IT
department with admin logins to SQL Server. Unless this is part of some
disaster recovery plan, and the data is placed offsite in a safe deposit
box, I do not see the need for it.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:B8CB31DE-F58E-4DB8-BE68-586E387DF217@.microsoft.com...
> Dear gurus,
> We have got front-end and back-end app write in VB6 which at the beginning
> retrieve information of paramount importance through XP registry, info
such
> as: login, password, strategic folders and so on. Well, I have been
thinking
> in change this and maybe storing that information in Sql tables help us to
> display better our hindrances as well as holes security.
> -Storing these data in Sql tables and encrypting the data there (how?)
> -Storing these data in XML ?
> Any help will be greatly welcomed.
> Thanks in advance,
> Enric
>