February 2, 2009 at 10:23 am
I have the strangest problem that I have ever seen. I am trying to add a few rows to a table on SQL Server 2005 through SQLSMS. I can't get it to accept the row. It keeps complaining that I can't insert a null into the field. I AM NOT TRYING TO unless 11725 is considered a NULL value somehow.
Now if I put a NULL in the field it takes it just fine. To make matters worse when I try to add the next row it won't take either a null or a value!!
here are the rows:
11001Executive900111725NULLNULL
11011Executive - Legal9002NULLNULLNULL
February 2, 2009 at 10:33 am
Please take the time to read the first article linked in my signature block below regarding asking for assistance. Follow the guidelines in that article and post your table DDL, etc. You will get much better answers to your questions.
Based on your post, it is hard to tell you what is going on.
February 2, 2009 at 10:39 am
To be clear I am not doing this via code. I am typing directly into the table row. here is the ddl
USE [GAGInventory]
GO
/****** Object: Table [dbo].[Store] Script Date: 02/02/2009 10:38:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Store](
[StoreID] [int] IDENTITY(1,1) NOT NULL,
[BusinessCodeID] [int] NULL,
[StoreName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StoreNumber] [int] NULL,
[ShippingAddressID] [int] NULL,
[ExceptionStartDate] [datetime] NULL,
[ExceptionEndDate] [datetime] NULL,
CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED
(
[StoreID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
February 2, 2009 at 10:45 am
john (2/2/2009)
To be clear I am not doing this via code. I am typing directly into the table row.
Why?
Any triggers on the table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2009 at 10:47 am
I need to add this records quickly and I don't have time to add the UI right now. Yes there is a trigger on this table.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER TRIGGER [dbo].[Store_Update_Trigger]
ON [dbo].[Store]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @newAddressID int,
@storeID int;
-- Insert statements for trigger here
-- Save OLD record.
select @newAddressID=del.ShippingAddressID from deleted del;
select @storeID =del.storeID from deleted del;
if not exists (select storeID,ShippingAddressID from StoreOldShippingAddressJoin
where storeID=@storeID and ShippingAddressID=@newAddressID)
INSERT INTO StoreOldShippingAddressJoin
(StoreID,
ShippingAddressID,
CreatedDate)
SELECT
del.StoreID,
del.ShippingAddressID,
getdate()
FROM deleted del
END
February 2, 2009 at 10:57 am
john (2/2/2009)
I need to add this records quickly and I don't have time to add the UI right now.
Use a query window and write insert statements?
What's the definition of StoreOldShippingAddressJoin?
Re that trigger, what happens when more than one row is inserted in a single statement?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2009 at 11:00 am
Are you inserting or updating data? You say inserting in your original post, but I'm just checking to be sure.
February 2, 2009 at 11:00 am
USE [GAGInventory]
GO
/****** Object: Table [dbo].[StoreOldShippingAddressJoin] Script Date: 02/02/2009 10:59:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StoreOldShippingAddressJoin](
[StoreOldShippingAddressJoinID] [int] IDENTITY(1,1) NOT NULL,
[StoreID] [int] NOT NULL,
[ShippingAddressID] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
CONSTRAINT [PK_StoreOldShippingAddressJoin] PRIMARY KEY CLUSTERED
(
[StoreOldShippingAddressJoinID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
February 2, 2009 at 11:01 am
I am typing (inserting) new data.
February 2, 2009 at 11:07 am
I don't know why but when I use a query window to to the inserts it works. Now my head hurts.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply