Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2

    Unanswered: Error 207 - new column branded invalid

    Hi all

    This one has been driving me steadily mad for most of the afternoon.

    I'm working in SQL2000 sp4. I've built a simple database to take snapshots from three ERPSs that contain related data and then analyse them to look for non-conforming records (items that are flagged differently between two systems, cost conversion errors, etc). The DTS packages all work fine, and suck the records out of the main systems without a problem.

    For info, the DTS packages all work in the same way:
    • Purge a holding table
    • Connect to the source ERPS
    • Populate the holding table via a SELECT statement
    • Invoke a stored proc to make the required changes in the main table


    Similarly, the stored procs all work in the same way:
    • Add records from the holding table to the main table that aren't already there
    • Update any records common to both:
      • If there's a record date field, have been updated in the holding table more recently
      • Otherwise, match on key fields and differ on detail fields
    • Delete records from the main table that aren't in the holding table


    The trouble started when I modified two of the tables to include the data that the record was last amended in the source ERPS. When I tried to incorporate this new column into the relevant stored proc, performing a syntax check resulted in error 207 - invalid column name.

    I did some checking, and found out that stored procs tend to rely on what the table looked like when the proc was created, rather than what it now looks like. Accordingly, I tried creating a new proc. I got the same result. What have I missed?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First and foremost, three new versions of SQL Server. SQL 2000 is no longer supported (at all, in any way, shape, or form) by Microsoft. Upgrade soon, any way that you can!

    If the column was removed, no amount of recreating or recompiling will do you any good. You'll have to either fix or remove the reference to the missing column.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Sadly, decisions about SQL versions are *way* above my pay grade. That said, we do have some 2005 and 2008 servers - I just don't have access to them yet.

    Also, I haven't removed a column. I've added one - the stored procs just won't acknowledge that it's there!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A 207 error can only occur when you specify the name of a column that is not in the table or view where it is referenced. You can do this by means of a typographical error (typing something like IOD when you meant ID), or by removing a column from a table or view and leaving the DML statement intact.

    Simply adding a column to a table can't produce a 207 error without some additional help!

    Let's try this from a different perspective. Can you post the offending SQL Statement along with the statements before and after it? Even the full text of the error message might help a bit.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    It's on my work laptop, and it's gone 22:00 BST here, so I'll pick this up in the morning. You have made me think about part of the problem, though - I might have screwed up something in an intermediate view that's used for the update. If I used "SELECT *" at any point, I'd have an unqualified reference floating around...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Okay, original table DDL :
    Code:
    CREATE TABLE [dbo].[tblHoldingOraStockBal] (
    	[OraWarehouse] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraItemNo] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraUnit] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraPhysStock] [decimal](20, 10) NULL ,
    	[OraDualUnit] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraDualPhysStock] [decimal](20, 10) NULL ,
    )
    CREATE TABLE [dbo].[tblOraStockBal] (
    	[OraWarehouse] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraItemNo] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraUnit] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraPhysStock] [decimal](20, 10) NULL ,
    	[OraDualUnit] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraDualPhysStock] [decimal](20, 10) NULL ,
    )
    Stored procedure code:
    Code:
    CREATE PROCEDURE dbo.uspRefreshOraStockBal AS
    
    --Insert new records into the main table
    INSERT INTO tblOraStockBal
    	(
    	OraWarehouse
    ,	OraItemNo
    ,	OraUnit
    ,	OraPhysStock
    ,	OraDualUnit
    ,	OraDualPhysStock
    	)
    SELECT DISTINCT
    	h.OraWarehouse
    ,	h.OraItemNo
    ,	h.OraUnit
    ,	h.OraPhysStock 
    ,	h.OraDualUnit
    ,	h.OraDualPhysStock
    FROM
    	vwOraStockBal h
    ,	vwTranslateWarehouses w
    WHERE
    	h.OraWarehouse + h.OraItemNo NOT IN
    	(SELECT
    		OraWarehouse + OraItemNo
    	FROM
    		tblOraStockBal
    	)
    AND
    	h.OraWarehouse = w.TrnOra
    
    --Amend existing records where the stock balance has changed
    UPDATE
    	s
    SET
    	s.OraPhysStock = h.OraPhysStock
    ,	s.OraDualPhysStock = h.OraDualPhysStock
    FROM
    	tblOraStockBal s
    INNER JOIN
    	vwOraStockBal h
    ON
    	s.OraWarehouse = h.OraWarehouse
    AND
    	s.OraItemNo = h.OraItemNo
    WHERE
    	s.OraPhysStock <> h.OraPhysStock
    OR
    	(
    	s.OraDualPhysStock IS NOT NULL
    AND
    	h.OraDualPhysStock IS NOT NULL
    AND
    	s.OraDualPhysStock <> h.OraDualPhysStock
    	)
    
    --Delete records where there is no longer a stock balance
    DELETE
    FROM
    	tblOraStockBal
    WHERE
    	OraWarehouse + OraItemNo NOT IN (SELECT h.OraWarehouse + h.OraItemNo FROM vwOraStockBal h)
    GO
    View SQL statements:
    Code:
    CREATE VIEW dbo.vwOraStockBal
    AS
    SELECT     TOP 100 PERCENT OraWarehouse, OraItemNo, OraUnit, SUM(OraPhysStock) AS OraPhysStock, OraDualUnit, SUM(OraDualPhysStock) 
                          AS OraDualPhysStock
    FROM         dbo.tblHoldingOraStockBal h
    GROUP BY OraWarehouse, OraItemNo, OraUnit, OraDualUnit
    ORDER BY OraWarehouse, OraItemNo
    
    CREATE VIEW dbo.vwTranslateWarehouses
    AS
    SELECT TrnOra, TrnPri, TrnS21, TrnCurrent
    FROM tblZZZTranslations
    WHERE TrnType = 'Warehouses'
    The holding table is run through the aggregation view because the source table also includes location information that I don't need.
    The translation view exists to deal with the fact that some warehouses have different codes in different systems, and some systems hold legacy warehouses that can be excluded from this database.

    Modified table DDL:
    Code:
    CREATE TABLE [dbo].[tblHoldingOraStockBal] (
    	[OraWarehouse] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraItemNo] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraUnit] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraPhysStock] [decimal](20, 10) NULL ,
    	[OraDualUnit] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraDualPhysStock] [decimal](20, 10) NULL ,
    	[OraStockLastUpdated] [datetime] NULL 
    )
    
    CREATE TABLE [dbo].[tblOraStockBal] (
    	[OraWarehouse] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraItemNo] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraUnit] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraPhysStock] [decimal](20, 10) NULL ,
    	[OraDualUnit] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[OraDualPhysStock] [decimal](20, 10) NULL ,
    	[OraStockLastUpdated] [datetime] NULL 
    )
    Modified stored procedure code that fails:
    Code:
    INSERT INTO tblOraStockBal
    	(
    	OraWarehouse
    ,	OraItemNo
    ,	OraUnit
    ,	OraPhysStock
    ,	OraDualUnit
    ,	OraDualPhysStock
    ,	OraStockLastUpdated
    	)
    SELECT DISTINCT
    	h.OraWarehouse
    ,	h.OraItemNo
    ,	h.OraUnit
    ,	h.OraPhysStock 
    ,	h.OraDualUnit
    ,	h.OraDualPhysStock
    ,	h.OraStockLastUpdated
    FROM
    	vwOraStockBal h
    ,	vwTranslateWarehouses w
    WHERE
    	h.OraWarehouse + h.OraItemNo NOT IN
    	(SELECT
    		OraWarehouse + OraItemNo
    	FROM
    		tblOraStockBal
    	)
    AND
    	h.OraWarehouse = w.TrnOra
    Apologies for the data dump!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Er, yes.

    Talking it through with a friend, I realised that I need to add the column to vwOraStockBal before it can be added from there... :$

    Hopefully, this will serve as a cautionary tale for others!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    That was the problem. Adding the column to the view made the error go away!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I love cautionary tales! I love them even more when they are someone else's cautionary tales!

    Thanks!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •