error in exec sp_msforeachtable 'delete from ?

Hi Experts,

I'm trying to perform some bulk updates on my database, however getting errors.

This is what I'm currently running
exec sp_msforeachtable 'delete from ? where employeeid in (select employeeid from tmpempid)';

Open in new window

And received those errors below
Msg 207, Level 16, State 1, Line 1
Invalid column name 'employeeid'.
DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Conversion failed when converting the nvarchar value 'BARRY FADIMA' to data type int.

Open in new window

While the first error is expected as not all tables have that column, the other errors are not..
would like to know how to detect which table's are getting this error and how to overcome them?
Thanks
LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
EXEC sp_msforeachtable '
IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(''?'') AND name = ''employeeid'')
BEGIN
    DECLARE @sql varchar(2000)
    SET @sql = ''delete from ? where employeeid in (select employeeid from tmpempid)''
    EXEC(@sql)
END /*IF*/
';
0
Aneesh RetnakaranDatabase AdministratorCommented:
exec sp_msforeachtable 'print "?";delete from ? where employeeid in (select employeeid from tmpempid)'

that will show which table it is processing
0
bfuchsAuthor Commented:
@Scott,
Your suggestion only addressed the known error, which I can ignore it, however still getting errors below
Msg 1934, Level 16, State 1, Line 1
DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'BARRY FADIMA' to data type int.

Open in new window

Thanks,
Ben
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
Sorry about that.  Good luck with your q.
0
Aneesh RetnakaranDatabase AdministratorCommented:
Error1
Just add this statement  before  your delete  "'SET QUOTED_IDENTIFIER ON; delete ...

Error2:
try this query and  "select employeeid from tmpempid" make sure that it returns the integerID's
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
@Aneesh,
Your suggestion helped me figure out which tables are giving me those errors, now how I go about fixing them?
Thanks,
Ben
0
bfuchsAuthor Commented:
@Aanesh,
Right the first error got fixed.
Re the 2nd error, SQL does return integers, so what is the solution?
Thanks,
Ben
0
bfuchsAuthor Commented:
Also is it possible the 2nd error is causing to stop the processes, as after that no other table gets printed?

Thanks,
Ben
0
Aneesh RetnakaranDatabase AdministratorCommented:
I should been clear, ensure that employeeID field in the tmpempid table and the all the other tables are of the same datatype. seems like tmpEmpID table stores some character data which is causing the statement to fail.
0
bfuchsAuthor Commented:
Right, one table is nvarchar(255)
Can I just ignore that table?
How can I ensure the process will continue for all other tables?
Is there a way to enforce the order of tables it executes?
Thanks,
Ben
0
Aneesh RetnakaranDatabase AdministratorCommented:
where employeeid in (select employeeid from tmpempid WHERE employeeid NOT LIKE '%[^0-9]%' )
0
bfuchsAuthor Commented:
Thank you!
0
Mark WillsTopic AdvisorCommented:
1) check your tmpempid table - shere should NOT be anything other than a numeric value in employeeid  -  the data type of the columns should be enough to ensure it is numeric

2) You can use Scotts code, just make sure the column is like an int value - which you can get from sys.columns e.g.
select object_name(c.object_id) as [table],c.name as [column], t.name as [type] from sys.columns c
inner join sys.types t on c.user_type_id = t.user_type_id and t.name like '%int%'

Open in new window


3) you must exclude tempempid from the list of tables to be checked :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.