Tsql Error In .net But Not In Ssms

Having just built a c# application to run SQL scripts, I've run into a few issues with error handling. We have a stored procedure in our database which modifies the default constra

Solution 1:

If running an SP-statement trace in SQL Profiler doesn't reveal a difference, can you log the @SQL variable contents being executed to a table and review them afterward? I'm really quite confused that they are truly identical.

Is it possible that the drop default constraint is failing due to a name problem? I think you answered this but it would be a plausible answer.

Did you know that dynamic SQL is executed with the permissions of the caller, not the SP owner, unless you take special measures (such as EXECUTE AS)? Could this be the issue?

Try commenting out the create default from the SP, running it from .Net, and seeing if the default was really dropped by the first statement. Or put a WAITFOR DELAY '5:00' in between them so you can check mid-run.

Solution 2:

Try tu use SQL Server Profiler and compare the query from the ADO.NET to your query from the SSMS.

Solution 3:

Try profiling the execution of the query and extracting the SET statements the SqlClient executes on connection. For example this is what I had profiled in the past when debugging performance differences of a query executed by the SqlClient versus running it in SSMS:

set quoted_identifier onset arithabort offset numeric_roundabort offset ansi_warnings onset ansi_padding onset ansi_nulls onset concat_null_yields_null onset cursor_close_on_commit offset implicit_transactions offset language us_english
set dateformat mdy
set datefirst 7set transaction isolation level read committed

Run these SET statements into SSMS and then try executing your query there again to see if you can replicate the behaviour. Whilst I don't believe this will surface your issue it is at least a good exercise to ensure your session configuration in SSMS is the same as if the query were being executed by the SqlClient