Invalid Object Name Sqlstate 42s02 Error 208
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: SQL Job Fails Because of Invalid Object Name SQL Server > Transact-SQL Question 0 Sign in to vote Why would a SQL Job in SQL Server 2008 R2 complain about an object not being valid when the job is not “instructed” to access (or use) that particular object? My SQL Server hosts a database called dbsVendors. In dbsVendors, I have a stored procedure, spValidate_Emails, plus a SQL Job called jobUpdateEmails that will execute the stored procedure. At one time, the same SQL instance use to host a database called dbsAudits that had a table called tblAudits. There were no plans to continue using dbsAudits and this database had not been used in over a year, so it was dropped 3 or 4 weeks ago. Since then, the jobUpdateEmails has been throwing the following error: Date: 3/18/2014 3:50:00 AM Log: Job History (jobUpdateEmails) Step ID: 1 Server: FooSQL\Fobar Job Name: jobUpdateEmails Step Name: Validate_Email_Addresses Duration: 01:02:00 Sql Severity: 16 Sql Message ID: 208 Operator Emailed: Operator Net sent: Operator Paged: Retries Attempted: 0 Message: Executed as user: NT AUTHORITY\NETWORK SERVICE. Invalid object name 'dbsAudits.dbo.tblAudits'. [SQLSTATE 42S02] (Error 208). The step failed. The error message mentions that the SQL Job references the old database, even though the job contains no references to dbsAudits. The job has a single step which is: EXEC spValidate_Emails This is a stored procedure that contains: ALTER PROCEDURE [dbo].[spValidate_Emails] AS UPDATE dbsVendors.dbo.tblSuppliers SET [Status] = 'Current' WHERE Email IN(SELECT Email FROM dbsVendors.dbo.tblEmails) DELETE FROM dbsVendors.dbo.tblEmails WHERE Email IN(SELECT EmailAddress FROM dbsVendors.dbo.tblOldContacts) As you can see, there is no reference to dbsAudits.dbo.tblAudits in the above procedure. So I don’t understand why the job would throw the above error. Thank you, Steve
visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 4 of 4 Thread: [SQL STATE 42S02] (Error 208) Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f6b26af8-f004-49f3-a838-16eb5e556072/sql-job-fails-because-of-invalid-object-name?forum=transactsql Mode 08-18-08,17:15 #1 khellian View Profile View Forum Posts Registered User Join Date Jul 2008 Posts 6 Unanswered: [SQL STATE 42S02] (Error 208) I have a stored procedure that writes data to a table in a database that is situated on a separate server. The table on the second server is accessed via a view on the originating server that http://www.dbforums.com/showthread.php?1633151-SQL-STATE-42S02-(Error-208) is using a linked server to the target server. The job is run through SQL Agent and I am getting the following error message: Executed as user: NT AUTHORITY\SYSTEM. Invalid object name 'dbo.OriginatingTable'. [SQL STATE 42S02] (Error 208). The step failed. However I can run a select against this table from the originating server and can insert data into the table without a problem using the view, hence FTC seems to be configured OK. I have recompiled the views and there is no difference. Does anyone have an suggestions? thanks Khellian Last edited by khellian; 08-18-08 at 17:17. Reason: Mis Type Reply With Quote 08-18-08,17:36 #2 Liebling View Profile View Forum Posts Distractedly Desultory Join Date May 2007 Location Lost in Translation Posts 946 http://support.microsoft.com/kb/281664/en-us Basically, it's missing the dependancy information it needs. "Passion rebuilds the world for the youth. It makes all things alive and significant. ~Ralph Waldo Emerson Reply With Quote 08-18-08,17:52 #3 khellian View Profile View Forum Posts Registered User Join Date Jul 2008 Posts 6 Dependent Objects I'm not using DTS in 2000 or
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 http://stackoverflow.com/questions/5267212/how-to-expose-more-information-about-the-failure-of-a-stored-proc-in-sql-agent 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 https://communities.vmware.com/thread/342678?start=0&tstart=0 x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up invalid object 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 proc fails then the SQL agent job will display an error message but there is no other information. Something like invalid object name 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 sysname ,@dtDateTime datetime select @ProcedureName = object_name(@@procid) begin try select @StepName = 'Step 01: Select from table PRINT 'Start' SELECT * FROM NonExistentTable PRINT 'End' end try begin catch select @Error = @@ERROR set @ErrorMsg = @ProcedureName + ' Err
NSXVirtual SAN vCenterFusionWorkstationvExpertVMware {code} CloudCredSubmit a Link Home > VMTN > VMware vSphere™ > vSphere Upgrade & Install > Discussions Please enter a title. You can not post a blank message. Please type your message and try again. 6 Replies Latest reply: Apr 26, 2012 1:06 AM by Techstarts vCenter Server upgrade from 4.1 to 5.0 FAILS SteveAntle Jan 11, 2012 12:34 PM Hello,Upgrade from vCenter Server 4.1 U2 to vCenter Server 5.0 fails with "Exception Thrown while executing SQL script".Here's my environment:SQL Server 2008 R2 Standard Edition (x64),vCenter Server 4.1 Update 2 (Windows 2003 R2 x64 Standard Edition)Here's an exercpt from the VCDatabaseUpgrade.log file:Preparing upgrade.DELETE FROM VPX_TABLEError in RunSqlScriptNonQuery(): Got exception: ERROR [42S02] [Microsoft][SQL Native Client][SQL Server]Invalid object name 'VPX_TABLE'.DELETE FROM VPX_INDEX_COLUMNError in RunSqlScriptNonQuery(): Got exception: ERROR [42S02] [Microsoft][SQL Native Client][SQL Server]Invalid object name 'VPX_INDEX_COLUMN'.DELETE FROM VPX_SCHEMA_HASHError in RunSqlScriptNonQuery(): Got exception: ERROR [42S02] [Microsoft][SQL Native Client][SQL Server]Invalid object name 'VPX_SCHEMA_HASH'....and here's an exercpt from the vminst.log file:----------CheckValidDbUpgrade():---------Getting Property DB_DSN_WINDOWS_AUTH = 1Getting Property LogonUser = vcenterSQLRETURN value is: -1SqlState: <42S02>, NativeError: <208>, msg: <[Microsoft][SQL Native Client][SQL Server]Invalid object name 'vcenter.VPX_VERSION'.>, msgLen: <84>ODBC Error: [Microsoft][SQL Native Client][SQL Server]Invalid object name 'vcenter.VPX_VERSION'.----------IsDbCollationCaseSensitive():---------Everything works perfectly in vCenter Server 4.1 (vCenter Server Service Health, etc.) prior to attempting the upgrade. When I browse the tables in the VIM_VCDB database, the tables that the log file indicates