SQL Server 2008 R2 - Invalid column name

Ok,

I execute this from SSIS 2008 Package it turns from yellow to red, then I stop debug to see the error in the Output window.

So
Jim Horn

Double clicking on the error does nothing.


lcohan
What is?
EXPLICIT transactions

also what part did you change in the SP


Thanks

After changing the SP to what
lcohan

mentions...

I get the same error:

This is the entire error:
Error: 0xC002F210 at Purge Claims, Execute SQL Task: Executing the query "EXEC Purge_By_FileId ?,?,?,?" failed with the following error: "Invalid column name 'FILE_ID'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Error: 0x0 at Purge Claims: IF OBJECT_ID ('dbo.CLAIM_FACILITY_DETAIL_2014_02') IS NOT NULL AND OBJECT_ID ('CK_CLAIM_FACILITY_DETAIL_2014_02_Max_File_ID') IS NOT NULL ALTER TABLE dbo.CLAIM_FACILITY_DETAIL_2014_02 DROP CONSTRAINT CK_CLAIM_FACILITY_DETAIL_2014_02_Max_File_ID ; DELETE FROM CLAIM_FACILITY_DETAIL_2014_02 FROM CLAIM_FACILITY_DETAIL_2014_02 AS T WHERE T.[FILE_ID] = 1204301 AND
                        EXISTS(SELECT #CLAIM_TEMP.CLM_KY
                                 FROM #CLAIM_TEMP
                                 WHERE #CLAIM_TEMP.CLM_KY = T.CLM_KY AND
                                       #CLAIM_TEMP.FILE_ID = 1204301)
Er

So,

It looks like this is working when I do just one Select statement:
SELECT DISTINCT
      FILE_ID,
                TBL_NM
  FROM DATABASE_TABLES
  Where
      TBL_STAT = 'purge_elig' And
      TBL_Typ = 'Claim' And
      TBL_NM not in (Select Distinct TBL_NM From DATABASE_TABLES Where TBL_TYP = 'CLAIM' and TBL_STAT = 'ACTIVE')
Go



BUT when I do multible Select Statements:
SELECT DISTINCT
      FILE_ID,
                TBL_NM
  FROM DATABASE_TABLES
  Where
      TBL_STAT = 'purge_elig' And
      TBL_Typ = 'Claim' And
      TBL_NM not in (Select Distinct TBL_NM From DATABASE_TABLES Where TBL_TYP = 'CLAIM' and TBL_STAT = 'ACTIVE')
Go
SELECT DISTINCT
      FILE_ID,
                TBL_NM
  FROM DATABASE_TABLES
  Where
      TBL_STAT = 'purge_elig' And
      TBL_Typ = 'CLAIM_ITEM' And
      TBL_NM not in (Select Distinct TBL_NM From DATABASE_TABLES Where TBL_TYP = 'CLAIM_ITEM' and TBL_STAT = 'ACTIVE')
Go
SELECT DISTINCT
      FILE_ID,
                TBL_NM
  FROM DATABASE_TABLES
  Where
      TBL_STAT = 'purge_elig' And
      TBL_Typ = 'CLAIM_FACILITY_DETAIL' And
      TBL_NM not in (Select Distinct TBL_NM From DATABASE_TABLES Where TBL_TYP = 'CLAIM_FACILITY_DETAIL' and TBL_STAT = 'ACTIVE')


IT does not.

and you are saying that this is the reason:
You can't alias a table in a "DELETE FROM"
change this line:
Looks like you use the "T" a couple lines below also on CLM_KY in your where clause

   BEGIN
            SELECT @sql = 'IF OBJECT_ID (''dbo.' + @TBLNM + ''') IS NOT NULL AND OBJECT_ID (''' + 'CK_' + @TBLNM + '_Max_File_ID'') IS NOT NULL ' +
           'ALTER TABLE dbo.' + @TBLNM + ' DROP CONSTRAINT CK_' + @TBLNM + '_Max_File_ID ;' +
            ' DELETE FROM ' + @TBLNM + ' FROM ' + @TBLNM + ' AS T' +
           ' WHERE T.[FILE_ID] = ' + cast(@fileId as sysname)+ ' AND
                        EXISTS(SELECT #CLAIM_TEMP.CLM_KY
                                 FROM #CLAIM_TEMP
                                 WHERE #CLAIM_TEMP.CLM_KY = T.CLM_KY AND
                                       #CLAIM_TEMP.FILE_ID = ' + cast(@fileId as sysname) + ')'
   

Take out were I am referring to the "T"?

Thanks

I am sorry there is only 2 parameters that I am referencing:
FILE_ID = Int32,
TBL_NM = String

This is a connection: OLE DB
Using a "Foreach Loop Container"

If you look at the snapshot posted you can see

TabVal = object System Object

Is this not the correct way to do this OR am I going to have to name the 2 variables sep on each Select Statement?  and instead of having 2, it would be 2 * 3 = 6 different variables?

Thanks

I do have to say that even when it appeared to be working with the ONE select Statement, it failed to delete the table in question...

So it looks like this whole
@sql =

statement is not working at all....

It was mentioned:
The multiple statements you're posting don't match the dynamic sql you're posting; they reference different columns in the tables.  "TBL_STAT", for example, doesn't appear anywhere in the dynamic sql.

I know this is grabbing the correct varibles because in the SP I have this:
      PRINT 'STARTING DELETION OF: ' + @TBLNM
      PRINT 'THIS IS THE VALUE OF FILE_ID: ' + cast(@fileId as sysname)
When I execute the package I get:
STARTING DELETION OF: CLAIM_2014_02
THIS IS THE VALUE OF FILE_ID: 1204301

The @sql
is just not deleting the Table....

Thanks

Yes it did drop the Constraint...

looks like  ScottPletcher is correct on that one.  I will have to look into that closer.

Now back to the question...

You see I am taking from 1 to as many are in database_tables for the FILE_ID & TBL_NM

so really it is FILE_ID(S) & TBL_NM(S) and trying to do that with 3 Select Statements which will cause "Foreach Loop Container" to loop as many times as needed

Then using the @sql
where it will change the name(s) of the Table(s) & File_Id(s) to match what is coming in from database_tables

If that makes sense?

Maybe I need 3 @sql statements for each Select Statement?

Thanks

I found the problem,

turns out that one of the tables:
'CLAIM_FACILITY_DETAIL'

Did not have a:

File_Id

as a field

Thanks