Can we insert NULL in place of foreign key.

I have a couple of tables in my database (SQL server 2000)
1. Student -- Columns are as
"StudentID" -- INT
"StudentName" -- NVARCHAR
"DepartmentID" -- INT
2. Department -- Columns are as
"DepartmentID" -- INT
"DepartmentName" -- NVARCHAR
So in the table "Student", the field "DepartmentID" is a foreign key. Now I
tried out turning "Allow Null" on for the column "DepartmentID" in the
"Student" table. As soon as I did this, I was able to insert a row with
"DepartmentID" as <NULL>.
Now isn't this wrong? Or is it that the database overrides the checking of
the foreign key against the table "Department" when I check the "Allow Null"
option ON?
It would be really helpful if someone could elaborate on this.
Locate me in the blogosphere: http://spaces.msn.com/aayushpuriHi
Yes, referencing table may accept NULL's. Like people that don't have
children.
"Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
news:C5A2D39D-5C28-4661-B68B-B2CA6BA48CDF@.microsoft.com...
>I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
> I
> tried out turning "Allow Null" on for the column "DepartmentID" in the
> "Student" table. As soon as I did this, I was able to insert a row with
> "DepartmentID" as <NULL>.
> Now isn't this wrong? Or is it that the database overrides the checking of
> the foreign key against the table "Department" when I check the "Allow
> Null"
> option ON?
> It would be really helpful if someone could elaborate on this.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri|||Aayush Puri wrote:
> I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
I
> tried out turning "Allow Null" on for the column "DepartmentID" in the
> "Student" table. As soon as I did this, I was able to insert a row with
> "DepartmentID" as <NULL>.
> Now isn't this wrong? Or is it that the database overrides the checking of
> the foreign key against the table "Department" when I check the "Allow Nul
l"
> option ON?
> It would be really helpful if someone could elaborate on this.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
SQL's foreign key constraints work like check constraints.
Specifically, values are permitted as long as the constraint isn't
violated. A null comparison returns an UNKNOWN result rather than a
FALSE one and UNKNOWN isn't regarded as a violation of the constraint.
So the behaviour you mentioned is "right" according to the SQL
standard. Whether it makes good logical sense is a different matter.
I'd suggest that you either disallow nulls or that you create some
default value in the Depatrtments table to represent the case that you
would otherwise have used a null for - a "Not Applicable" department
for example.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Can the transaction log be turned off?

I have a couple of databases that see a lot of updates (WEB user session state stuff) and I don't care at all if I lose any or all of the data. They don't get backed up and fresh copies can be created in seconds. Is there a way I can turn off the transaction logging to cut down on overhead?

ThanksNo

Why what the problem?

Waht's your recovery model set to?|||Recovery model is 'Simple'. Just thought I'd be able to cut down overhead. I've been getting timeouts and am grasping at straws.|||Originally posted by grahamt
Recovery model is 'Simple'. Just thought I'd be able to cut down overhead. I've been getting timeouts and am grasping at straws.

When you say timeouts, what do you mean by that?

From QA, from the application layer?

Are you blocking yourself (sp_lock)

How many connections are open (sp_who2)

How big is the database?

How often do you take backups?|||Setting recovery model is nothing to do with blocks or timeouts on the database. Ensure there are no issues with network, client connections and query conditions involved which may help causing timeouts.

Whatever the recovery model ensure to maintain & schedule full backup of the database.|||The SQL Server timeouts are reported by the ASP.
A bit of config info.

Hardware

SQL Server 2000
Dual Xeon 2.4GHz CPU
Ultra 320 SCSI controller
Two Ultra 320 18GB, 15000 RPM Hard Disks (C: and D:)
Two GB RAM (1.5GB assigned to SQL Server)
Windows 2000 Server

WEB Server connected to SQL server by Gigabit Ethernet.

Two databases involved. One has a single table of perhaps 2000 records (Session state info for WEB users) and is heavily used (Insert, Delete, and Select). It resides on drive C:.

Second DB residing on drive D: has one Master table and 26 others (A-Z). Master table has about 16000 6K records, Primary key on a varchar(50) and a char(2) column. Other tables run from 1000 to 50000 records 70 bytes/record (PK on a varchar(50) field). No updates are ever done (well, every few months maybe) and the Selects are done using stored procedures, one for the Master table (2 parameters) and 26 for the other tables (1 parameter).

Every few minutes (time varies as does the DB with the DB on drive D: getting the most) the WEB app logs an SQL Server timeout. With this horsepower driving these small DBs (and nothing else running) I wouldn't expect any timeouts at all.

Since I don't care about the data in one table and the data in the other never changes, I don't bother with backups.

Any thoughts?|||What are the timeout settings on SQL & ASP script?
IT may be worth if you enable DBCC DBREINDEX and other maint.plan checks on database which will addup performance.

And also consider network settings and take help of netadmin.|||http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm - a useful guide to troubleshoot timeouts when using SQL & ASP.|||I've been monitoring network traffic with 3COMs Network Monitor and everything seems fine. The WEB server showed high FTP traffic so I've shut that service down but there's no perceptable load on SQL server. Performance monitor shows Disk Idle Time averaging 98% or better on both drives and the CPU load never exceeds 3-4%. I've run the SPs through QA and because they are so basic the execution cost is almost nil. The two SPs are

CREATE PROCEDURE [DBO].[prGetOrigins_A]
@.SomeVar Varchar(50)
AS

SET NOCOUNT ON

SELECT Code, Master FROM A Where SomeVar = @.Somevar
GO

and

CREATE PROCEDURE [DBO].[prGetMasterRec]
@.master varChar(20),
@.code varCHar(3)
AS
SET NOCOUNT ON

SELECT id, code,SomeData,
FROM Masters WHERE Master = @.master AND Code = @.Code
GO

and still I get random timeouts.

Personally I am not concerned. A timeout every 5 minutes or so with 250 users on the WEB server probably shouldn't happen but I can live with it. It's the boss who panics and worries that he may have lost a $5.00 sale. 8-)

Can SQL Server work with a Paradox table?

I've never really setup or used MS SQL Server (just a couple hours, one
day, several months ago). I think MS SQL Server has the ability to use
"linked tables", like MS Access does. Is this correct? What I want to
do is have an MS SQL Server setup, which compatible applications can
talk with, but I want the data to come from a Paradox database. I can
use any MS SQL version, whatever would work best. I'm not sure about
the Paradox version, I know it is an old DOS version. I can't just
convert the data to another format, because Paradox still needs to use
it.

I tried using MS Access before, with ODBC drivers it *should* be able
to work with the Paradox data. However, I ended up with lots of data
corruption. I'm hoping MS SQL Server may work better, and not corrupt
the Paradox data everytime it is updated.It's been a while since I've used Paradox, but you should be able to
what you want. I would find the appropriated ODBC drive from Paradox,
rather than use the generic MS drivers. That was probably the cause of
your corruption issues in the past.

How real-time does your data access need to be? If there can be some
delay, you may consider using an import process to fetch the data from
Paradox and convert it to MSSQL; that may reduce locking issues on the
Paradox side.

HTH,
Stu|||Thanks for the reply. The data access needs to be real-time, so any
import/export or even synchronization would not work.

I'll try again, since I don't remember all of the details. I'm pretty
sure over my several attempts I tried both the MS drivers, and the BDE
drivers (Borland, but as far as I know those are the only Paradox
drivers, since Corel (current Paradox owners) haven't really updated
Paradox or done any work on it). I only tried with Access though, so
I'll see if I have better luck with MSSQL.|||For the project I worked on integrating Paradox with SQL Server, I
ended up buying a copy of Paradox to get the ODBC drivers that came
with it. It was some third-party driver (and it was years ago), but
they worked great. Of course, I wasn't doing real-time integration; it
happened every 5 minutes or so.

Not sure that helps; I tried googling for the vendor we used (I think
it was DataDirect, but not sure). All I remember was that it was
cheaper to get a single copy of Paradox than to get the server licence
from the vendor.

Stu|||Thank you, and that is exactly what I found. After reading the last
post I decided to try again. This time I found that ODBC driver that
comes with Paradox (by Intersolv) and that fixed the data corruption
which I was getting with the Microsoft driver. Great!

I just started to play around with it. Most areas seem pretty fast.
The only weird thing, which I'm hoping I can find a way around, is that
it takes about 5 seconds to enter "edit mode", for example:

1. Open Linked table in Access (opens really fast, like in Paradox)

2. Browse through records (really fast), then start to edit one of the
records

... Access hangs for about 5 seconds, then enters "edit mode"...

3. Edit the record and save it (really fast), edit aditional records
(really fast).

4. Close table, then reopen...

5. First edit has that 5 second delay again.

I'm sure it has to do with locking. In Paradox it takes less than a
second to enter edit mode (a split second), so hopefully there is a way
to get the ODBC driver to work just as fast. Not sure if it is an ODBC
thing, or Access, but I guess I could check by using some other
utilities which can use the ODBC driver (like Borland's "Database
Desktop").

I haven't brought SQL into the picture yet, but I might, for one thing
I'll only need one server license for the ODBC driver, instead of one
for each workstation if I only use Access.

Stu wrote:
> For the project I worked on integrating Paradox with SQL Server, I
> ended up buying a copy of Paradox to get the ODBC drivers that came
> with it. It was some third-party driver (and it was years ago), but
> they worked great. Of course, I wasn't doing real-time integration; it
> happened every 5 minutes or so.
> Not sure that helps; I tried googling for the vendor we used (I think
> it was DataDirect, but not sure). All I remember was that it was
> cheaper to get a single copy of Paradox than to get the server licence
> from the vendor.
> Stu

Can report be design without VS.NET

Hi. I just started playing with reporting services and I have a couple of
questions if anyone can help. First, I've noticed most of samples everywhere
is using report designer using Visual Studio.NET, am I force to use VS.NET or
can I use other applicaitons to generate the report. Also, if I have a
normal ASP website, can I link the report into ASP pages or do I need to
display via ASP.NET. I'm trying to figure out if I need to rewrite my
website into ASP.NET application and if I don't need ASP.NET, what other
report designer is out there to allow me to create a report. Any feedback
would be great.
HenryToday VS.Net is required to design using the report designer shipping with
the product. There are 3rd party designers out there, check the MS site for
3rd party partners. Most people though use the report designer that ships
with the product. Any version of vs.net will do (the cheapest is to buy
vb.net, about $100).
One thing to realize is RS is an asp.net application so to run it you have
to have IIS and asp.net running. Yes you can have your own web page in some
other technology somewhere else and use either URL integration or web
services (soap) to integrate but that would be in addition to the asp.net
web site running RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:11F5BC01-6326-4284-B0A4-5C18FC2E4C8A@.microsoft.com...
> Hi. I just started playing with reporting services and I have a couple of
> questions if anyone can help. First, I've noticed most of samples
everywhere
> is using report designer using Visual Studio.NET, am I force to use VS.NET
or
> can I use other applicaitons to generate the report. Also, if I have a
> normal ASP website, can I link the report into ASP pages or do I need to
> display via ASP.NET. I'm trying to figure out if I need to rewrite my
> website into ASP.NET application and if I don't need ASP.NET, what other
> report designer is out there to allow me to create a report. Any feedback
> would be great.
> Henry