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

Question

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?

Is there a reason you didn't just do something like `DELETE FROM t1 WHERE name >= 2000`? Why the while loop? — mason, Jan 24 '19 at 13:23
I'm wary of `WHERE name >= 20000`, smells like maybe a collation/sort issue. Maybe even a data type problem. — Jacob H, Jan 24 '19 at 13:24
can youtry to make it a SP, and call that? not sure how procedural T-SQL will execute from ADO.NET — Jeremy, Jan 24 '19 at 13:31
You cannot be sure @@rowcount > 0 the first time, i suggest you change your logic to a do-while syntax instead, e.g. https://stackoverflow.com/questions/4487546/do-while-loop-in-sql-server-2008 — MikNiller, Jan 24 '19 at 13:32
thanks for help works now, @@rowcount was 0 on the first run — user1519979, Jan 24 '19 at 13:43

score 3 · Accepted Answer · answered Jan 24 '19 at 13:33

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]

Thanks, that was the problem – user1519979 Jan 24 '19 at 13:43 — user1519979, Jan 24 '19 at 13:43

score 2 · Answer 2 · answered Jan 24 '19 at 13:41

2

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

answered Jan 24 '19 at 13:41

Slava Murygin

1,951
1
10
10

score 1 · Answer 3 · answered Jan 24 '19 at 13:41

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

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

3 Answers3