Msg 207, Level 16, State 3, Line 27

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Msg 207, Level 16, State 3, Line 27

Author

Topic

NaughtyZute
Starting Member

3 Posts

Posted - 2013-05-04 : 04:12:17

A little background: When an extra line feed was inadvertently added to a flat file, an import process translated that LF to a row where the only fields not null are LOAD_DATE and SOURCE_FILE. Now I need to locate and delete the bad rows.

My problem is a Msg 207 error when I try to add a PROCESSED flag to the #temp table containing target table names (or in the test script below... just one name). Can someone help me figure out why I'm getting this error? Thanks!

DECLARE

@T_NAME sysname,
@cCount int,
@sql nvarchar(4000)

SET NOCOUNT ON

CREATE TABLE #temp
(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname,
TABLE_NAME sysname,
TABLE_TYPE varchar(32),
REMARKS varchar(254),
)
--TESTING
SELECT * FROM #temp

INSERT INTO #temp
-- TARGET TABLE STRING
EXEC sp_tables 'TBL_SQL_TEXT_CS_CO%'

EXEC sp_executesql N'ALTER TABLE #temp ADD PROCESSED BIT NOT NULL DEFAULT (0)'

WHILE (SELECT COUNT(*) FROM #temp WHERE PROCESSED = 0) > 0

BEGIN

-- GET FIRST UNPROCESSED TABLE NAME
SET @T_NAME = (SELECT TOP 1 TABLE_NAME FROM #temp WHERE PROCESSED = 0)
-- GET COLUMN COUNT
SET @cCount = (SELECT count(*) FROM information_schema.columns WHERE table_name = @T_NAME)

-- GET COLUMN NAMES
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @T_NAME ORDER BY ordinal_position;

-- HERE I NEED TO DO A CURSOR OR ITERATE THROUGH COLUMNS TO FIND ROWS WHERE *ALL* FIELDS
-- ARE NULL *EXCEPT* : 'LOAD_DATE' & 'SOURCE_FILE'

SET @sql = 'SELECT COUNT(*) FROM ' + @T_NAME
EXEC sp_executesql @sql
UPDATE #temp SET PROCESSED = 1 WHERE TABLE_NAME = @T_NAME
END

DROP TABLE #temp

_____________________________________

Msg 207, Level 16, State 3, Line 27
Invalid column name 'PROCESSED'.
Msg 207, Level 16, State 3, Line 32
Invalid column name 'PROCESSED'.
Msg 207, Level 16, State 1, Line 44
Invalid column name 'PROCESSED'.

If you don't like your life; change the way you're living.

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-05-04 : 08:46:35

Do you have to add PROCESSED to the #temp table?
Why not replace the following with a cursor

WHILE (SELECT COUNT(*) FROM #temp WHERE PROCESSED = 0) > 0

BEGIN

-- GET FIRST UNPROCESSED TABLE NAME
SET @T_NAME = (SELECT TOP 1 TABLE_NAME FROM #temp WHERE PROCESSED = 0)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-05-10 : 18:21:44

Do you have to use dynamic SQL to perfrom the ALTER TABLE when adding the new column? The 207 error indicates that the column isn't recognized as existing. Maybe the column is added but "goes out of scope" when the call to sp_executesql returns. (This wouldn't happen with a permanent table so maybe this is some bizarre side effect of using ephemeral schema objects). Try issuing the ALTER TABLE directly and see if the issue resolves itself.

Also, are you getting the error only at run-time or won't the code execute because of the error being reported?

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources