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
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.
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
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.
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
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?