I was trying to truncate a table on a remote server using a Linked Server and received the following error:
Msg 4701, Level 16, State 1, Line 6
Cannot find the object "bill" because it does not exist or you do not have permissions.
Read on to see how I solved this issue.
Here I can query the table through a Linked Server to a remote database.
Below I am trying to truncate a table through a Linked Server to a remote database and I get the error.
To get around this error, you can either change the command to a DELETE or use dynamic SQL by using SP_EXECUTESQL. I do not want to use a DELETE command, because I have millions of rows to delete and that could take a long time. I just want to get rid of all of the rows in the table as quickly as possible.
Using SP_EXECUTESQL through the Linked Server runs successfully as seen below.
To read more about this command, please follow this link for additional uses.
I query the table again, and all of the rows are now gone.