We help IT Professionals succeed at work.

SQL Server 2008 R2 - Invalid column name

4,956 Views
Last Modified: 2014-05-06
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
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
One advice...do not use EXPLICIT transactions in SQL.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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.
lcohanDatabase Analyst
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Author

Commented:
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
You can't alias a table in a "DELETE FROM"
change this line:
CLAIM_FACILITY_DETAIL_2014_02  WHERE [FILE_ID] = 1204301 AND
Looks like you use the "T" a couple lines below also on CLM_KY in your where clause

Author

Commented:
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 PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.

Author

Commented:
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

Author

Commented:
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
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Author

Commented:
I found the problem,

turns out that one of the tables:
'CLAIM_FACILITY_DETAIL'

Did not have a:

File_Id

as a field

Thanks

Author

Commented:
I found the problem,

turns out that one of the tables:
'CLAIM_FACILITY_DETAIL'

Did not have a:

File_Id

as a field

Thanks

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.