CODEWITHSUNDEEP
c#sql-serverdatabase
Daniel Jackson
Daniel Jackson

Reputation: 1126

Truncate Table C# Cannot Find Table - Does not Exist or no Permission

I'm going to use my full table name because I'm trying to pinpoint what I'm doing wrong as to why the table can't be found.

I'm trying to truncate a table within C#. I am able to run this command, "TRUNCATE TABLE NS_Vendor" within SQL Server and it correctly finds and truncates the table.

If I try to do this in my C# application, I'm told does not exist or no permission. I'm using DB admin account so it can't be permission. I'm using the code below. I'm inside of the database named Public with admin user credentials and I'm looking to truncate the "NS_Vendor" table. The cmd below works in SQL Server. I've tried different strings like "dbo.NS_Vendor" amongst others but none of them work.

Would someone mind pointing out to me what I'm not understanding about what I'm doing wrong?

SqlConnection sqlConnection = new SqlConnection("Server = server; Database = Public; User = ; Password = ;");
            string query = "TRUNCATE TABLE " + "NS_Vendor";
            SqlCommand cmd = new SqlCommand(query, sqlConnection);
            sqlConnection.Open();
            cmd.ExecuteNonQuery();
            sqlConnection.Close();

Upvotes: 1

Views: 2373

Answers (1)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

To resolve the issue, i would suggest to do couple of things. At first try with this :

string query = "DELETE NS_Vendor";
...
cmd.ExecuteNonQuery();

If you still get similar error, then i think you are interacting with different database. But if it throws no error, then it is sure that your provided user does not have required permission for TRUNCATE

You can get some useful information here on TRUNCATE TABLE which indicates

The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.

Upvotes: 2

Related Questions