Invalid Object Name Sqlstate 42s02 Error 208. The Step Failed
object name 'ITEMS'. [SQLSTATE 42S02] (Error 208). The step failed. 0 Hi, I created a job which runs at 4 AM every morning. Recently i found that the job is failed and from the log history i got an error as "Executed as user: XXX\SQLServices. Invalid object name 'Items'. [SQLSTATE 42S02] (Error 208). The step failed. " But when i run the same stored procedure manually from SSMS i am not getting any error. How do i resolve this? do i have any other chance to check the log of my job to trace this error? more ▼ 7 total comments 440 characters / 85 words asked Sep 17, 2013 at 11:23 AM in Default Bhuvans 220 ● 19 ● 19 ● 24 Has the job ever succeeded or has it failed from the start? Sep 17, 2013 at 12:06 PM KenJ What type of job step is it? T-SQL, SSIS, etc? Do you know what changed prior to 1 September? Sep 17, 2013 at 01:17 PM KenJ its a T-SQL job. We added two columns in the stored procedure. basically this job inserts data from one db to another db by using stored procedure. Sep 17, 2013 at 01:39 PM Bhuvans Is there a different step in the job that might create or drop "Items?" Is Items in both databases or just one? Is it in the expected schema? Can the SQL Agent user see Items (I'm sure it does, but thought I'd ask - maybe it's a proxy account without permission)? Sep 17, 2013 at 03:47 PM KenJ No there's no drop statements in job. Items are in both the databases. yes SQL server agent user can see the user items Sep 18, 2013 at 03:25 AM Bhuvans show 2 more replies show all comments add new comment (comments are locked) 10|1200 characters needed characters left ▼ Everyone Moderators Original poster and moderators Other... Viewable by all users 2 answers: sort voted first ▼ oldest newest voted first 0 Are you using a three part naming conventions? Try to use databasename.schema.table in the proc. more ▼ 3 total comments 100 characters / 15 words answered Sep 17, 2013 at 03:49 PM raadee 4.5k ● 12 ● 17 ● 22 edited Sep 17, 2013 at 03:50 PM this cannot be used because this job has to run in 10 databases then i need to change all the stored procedures in 10 databases and job is working fine before 10 days actually... Sep 18, 2013 at 03:27 AM Bhuvans just out of curiosity, how does the stored procedure move data from one database to another without using 3 part names? Synonyms? Sep 18, 2013 at 03:37 AM KenJ Hi KenJ... sorry for the late reply. I cannot do like this because we have nearly 25 client DB's so our build manager doesn't accept these scripts.
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like https://ask.sqlservercentral.com/questions/106501/executed-as-user-xxsqlservices-invalid-object-name.html you, helping each other. Join them; it only takes a minute: Sign up How to expose more information about the failure of a stored proc in SQL agent up vote 0 down vote favorite I have a SQL agent job setup and in that job there is a step to execute a stored proc. If that stored http://stackoverflow.com/questions/5267212/how-to-expose-more-information-about-the-failure-of-a-stored-proc-in-sql-agent proc fails then the SQL agent job will display an error message but there is no other information. Something like a stacktrace or at least the stored proc that was running and the line number would be highly useful. e.g. If the following stored proc is executed then an error message like "Executed as user: NT AUTHORITY\NETWORK SERVICE. Start [SQLSTATE 01000] (Message 0) Invalid object name 'NonExistentTable'. [SQLSTATE 42S02] (Error 208). The step failed." with no indication where exactly the failure occured. CREATE PROCEDURE TestSpLogging AS BEGIN PRINT 'Start' SELECT * FROM NonExistentTable PRINT 'End' END What's the best way to expose this information? sql-server error-handling sql-server-agent share|improve this question asked Mar 10 '11 at 23:30 Chris Herring 2,63812141 add a comment| 2 Answers 2 active oldest votes up vote 1 down vote One way to do this would be add some error handling to the stored procedure. Here is a simple method we use here is something like this declare @Error int ,@ErrorMsg varchar(1000) ,@StepName varchar(500) ,@ProcedureName sys
SERVER - Error: Fix - Msg 208 - Invalid object name ‘dbo.backupset' - Invalid object name ‘dbo.backupfile' August 31, 2012Pinal DaveSQL, SQL http://blog.sqlauthority.com/2012/08/31/sql-server-error-fix-msg-208-invalid-object-name-dbo-backupset-invalid-object-name-dbo-backupfile/ Server, SQL Tips and Tricks53 commentsJust a day before I got a very interesting email. Here is the email (modified a bit to make it relevant to this blog post)."Pinal,We are facing a very strange issue.One of our query related to backup files and backup set has invalid object stopped working suddenly in SSMS. It works fine in application where we have and in the stored procedure but when we have it in our SSMS it gives following error.Msg 208, Level 16, State 1, Line 1 Invalid object name ‘dbo.backupfile'.Here are our queries which we are trying to invalid object name execute. SELECT name, database_name, backup_size, TYPE,
compatibility_level, backup_set_id
FROM dbo.backupset;
SELECT logical_name, backup_size, file_type
FROM dbo.backupfile;This query gives us details related to backupset and backup files when the backup was taken."When I receive this kind of email, usually I have no answers directly. The claim that it works in stored procedure and in application but not in SSMS gives me no real data. I have requested him to very first check following two things:If he is connected to correct server? His answer was yes.If he has enough permissions? His answer was he was logged in as an admin.This means there was something more to it and I requested him to send me a