TRUNCATE TABLE on linked server failing

Question

Context: I need to modify a stored procedure on my local instance of SQL Server 2019 to remotely connect to a linked server to truncate and re-populate a table.

I create & verify the linked server with below, runs successfully:

EXEC sp_addlinkedserver @server='PROD01' 
EXEC sp_linkedservers

I run a test insert with the following that executes successfully:

INSERT OPENQUERY([PROD01],'SELECT Purchase_Order,PO_Line_Number, 
       Buyer_ID, 
       Requisition_ID, 
       Item_ID, 
       Item_Revision_ID, 
       PO_Line_Description, 
       Order_Quantity, 
       RECV_Qty, 
       Due_Date, 
       Original_Due_Date, 
       Todays_Status, 
       Delivery_Status, 
       Vendor_Name, 
       Requisitioner, 
       Level_1_Project_Name, 
       Inventory_Abbreviation_Code, 
       [Acknowledgment_Required_Flag YN], 
       Acknowledgment_Date, 
       REL_TO_VEND_DT, 
       Order_Date, 
       Purchase_Order_Line_Type, 
       Last_Update
FROM [ORT].[dbo].[po_masterlist_report]') 
--Local server instance below
SELECT Purchase_Order, 
       PO_Line_Number, 
       Buyer_ID, 
       Requisition_ID, 
       Item_ID, 
       Item_Revision_ID, 
       PO_Line_Description, 
       Order_Quantity, 
       RECV_Qty, 
       Due_Date, 
       Original_Due_Date, 
       Todays_Status, 
       Delivery_Status, 
       Vendor_Name, 
       Requisitioner, 
       Level_1_Project_Name, 
       Inventory_Abbreviation_Code, 
       [Acknowledgment_Required_Flag YN], 
       Acknowledgment_Date, 
       REL_TO_VEND_DT, 
       Order_Date, 
       Purchase_Order_Line_Type, 
       [Last_Update]
FROM [20Z2\SQLEXPRESS].[co_sb].[dbo].[po_masterlist_report]

Question: When I try to run a truncate statement I raise following errors:

SELECT * FROM OPENQUERY ([PROD01], 'TRUNCATE TABLE [ORT].[dbo].[po_masterlist_report] SELECT "OK"' )

OLE DB provider "SQLNCLI10" for linked server "MSS-ISD-PROD01" returned message "Deferred prepare could not be completed.".

Msg 8180,Level 16, State 1, Line 1 Statement(s) could not be prepared.

Msg 102,Level 15, State 1, Line 1 Incorrect syntax near 'OK'.

As suggested by a commenter I replaced the double quotes with single quotes and the query does execute without errors but the underlying table hasnt been truncated....so the query clearly isnt executing properly. Can someone please help me correct this syntax so it truncates the linked server table as intended?

SELECT * FROM OPENQUERY ([PROD01], 'TRUNCATE TABLE [ORT].[dbo].[po_masterlist_report] SELECT ''OK''' )

I read somewhere that either columns or some text needs to be returned from the linked server query...which could of course be incorrect. — emalcolmb, Mar 11 '20 at 20:07
You keep changing the code in your question. But look at your select statement at the end. select "OK" is invalid syntax. You would need to use select ''OK'' where you have 2 single quotes, not double quotes. — Sean Lange, Mar 11 '20 at 20:13
@ Sean Lange, using 2 single quotes does allow the query to run without errors but when I check the underlying table it hasnt been truncated, so the query is somehow not executing properly: SELECT * FROM OPENQUERY ([PROD01], 'TRUNCATE TABLE [ORT].[dbo].[po_masterlist_report] SELECT ''OK''' ) — emalcolmb, Mar 11 '20 at 20:17
Is that table truncatable? Meaning are there foreign keys referencing that table? — Sean Lange, Mar 11 '20 at 20:21

TRUNCATE TABLE on linked server failing

0 Answers0