February 20, 2008 at 2:26 pm
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.
February 20, 2008 at 8:55 pm
Go to the source server to check whether or not the table exists, including its owner;
Check whether or not the link works.
February 20, 2008 at 9:55 pm
If it exists, check to see if that user has rights.
March 9, 2010 at 8:26 pm
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???
March 9, 2010 at 9:27 pm
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]')
March 10, 2010 at 9:08 am
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.
March 10, 2010 at 6:56 pm
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'
March 11, 2010 at 8:25 pm
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