how to interpret this error message

  • I've just started a job where I have a set of stored procedures which run on variuos servers through linked servers

    One of these jobs (it runs a stored procedure that selects data from a table in a msdb on the linked server) is

    failing with an:

    'Executed as user: domain\sqlserviceuser. OLE DB provider 'servername' does not contain table

    '"db_name"."dbo"."table_name"'. The table either does not exist or the current user does not

    have permissions on that table. [SQLSTATE 42000] (Error 7314) OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='servername',

    TableName='"db_name"."dbo"."table_name"']. [SQLSTATE 01000] (Error 7300). The step failed.'

    error.

    Now I checked the login id for the linked server across the servers and i also see that the requires table does exist.

    Does anyone have an idea why it is failing.

  • Go to the source server to check whether or not the table exists, including its owner;

    Check whether or not the link works.

  • If it exists, check to see if that user has rights.

  • select * from [112.137.162.205].[CCRSVTEAM]..[dbo.tbl_SvDetails]

    OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='112.137.162.205', TableName='"CCRSVTEAM"."dbo.tbl_SvDetails"'].

    Msg 7314, Level 16, State 1, Line 1

    OLE DB provider '112.137.162.205' does not contain table '"CCRSVTEAM"."dbo.tbl_SvDetails"'. The table either does not exist or the current user does not have permissions on that table.

    Pls help me???

  • mahalirajesh (3/9/2010)


    select * from [112.137.162.205].[CCRSVTEAM]..[dbo.tbl_SvDetails]

    Hi,

    Suppose you have the table permission and the table exists in the target server, then again check with select statement by,

    SELECT * FROM [112.137.162.205].[CCRSVTEAM].[DBO].[TBL_SVDETAILS]

    OR

    SELECT *

    FROM OPENQUERY([112.137.162.205], 'SELECT * FROM [CCRSVTEAM].[DBO].[TBL_SVDETAILS]')

  • I believe this can also happen if the user does not have NTFS rights to the folder on the remote server. If you're running under a local account you might need to change to a domain account.

  • Thanks for immediate response.

    its working good.I found two solutions

    exec sp_serveroption @server='202.190.199.69', @optname='rpc', @optvalue='true'

    exec sp_serveroption @server='202.190.199.69', @optname='rpc out', @optvalue='true'

    Sol1:

    exec [202.190.199.69].[CCRSVTEAM202].dbo.sp_executesql N'select * from tbl_SvDetails'

    Sol2:

    SELECT * FROM OPENQUERY([202.190.199.69], 'select * from [CCRSVTEam202]..[tbl_SvDetails]') where dbs_Userid='HANIF'

  • HI ANy body Help me update statement on Linked diff Servers

    It working fine on Selecect statement but Update can not

    Update OPENQUERY([112.137.162.205], 'select * from [CCRSVTEam]..[tbl_SvDetails]') set dbs_UpdatedStatus = A.dbs_UpdatedStatus

    from tbl_SvDetails A

    inner join OPENQUERY([112.137.162.205], 'select * from [CCRSVTEam]..[tbl_SvDetails]') B

    on A.dbs_Accno=B.dbs_Accno

    where B.dbs_client in ('CIMBHP','HSBC','CITIBANK','CIMBRETAIL') and A.dbs_UpdatedStatus<>B.dbs_UpdatedStatus

    select A.dbs_Accno,B.dbs_Accno,B.dbs_client,A.dbs_UpdatedStatus,B.dbs_UpdatedStatus from tbl_SvDetails A

    inner join OPENQUERY([112.137.162.205], 'select * from [CCRSVTEam]..[tbl_SvDetails]') B

    on A.dbs_Accno=B.dbs_Accno

    where B.dbs_client in ('CIMBHP','HSBC','CITIBANK','CIMBRETAIL') and A.dbs_UpdatedStatus<>B.dbs_UpdatedStatus

    Order by B.dbs_client

    Thanks in Advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply