• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Null Value Insertion Error.

I'm getting an error in DTS in a step where I try to import a temp table into a journal table.
I've copied the step into query analyser and I get the same error which is:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Date', table 'PAAccounting.dbo.Journal'; column does not allow nulls. INSERT fails.
The statement has been terminated.

It doesn't make sense. I have the date in the query. When I run the selection statement there it is.
Why am I getting this error?
Here is the query:
Insert Into Journal(
      [JournalID],
      [Date],TransactionTypeID,
      BudgetCode,MainExpenseAccount,SubCode,AllocationIndex,
      Voucher,Amount,[Description],Vendor,
      Invoice,Project,TempCounter
      )
Select [Journal ID],
      [Date],CASE When [Val Basis] = 'PY999' THEN 6 ELSE 2 END As TransactionTypeID,
      Dept,LEFT(Account,5) AS MainExpenseAccount,SUBSTRING(Account,6,3) AS SubCode,ISNULL([Alloc Idx],'') AS AllocationIndex,
      Voucher,Amount,Descr,Vendor,
      Invoice,ISNULL([TC Project],'') As Project,[ID]
From TEMPGLIMPORT
0
stopher2475
Asked:
stopher2475
  • 3
  • 2
1 Solution
 
rafranciscoCommented:
Make sure that the Date field in TEMPGLIMPORT all has a value.  To verify, execute this:

SELECT * FROM TEMPGLIMPORT
WHERE Date IS NULL

If there are records returned, then this is the problem.
0
 
stopher2475Author Commented:
It appears some of the dates are not importing correctly from the spreadsheet in a previous step
They are in this format:
2/22/2005 00:00:00
Where as the other dates in the spread sheet are like this:
2/22/2005
How would I fix this?
I used the dts wizard to create the import part.
0
 
rafranciscoCommented:
In your spreadsheet, try Find and Replace.  You look for the string '00:00:00' and replace it with an empty string.  Given this, all dates will be of the same format.
0
 
stopher2475Author Commented:
I know how to do that. Unfortunately my users running this DTS procedure aren't that quick. I'm looking for a one click method to get their data updates into this database.
0
 
rafranciscoCommented:
Another option you have is to change the data type of your Date field in your temp table to varchar.  Then in your query, CAST that column to datetime data type.  SQL Server will take care of the dates whether the time is there or not.

Insert Into Journal(
     [JournalID],
     [Date],TransactionTypeID,
     BudgetCode,MainExpenseAccount,SubCode,AllocationIndex,
     Voucher,Amount,[Description],Vendor,
     Invoice,Project,TempCounter
     )
Select [Journal ID],
     CAST([Date] AS DATETIME),CASE When [Val Basis] = 'PY999' THEN 6 ELSE 2 END As TransactionTypeID,
     Dept,LEFT(Account,5) AS MainExpenseAccount,SUBSTRING(Account,6,3) AS SubCode,ISNULL([Alloc Idx],'') AS AllocationIndex,
     Voucher,Amount,Descr,Vendor,
     Invoice,ISNULL([TC Project],'') As Project,[ID]
From TEMPGLIMPORT
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now