Dependant IDENTITY columns

Anyone know if MS-SQL Server supports IDENTITY columns that are incremented for each new value of the column it depends on.

For exameple:
Let's say I have a client table with a ClientID column as it's PRIMARY KEY.
This column can be an auto-incrementing IDENTITY column.

Then I have an orders table. The PRIMARY KEY for the orders table is composed of (ClientID, OrderID). I would like the OrderID to be an IDENTITY field that increments by an arbitrary value (1 in this case) for every new value of ClientID...therefore creating a unique PRIMARY KEY.

The contents of the table would look like this

ClientID OrderID
--- ---
1 0
1 1
2 0
1 2
2 1
2 2
1 3

and so on...

MySQL (and maybe other RDMS's...I haven't checked) seems to do this automatically when you set a column as AUTOINCREMENT and then define a composite PRIMARY KEY on two fields.
I know this can be done manually using triggers, but I was wondering if there was a better way...

Thanks in advanceYou are only allowed 1 IDENTITY Column per table...

How do you plan to INSERT the data in to the table?|||MySQL will do that? I'm skeptical...

Yes, you can do this using triggers, but it seems as if you are going to be using these values and their order as part of your application logic. That's generally not a good idea. An autoincrementing ID is a surrogate key, and should not have any inherent relationship to the data it represents.

What are you planning to use these values for?|||I know.

In an INSERT into the Orders table, I would supply the ClientID (and all the other fields I didn't mention in the orginal post) and the OrderID "for that client" would be incremented. Maybe you thought I meant that in the orders table the ClientID and OrderID are IDENTITY columns...|||BlindMan:
I wasn't planning on using it for anything (for now anyways), I was just curious. The Clients/Orders table was just a commonly used example.

As for it being possible with MySQL, I checked it again (since I first discovered this a while back), and yes it is possible. The syntax under MySQL would roughly be:

CREATE TABLE Clients (
ClientID INT AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE Orders (
ClientID INT,
OrderID INT AUTO_INCREMENT,
CONSTRAINT PK_SomeName PRIMARY KEY (ClientID, OrderID));

Obviously there would be a foreign key set on Orders.ClientID...but I just want to show the example..

After populating Clients with a few values, you can insert a few into orders
like so:

INSERT INTO Orders (ClientID) VALUES (SomeValues);

and the OrderID field will increment like I mentionned in the original post,
except that the default AUTO_INCREMENT seed is "1", not "0".

Like I said, I was just curious if it was possible to use this with MS-SQL
without using a workaround.

Thanks for the information!!|||MySQL will do that? I'm skeptical...

oh ye of little faith, yes, it certainly can (as afx2029 so nicely illustrated)

mysql can do several things that make sense when you see them

other databases could learn from some of what they're doing

and before y'all jump all over me, i did not say mysql was better than other databases, just that they actually do some neat things

i'm still undecided about whether i like the ability to store 2004-05-00 and 2002-00-00 as perfectly valid datetime values (there's a good reason for it), but i am aghast at them allowing 2003-02-29 and 2001-02-31|||Rudy, what would be a good reason for allowing (2002-00-00) or (2004-05-00 ) as valid datetime?|||Yeah Rudy! If you love MySQL so much, why don't you just MARRY it? Huh? Well, why dontcha? If it's so much BETTER? :p|||GDMI, to allow you to use a single date column to record an event (birthday, battle, etc.) and be able to store partial information, e.g. if all you know about great-great-grand-uncle Fritz is that he was born in 1903 but you don't know the month or year, put 1903-00-00

you can't do that with a "normal" date column in other databases, which require an exact date, so you'd either have to carry separate year, month, day columns and allow nulls, or else fake it by putting 1903-01-01 (which, i hasten to point out, is wrong and misleading)

blindman, i've sworn off marriage, having gone through two of them

:cool:|||Rudy, Thanks for the explanation. I admit I haven't thought about that before ...|||Rudy, Thanks for the explanation. I admit I haven't thought about that before ...

I can't tell. Is he talking about your code or your marriages? I quit after one, so I guess that makes me a more efficient marriager than you. ;)|||I can't tell. Is he talking about your code or your marriages? I quit after one, so I guess that makes me a more efficient marriager than you. ;)Everybody should do it at least once. It helps promote the species, and it instills a real fear of the potential of hell.

This coming from a guy who's never found an eligible woman smart enough to be interesting and dumb enough to say "yes".

-PatP|||hell hath no fury like a woman with a divorce lawyer

but it is worth the pain

i have two fantastic boys from my first marriage, 32, and 30

and a fabulous daughter, 13, and son, 11, from my second

i would love to have even more kids, but i am broke, out of work, and too old and unattractive to have any hope of a third marriage

my only hope now is that one of my older boys has grandkids soon|||I can't tell. Is he talking about your code or your marriages?

Nope! I was talking about his code! ;)

But speaking about marriages .. I am preparing for my marriage .. hopefully it will be my first and last eventhough statistics are against that.

Blindman, Rudy you guys are great SQL consultants ;) in a "SQL World" that alone should qualify to get chicks! :p|||Careful, GDMI. Statistics shouw that just over half of the marriages in the US end in divorce. But just under half end in death!|||Nope! I was talking about his code! ;)

But speaking about marriages .. I am preparing for my marriage .. hopefully it will be my first and last eventhough statistics are against that.

Blindman, Rudy you guys are great SQL consultants ;) in a "SQL World" that alone should qualify to get chicks! :p

What IT offices have you been working in?|||a long time ago, i knew a woman who knew sql...

$expletive, was she ever hot!!!!!!!!!!

in my experience, IT has as many attractive women as any other vocation|||hell hath no fury like a woman with a divorce lawyer

but it is worth the pain

i have two fantastic boys from my first marriage, 32, and 30

and a fabulous daughter, 13, and son, 11, from my second

i would love to have even more kids, but i am broke, out of work, and too old and unattractive to have any hope of a third marriage

my only hope now is that one of my older boys has grandkids soonGood kids do help to ease the pain!

I can't comment on the "broke" part, and I don't see you as "unemployed" although I know that you'd debate that point. I'm not qualified to comment on unattractive (I only pay attention to females on that point), but somehow I still can't buy that one either.

Grandkids are great fun! They are even more fun than the first time around, and at least in my opinion give you your first real chance to "check your work"

-PatP|||I don't mean to be off-topic or anything....but is what I originally posted
possible with MS-SQL Server without resorting to triggers or another workaround?

:)|||Anyone know if MS-SQL Server supports IDENTITY columns that are incremented for each new value of the column it depends on.

For exameple:
Let's say I have a client table with a ClientID column as it's PRIMARY KEY.
This column can be an auto-incrementing IDENTITY column.

Then I have an orders table. The PRIMARY KEY for the orders table is composed of (ClientID, OrderID). I would like the OrderID to be an IDENTITY field that increments by an arbitrary value (1 in this case) for every new value of ClientID...therefore creating a unique PRIMARY KEY.

The contents of the table would look like this

ClientID OrderID
--- ---
1 0
1 1
2 0
1 2
2 1
2 2
1 3

and so on...

MySQL (and maybe other RDMS's...I haven't checked) seems to do this automatically when you set a column as AUTOINCREMENT and then define a composite PRIMARY KEY on two fields.
I know this can be done manually using triggers, but I was wondering if there was a better way...

Thanks in advance

No

I asked you how you plan to do the INSERT.

If you tell us, we might be able to suggest something...|||I don't mean to be off-topic or anything....but is what I originally posted
possible with MS-SQL Server without resorting to triggers or another workaround?

:)As Brett answered (twice), no. No truely relational database can support what you are looking for, since it is application specific and violates the single domain rule of first normal form as expressed in relational algebra.

The only way to add application specific code to SQL Server is via a trigger.

-PatP|||No truely relational database can support what you are looking for ...mysql can do it

or is that why you were so careful to qualify your statement? ;)

...since it is application specific and violates the single domain rule of first normal form as expressed in relational algebra.stop that! go to your room!

it's a feature

;)|||Just like

SELECT + 'This is a feature?'

Is a feature|||stop that! go to your room!

it's a feature

;)I just said that it wasn't relational, and pointed out how the feature could be simulated in a relational database. Was I naughty?

Just like

SELECT + 'This is a feature?'

Is a featureI see it that way!

-PatP

Dependant Dropdown Filters displaying <Select a Value>

I have a few reports that we have filters where our client can select from dropdown menus. There are four menus and each one is dependant on the previous one. The problem is that initially all of the dropdowns are defaulted to <All>, but once you have gone and selected a value in the dropdowns then you go back and change any of the dropdowns, the dropdowns that are then dependant on the dropdown changed revert to <Select a Value> instead of going back to the default assigned of <All>. This is happening to all of the dropdowns dependant on the one changed no matter how many are changed initially or left alone.

Is there any way to revert them back to their assigned default <All> or is this a bug in SQL Server Reporting Services?

Is there anyone who can help me out with this issue?

Thank you for any help you may have.

Dependant Dropdown Filters displaying <Select a Value>

I have a few reports that we have filters where our client can select from dropdown menus. There are four menus and each one is dependant on the previous one. The problem is that initially all of the dropdowns are defaulted to <All>, but once you have gone and selected a value in the dropdowns then you go back and change any of the dropdowns, the dropdowns that are then dependant on the dropdown changed revert to <Select a Value> instead of going back to the default assigned of <All>. This is happening to all of the dropdowns dependant on the one changed no matter how many are changed initially or left alone.

Is there any way to revert them back to their assigned default <All> or is this a bug in SQL Server Reporting Services?

Is there anyone who can help me out with this issue?

Thank you for any help you may have.

Dependant Data Table

I am new to SQL Server. I am using Sql Server2000. I am designing and
application that offers services to user. I have a webapplication with a no
of forms. In form1 i am asking the user to select all those services he
wants. Once he selects services I take him to the other forms where he
customizes services as per his requirements.
I am storing the selected services in table1 that has following columns
1)CaseID (Primary Key)
2)Service1 Boolean
3)Service2 Boolean
Now I want that if value of Service1 is yes/true then the customized details
for this service shall be stored in Table2 that has following Fileds
1)CaseID
2)Preference1
3)preference2
Similarly for service2 I have another table - table3 becoz for each service
i have diffent attributes to be customized. I want to store the values in the
tables associated with the services if and only if service field is yes in
Table1.
Also I want to create a view that would State all the services selected in a
case and also retrieve the customized preferences from all the tables realted
to selected services. How shall i do that ? I cannot create a static view
with all the services since different users may select different services.
Please Help. I am new to SQL Server development.
Thank You.
Saket Mundra
Your design of Table1 needs improving. Something like this maybe:
CREATE TABLE Cases /* Table1 in your example */ (case_id INTEGER PRIMARY KEY
/* The presence of absence of rows in the services tables indicates what
services are chosen so the repeating group of booleans is redundant */)
CREATE TABLE Services1 /* Table2 in your example */ (case_id INTEGER PRIMARY
KEY REFERENCES Cases (case_id), service_no INTEGER DEFAULT (1) NOT NULL
CHECK (service_no = 1) /*, some other cols here ... */)
CREATE TABLE Services2 (case_id INTEGER PRIMARY KEY, service_no INTEGER
DEFAULT (2) NOT NULL CHECK (service_no = 2), FOREIGN KEY (case_id)
REFERENCES Cases (case_id), foo_col ... etc)
CREATE TABLE Services3 (case_id INTEGER PRIMARY KEY, service_no INTEGER
DEFAULT (3) NOT NULL CHECK (service_no = 3), FOREIGN KEY (case_id)
REFERENCES Cases (case_id), bar_col ... etc)
The view you require is just a left join from Cases to each of the Service
tabes.
David Portas
SQL Server MVP
|||Thank you david for your response. I regret for posting the query in
different groups.
I have looked into my table design and now its something like this
Table Cases [CaseID(primary Key), Case_details]
This table is used for case creation
Table Case_Service [CaseID (Foreign Key), ServiceID].
this table is used to store all the services selected by customer
Table Service1_details [CaseID (foreign Key), Pref1, Pref2]
Table Service2_details [CaseID (foreign Key), Pref1, Pref2]
These tables are used to store deatils of services if selected.
As per your advice I have created a view using left join and it works.But it
returns null values for the services not selected. I am curious to know if
its possible to create a view dynamically on the fly in which only fileds
from those Service_details tables are selected for which service ID is
present in Case_service Table so that for each customer a view is created
dynamically as per his selection of services. If yes I would request you to
please guide me.
Thank you very much for your help once again.
Saket Mundra
"David Portas" wrote:

> Your design of Table1 needs improving. Something like this maybe:
> CREATE TABLE Cases /* Table1 in your example */ (case_id INTEGER PRIMARY KEY
> /* The presence of absence of rows in the services tables indicates what
> services are chosen so the repeating group of booleans is redundant */)
> CREATE TABLE Services1 /* Table2 in your example */ (case_id INTEGER PRIMARY
> KEY REFERENCES Cases (case_id), service_no INTEGER DEFAULT (1) NOT NULL
> CHECK (service_no = 1) /*, some other cols here ... */)
> CREATE TABLE Services2 (case_id INTEGER PRIMARY KEY, service_no INTEGER
> DEFAULT (2) NOT NULL CHECK (service_no = 2), FOREIGN KEY (case_id)
> REFERENCES Cases (case_id), foo_col ... etc)
> CREATE TABLE Services3 (case_id INTEGER PRIMARY KEY, service_no INTEGER
> DEFAULT (3) NOT NULL CHECK (service_no = 3), FOREIGN KEY (case_id)
> REFERENCES Cases (case_id), bar_col ... etc)
> The view you require is just a left join from Cases to each of the Service
> tabes.
> --
> David Portas
> SQL Server MVP
> --
>
>
|||A static query has static metadata. In other words the number, name and
type of its columns is fixed. You could create an SP that optionally
returns different sets of columns (using IF statements for exaple) or
you could use dynamic SQL. Dynamic SQL is generally something to be
avoided in production code though. I would tend towards the idea that
you should hide the redundant columns in your presentation tier rather
than worry too much about handling it in the database.
Regarding your revised design. I recommend you add the Serviceid to
each service table (properly constrained with a CHECK constraint in
each case) and make the foreign key reference as follows: (caseid,
serviceid) REFERENCES Case_Service (caseid, serviceid). Otherwise you
could potentially have an anomaly where a case has details for a
service that doesn't exist in Case_Service. That anomaly wasn't
possible in my model because I didn't have the extra table but if you
feel you need that table then I think the compound foreign key is
essential.
David Portas
SQL Server MVP
|||Thanx David. Your suggestions helped me greatly.
If you could please elaborate on how to create an SP that optionally
returns different sets of columns or how to use dynamic SQL. I am new to Sql
server development and would be grateful if you cud help me out with this.
Lets assume that a customer selects service one and discards service two.
Now if you could please tell me how to create a view dynamically that checks
which service the customer has selected and then includes fields from
respective service_details tables. Kindly Help.
Thank You very much.
Saket Mundra
"David Portas" wrote:

> A static query has static metadata. In other words the number, name and
> type of its columns is fixed. You could create an SP that optionally
> returns different sets of columns (using IF statements for exaple) or
> you could use dynamic SQL. Dynamic SQL is generally something to be
> avoided in production code though. I would tend towards the idea that
> you should hide the redundant columns in your presentation tier rather
> than worry too much about handling it in the database.
> Regarding your revised design. I recommend you add the Serviceid to
> each service table (properly constrained with a CHECK constraint in
> each case) and make the foreign key reference as follows: (caseid,
> serviceid) REFERENCES Case_Service (caseid, serviceid). Otherwise you
> could potentially have an anomaly where a case has details for a
> service that doesn't exist in Case_Service. That anomaly wasn't
> possible in my model because I didn't have the extra table but if you
> feel you need that table then I think the compound foreign key is
> essential.
> --
> David Portas
> SQL Server MVP
> --
>
|||Examples:
CREATE PROC usp_service_details_static
(@.caseid, @.serviceid)
AS
IF @.serviceid = 1
SELECT /* ... column list */
FROM service1_details
WHERE caseid = @.caseid ;
IF @.serviceid = 2
SELECT /* ... column list */
FROM service2_details
WHERE caseid = @.caseid ;
IF @.serviceid = 3
SELECT /* ... column list */
FROM service3_details
WHERE caseid = @.caseid ;
RETURN
GO
CREATE PROC usp_service_details_dynamic
(@.caseid, @.serviceid)
AS
DECLARE @.sql VARCHAR(8000)
SET @.sql =
'SELECT * FROM '+
CASE @.serviceid
WHEN 1 THEN 'service1_details'
WHEN 2 THEN 'service2_details'
WHEN 3 THEN 'service3_details'
END
+' WHERE caseid = @.caseid ;'
EXEC (@.sql)
RETURN
GO
Before you attempt any dynamic code read the following article to make
sure you understand the implications:
http://www.sommarskog.se/dynamic_sql.html
David Portas
SQL Server MVP
|||Thank You for your help and time David. you really helped me a great deal.
Saket Mundra
"David Portas" wrote:

> Examples:
> CREATE PROC usp_service_details_static
> (@.caseid, @.serviceid)
> AS
> IF @.serviceid = 1
> SELECT /* ... column list */
> FROM service1_details
> WHERE caseid = @.caseid ;
> IF @.serviceid = 2
> SELECT /* ... column list */
> FROM service2_details
> WHERE caseid = @.caseid ;
> IF @.serviceid = 3
> SELECT /* ... column list */
> FROM service3_details
> WHERE caseid = @.caseid ;
> RETURN
> GO
>
> CREATE PROC usp_service_details_dynamic
> (@.caseid, @.serviceid)
> AS
> DECLARE @.sql VARCHAR(8000)
> SET @.sql =
> 'SELECT * FROM '+
> CASE @.serviceid
> WHEN 1 THEN 'service1_details'
> WHEN 2 THEN 'service2_details'
> WHEN 3 THEN 'service3_details'
> END
> +' WHERE caseid = @.caseid ;'
> EXEC (@.sql)
> RETURN
> GO
> Before you attempt any dynamic code read the following article to make
> sure you understand the implications:
> http://www.sommarskog.se/dynamic_sql.html
> --
> David Portas
> SQL Server MVP
> --
>

Dependant Data Table

I am new to SQL Server. I am using Sql Server2000. I am designing and
application that offers services to user. I have a webapplication with a no
of forms. In form1 i am asking the user to select all those services he
wants. Once he selects services I take him to the other forms where he
customizes services as per his requirements.
I am storing the selected services in table1 that has following columns
1)CaseID (Primary Key)
2)Service1 Boolean
3)Service2 Boolean
Now I want that if value of Service1 is yes/true then the customized details
for this service shall be stored in Table2 that has following Fileds
1)CaseID
2)Preference1
3)preference2
Similarly for service2 I have another table - table3 becoz for each service
i have diffent attributes to be customized. I want to store the values in the
tables associated with the services if and only if service field is yes in
Table1.
Also I want to create a view that would State all the services selected in a
case and also retrieve the customized preferences from all the tables realted
to selected services. How shall i do that ? I cannot create a static view
with all the services since different users may select different services.
Please Help. I am new to SQL Server development.
--
Thank You.
Saket MundraYour design of Table1 needs improving. Something like this maybe:
CREATE TABLE Cases /* Table1 in your example */ (case_id INTEGER PRIMARY KEY
/* The presence of absence of rows in the services tables indicates what
services are chosen so the repeating group of booleans is redundant */)
CREATE TABLE Services1 /* Table2 in your example */ (case_id INTEGER PRIMARY
KEY REFERENCES Cases (case_id), service_no INTEGER DEFAULT (1) NOT NULL
CHECK (service_no = 1) /*, some other cols here ... */)
CREATE TABLE Services2 (case_id INTEGER PRIMARY KEY, service_no INTEGER
DEFAULT (2) NOT NULL CHECK (service_no = 2), FOREIGN KEY (case_id)
REFERENCES Cases (case_id), foo_col ... etc)
CREATE TABLE Services3 (case_id INTEGER PRIMARY KEY, service_no INTEGER
DEFAULT (3) NOT NULL CHECK (service_no = 3), FOREIGN KEY (case_id)
REFERENCES Cases (case_id), bar_col ... etc)
The view you require is just a left join from Cases to each of the Service
tabes.
--
David Portas
SQL Server MVP
--|||Thank you david for your response. I regret for posting the query in
different groups.
I have looked into my table design and now its something like this
Table Cases [CaseID(primary Key), Case_details]
This table is used for case creation
Table Case_Service [CaseID (Foreign Key), ServiceID].
this table is used to store all the services selected by customer
Table Service1_details [CaseID (foreign Key), Pref1, Pref2]
Table Service2_details [CaseID (foreign Key), Pref1, Pref2]
These tables are used to store deatils of services if selected.
As per your advice I have created a view using left join and it works.But it
returns null values for the services not selected. I am curious to know if
its possible to create a view dynamically on the fly in which only fileds
from those Service_details tables are selected for which service ID is
present in Case_service Table so that for each customer a view is created
dynamically as per his selection of services. If yes I would request you to
please guide me.
Thank you very much for your help once again.
Saket Mundra
"David Portas" wrote:
> Your design of Table1 needs improving. Something like this maybe:
> CREATE TABLE Cases /* Table1 in your example */ (case_id INTEGER PRIMARY KEY
> /* The presence of absence of rows in the services tables indicates what
> services are chosen so the repeating group of booleans is redundant */)
> CREATE TABLE Services1 /* Table2 in your example */ (case_id INTEGER PRIMARY
> KEY REFERENCES Cases (case_id), service_no INTEGER DEFAULT (1) NOT NULL
> CHECK (service_no = 1) /*, some other cols here ... */)
> CREATE TABLE Services2 (case_id INTEGER PRIMARY KEY, service_no INTEGER
> DEFAULT (2) NOT NULL CHECK (service_no = 2), FOREIGN KEY (case_id)
> REFERENCES Cases (case_id), foo_col ... etc)
> CREATE TABLE Services3 (case_id INTEGER PRIMARY KEY, service_no INTEGER
> DEFAULT (3) NOT NULL CHECK (service_no = 3), FOREIGN KEY (case_id)
> REFERENCES Cases (case_id), bar_col ... etc)
> The view you require is just a left join from Cases to each of the Service
> tabes.
> --
> David Portas
> SQL Server MVP
> --
>
>|||A static query has static metadata. In other words the number, name and
type of its columns is fixed. You could create an SP that optionally
returns different sets of columns (using IF statements for exaple) or
you could use dynamic SQL. Dynamic SQL is generally something to be
avoided in production code though. I would tend towards the idea that
you should hide the redundant columns in your presentation tier rather
than worry too much about handling it in the database.
Regarding your revised design. I recommend you add the Serviceid to
each service table (properly constrained with a CHECK constraint in
each case) and make the foreign key reference as follows: (caseid,
serviceid) REFERENCES Case_Service (caseid, serviceid). Otherwise you
could potentially have an anomaly where a case has details for a
service that doesn't exist in Case_Service. That anomaly wasn't
possible in my model because I didn't have the extra table but if you
feel you need that table then I think the compound foreign key is
essential.
--
David Portas
SQL Server MVP
--|||Thanx David. Your suggestions helped me greatly.
If you could please elaborate on how to create an SP that optionally
returns different sets of columns or how to use dynamic SQL. I am new to Sql
server development and would be grateful if you cud help me out with this.
Lets assume that a customer selects service one and discards service two.
Now if you could please tell me how to create a view dynamically that checks
which service the customer has selected and then includes fields from
respective service_details tables. Kindly Help.
Thank You very much.
Saket Mundra
"David Portas" wrote:
> A static query has static metadata. In other words the number, name and
> type of its columns is fixed. You could create an SP that optionally
> returns different sets of columns (using IF statements for exaple) or
> you could use dynamic SQL. Dynamic SQL is generally something to be
> avoided in production code though. I would tend towards the idea that
> you should hide the redundant columns in your presentation tier rather
> than worry too much about handling it in the database.
> Regarding your revised design. I recommend you add the Serviceid to
> each service table (properly constrained with a CHECK constraint in
> each case) and make the foreign key reference as follows: (caseid,
> serviceid) REFERENCES Case_Service (caseid, serviceid). Otherwise you
> could potentially have an anomaly where a case has details for a
> service that doesn't exist in Case_Service. That anomaly wasn't
> possible in my model because I didn't have the extra table but if you
> feel you need that table then I think the compound foreign key is
> essential.
> --
> David Portas
> SQL Server MVP
> --
>|||Examples:
CREATE PROC usp_service_details_static
(@.caseid, @.serviceid)
AS
IF @.serviceid = 1
SELECT /* ... column list */
FROM service1_details
WHERE caseid = @.caseid ;
IF @.serviceid = 2
SELECT /* ... column list */
FROM service2_details
WHERE caseid = @.caseid ;
IF @.serviceid = 3
SELECT /* ... column list */
FROM service3_details
WHERE caseid = @.caseid ;
RETURN
GO
CREATE PROC usp_service_details_dynamic
(@.caseid, @.serviceid)
AS
DECLARE @.sql VARCHAR(8000)
SET @.sql = 'SELECT * FROM '+
CASE @.serviceid
WHEN 1 THEN 'service1_details'
WHEN 2 THEN 'service2_details'
WHEN 3 THEN 'service3_details'
END
+' WHERE caseid = @.caseid ;'
EXEC (@.sql)
RETURN
GO
Before you attempt any dynamic code read the following article to make
sure you understand the implications:
http://www.sommarskog.se/dynamic_sql.html
--
David Portas
SQL Server MVP
--|||Thank You for your help and time David. you really helped me a great deal.
Saket Mundra
"David Portas" wrote:
> Examples:
> CREATE PROC usp_service_details_static
> (@.caseid, @.serviceid)
> AS
> IF @.serviceid = 1
> SELECT /* ... column list */
> FROM service1_details
> WHERE caseid = @.caseid ;
> IF @.serviceid = 2
> SELECT /* ... column list */
> FROM service2_details
> WHERE caseid = @.caseid ;
> IF @.serviceid = 3
> SELECT /* ... column list */
> FROM service3_details
> WHERE caseid = @.caseid ;
> RETURN
> GO
>
> CREATE PROC usp_service_details_dynamic
> (@.caseid, @.serviceid)
> AS
> DECLARE @.sql VARCHAR(8000)
> SET @.sql => 'SELECT * FROM '+
> CASE @.serviceid
> WHEN 1 THEN 'service1_details'
> WHEN 2 THEN 'service2_details'
> WHEN 3 THEN 'service3_details'
> END
> +' WHERE caseid = @.caseid ;'
> EXEC (@.sql)
> RETURN
> GO
> Before you attempt any dynamic code read the following article to make
> sure you understand the implications:
> http://www.sommarskog.se/dynamic_sql.html
> --
> David Portas
> SQL Server MVP
> --
>

Dependant Data Table

I am new to SQL Server. I am using Sql Server2000. I am designing and
application that offers services to user. I have a webapplication with a no
of forms. In form1 i am asking the user to select all those services he
wants. Once he selects services I take him to the other forms where he
customizes services as per his requirements.
I am storing the selected services in table1 that has following columns
1)CaseID (Primary Key)
2)Service1 Boolean
3)Service2 Boolean
Now I want that if value of Service1 is yes/true then the customized details
for this service shall be stored in Table2 that has following Fileds
1)CaseID
2)Preference1
3)preference2
Similarly for service2 I have another table - table3 becoz for each service
i have diffent attributes to be customized. I want to store the values in th
e
tables associated with the services if and only if service field is yes in
Table1.
Also I want to create a view that would State all the services selected in a
case and also retrieve the customized preferences from all the tables realte
d
to selected services. How shall i do that ? I cannot create a static view
with all the services since different users may select different services.
Please Help. I am new to SQL Server development.
--
Thank You.
Saket MundraYour design of Table1 needs improving. Something like this maybe:
CREATE TABLE Cases /* Table1 in your example */ (case_id INTEGER PRIMARY KEY
/* The presence of absence of rows in the services tables indicates what
services are chosen so the repeating group of booleans is redundant */)
CREATE TABLE Services1 /* Table2 in your example */ (case_id INTEGER PRIMARY
KEY REFERENCES Cases (case_id), service_no INTEGER DEFAULT (1) NOT NULL
CHECK (service_no = 1) /*, some other cols here ... */)
CREATE TABLE Services2 (case_id INTEGER PRIMARY KEY, service_no INTEGER
DEFAULT (2) NOT NULL CHECK (service_no = 2), FOREIGN KEY (case_id)
REFERENCES Cases (case_id), foo_col ... etc)
CREATE TABLE Services3 (case_id INTEGER PRIMARY KEY, service_no INTEGER
DEFAULT (3) NOT NULL CHECK (service_no = 3), FOREIGN KEY (case_id)
REFERENCES Cases (case_id), bar_col ... etc)
The view you require is just a left join from Cases to each of the Service
tabes.
David Portas
SQL Server MVP
--|||Thank you david for your response. I regret for posting the query in
different groups.
I have looked into my table design and now its something like this
Table Cases [CaseID(primary Key), Case_details]
This table is used for case creation
Table Case_Service [CaseID (Foreign Key), ServiceID].
this table is used to store all the services selected by customer
Table Service1_details [CaseID (foreign Key), Pref1, Pref2]
Table Service2_details [CaseID (foreign Key), Pref1, Pref2]
These tables are used to store deatils of services if selected.
As per your advice I have created a view using left join and it works.But it
returns null values for the services not selected. I am curious to know if
its possible to create a view dynamically on the fly in which only fileds
from those Service_details tables are selected for which service ID is
present in Case_service Table so that for each customer a view is created
dynamically as per his selection of services. If yes I would request you to
please guide me.
Thank you very much for your help once again.
Saket Mundra
"David Portas" wrote:

> Your design of Table1 needs improving. Something like this maybe:
> CREATE TABLE Cases /* Table1 in your example */ (case_id INTEGER PRIMARY K
EY
> /* The presence of absence of rows in the services tables indicates what
> services are chosen so the repeating group of booleans is redundant */)
> CREATE TABLE Services1 /* Table2 in your example */ (case_id INTEGER PRIMA
RY
> KEY REFERENCES Cases (case_id), service_no INTEGER DEFAULT (1) NOT NULL
> CHECK (service_no = 1) /*, some other cols here ... */)
> CREATE TABLE Services2 (case_id INTEGER PRIMARY KEY, service_no INTEGER
> DEFAULT (2) NOT NULL CHECK (service_no = 2), FOREIGN KEY (case_id)
> REFERENCES Cases (case_id), foo_col ... etc)
> CREATE TABLE Services3 (case_id INTEGER PRIMARY KEY, service_no INTEGER
> DEFAULT (3) NOT NULL CHECK (service_no = 3), FOREIGN KEY (case_id)
> REFERENCES Cases (case_id), bar_col ... etc)
> The view you require is just a left join from Cases to each of the Service
> tabes.
> --
> David Portas
> SQL Server MVP
> --
>
>|||A static query has static metadata. In other words the number, name and
type of its columns is fixed. You could create an SP that optionally
returns different sets of columns (using IF statements for exaple) or
you could use dynamic SQL. Dynamic SQL is generally something to be
avoided in production code though. I would tend towards the idea that
you should hide the redundant columns in your presentation tier rather
than worry too much about handling it in the database.
Regarding your revised design. I recommend you add the Serviceid to
each service table (properly constrained with a CHECK constraint in
each case) and make the foreign key reference as follows: (caseid,
serviceid) REFERENCES Case_Service (caseid, serviceid). Otherwise you
could potentially have an anomaly where a case has details for a
service that doesn't exist in Case_Service. That anomaly wasn't
possible in my model because I didn't have the extra table but if you
feel you need that table then I think the compound foreign key is
essential.
David Portas
SQL Server MVP
--|||Thanx David. Your suggestions helped me greatly.
If you could please elaborate on how to create an SP that optionally
returns different sets of columns or how to use dynamic SQL. I am new to Sql
server development and would be grateful if you cud help me out with this.
Lets assume that a customer selects service one and discards service two.
Now if you could please tell me how to create a view dynamically that checks
which service the customer has selected and then includes fields from
respective service_details tables. Kindly Help.
Thank You very much.
Saket Mundra
"David Portas" wrote:

> A static query has static metadata. In other words the number, name and
> type of its columns is fixed. You could create an SP that optionally
> returns different sets of columns (using IF statements for exaple) or
> you could use dynamic SQL. Dynamic SQL is generally something to be
> avoided in production code though. I would tend towards the idea that
> you should hide the redundant columns in your presentation tier rather
> than worry too much about handling it in the database.
> Regarding your revised design. I recommend you add the Serviceid to
> each service table (properly constrained with a CHECK constraint in
> each case) and make the foreign key reference as follows: (caseid,
> serviceid) REFERENCES Case_Service (caseid, serviceid). Otherwise you
> could potentially have an anomaly where a case has details for a
> service that doesn't exist in Case_Service. That anomaly wasn't
> possible in my model because I didn't have the extra table but if you
> feel you need that table then I think the compound foreign key is
> essential.
> --
> David Portas
> SQL Server MVP
> --
>|||Examples:
CREATE PROC usp_service_details_static
(@.caseid, @.serviceid)
AS
IF @.serviceid = 1
SELECT /* ... column list */
FROM service1_details
WHERE caseid = @.caseid ;
IF @.serviceid = 2
SELECT /* ... column list */
FROM service2_details
WHERE caseid = @.caseid ;
IF @.serviceid = 3
SELECT /* ... column list */
FROM service3_details
WHERE caseid = @.caseid ;
RETURN
GO
CREATE PROC usp_service_details_dynamic
(@.caseid, @.serviceid)
AS
DECLARE @.sql VARCHAR(8000)
SET @.sql =
'SELECT * FROM '+
CASE @.serviceid
WHEN 1 THEN 'service1_details'
WHEN 2 THEN 'service2_details'
WHEN 3 THEN 'service3_details'
END
+' WHERE caseid = @.caseid ;'
EXEC (@.sql)
RETURN
GO
Before you attempt any dynamic code read the following article to make
sure you understand the implications:
http://www.sommarskog.se/dynamic_sql.html
David Portas
SQL Server MVP
--|||Thank You for your help and time David. you really helped me a great deal.
Saket Mundra
"David Portas" wrote:

> Examples:
> CREATE PROC usp_service_details_static
> (@.caseid, @.serviceid)
> AS
> IF @.serviceid = 1
> SELECT /* ... column list */
> FROM service1_details
> WHERE caseid = @.caseid ;
> IF @.serviceid = 2
> SELECT /* ... column list */
> FROM service2_details
> WHERE caseid = @.caseid ;
> IF @.serviceid = 3
> SELECT /* ... column list */
> FROM service3_details
> WHERE caseid = @.caseid ;
> RETURN
> GO
>
> CREATE PROC usp_service_details_dynamic
> (@.caseid, @.serviceid)
> AS
> DECLARE @.sql VARCHAR(8000)
> SET @.sql =
> 'SELECT * FROM '+
> CASE @.serviceid
> WHEN 1 THEN 'service1_details'
> WHEN 2 THEN 'service2_details'
> WHEN 3 THEN 'service3_details'
> END
> +' WHERE caseid = @.caseid ;'
> EXEC (@.sql)
> RETURN
> GO
> Before you attempt any dynamic code read the following article to make
> sure you understand the implications:
> http://www.sommarskog.se/dynamic_sql.html
> --
> David Portas
> SQL Server MVP
> --
>

Dependant assemblies in CLR

This is related to one of my previous posts.

I am running a CLR stored proc that goes to an EDS (Novell) server with LDAP and returns records into a SQL table.

I am using the Novell ldap library.

I want to do this with SSL so my code referneces the Mono security library as well.

However when I make the call to the stored proc to run in SSL, I get an object not found error. I do not think that the the Novell assembly can "find" the Mono assembly.

Two points:
1/ I can do the SSL if I run it as an asp.net page (so I know the SSL works)
2/ The proc runs and pulls all the records in non-SSL (so I know the proc works)

Any ideas?

Thanks,

BIG

Hi BIG,

Are you loading the Novell library into SQL? One restriction with CLR integration is that outside of a static list of "approved" assemblies that we access in the GAC, all assemblies must be loaded into SQL.

Cheers,

-Isaac

|||Yes, they are loaded assemblies.
New info:

System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm, Boolean forIntrospection, StackCrawlMark& stackMark)
at System.Reflection.Assembly.LoadFrom(String assemblyFile)
at Novell.Directory.Ldap.Connection.connect(String host, Int32 port, Int32 semaphoreId)|

what the heck?|||

Hi BIG,

As you surmised, Novell is trying to load the Mono security assembly as part of the connect method. The problem is that dynamically loading assemblies under SQL CLR is explicitly disallowed as the error message states.

Unfortunately, Novell is explicitly calling Assembly.LoadFrom("Mono.Security.dll") which will always try to load the assembly from disk so there is no easy way to work around this problem. If instead the call had been Assembly.Load with the Mono.Security DLL's four-part name then you could solve this by preloading the Mono.Security.Dll into SQL Server yourself.

Steven

|||What if I use the sgen.exe tool and serialize the assembly and load teh serialized assembly?|||

That is the solution for a different problem where Xml Serialization also tries to load a dynamic assembly. Good try though.

Without changing the Novell source code to use Assembly.Load rather than Assembly.LoadFrom, I don't think there is any way around this. Sorry I can't be of any help.

Steven

|||Believe it or not, I got the source code for that .dll and did change it to Load now it does not want to work...gonna contact Novell.

Thanks,

BIG|||

So the Mono.Security assembly now loads correctly, but you're hitting a different error that prevents you from using it? Or are you still running into Loading issues?

|||Loading issue...
Original code: Assembly.LoadFrom("Mono.Security.dll");
My code: Assembly.Load("Mono.Security.dll");

Actually:

// Load Mono.Security.dll
Assembly a;
try
{
a = Assembly.Load("Mono.Security.dll");
}
catch(System.IO.FileNotFoundException)
{
throw new LdapException(ExceptionMessages.SSL_PROVIDER_MISSING,LdapException.SSL_PROVIDER_NOT_FOUND, null);
}

Error message is from the Exception thrown when the assembly is not found.

I am gonna research other ways to load an assembly, but I am sure I am screwed here, will try to post this in the relavant Novell forum as well.

What I may end up doing is trying to integrate these two assemblies into one (which I think Novell should have done in the first place :( )
OR
Making this into a standard exec and have SQL Server run the job calling that exec every night, so not in CLR, and lose portability with db.

Unless anyone has a better idea?

Thanks,

BIG S|||

Do you have the Mono.Security.Dll loaded in SQL Server yet? In order for the Assembly.Load to suceed, the assembly already needs to exist in your appdomain. So you need to do CREATE ASSEMBLY [Mono.Security] FROM 'path\Mono.Security.dll' with permission_set = unsafe first.

Also, you need to pass the full, 4-part assembly name to the Mono.Security assembly as documented: http://msdn2.microsoft.com/en-us/library/ky3942xh.aspx. You can get the 4-part name from sys.assemblies.

If this still doesn't work, you can verify that the Mono.Security.dll is reported as being loaded in your appdomain from the sys.dm_clr_loaded_assemblies dmv. If it's not there, try doing a dummy CREATE FUNCTION foo() returns int as external name [Mono.Security].bar.foo. (Sorry I can't verify if this is necessary right now - I just moved to Vista at home and don't have SQL Server installed yet).

Steven

|||ok working on it...

BTW: Even if I can't get it going, Steven u rock!

BIG S|||You are bloody brilliant...I got it working!

If you are ever in Toronto, beer is on me!

BIG S