asked on

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

Scott Pletcher

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*/
';

Aneesh

exec sp_msforeachtable 'print "?";delete from ? where employeeid in (select employeeid from tmpempid)'

that will show which table it is processing

bfuchs

ASKER

@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

Scott Pletcher

Sorry about that. Good luck with your q.

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bfuchs

ASKER

@Aneesh,
Your suggestion helped me figure out which tables are giving me those errors, now how I go about fixing them?
Thanks,
Ben

bfuchs

ASKER

@Aanesh,
Right the first error got fixed.
Re the 2nd error, SQL does return integers, so what is the solution?
Thanks,
Ben

bfuchs

ASKER

Also is it possible the 2nd error is causing to stop the processes, as after that no other table gets printed?

Thanks,
Ben

Aneesh

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.

bfuchs

ASKER

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

Aneesh

where employeeid in (select employeeid from tmpempid WHERE employeeid NOT LIKE '%[^0-9]%' )

bfuchs

ASKER

Thank you!

Mark Wills

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 :)