Geek asks and answers

TSQL error in .Net, but not in SSMS

Just building a C # application to run SQL scripts, I ran into several problems with error handling.

We have a stored procedure in our database that changes the default restriction for tables (by deleting any existing one and then creating a new one), and it works fine when we call it from SSMS. However, when we call the same stored procedure from .Net SqlClient, it gives an error. The error I get in SqlExceptionhas the following message:

System.Data.SqlClient.SqlException: Column already has a DEFAULT bound to it.
Could not create constraint. See previous errors.

This should not be, since we will never see a message in SSMS unless we run the drop statement first. If the instruction is Drop Constraintcommented out, we see the same message.

Why the restriction will not be removed when calling the stored procedure from .Net?

For reference, the corresponding code inside the stored procedure:

  SELECT @strSQL = 'ALTER TABLE [' + @strTableName + '] DROP CONSTRAINT  [' + @strConstraintName + ']'
  EXEC sp_executesql @strSQL

  SELECT @strSQL = 'ALTER TABLE [' + @strTableName + '] ADD CONSTRAINT DF_' + @strTableName + '_' + @strColumnName + ' DEFAULT (' + @strDefaultValue + ') FOR ' + @strColumnName
  EXEC sp_executesql @strSQL

EDIT: I was considering capturing for errors and ignoring non-fatal errors like this. However, unfortunately, this particular error is a level 16 error, and errors such as updating a nonexistent table are only level 15. Therefore, I cannot let this error go through filtering the exception error class.

EDIT 2: Should be specified, @strConstraintNamedetermined automatically based on table name and column name:

SELECT
        @strConstraintName = vdc.CONSTRAINT_NAME
  FROM
        dbo.vw_DEFAULT_CONSTRAINT vdc
  WHERE
        vdc.TABLE_NAME = @strTableName
        AND vdc.COLUMN_NAME = @strColumnName

Resolution

So it turns out that vw_DEFAULT_CONSTRAINT, which defines the name of the default constraint, was poorly written.

For some reason, filtering was based on the value of USER_ID () (the view was written before I started), which was not necessary. Removing this constraint from the view returned the correct constraint name.

@ErikE . , , . @ErikE , .

, USER_ID() , .

+1
3

SP- SQL Profiler , @SQL ? , .

, - ? , , .

, SQL , SP, (, EXECUTE AS)? ?

SP, .Net , . WAITFOR DELAY '5:00' , .

+3

SQL Server Profiler ADO.NET SSMS.

+1

SET, SqlClient. , , , , SqlClient, SSMS:

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

SET SSMS, , , . , , , , , SSMS , SqlClient

+1

More articles:


All Articles