[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name 'column_name'

Question

In my ColdFusion 11 website (with SQL Server 2008 R2), the following cfquery is throwing the above error:

<cfquery name="deleteGrantModificationItems" datasource="#dsource#">
  DELETE col_key FROM myTable
  WHERE col_key = <cfqueryPARAM value = "#appkey#" CFSQLType = "CF_SQL_VARCHAR">
</cfquery>

When I run the sql profiler to capture the query, I get:

Declare @p1 int
Set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 varchar(8000)',N'DELETE col_key FROM myTable
WHERE col_key = @P1 ','000000001644'
select @p1 

when I run the above in the Query Analyzer, I get the following error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'col_key'.
Msg 8180, Level 16, State 1, Procedure sp_prepexec, Line 1
Statement(s) could not be prepared.

I've been unable to find the cause of the error.

Answer 1

You have a syntax error because you should use DELETE FROM table_name ... like:

<cfquery name="deleteGrantModificationItems" datasource="#dsource#">
  DELETE FROM myTable
  WHERE col_key = <cfqueryPARAM value = "#appkey#" CFSQLType = "CF_SQL_VARCHAR">
</cfquery>

DELETE doc:

[ WITH [ ,...n ] ] DELETE

[ TOP ( expression ) [ PERCENT ] ]

[ FROM ]