Styjun

Query about absorption line spectra

What (else) happened July 1st 1858 in London?

What major Native American tribes were around Santa Fe during the late 1850s?

Why do IPv6 unique local addresses have to have a /48 prefix?

Can a significant change in incentives void an employment contract?

Is XSS in canonical link possible?

Drawing ramified coverings with tikz

Open a doc from terminal, but not by its name

Do Legal Documents Require Signing In Standard Pen Colors?

Proving a function is onto where f(x)=|x|.

Is it possible to have a strip of cold climate in the middle of a planet?

What is the gram­mat­i­cal term for “‑ed” words like these?

Greco-Roman egalitarianism

Hot bath for aluminium engine block and heads

Journal losing indexing services

Folder comparison

What is this type of notehead called?

Should I stop contributing to retirement accounts?

Are lightweight LN wallets vulnerable to transaction withholding?

How must one send away the mother bird?

Freedom of speech and where it applies

Divine apple island

THT: What is a squared annular “ring”?

Why has "pence" been used in this sentence, not "pences"?

Deleting rows from a table on a linked server

How can I remove duplicate rows?Add a column with a default value to an existing table in SQL ServerHow to return only the Date from a SQL Server DateTime datatypeHow to check if a column exists in a SQL Server table?Check if table exists in SQL ServerHow to concatenate text from multiple rows into a single text string in SQL server?LEFT JOIN vs. LEFT OUTER JOIN in SQL ServerInserting multiple rows in a single SQL query?Insert results of a stored procedure into a temporary tableHow do I UPDATE from a SELECT in SQL Server?

0

I have created a linked server named [abc]. this linked server has a table named [Shipment_data] . Following are the columns in [Shipment_data] table: [start_date], [end_date],[route_type]

I want to delete the rows from this table where start_date and end_date is null.

But it looks like the normal delete operation does not work for tables on linked servers:

I tried the following codes, but no luck. Below are my codes and the error messages that i am getting for the same:

method1:

Declare @deleteRow nvarchar(max)
Declare @linkedServerName nvarchar(max)
Set @linkedServerName = 'abc'
Set @DeleteRow = 'Delete from ' + @LinkedServerName + '...Shipment_data where [Start_Date] is null and [End_Date] is null'

Set @sql = 'exec ' + @LinkedServerName + '...sp_executesql ' + (@DeleteRow)

exec (@sql)

error message:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "abc" returned message "Could not delete from specified tables.".
Msg 7320, Level 16, State 2, Line 12
Cannot execute the query "DELETE FROM Shipment_data " against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "abc".

Method2:Direct method

DELETE FROM [abc]...[Shipment_data]
 WHERE Start_Date is null and End_date is null

error message:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "abc" returned message "Bookmark is invalid.".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "abc".

Can someone please help me with a way to accomplish this on MS-SQL?

sql-server tsql

share|improve this question

edited Mar 21 at 13:53

Larnu

21.9k51833

asked Mar 21 at 13:49

Varun Shekhar RCVarun Shekhar RC

12

New contributor

Varun Shekhar RC is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That first statement looks concerning open to Injection.
  
  – Larnu
  Mar 21 at 13:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You should take a look on openquery docs.microsoft.com/en-us/sql/t-sql/functions/…
  
  – kcotman
  Mar 21 at 13:55
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I assume you are trying to delete rows from either an MS-Access database, or some other Office type database? From my experience this "invalid bookmark" error is related to security. Can you use your linked server to SELECT or UPDATE data? In other words is it only DELETE giving you problems?
  
  – Richard Hansell
  Mar 21 at 14:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RichardHansell In my example, the linked server is an excel spreadsheet. I am able to perform Select operation on this linked server. Cannot execute any Update query or Delete query. throws an error saying: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Zev" returned message "Could not delete from specified tables.". Msg 7320, Level 16, State 2, Line 17 Cannot execute the query "DELETE FROM [Shipment_data] " against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ABC".
  
  – Varun Shekhar RC
  2 days ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is this is a inherent bug in the provider itself? It is not working for any operations(except Select) at all like Update, Delete, Insert into..
  
  – Varun Shekhar RC
  2 days ago
  

  
  
  
  

 | 
show 4 more comments

0

I have created a linked server named [abc]. this linked server has a table named [Shipment_data] . Following are the columns in [Shipment_data] table: [start_date], [end_date],[route_type]

I want to delete the rows from this table where start_date and end_date is null.

But it looks like the normal delete operation does not work for tables on linked servers:

I tried the following codes, but no luck. Below are my codes and the error messages that i am getting for the same:

method1:

Declare @deleteRow nvarchar(max)
Declare @linkedServerName nvarchar(max)
Set @linkedServerName = 'abc'
Set @DeleteRow = 'Delete from ' + @LinkedServerName + '...Shipment_data where [Start_Date] is null and [End_Date] is null'

Set @sql = 'exec ' + @LinkedServerName + '...sp_executesql ' + (@DeleteRow)

exec (@sql)

error message:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "abc" returned message "Could not delete from specified tables.".
Msg 7320, Level 16, State 2, Line 12
Cannot execute the query "DELETE FROM Shipment_data " against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "abc".

Method2:Direct method

DELETE FROM [abc]...[Shipment_data]
 WHERE Start_Date is null and End_date is null

error message:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "abc" returned message "Bookmark is invalid.".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "abc".

Can someone please help me with a way to accomplish this on MS-SQL?

sql-server tsql

share|improve this question

edited Mar 21 at 13:53

Larnu

21.9k51833

asked Mar 21 at 13:49

Varun Shekhar RCVarun Shekhar RC

12

New contributor

Varun Shekhar RC is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That first statement looks concerning open to Injection.
  
  – Larnu
  Mar 21 at 13:54
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You should take a look on openquery docs.microsoft.com/en-us/sql/t-sql/functions/…
  
  – kcotman
  Mar 21 at 13:55
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I assume you are trying to delete rows from either an MS-Access database, or some other Office type database? From my experience this "invalid bookmark" error is related to security. Can you use your linked server to SELECT or UPDATE data? In other words is it only DELETE giving you problems?
  
  – Richard Hansell
  Mar 21 at 14:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @RichardHansell In my example, the linked server is an excel spreadsheet. I am able to perform Select operation on this linked server. Cannot execute any Update query or Delete query. throws an error saying: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Zev" returned message "Could not delete from specified tables.". Msg 7320, Level 16, State 2, Line 17 Cannot execute the query "DELETE FROM [Shipment_data] " against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ABC".
  
  – Varun Shekhar RC
  2 days ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Is this is a inherent bug in the provider itself? It is not working for any operations(except Select) at all like Update, Delete, Insert into..
  
  – Varun Shekhar RC
  2 days ago
  

  
  
  
  

 | 
show 4 more comments

I have created a linked server named [abc]. this linked server has a table named [Shipment_data] . Following are the columns in [Shipment_data] table: [start_date], [end_date],[route_type]

I want to delete the rows from this table where start_date and end_date is null.

But it looks like the normal delete operation does not work for tables on linked servers:

I tried the following codes, but no luck. Below are my codes and the error messages that i am getting for the same:

method1:

Declare @deleteRow nvarchar(max)
Declare @linkedServerName nvarchar(max)
Set @linkedServerName = 'abc'
Set @DeleteRow = 'Delete from ' + @LinkedServerName + '...Shipment_data where [Start_Date] is null and [End_Date] is null'

Set @sql = 'exec ' + @LinkedServerName + '...sp_executesql ' + (@DeleteRow)

exec (@sql)

error message:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "abc" returned message "Could not delete from specified tables.".
Msg 7320, Level 16, State 2, Line 12
Cannot execute the query "DELETE FROM Shipment_data " against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "abc".

Method2:Direct method

DELETE FROM [abc]...[Shipment_data]
 WHERE Start_Date is null and End_date is null

error message:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "abc" returned message "Bookmark is invalid.".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "abc".

Can someone please help me with a way to accomplish this on MS-SQL?

sql-server tsql

share|improve this question

edited Mar 21 at 13:53

Larnu

21.9k51833

asked Mar 21 at 13:49

Varun Shekhar RCVarun Shekhar RC

12

New contributor

Varun Shekhar RC is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

Declare @deleteRow nvarchar(max)
Declare @linkedServerName nvarchar(max)
Set @linkedServerName = 'abc'
Set @DeleteRow = 'Delete from ' + @LinkedServerName + '...Shipment_data where [Start_Date] is null and [End_Date] is null'

Set @sql = 'exec ' + @LinkedServerName + '...sp_executesql ' + (@DeleteRow)

exec (@sql)

DELETE FROM [abc]...[Shipment_data]
 WHERE Start_Date is null and End_date is null

sql-server tsql

sql-server tsql

share|improve this question

edited Mar 21 at 13:53

Larnu

21.9k51833

asked Mar 21 at 13:49

Varun Shekhar RCVarun Shekhar RC

12

New contributor

Varun Shekhar RC is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited Mar 21 at 13:53

Larnu

21.9k51833

asked Mar 21 at 13:49

Varun Shekhar RCVarun Shekhar RC

12

New contributor

Varun Shekhar RC is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited Mar 21 at 13:53

Larnu

21.9k51833

edited Mar 21 at 13:53

Larnu

21.9k51833

asked Mar 21 at 13:49

Varun Shekhar RCVarun Shekhar RC

12

New contributor

Varun Shekhar RC is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked Mar 21 at 13:49

Varun Shekhar RCVarun Shekhar RC

12