Problem with Nuuls during Append

[Trudye]

I wrote an Input Into SQL statement, during exec I received the following error:


"You tried to assign the NULL value to a variable that is not a variant data type."

From EM I set all my fields to accept NUllS.

In my input table I have fields that are required but I don't think that's the problem.

I have an Identity/autonumber field. I have some date fields, but I changed their type to text. All other fields are number/currency. The number/currency fields have no NUll values, I entered a zero(0) in all Null fields.

Some of the date fields are blank in the input table, the output table has them defined as smalldatetime. I removed all the dates and I still got the same msg.

 

[SonOfEmidec1100]

No answer but a couple of pointers -

The error message does not look like a sql server message, maybe the problem is in your code or is it an ACCESS database you are connecting to ?

As far as Im aware in SQL server if ALLOW NULLS is set then the field should always allow a null value ir-respective of the data type.

 

[Trudye]

Thanks SonOfEmidec fro responding, you are correct I was running from query analyzer against SQL Server 2000 tables. That may be why the msg does not look like an SQL Server Msg.

The tables are connected to an Access db, and in Access there are some required fields. Could that be the problem? I checked all the fields in Access and set ALLOW NULLS to Yes.

Thanks again,
Trudye

 

[RobertT687]

Make sure there are no blank rows in the input table or data file. I saw this error recently. The problem was blank rows in an Excel sheet. Access thought they were data rows.

 

[Trudye]

Thank you so much Robert I'll take a look at that.

Trudye

 

[SonOfEmidec1100]

Another thought - is the query trying to insert a row that contains a null into an AutoNumber field position ?

That will generate your error message.

 

[SonOfEmidec1100]

Ooops to quick, I should have added this reference

http://support.microsoft.com/default.aspx?scid=kb;en-us;197587