在.net中，但不是在SSMS TSQL错误 [英] TSQL error in .Net but not in SSMS

问题描述

刚刚建一个C＃应用程序来运行SQL脚本，我已经与错误处理遇到的一些问题。

Having just built a c# application to run SQL scripts, I've run into a few issues with error handling.

我们已经在我们的数据库，修改为表的默认约束（通过丢弃任何现有的一个，然后创建新的）存储过程，当我们从SSMS中调用它运行良好。然而，当我们调用同一个存储过程从.NET SqlClient中，它给出了一个错误。我得到的 SQLEXCEPTION 的错误有以下消息：

We have a stored procedure in our database which modifies the default constraint for tables (by dropping any existing one and then creating the new one), and it runs fine when we call it from SSMS. However, when we call the same stored procedure from the .Net SqlClient, it gives an error. The error I get in the SqlException has the message:

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

这是不应该的，因为我们从来没有看到在SSMS的消息，除非我们不先运行DROP语句。如果删除约束语句被注释掉，我们看到了同样的信息。

This shouldn't happen, since we never see the message in SSMS unless we don't run the drop statement first. If the Drop Constraint statement is commented out, we see the same message.

为什么当我们调用存储过程：在.NET约束没有掉下来？

Why would the constraint not get dropped when we call the stored procedure from in .Net?

有关参考，存储过程中的有关code是：

For reference, the relevant code inside the stored procedure is:

  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

编辑：我认为捕获的错误，而忽略非致命错误，如这一点。然而，遗憾的是这种特定的错误是一个16级的错误，并且错误，例如更新一个不存在的表只有15级。所以，我不能让通过异常的错误类过滤这个错误通过。

I considered trapping for errors, and ignoring non-fatal errors such as this. However, unfortunately this specific error is a Level 16 error, and errors such as updating a non-existent table are only Level 15. So I can't let this error pass by filtering on the Error Class of the exception.

编辑2：应该提到，在 @strConstraintName 确定自动根据表名和列名：

EDIT 2: Should have mentioned, the @strConstraintName is determined automatically based on the 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

分辨率

所以，事实证明，这是确定的默认约束的名字vw_DEFAULT_CONSTRAINT被写的不好。

So, it turns out that the vw_DEFAULT_CONSTRAINT that was determining the name of the default constraint was badly written.

这是基于USER_ID（），由于某种原因（的观点之前，我开始写），这是没有必要的值过滤。去除这个限制从视图返回正确的约束名称。

It was filtering based on the value of USER_ID() for some reason (the view was written before I started) which wasn't necessary. Removing this restriction from the view returned the correct constraint name.

@ ErikE的答案提供了一个很好的推动正确的方向。保存变量值到一个临时表让我看到了这2种方法都没有真正的工作是相同的，这使我进一步调查的看法。荣誉对@ErikE这一点，这就是为什么我选择了他作为正确答案。

@ErikE's answer provided a good push in the correct direction. Saving the variable values into a temporary table allowed me to see that the 2 methods weren't actually working the same, which led me to investigate the view further. Kudos to @ErikE for this, which is why I selected his as the correct answer.

我不觉得奇怪，虽然这USER_ID的值（）是不一样的，因为连接是相同的。

I do find it strange though that the value of USER_ID() was not the same, since the connections were identical.

推荐答案

如果运行SQL事件探查器的SP-声明一丝不露的差别，你能记录@SQL变量内容执行到一个表，并加以之后？我真的很困惑，他们是真正的一致。

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.

你知道吗，动态SQL与来电者，而不是SP所有者的权限执行的，除非你采取特殊措施（如 EXECUTE AS ）？难道这是问题？

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?

尝试注释掉创建默认从SP，从.net运行它，并查看是否默认真的下降了第一条语句。或者把 WAITFOR DELAY'5:00'在他们之间，所以你可以检查中间跑。

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.

这篇关于在.net中，但不是在SSMS TSQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！