problem with sp_MSForEachTable

[fikir]

what is wrong with this query

EXEC sp_MSForEachTable 'IF left(''?'',12) = ''[FastActRes]''
begin
UPDATE ?
SET TO_BE_LOADED = ''Yes''
end'

Only those with schema FastActRes have TO_BE_LOADED column
and trying to update this column.

There are other tables with different schema, theses tables don't have TO_BE_LOADED column,

when I am trying to run the above query, it is complaining that 'Invalid column name TO_BE_LOADED', but it still updates the TO_BE_LOADED for those tables with the schema FastActRes.
where is the error coming, is the script cheching the other tables with different schema also?

thanks,

 

[ESquared]

There must be a table in FastActRes that doesn't have the TO_BE_LOADED column.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[mrdenny]

Try using something like this instead.

exec sp_MSforeachtable 'if exists (select * from sys.columns where object_id = object_id(''?'') and name = ''TO_BE_LOADED'')
begin
    update ?
    set TO_BE_LOADED = ''Yes''
end'

exec sp_MSforeachtable 'if exists (select * from syscolumns where id = object_id(''?'') and name = ''TO_BE_LOADED'')
begin
    update ?
    set TO_BE_LOADED = ''Yes''
end'

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[fikir]

I am sorry guys, I am late to respond

Thank you for your suggestions. I tried what Danny suggested, I am still getting the same error

 

[fikir]

FYI

I went through each tables and every table has the column 'TO_BE_LOADED'.

But some of the tables don't have data in them

Do you think the error is because of those tables not having data in them?

Thanks

 

[ESquared]

EXEC sp_MSForEachTable 'IF left(''?'',12) = ''[FastActRes]''
                        begin
                             PRINT ''UPDATE ?
                             SET TO_BE_LOADED = ''''Yes''''''
                         end'

Now copy the printed result and paste it into a new window. Run it and you'll get a line number where the error is. See table without the column.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[fikir]

Thank you all

when I run the result of the Esquared code, it runs fine;

I presume the problem is may be the sp_MSForEachTable procedure with update statment;

I am quitting this proc and write one update statment for each table

Thanks again