Refresh Fields list

Ok, what is the secret key combination to refresh the fields list? Please? :-)
I am modifying a drilldown report I created, and have added a field returned from my stored procedure, and would now like to include this field on my report. I am clicking around and I cannot seem to trigger it to refresh the fields list, and I am receiving a build error as follows:
"The value expression for the textbox â'Regionâ' refers to the field â'REGION_ORG_CODEâ'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
This field is within the current data set scope, or at least it should be. How can I include this field on my report? CTRL-ALT-F?DOH! Just noticed there is a button on the toolbar in the Data tab for this. It would be nice if this were a little more obvious.
Also, I would suggest that this feature be added to the Right click context menu in the Fields list itself.
Thanks.
"Joe" wrote:
> Ok, what is the secret key combination to refresh the fields list? Please? :-)
> I am modifying a drilldown report I created, and have added a field returned from my stored procedure, and would now like to include this field on my report. I am clicking around and I cannot seem to trigger it to refresh the fields list, and I am receiving a build error as follows:
> "The value expression for the textbox â'Regionâ' refers to the field â'REGION_ORG_CODEâ'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
> This field is within the current data set scope, or at least it should be. How can I include this field on my report? CTRL-ALT-F?

Referencing composite Primary KEYS

Hi,
i want to make a reference from a table on itself.
The table has a composite Primary Key. But I just want to refernce the TEstCaseID.
So whats wrong? Can anyone help me?

CREATE TABLE dbo.TestCase (
Project_projectID VARCHAR(20) NOT NULL references Project,
testCaseID VARCHAR(50) NOT NULL,
PRIMARY KEY(Project_projectID, testCaseID),
FatherID VARCHAR(50) references TestCase(testCaseID)

)

THanx CreanFor logical reasons, you can only reference unique values. Otherwise, how would SQL Server (or any database engine) know which of many records you were referencing?|||:D Already found out. But thanx a lot

Referencing a composite foreign key

I can't find any examples on how to do the below. Can anyone advise?
I have an 'Address' table which has a primary key constructed from
'postcode' and 'house no'.
I have numerous other tables that reference this table with a foreign
key - how do I reference a composite foreign key such as this - It
isn't a matter of just entering an integer as I won't know what the
composite value for a postcode and house no is.
Am I right in thinking that I need to make the 'postcode' and 'house
no' a composite UNIQUE to ensure these are unique and add an additional
integer primary key to the address entity which the other tables can
then reference?
Thanks,
DJWYou can reference a composite key via:
alter table MyTable
add constraint FK1_MyTable foreign key (postcode, houseno)
references OtherTable (postcode, houseno)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"dwj" <danielwatkinslearn@.hotmail.com> wrote in message
news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>I can't find any examples on how to do the below. Can anyone advise?
> I have an 'Address' table which has a primary key constructed from
> 'postcode' and 'house no'.
> I have numerous other tables that reference this table with a foreign
> key - how do I reference a composite foreign key such as this - It
> isn't a matter of just entering an integer as I won't know what the
> composite value for a postcode and house no is.
> Am I right in thinking that I need to make the 'postcode' and 'house
> no' a composite UNIQUE to ensure these are unique and add an additional
> integer primary key to the address entity which the other tables can
> then reference?
> Thanks,
> DJW
>|||How do you insert data into this table with the foreign key (say it just
contains the foreign key and a varchar name). As follows...
INSERT INTO MyTable VALUES ('QW1 4BP', 90, 'A Name')
DJW
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#Bpu9IS9FHA.2264@.tk2msftngp13.phx.gbl...
> You can reference a composite key via:
> alter table MyTable
> add constraint FK1_MyTable foreign key (postcode, houseno)
> references OtherTable (postcode, houseno)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "dwj" <danielwatkinslearn@.hotmail.com> wrote in message
> news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>|||Precisely. As long as ('QW1 4BP', 90) exists in OtherTable, then the INSERT
will succeed.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Daniel J Watkins" <danielwatkinslearn@.hotmail.com> wrote in message
news:438d4ecc$1_1@.glkas0286.greenlnk.net...
How do you insert data into this table with the foreign key (say it just
contains the foreign key and a varchar name). As follows...
INSERT INTO MyTable VALUES ('QW1 4BP', 90, 'A Name')
DJW
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#Bpu9IS9FHA.2264@.tk2msftngp13.phx.gbl...
> You can reference a composite key via:
> alter table MyTable
> add constraint FK1_MyTable foreign key (postcode, houseno)
> references OtherTable (postcode, houseno)
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "dwj" <danielwatkinslearn@.hotmail.com> wrote in message
> news:1133295056.924542.76350@.g43g2000cwa.googlegroups.com...
>

Referencial Integrity Constraints Questions ?

Hi, Group
Please let me know how can I do the following:
Will prevent the user from deleting an entry if the value is used in a
foreign key.
Best regard
MarioMario,
If you have Foreign key setup, you should not be able to delete a parent
before deleting the child. Below is an example showing FK in effect.
e.g.
create table t1(i int primary key, j int)
create table t2(k int primary key,i int foreign key references t1(i),l int)
go
insert t1 values(1,1)
insert t2 values(2,1,2)
go
--this delete will fail
--due to FK constraint
delete t1
where i=1
go
drop table t2,t1
go
-oj
http://www.rac4sql.net
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>

---

|||OJ describes the default behavour of FK constraints, which seems to be the
behavour you want... You may add the cascade option to the constraint, which
will cause the child records to be automatically deleted when the parent row
is deleted. (Just an FYI)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>

---

|||OK my Friends but what can i do then and thanks so much for yours response.
Best Regard
Mario
"Mario Reiley" <mreiley@.cantv.net> escribi en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>

---

|||OK, let me try explaining.
I have two tables:
Personal (Is my Parent table)
Profession ( Is my Child table)
In Parent is: ProfessionId field.
The thing is how Can I void Delete a row in Profession table when there is a
value in Personal table.
Example:
IF the user not put the profession in the Personal record then no problem
the profession row Is OK (Delete) else the row profession can't be deleting.
Note: Sorry my English.
Best regard
MArio
"Mario Reiley" <mreiley@.cantv.net> escribi en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>

---

|||Here's how to add a the relationship.
ALTER TABLE Personal
ADD FOREIGN KEY (ProfessionId)
REFERENCES Profession (ProfessionId)
ON DELETE NO ACTION
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23gTfxJd2DHA.2620@.TK2MSFTNGP09.phx.gbl...

quote:

---

> OK, let me try explaining.
>
> I have two tables:
>
> Personal (Is my Parent table)
> Profession ( Is my Child table)
>
> In Parent is: ProfessionId field.
>
> The thing is how Can I void Delete a row in Profession table when there is

---

a

quote:

---

> value in Personal table.
>
> Example:
>
> IF the user not put the profession in the Personal record then no problem
> the profession row Is OK (Delete) else the row profession can't be

---

deleting.

quote:

---

>
> Note: Sorry my English.
>
> Best regard
> MArio
> "Mario Reiley" <mreiley@.cantv.net> escribi en el mensaje
> news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
>

---

Referencial Integrity Constraints Questions ?

Hi, Group
Please let me know how can I do the following:
Will prevent the user from deleting an entry if the value is used in a
foreign key.
Best regard
MarioMario,
If you have Foreign key setup, you should not be able to delete a parent
before deleting the child. Below is an example showing FK in effect.
e.g.
create table t1(i int primary key, j int)
create table t2(k int primary key,i int foreign key references t1(i),l int)
go
insert t1 values(1,1)
insert t2 values(2,1,2)
go
--this delete will fail
--due to FK constraint
delete t1
where i=1
go
drop table t2,t1
go
-oj
http://www.rac4sql.net
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OJ describes the default behavour of FK constraints, which seems to be the
behavour you want... You may add the cascade option to the constraint, which
will cause the child records to be automatically deleted when the parent row
is deleted. (Just an FYI)
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OK my Friends but what can i do then and thanks so much for yours response.
Best Regard
Mario
"Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OK, let me try explaining.
I have two tables:
Personal (Is my Parent table)
Profession ( Is my Child table)
In Parent is: ProfessionId field.
The thing is how Can I void Delete a row in Profession table when there is a
value in Personal table.
Example:
IF the user not put the profession in the Personal record then no problem
the profession row Is OK (Delete) else the row profession can't be deleting.
Note: Sorry my English.
Best regard
MArio
"Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||Here's how to add a the relationship.
ALTER TABLE Personal
ADD FOREIGN KEY (ProfessionId)
REFERENCES Profession (ProfessionId)
ON DELETE NO ACTION
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23gTfxJd2DHA.2620@.TK2MSFTNGP09.phx.gbl...
> OK, let me try explaining.
>
> I have two tables:
>
> Personal (Is my Parent table)
> Profession ( Is my Child table)
>
> In Parent is: ProfessionId field.
>
> The thing is how Can I void Delete a row in Profession table when there is
a
> value in Personal table.
>
> Example:
>
> IF the user not put the profession in the Personal record then no problem
> the profession row Is OK (Delete) else the row profession can't be
deleting.
>
> Note: Sorry my English.
>
> Best regard
> MArio
> "Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
> news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> > Hi, Group
> >
> > Please let me know how can I do the following:
> >
> > Will prevent the user from deleting an entry if the value is used in a
> > foreign key.
> >
> > Best regard
> > Mario
> >
> >
>

REFERENCEing two columns to the same key

Hi

I'm just wondering whether SQL Server will let me link two columns in one table to the same column in another table. Basically, my table looks like this:

BOOKINGS
-----
BookingID (PK)
BorrowerID (FK1)
ModuleID (FK2)
LecturerID (FK3)
CollectionDateTime
ReturnDateTime
Authorised
TakenUp

FK1 and FK3 are related to BorrowerID - both lecturers and students are stored in the same table because lecturers are able to book and borrow equipment as well as students. Will SQL Server let me create a Foreign Key on BorrowerID and LecturerID that both point to Borrowers.BorrowerID?

My understanding of Foreign Keys is that they're just a way of enforcing referential integrity, so they'll only come into play when data is being deleted that might cause orphaned rows in the dependent table. So there shouldn't be too much of a problem with this, is that right?

Thanks
JonYes, absolutely. SQL Server will let you link FK1 and FK3 to the same table. However, if you want to enforce referential integrity systematically (ie cascading updates and deletes), you will only be able to do so on one of the foreign keys. With the other foreign key you will have to use a trigger. This is one of SQL's annoying "personalities" .

Terri|||Cool - thanks for your reply

You might be able to tell, but I'm quite a SQL novice. I'm guessing the trigger would be in effect on INSERTs and would check that one of the Foreign Keys is a valid value? Would it need to be effective on anything else? I guess UPDATES, but what about DELETES?

Great, now I have to learn triggers!!!

Cheers
Jon|||This KB article describes the problem you will encounter:PRB: Error Message 1785 Occurs When You Create a FOREIGN KEY Constraint That May Cause Multiple Cascade Paths

So, in your case you cannot use what is referred to as Declarative Referential Integrity (DRI).

This is an area where I am not particularly knowledgeable. I have not used DRI nor triggers for referential integrity. For better or worse, the stored procedure doing the data modification is where I have put such code.

Terri

Refer to ROWGUID col in update trigger

When writing an Update trigger on a table that has a UniqueIdentifier as the
primary key, how to I refer to the current row being updated.
I am used to using @.@.identity when working with primary keys that are of
type int but a UniqueIdentifier cannot be an identity column. I suspect it
has something to do with the "Is ROWGUID" property but I can't find the
function for getting the GUID of the row being updated.
Thanks,
Andrew.There isn't one. If you need to know this then you should generate the Guid
beforehand and use it in the insert statement.
--
Andrew J. Kelly
SQL Server MVP
"Andrew" <sql@.ses.ca> wrote in message
news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> When writing an Update trigger on a table that has a UniqueIdentifier as
the
> primary key, how to I refer to the current row being updated.
> I am used to using @.@.identity when working with primary keys that are of
> type int but a UniqueIdentifier cannot be an identity column. I suspect
it
> has something to do with the "Is ROWGUID" property but I can't find the
> function for getting the GUID of the row being updated.
> Thanks,
> Andrew.
>|||I want to access the row that is being updated, not inserted. The GUID is
already there.
Does this mean it is not possible to use a trigger to update a
"DateOfLastUpdate" field if the primary key is a GUID? I realize date could
be updated if the update was performed using a stored procedure but I feel
it would be safer to have this functionality on the table itself.
Thanks,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237WJLGiRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> There isn't one. If you need to know this then you should generate the
Guid
> beforehand and use it in the insert statement.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Andrew" <sql@.ses.ca> wrote in message
> news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> > When writing an Update trigger on a table that has a UniqueIdentifier as
> the
> > primary key, how to I refer to the current row being updated.
> >
> > I am used to using @.@.identity when working with primary keys that are of
> > type int but a UniqueIdentifier cannot be an identity column. I suspect
> it
> > has something to do with the "Is ROWGUID" property but I can't find the
> > function for getting the GUID of the row being updated.
> >
> > Thanks,
> > Andrew.
> >
> >
>|||> You threw me off when you mentioned @.@.IDENTITY. @.@.IDENTITY has nothing to
> do with an UPDATE, it is only useful for Inserts. Any time you want to
> reference a row that is being updated in a trigger you can use the
Inserted
> table. To update a datetime column you would simply do this:
> UPDATE YourTable SET ModDate = GETDATE()
> WHERE PK IN (SELECT i.PK FROM Inserted as i)
> This way it works when more than 1 rows is updated and there is no need to
> know what the actual value of the PK is.
I tend to use a wrapper like:
IF NOT UPDATE(ModDate)
Just to prevent recursion. :-)|||Thanks for your help. I had just "discovered" the inserted table but the
method I came up with is not as clean as yours.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uiAkt8iRDHA.3236@.TK2MSFTNGP10.phx.gbl...
> You threw me off when you mentioned @.@.IDENTITY. @.@.IDENTITY has nothing to
> do with an UPDATE, it is only useful for Inserts. Any time you want to
> reference a row that is being updated in a trigger you can use the
Inserted
> table. To update a datetime column you would simply do this:
> UPDATE YourTable SET ModDate = GETDATE()
> WHERE PK IN (SELECT i.PK FROM Inserted as i)
> This way it works when more than 1 rows is updated and there is no need to
> know what the actual value of the PK is.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Andrew" <sql@.ses.ca> wrote in message
> news:u53MXdiRDHA.2424@.tk2msftngp13.phx.gbl...
> > I want to access the row that is being updated, not inserted. The GUID
is
> > already there.
> >
> > Does this mean it is not possible to use a trigger to update a
> > "DateOfLastUpdate" field if the primary key is a GUID? I realize date
> could
> > be updated if the update was performed using a stored procedure but I
feel
> > it would be safer to have this functionality on the table itself.
> >
> > Thanks,
> > Andrew
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%237WJLGiRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> > > There isn't one. If you need to know this then you should generate
the
> > Guid
> > > beforehand and use it in the insert statement.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Andrew" <sql@.ses.ca> wrote in message
> > > news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> > > > When writing an Update trigger on a table that has a
UniqueIdentifier
> as
> > > the
> > > > primary key, how to I refer to the current row being updated.
> > > >
> > > > I am used to using @.@.identity when working with primary keys that
are
> of
> > > > type int but a UniqueIdentifier cannot be an identity column. I
> suspect
> > > it
> > > > has something to do with the "Is ROWGUID" property but I can't find
> the
> > > > function for getting the GUID of the row being updated.
> > > >
> > > > Thanks,
> > > > Andrew.
> > > >
> > > >
> > >
> > >
> >
> >
>