ambiguous column name in delete statement

Question

Post reply

ambiguous column name in delete statement

Martin Stephenson

SSCrazy

Points: 2950
More actions
October 23, 2018 at 7:53 am

#375772

Inside a stored proc for an ETL process is the following code
declare @whereclause_1 nvarchar(400)
DECLARE @sql_stmt nvarchar (500) = 'DELETE FROM [dbo].[fact_table] WHERE 1=1 ' + @WhereClause_1
EXEC sp_executesql
@query = @SQL_STMT,
@params = N'@Record_Count INT OUTPUT',
@Record_Count = @Record_Count OUTPUT
when it runs it fails and the error log for sp records an error message of ambiguous column name.
I captured the @sql_stmt and it resolves out to
DELETE FROM [dbo].[fact_table] WHERE 1=1 AND LAST_MODIFIED Between '2018-09-01' AND '2018-10-31'
and it runs no problem
I'm stumped

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 1

I usually put a commented out print before the exec so you can uncomment it in these situations
PRINT @sql_stmt

Martin Stephenson SSCrazy Points: 2950 More actions · Answer 2

I dropped this in so I can see what its doing which is how I resolved the sql
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Admin'

, @recipients = 'martin.stephenson@email.com'

, @subject = 'fact table'

, @body = @SQL_STMT

sgmunson SSC Guru Points: 110640 More actions · Answer 3

I'm guessing that the error comes from somewhere else, unless there's a security context difference between the way it runs in production and the way you are running it.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Martin Stephenson SSCrazy Points: 2950 More actions · Answer 4

yeah, the error was there as originally the delete was trying to use an alias to the table, further on in the SP it enters new data and wasn't referencing the table using an alias it took me a while to remember the error message can refer to inserts a swell as deletes. I inserted the email code into each load section until I found the offender but for a while I was convinced it was the delete.