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?

Answer 1

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?