Obvious Newbile Question--How do I create a variable within SQL?

I'm making an online Quiz and I have a table with a list of Category names associated with CategoryIDs that remains static.
Another table collects data associated with these categories, and in addition to the data, contains the CategoryID for the data.

I want to populate the select options of a drop down menu with the Categories NOT YET ENTERED for a specific Quiz. I have a form which posts to itself.

SQL 7, ASP Javascripting goin' on.

I did some more looking around yesterday and came up with something that works, I just have to determine how to create a variable in SQL and then pass the CaseStudyID from an ASP page...

SELECT DISTINCT
tblDetailCaseStudyCategory.intDetailCaseStudyCateg oryID,
tblDetailCaseStudyCategory.strCaseStudyCategory
FROM tblDetailCaseStudyCategory, tblCaseStudyData
WHERE (tblDetailCaseStudyCategory.intDetailCaseStudyCate goryID NOT
IN
(SELECT intCaseStudyCategory
FROM tblCaseStudyData
WHERE intCaseStudyID = 69))

Can anyone help with this?
Thanks,Are you using a recordset object in your asp code to loop through to create the options ? Since you have a form that posts to itself, you can use that to pass the id that you need to populate the listbox - that loops through the recordset.

obtaining the names of triggers within T-SQL

I have a db containing 2 base tables and a view with an INSTEAD OF INSERT
trigger that updates both tables. I want to prevent inserts on the base
tables themselves with an INSTEAD OF INSERT trigger that would raise an erro
r
indicating that the appliation should insert against the view and then
rolling back the insert on the base table.
Is there a way to find out the names of the triggers in play so I could do
this? (I've experimented with trigger_nestlevel() but it doesn't do exactly
what I want.)
This is on SQL Server 2000.Brian,
Deny "insert" permission on the table, grant "insert" on the view.
AMB
"Brian Cobb" wrote:

> I have a db containing 2 base tables and a view with an INSTEAD OF INSERT
> trigger that updates both tables. I want to prevent inserts on the base
> tables themselves with an INSTEAD OF INSERT trigger that would raise an er
ror
> indicating that the appliation should insert against the view and then
> rolling back the insert on the base table.
> Is there a way to find out the names of the triggers in play so I could do
> this? (I've experimented with trigger_nestlevel() but it doesn't do exact
ly
> what I want.)
> This is on SQL Server 2000.
>|||That will work only if one can safely assume one has control over what users
and roles will be permitted in the database, won't it?
"Alejandro Mesa" wrote:
> Brian,
> Deny "insert" permission on the table, grant "insert" on the view.
>
> AMB
> "Brian Cobb" wrote:
>|||> That will work only if one can safely assume one has control over what
> users
> and roles will be permitted in the database, won't it?
Yes - And why wouldn't one make this assumption? Any "solution" can be
circumvented by someone with administrative access to the database. The
suggestion, by far, is the most straightforward one and involves no obscure
code to determine the "triggers in play" (without know specifically what
that means).|||I thought "triggers in play" was obvious given the context in which it was
used in my original post. But let me try to clarify. If a trigger,
trigger1, performs an action that initiates another trigger, trigger2, is it
possible within trigger2 to determine the name of the initiating trigger?
I am only interested in an answer to my question as posed in the original
post, and (hopefully) clarified here. Please feel free not to respond if yo
u
can't answer it.
"Scott Morris" wrote:

> Yes - And why wouldn't one make this assumption? Any "solution" can be
> circumvented by someone with administrative access to the database. The
> suggestion, by far, is the most straightforward one and involves no obscur
e
> code to determine the "triggers in play" (without know specifically what
> that means).
>
>|||On Fri, 17 Mar 2006 12:41:26 -0800, Brian Cobb wrote:

>I thought "triggers in play" was obvious given the context in which it was
>used in my original post. But let me try to clarify. If a trigger,
>trigger1, performs an action that initiates another trigger, trigger2, is i
t
>possible within trigger2 to determine the name of the initiating trigger?
Hi Brian,
There's no easy way to do that. The only method I can think of is to
query TRIGGER_NESTLEVEL(object_id) with the object_id of each trigger
that might be active when trigger2 runs; if it's not 0, trigger2 must
have been fired (either directly or indirectly) by that trigger.

>I am only interested in an answer to my question as posed in the original
>post, and (hopefully) clarified here. Please feel free not to respond if y
ou
>can't answer it.
The only correct answer to your original post would be to dump the idea
of enforcing this with triggers. Use DCL, as suggested by Alejandro.
Hugo Kornelis, SQL Server MVP|||I suspected it probably couldn't be done the way I hoped but wanted some
outside input. I had already considered something similar to what Mr. Mesa
had suggested, prior to my first post, which I will now revisit.
Thank you for your response.
"Hugo Kornelis" wrote:

> On Fri, 17 Mar 2006 12:41:26 -0800, Brian Cobb wrote:
>
> Hi Brian,
> There's no easy way to do that. The only method I can think of is to
> query TRIGGER_NESTLEVEL(object_id) with the object_id of each trigger
> that might be active when trigger2 runs; if it's not 0, trigger2 must
> have been fired (either directly or indirectly) by that trigger.
>
> The only correct answer to your original post would be to dump the idea
> of enforcing this with triggers. Use DCL, as suggested by Alejandro.
> --
> Hugo Kornelis, SQL Server MVP
>

obtaining physical file and path using T-SQL

SQL 2000.
Is there a way to get a databases physical file names for data and log
using t-sql?
exec sp_helpdb '<database name>' returns two resultsets, the latter
would be good if I can get at the filename in the second resultset. If
anyone knows how?...
sysdatabases seems to hold the physical name for the data only. So
where is the logfile physical name stored?
I need to get the physical names into t-sql variables for later use.
Any help much appreciate.
thanks
PaulDid you look at sp_helpfile?
"Paul Fell" <penzina@.iinet.net.au> wrote in message
news:bea9531b.0308271721.7a2ea54f@.posting.google.com...
> SQL 2000.
> Is there a way to get a databases physical file names for data and log
> using t-sql?
> exec sp_helpdb '<database name>' returns two resultsets, the latter
> would be good if I can get at the filename in the second resultset. If
> anyone knows how?...
> sysdatabases seems to hold the physical name for the data only. So
> where is the logfile physical name stored?
> I need to get the physical names into t-sql variables for later use.
> Any help much appreciate.
> thanks
> Paul|||> I found all the info I needed by looking at sysdatabases and sysaltfiles
Note that the behavior (and existence!) of these tables might change in a
future release, which is why I tend to recommend using system stored
procedures (and information_schema views, where possible) rather than direct
system table access, especially if you're putting code into production.|||Agreed. But if you look at my orginal posting, you'll see I did try
sp_helpfile.
This system SP returns two recordsets. How do I get at the second
records set in my SP? I need to be able to access this data and I
don't know of a way of doing it dynamically.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message news:<#FImIRWbDHA.2580@.TK2MSFTNGP09.phx.gbl>...
> > I found all the info I needed by looking at sysdatabases and sysaltfiles
> Note that the behavior (and existence!) of these tables might change in a
> future release, which is why I tend to recommend using system stored
> procedures (and information_schema views, where possible) rather than direct
> system table access, especially if you're putting code into production.|||> This system SP returns two recordsets. How do I get at the second
> records set in my SP?
No can do. INSERT ... EXEC only picks up the first resultset. Either pick it up in a client program,
or use the source code for the proc to write your own proc.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Paul Fell" <penzina@.iinet.net.au> wrote in message
news:bea9531b.0308281845.730c942b@.posting.google.com...
> Agreed. But if you look at my orginal posting, you'll see I did try
> sp_helpfile.
> This system SP returns two recordsets. How do I get at the second
> records set in my SP? I need to be able to access this data and I
> don't know of a way of doing it dynamically.
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:<#FImIRWbDHA.2580@.TK2MSFTNGP09.phx.gbl>...
> > > I found all the info I needed by looking at sysdatabases and sysaltfiles
> >
> > Note that the behavior (and existence!) of these tables might change in a
> > future release, which is why I tend to recommend using system stored
> > procedures (and information_schema views, where possible) rather than direct
> > system table access, especially if you're putting code into production.

Obtaining foreing keys dependences

I want to know how to obtain the relationships of foreing keys when they have diferent names in diferent tables using SQL querys from VB.net in SQL Server 2000.

Example:

Table Person
----
IDPerson
Name
Address

Table Customer
-----
IDCustomer
.
.
.

Table Employee
-----
IDEmployee
.
.
.

The dependencies are:

IDPerson-->IDCustomer
IDPerson-->IDEmployeeI think you need to query sysforiegnkeys, syscolumns and sysobjects|||Thanks, it wasnt easy but it's done.

Rodrigo