Hi there,

I am relatively new to SQL Server so do forgive me if i seem a tad nieve.

I have recently upsized an Access 97 DB to SQL server 7, simply to implement
an ASP application on the same data that is in the Access DB ( had to be in
SQL, not direct on Acces db).
The data is still input and manipulated into Access and exported to .csv
files every month, then imported into the SQL DB. So the ASP pages are kept
up to date.
The problem i am having is as follows.

For some reason i was under the impression that SQL tables HAD to have a
primary key field in order to be read/write. Not sure where i heard that
from.
Due to the bad design of the Access database (not on my part, it wasnt me)
alot of the tables are not indexed nor have primary keys.
So, upon creating the Sql tables i added a field called SQL_REF simply as a
unique identifier (autonumber, or identity increment in SQL) PK field.

Now when i run my DTS imports on some of these tables it states you cannot
insert a NULL value into a primary key field or the like (as allow Null
values is unticked for pk's)

1st id like to know whether it is entirely neccessary for me to have PKs in
the SQL tables, as all they are used for is running Views to get data onto
ASP pages.
And second, short of deleting the PK field, how can i stop this error
appearing? the SQL_REF pk field is entirely uneccessary, simply is there
because i was told SQL tables HAD to have some sort of unique identifier.

Thanks for your patience

Kind Regards

--
G


www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

A primary key constraint is a way if uniqely identifying a row in a SQL
Server table. Every table should have one as it stops all kinds of bogus
entries and confusion about which row you actually require.

I presume you have made the PK column an INT IDENTITY(1,1) NOT NULL.

Have a look here

Problems With IDENTITY() and the DataPump task.
(http://www.sqldts.com/default.aspx?293)

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Guy Hocking" <guy (AT) ANTIbradflack (DOT) SPAMcom> wrote

It is preferable to have a primary key (actually to have a clustered index which tend to be the kind of index for primary key). I know that if you are using Access to connect with ODBC and want to update the data, you need some kind of unique identifier and maybe ADO is the same. But your case is read only, so there is no such requirement

Two things come to mind: the primary key field does not have its identity property set. What sometimes happens is that you create the table, set the primary key, then forget to set the identity property of that column

The other possibility is that you have a copy column transformation that is trying to fill the primary key column and some of the values are null

Russ