Getting information dependant upon primary & foreign key

Hi All,

It seems I have been requested to carry out a complex query and the best way I think I can do this is with the use of a stored procedure. The problem is that I am not quite sure whether my SP is stated correctly and also how I would go about stating the SP in my VB.net code!

I would be ever so grateful if somebody could look over my SP code and possibly recommend a way of stating my code. My ability is limited so I would appreciate it if examples could be used with possible relations to my problem.

The Problem?

Tables:

1.tblRisk: Ref(pk), subject, status(fk), staff(fk),Dept(fk)

2.tblDept:Ref(pk), Department

The SP should state that Department should appear as the end result of the query when the page is loaded. So when a row is selected in tblRisk, dependant upon what the Dept is in that table, it then populates the department in which it is associated with from tblDept. I have left the SP below.

Many Thanks,

Kunal

CREATE PROCEDURE dbo.ShowMe @.yourInputValue INT
AS
SELECT tblDept.Department
FROM tblDept JOIN tblRisk
ON tblDept.Ref = tblRisk.Dept
WHEREtblDept.Ref = @.yourInputValue
RETURN 0
GO

You stored procedure is ok

You can use SqlCommand to call stored procedures in your ASP .NET application, just set the CommandType property of the SqlCommand to CommandType.StoredProcedure and pass proper parameter(s) to the stored procedure.

Getting FKey violations in Replication

I have parent and child tables both in publisher and subscriber and I have
identical foreign keys in publisher and subscriber. However, sometimes
replication will report foreign key violation for the data getting
replicated.
To troubleshoot, I dropped and recreated the foreign keys with ‘Not For
Replication’ option which made the error go away. I ran some SQLs to see
where the data was inconsistent in the reported parent/child tables but
didn’t find any thing. To be sure, I dropped and recreated the foreign keys
and no error was reported.
My question is, why did I get the error in replication when I had the FKeys
without “Not For Replication” option. I would prefer to have FKeys check for
data integrity for replicated data on the subscriber side as well.
-A
Where did you do your validation? On the Publisher or Subscriber or both?
Also check to see if you are replicating cascading updates and deletes -
this could trigger this error.
What you should do next time you get this error is to look at the conflict
in the conflict viewer and determine which row is causing the problem.
In general you want to maintain DRI on both sides. Using the NFR option on
constraints will have the effect of not enforcing the constraint when the
DML is caused by a replication process. Merge replication can replicate
child records before parent records and this can cause the error you are
seeing.
Another option is to increase the download and upload generations per batch
setting, and if you get the error, re-run the agent..
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:364E4E6B-A323-43CE-A728-394846D36C05@.microsoft.com...
>I have parent and child tables both in publisher and subscriber and I have
> identical foreign keys in publisher and subscriber. However, sometimes
> replication will report foreign key violation for the data getting
> replicated.
> To troubleshoot, I dropped and recreated the foreign keys with 'Not For
> Replication' option which made the error go away. I ran some SQLs to see
> where the data was inconsistent in the reported parent/child tables but
> didn't find any thing. To be sure, I dropped and recreated the foreign
> keys
> and no error was reported.
> My question is, why did I get the error in replication when I had the
> FKeys
> without "Not For Replication" option. I would prefer to have FKeys check
> for
> data integrity for replicated data on the subscriber side as well.
> -A
>
>