CODEWITHSUNDEEP
c#sqlsql-serverssms
user1519979
user1519979

Reputation: 1874

Cannot execute SQL script by code, but executing in SSMS works

Following SQL-Script is saved in a file:

WHILE @@rowcount > 0
BEGIN
    DELETE TOP(100) t1 
    WHERE name >= 20000
END

For executing in my software, I read the file and then execute the script by following method: 

private static void CreateCommand(string queryString, string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

So now my problem is that the script isn't executed correctly on the MSSQL server, the entries still exist. There are no exceptions at all.

If I run the script in Microsoft SQL Server Management Studio, the script is being executed without any problems.

What can cause this issue?

Upvotes: 0

Views: 555

Answers (3)

Slava Murygin
Slava Murygin

Reputation: 1955

When you run your statement from the app @@rowcount is equal to "0" You have to initialize it first, like this:

SELECT 1;
WHILE @@rowcount > 0
BEGIN
    DELETE TOP(100) t1 
    WHERE name >= 20000
END

Upvotes: 2

LukaszBalazy
LukaszBalazy

Reputation: 577

As mentioned by Michał:

@@rowcount returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG. From microsoft docs.

You should perform DO WHILE operation if you want to delete big chunk of records:

DECLARE @iter INT;

SET @iter = 1;

WHILE @iter > 0 BEGIN BEGIN TRANSACTION;

DELETE TOP(100) t1 WHERE name >= 20000

SET @iter = @@ROWCOUNT;

COMMIT TRANSACTION; 

END

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37480

Let's start with definition:

@@rowcount returns the number of rows affected by the last statement.

Looks like @@rowcount equals to 0.

When creating new connection, this value defaults to 1, so it has to enter your loop, but maybe you deleted all rows satisfying where clause.

Another guess is: there are some operations, which affect 0 rows, so @@rowcount equals to 0 and your loop isn't entered.

You could inspect the above guesses by executing and retrieving results of below query:

SELECT @@rowcount [rowcount]

Upvotes: 3

Related Questions