OpenRowSet does not delete records in Remote DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

OpenRowSet does not delete records in Remote DB

Author

Topic

Geeta_chugh
Starting Member

1 Post

Posted - 2013-11-07 : 04:27:51

Hi All,

I am trying to execute the following query in SQL SERVER 2012.
SELECT * FROM OPENROWSET ('SQLNCLI', 'Server=DBName;UID=newuser;PWD=newuser;', 'exec TESTDB.[dbo].[DeleteTableData]')

I am trying to delete a record from a Remote DB Server. The query gets executed without any issues, but does not delete the record. Also it does not throw an error.

SP Created:
CREATE PROCEDURE [dbo].[DeleteTableData]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
delete from [TestDB].[dbo].[Table1] where id=1
select 1 as iREsult

END

Kindly help to resolve this issue.

Regards,
Geeta.

mattluk
Starting Member

15 Posts

Posted - 2013-11-08 : 11:58:45

the DB have rights to delete data in that table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources