SMSS 'Invalid column name'

Question

Before you reprimand me about how 'this has been asked here:', I'd like to point out I did indeed google. I even went to page 3 in some cases. shudders

So here's the deal: I'm trying to audit a database we have, setting triggers for UPDATE, INSERT, DELETE statements for several tables. The triggers are created, and linked, succesfully. Each trigger executes a stored-procedure that inserts required data into our tick_audit table.

This information is:

• user_account; to store who changed something
• client_id; to store which client had their data changed
• date_time; the date of the edit
• table_name; the name of the table that was changed
• table_record_id; the id of the record that was changed
• descr; a description of why something was changed
• remote_ip_address; so we can keep tabs where something was changed (internal or external)

The table also has a PRIMARY_KEY, AUTO_INCREMENT id field.

When I try to create the stored-procedure

create procedure update_tick_user
@UserId         varchar(32),
@ClientId       varchar(32),
@Table          varchar(64),
@TableRecord    varchar(512),
@Descr          varchar(128),
@RemoteIP       varchar(16)
as
begin
    insert into tick_audit ('user_account', 'client_id', 'date_time', 'table_name', 'table_record_id', 'descr', 'remote_ip_address')
    values
    (@UserId, @ClientId, getdate(), @Table, @TableRecord, @Descr, @RemoteIP)
end;

I get the following error(s):

Msg 207, Level 16, State 1, Procedure update_tick_user, Line 10 Invalid column name 'user_account'.

This repeats for each column. When I run

exec sp_columns tick_audit

I get all the columns from tick_audit, and even copying their names into the column-fields for the insert, I get the above-mentioned errors. I even get the errors when I simply run

insert into tick_audit 
('user_account', 'client_id', 'date_time', 'table_name', 'table_record_id', 'descr', 'remote_ip_address')
values
('', '', getdate(), '', '', '', '')

Whenever I try an insert, update or delete on a different table, I get no errors. Is there anything I could try to find out if there's a fault in my table, or some super-secret hocus-pocus, ritual-esque method?

Here's what I've tried so far:

• Drop the table (On my feet, many many times), re-create it.
• Google for three hours
• Asked my co-workers
• Put on my thinking cap
• Check if columns actually exists (through exec, select)

Crossing my fingers and hoping someone can help me.

Answer 1

Do not use single quotes arround column names.

create procedure update_tick_user
@UserId         varchar(32),
@ClientId       varchar(32),
@Table          varchar(64),
@TableRecord    varchar(512),
@Descr          varchar(128),
@RemoteIP       varchar(16)
as
begin
    insert into tick_audit (user_account, client_id, date_time, table_name, table_record_id, descr, remote_ip_address)
    values
    (@UserId, @ClientId, getdate(), @Table, @TableRecord, @Descr, @RemoteIP)
end;

Answer 2

Use brackets instead of quotes:

insert into tick_audit ([user_account], [client_id], [date_time], [table_name], [table_record_id], [descr], [remote_ip_address])
values (@UserId, @ClientId, getdate(), @Table, @TableRecord, @Descr, @RemoteIP)

Single quote are for literals. For delimiting object names you should use brackets or double quotes but you can use double quotes only when QUOTED_IDENTIFIER is set to ON.

Answer 3

Remove ' to make insert as below

 insert into tick_audit (user_account, client_id, date_time, table_name, table_record_id, descr, remote_ip_address)
 values (@UserId, @ClientId, getdate(), @Table, @TableRecord, @Descr, @RemoteIP)