SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Msg 207, Level 16, State 3, Line 27
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NaughtyZute
Starting Member

USA
3 Posts

Posted - 05/04/2013 :  04:12:17  Show Profile  Reply with Quote
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

Australia
54 Posts

Posted - 05/04/2013 :  08:46:35  Show Profile  Reply with Quote
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)
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1643 Posts

Posted - 05/10/2013 :  18:21:44  Show Profile  Reply with Quote
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

Edited by - Bustaz Kool on 05/10/2013 18:24:47
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000