Msg 4701, Level 16 trying to truncate table through Linked Server

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.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15

I query the table again, and all of the rows are now gone.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s