DTS Error: it tries to insert null into column when no not null

DTS Error: it tries to insert null into column when no not null

Post by Cindy Gro » Thu, 23 Aug 2001 00:36:18



It might have something to do with the identity property.  Can you repro it
for a table without identity?

Did you choose the "identity insert" option?

Cindy
http://cindygross.tripod.com
--------------------

| Newsgroups:
microsoft.public.sqlserver,microsoft.public.sqlserver.misc,microsoft.public.
sqlserver.replication
| Subject: DTS Error: it tries to insert null into column when no not null
is set
| Lines: 78
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200

| Date: Mon, 20 Aug 2001 00:58:51 GMT
| NNTP-Posting-Host: 24.100.222.131

| X-Trace: news3.rdc2.on.home.com 998269131 24.100.222.131 (Sun, 19 Aug
2001 17:58:51 PDT)
| NNTP-Posting-Date: Sun, 19 Aug 2001 17:58:51 PDT

| Path:
cppssbbsa01.microsoft.com!news-out.cwix.com!newsfeed.cwix.com!demos!newsfeed
.gamma.ru!Gamma.RU!newsfeed.direct.ca!look.ca!newshub2.rdc1.sfba.home.com!ne
ws.home.com!news3.rdc2.on.home.com.POSTED!not-for-mail
| Xref: cppssbbsa01.microsoft.com
microsoft.public.sqlserver.replication:22056
| X-Tomcat-NG: microsoft.public.sqlserver
|
| I've been trying to copy all my database objects (using a DTS package)
from
| my database on the ISP to a local copy for development, however I keep on
| running into a problem of a null being inserted into a column that does
not
| allow nulls. This doesn't make sense since the table has been set to not
| allow nulls (in that column) since it was created. I decided to whip up my
| own version on a smaller scale to see if I could duplicate the problem and
| sure enough I can.
|
| Here's the code (adjust the creation path as required if you're actually
| gonna run it):
|
| create database tester
| on
| ( name= tester_dat,
|   filename= 'c:\program files\mssql7\data\tester.mdf',
|   size= 10MB,
|   maxsize= 50MB,
|   filegrowth= 5%
| )
| log on
| ( name= tester_log,
|   filename= 'c:\program files\mssql7\data\tester_log.ldf',
|   size= 10MB,
|   maxsize= 50MB,
|   filegrowth= 5%
| )
| go
|
| use tester
| go
|
| CREATE TABLE Inv (
|  item_id int identity (10000, 1) not null,
|  cat_id smallint not null,
|  manu varchar (100) not null,
|  model varchar (50) not null,
|  description varchar (7500) not null,
|  notes varchar (200),
|  selling_price money not null,
|  cost_price money not null,
| )
|
| insert into inv (cat_id, manu, model, description, notes, selling_price,
| cost_price)
|  values (1000, 'Foobar tools', 'Foobar 2010 w/comp', 'A complete Foobar
| setup', 'must clear', 1000, 900)
| insert into inv (cat_id, manu, model, description, notes, selling_price,
| cost_price)
|  values (1000, 'Foobar tools', 'Foobar 3010 w/o comp', 'Basic Foobar 3010
| setup', 'limited stock', 2000, 1900)
| insert into inv (cat_id, manu, model, description, notes, selling_price,
| cost_price)
|  values (1000, 'Foobar tools', 'Foobar 4010 w/comp', 'A complete Foobar
| setup', 'something', 3000, 2900)
|
|
| ----------------------------
| Now if I try to use the "Transfer SQL Server Objects" task (or if you use
| the DTS wizard with the "Transfer objects and data between SQL Server 7.0
| databases"), I get the error (note, source database is obviously 'tester',
| and I just setup the destination to be NorthWind for testing purposes; I
get
| the same error when copying the real table & data from my ISP to my
| computer):
|
| "A syntax error occurred trying to evaluate a query string
| [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value
NULL
| into column 'description', table 'Northwind.dbo.Inv'; column does not
| allow nulls. INSERT fails.
| [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
| terminated."
|
| What is causing this, and how could I get around it?
|
| Thanks in advance,
| -Dan
|
|
|
|

 
 
 

1. DTS Error: it tries to insert null into column when no not null is set

I've been trying to copy all my database objects (using a DTS package) from
my database on the ISP to a local copy for development, however I keep on
running into a problem of a null being inserted into a column that does not
allow nulls. This doesn't make sense since the table has been set to not
allow nulls (in that column) since it was created. I decided to whip up my
own version on a smaller scale to see if I could duplicate the problem and
sure enough I can.

Here's the code (adjust the creation path as required if you're actually
gonna run it):

create database tester
on
( name= tester_dat,
  filename= 'c:\program files\mssql7\data\tester.mdf',
  size= 10MB,
  maxsize= 50MB,
  filegrowth= 5%
)
log on
( name= tester_log,
  filename= 'c:\program files\mssql7\data\tester_log.ldf',
  size= 10MB,
  maxsize= 50MB,
  filegrowth= 5%
)
go

use tester
go

CREATE TABLE Inv (
 item_id int identity (10000, 1) not null,
 cat_id smallint not null,
 manu varchar (100) not null,
 model varchar (50) not null,
 description varchar (7500) not null,
 notes varchar (200),
 selling_price money not null,
 cost_price money not null,
)

insert into inv (cat_id, manu, model, description, notes, selling_price,
cost_price)
 values (1000, 'Foobar tools', 'Foobar 2010 w/comp', 'A complete Foobar
setup', 'must clear', 1000, 900)
insert into inv (cat_id, manu, model, description, notes, selling_price,
cost_price)
 values (1000, 'Foobar tools', 'Foobar 3010 w/o comp', 'Basic Foobar 3010
setup', 'limited stock', 2000, 1900)
insert into inv (cat_id, manu, model, description, notes, selling_price,
cost_price)
 values (1000, 'Foobar tools', 'Foobar 4010 w/comp', 'A complete Foobar
setup', 'something', 3000, 2900)

----------------------------
Now if I try to use the "Transfer SQL Server Objects" task (or if you use
the DTS wizard with the "Transfer objects and data between SQL Server 7.0
databases"), I get the error (note, source database is obviously 'tester',
and I just setup the destination to be NorthWind for testing purposes; I get
the same error when copying the real table & data from my ISP to my
computer):

"A syntax error occurred trying to evaluate a query string
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL
into column 'description', table 'Northwind.dbo.Inv'; column does not
allow nulls. INSERT fails.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated."

What is causing this, and how could I get around it?

Thanks in advance,
-Dan

2. date format :SQL to VFP

3. DTS transfer INSERT FAILS error on VarChar NOT NULL column

4. SQL mail

5. ORA-1400 - mandatory (NOT NULL) column is missing or NULL during insert

6. simple question

7. Differencess between VB5 and Access 8

8. inserting null values into not null column in Server 7.0

9. not null column during CTAS(not null definition did not come across)

10. How to change a NULL column to NOT NULL column

11. Converting null columns to not null