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:
Every reference to X in child is aliased to the relevant view or table, and the "Line Number" the exception returns isn't actually a line of a query that includes X.
All tables/views contain this column
The child proc runs successfully on its' own, and even within the parent proc under most circumstances
All accounts have proper permissions to execute, select and insert data where they need it
Parent already runs other stored procedures from the same Database.schema as child
Why this proc is throwing an error only when called from a .Net app?
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();