.netsql-serverstored-procedures

"Invalid column" when called from another stored procedure from .Net


I've added a call to a SQL Server stored procedure (let's call it "child") at the end of an existing stored procedure ("parent"). Both child and parent (which, again, includes child) run successfully when run from SSMS both under my account and under the service account which is running the procs in production.

When I run child directly from a .Net app, using a SqlClient.SqlCommand.ExecuteNonQuery() call, it runs successfully. However, when I run parent using the same connection, it fails with an error of "Invalid Column 'X'" from within child.

Things I've confirmed:

Why this proc is throwing an error only when called from a .Net app?


Solution

  • After looking through the Profiler's trace, the first error was saying one of the temp tables was an invalid object. I explicitly created the table in the proc instead of using an "Insert into" statement and that fixed it.

    Strange how that didn't happen with any of the other "insert into" statements in either proc.

    Edit:

    After looking into it more, this is happening because both parent and child have a temp table named the same thing, and the child proc is updating it's temp table with any columns that don't exist in the parent's temp table.

    Toy code:

    CREATE PROCEDURE parent
    AS
    BEGIN
        select top 100 *
        into #tempTable
        from sys.all_parameters
    
        exec Financial.dbo.child
    END
    GO
    
    CREATE PROCEDURE child
    AS
    BEGIN
        select *
        into #tempTable --changing this temp table name will solve the issue
        from sys.all_columns
    
        update #tempTable
        set column_id = 1  --this will throw an error when called from .Net code below
        where collation_name is null  --and this will throw an error when called from .Net code below
    END
    GO
    

    And .Net:

    System.Data.SqlClient.SqlCommand cmd = new SqlCommand("parent", SqlClient.SqlConnection);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandTimeout = 0;
    cmd.ExecuteNonQuery();