Avatar of Amour22015
Amour22015
 asked on

SQL Server 2008 R2 - Invalid column name

Hi Experts,

I have this error message:

When I use SQLStatement:
SELECT DISTINCT
      FILE_ID,
                TBL_NM
  FROM DATABASE_TABLESInvalid column name

  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

This works fine...

But when I use the same SQLStatement with this added on:
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')


I get the error:
Invalid column name

I think this is the section within the SP that is giving the problem, because it is a new section:
BEGIN
            BEGIN TRAN
            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 varchar(20))+ ' 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 varchar(20)) + ')'
                  exec sp_executesql @sql;
                  
                  SET @rc = @@ROWCOUNT
            COMMIT TRAN


But to see anything:
Please see attached Stored Procedure and Snap Shots

Thank you for helping me...
Purge-by-fileid.docx
SnapshotPassingVariables.docx
Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Amour22015

8/22/2022 - Mon
lcohan

One advice...do not use EXPLICIT transactions in SQL.
Jim Horn

>I get the error: Invalid column name
Double-clicketh on this error message.  Eyeballeth where the cursor jumps, which will be the source of this error, and telleth us which line this is.
SOLUTION
lcohan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Amour22015

ASKER
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Amour22015

ASKER
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
UnifiedIS

You can't alias a table in a "DELETE FROM"
change this line:
CLAIM_FACILITY_DETAIL_2014_02  WHERE [FILE_ID] = 1204301 AND
UnifiedIS

Looks like you use the "T" a couple lines below also on CLM_KY in your where clause
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Amour22015

ASKER
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
Scott Pletcher

You can alias a table source in the DELETE statement (although you can't alias the object itself directly).

The code above (minus the "Er" after it) runs perfectly stand-alone.  Thus, there must be something else going on, perhaps one of the things stated in the error message.
Scott Pletcher

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Amour22015

ASKER
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
Amour22015

ASKER
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
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Amour22015

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Amour22015

ASKER
I found the problem,

turns out that one of the tables:
'CLAIM_FACILITY_DETAIL'

Did not have a:

File_Id

as a field

Thanks
Amour22015

ASKER
I found the problem,

turns out that one of the tables:
'CLAIM_FACILITY_DETAIL'

Did not have a:

File_Id

as a field

Thanks