Deleting records from all tables, what's wrong here?

Question

So I'm running the SqlRestore utility created by AppHarbor, which can be found here:

https://github.com/appharbor/AppHarbor-SqlServerBulkCopy

The first step of the utility is to wipe out the data from the destination database, using these commands:

// http://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql/156813#156813
StringBuilder commandBuilder = new StringBuilder();
commandBuilder.Append(
    @"
    -- disable all constraints
    EXEC sp_msforeachtable ""ALTER TABLE ? NOCHECK CONSTRAINT all""

    -- delete data in all tables
    EXEC sp_msforeachtable ""DELETE FROM ?""

    -- enable all constraints
    exec sp_msforeachtable ""ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all""
");

When I run this (both via the program, and also manually via Management Studio), the delete statement is throwing an error saying invalid column name schoolid

At first, I had no idea which table was throwing the error, so I re-wrote the delete step to a cursor, shown here:

declare tableCursor cursor local forward_only for

    select [name]
    from sys.objects
    where [type] = 'U'

    declare @tableName varchar(50)

    open tableCursor

    fetch next from tableCursor into @tableName

    while (@@FETCH_STATUS = 0) begin
        print 'trying to delete from ' + @tableName
        exec('delete from ' + @tableName)
        print 'deleted from ' + @tableName

        fetch next from tableCursor into @tableName
    end

close tableCursor
deallocate tableCursor

Executing the script this way tells me that it was "trying to delete from table X", but when I look at the table definition of table X, that column DOES NOT EXIST (and never has)!

So next, I decide to just manually delete the table, then use VS Sql Server Schema comparison to re-generate the table to my destination database, since it was possibily corrupt somehow.

Once it's re-generated, I re-run that delete script, AND IT STILL THROWS THE ERROR!

Here's the table definition:

CREATE TABLE [dbo].[TourneyPoolMatchResult] (
    [TourneyPoolMatchResultId]      INT            IDENTITY (1, 1) NOT NULL,
    [TournamentTypeId]              INT            NOT NULL,
    [WinningWrestlerId]             INT            NOT NULL,
    [LosingWrestlerId]              INT            NOT NULL,
    [WinType]                       VARCHAR (5)    NOT NULL,
    [Score]                         VARCHAR (20)   NULL,
    [BonusPoints]                   DECIMAL (2, 1) NOT NULL,
    [AdvancementPoints]             DECIMAL (2, 1) NOT NULL,
    [PlacementPoints]               INT            NOT NULL,
    [LosingWrestlerPlacementPoints] INT            NOT NULL,
    PRIMARY KEY CLUSTERED ([TourneyPoolMatchResultId] ASC)
);


GO
CREATE trigger [dbo].[trg_TourneyPoolMatchResult_Change]
    on [dbo].[TourneyPoolMatchResult]
    after insert, update, delete
    as

    exec [UpdateTeamPoints];
    exec [UpdatePointsForSubmittal];

As you can see, nowhere in that table definition does it have anything about SchoolId.

What in the world is going on here?

You use the delete statement and you have some triggers on delete. Maybe your trigger function (e.g. `UpdateTeamPoints` or `UpdatePointsForSubmittal`) reference a column named schoolid? Search your sql code, is there any `schoolid` anywhere? — dev.null, Sep 13 '15 at 02:40
Yep, that was it, please add as an answer so I can give you credit. — ganders, Sep 13 '15 at 02:43

score 2 · Accepted Answer · answered Sep 13 '15 at 03:22

2

You use the delete statement and you have some triggers on delete. Maybe your trigger function (e.g. UpdateTeamPoints or UpdatePointsForSubmittal) reference a column named schoolid? Please search your sql code, is there any schoolid anywhere?

answered Sep 13 '15 at 03:22

dev.null

538
2
7

Lukasz Szozda · Answer 2 · 2015-09-13T08:14:04.287

If your tables haven't Foreign Key constraints instead of

DELETE FROM table_name;

use:

TRUNCATE TABLE table_name;

From TRUNCATE:

Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

and

TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. For more information, see CREATE TRIGGER (Transact-SQL).

Deleting records from all tables, what's wrong here?

2 Answers2