SQL: error on invalid column name that does exist

0
votes

i'm trying to create a package that will copy data from a file and put it into a new table. so far so good. but should the package fail for some reason, i want it to truncate the newly added data. so, i want it to execute this statement

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TABLE_NAME') AND type in (N'U')) DELETE FROM TABLE_NAME WHERE date='2015-11-10'

but i keep getting this error: "Error: 0xC002F210 at Delete From MYTABLE, Execute SQL Task: Executing the query "IF EXISTS (SELECT * FROM sys.objects WHERE object_..." failed with the following error: "Invalid column name 'date'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

but MYTABLE does have a column called "date"... i just can't figure out what the problem is, and google hasn't been able to help me.

2
sqlsql-serverbids
Date is a keyword. Put it in brackets like this: [date]. Might want to do the same thing with [type] - Bob Kaufman

2 Answers

0
votes

I believe "date" is a reserved word, try using [date]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TABLE_NAME') AND [type] in (N'U')) DELETE FROM TABLE_NAME WHERE [date]='2015-11-10'

SQL Server reserved Words: MSDN

0
votes

Chuck the column name in brackets as date is a reserved word in SQL Server

[Date]