Results 1 to 4 of 4
  1. #1
    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 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 18:17. Reason: Mis Type

  2. #2
    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

  3. #3
    Join Date
    Jul 2008
    Posts
    6

    Dependent Objects

    I'm not using DTS in 2000 or SSIS in 2005, but if the logic hold true for a view in 2005, I don't see anywhere where there is an option to include all dependents as per the article Q281664?

    thanks

    Khellian

  4. #4
    Join Date
    May 2007
    Location
    Lost in Translation
    Posts
    946
    I suppose it's a good lesson in asking better questions. Including more information from the beginning would have yielded you better results. Specifying what platform SQL 7.0/2000/2005, and perhaps telling us what you are using as the engine, stored procedure, SQL agent, Query Analyzer or what might help as well.

    That said;

    If the view is not owned by dbo, then you will have use the context ownername.OriginatingTable where ownername is equal to the owner of the view itself.

    Alternately, you could change the ownership of the view to dbo by using the sp_changeobjectowner stored procedure. Or, you could use enterprise manager/SSMS to change the owner as well.

    If the owner is dbo already, then the issue is the odbc connection you are providing between the two servers. There is an issue in the string you use to connect, and the reason that you don't see it when you run query anaylzer is that query analyzer builds the string for you behind the scenes.
    "Passion rebuilds the world for the youth. It makes all things alive and significant. ~Ralph Waldo Emerson

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •