SQL Server 错误 1934 发生在 INSERT 到带有计算列 PHP/PDO 的表 [英] SQL Server error 1934 occurs on INSERT to table with computed column PHP/PDO

问题描述

在 SQL Server 2005 中向表中添加计算列后，我在 INSERT 上收到以下消息，仅通过 PHP(使用 PDO)它在 SQL Server Management Studio 中工作正常.

After adding a computed column to a table in SQL Server 2005 I am getting the following message on INSERT, only via PHP (using PDO) it's working fine in SQL Server Managment Studio.

为了确保一切正确，我使用 SQL Server Profiler 设置了跟踪并将 INSERT 语句复制/粘贴到 SQL Server Management Studio.它在 SSMS 中运行良好，但在 PHP 中继续失败.

To ensure I had everything correct I setup a trace with SQL Server Profiler and copy/pasted the INSERT statement into SQL Server Managment Studio. It ran just fine in SSMS, but continues to fail in PHP.

添加联系人时出错:SQLSTATE[HY000]:一般错误:1934 一般 SQL服务器错误:检查来自 SQL Server [1934] 的消息(严重性 16)[(空)]

Error adding contact: SQLSTATE[HY000]: General error: 1934 General SQL Server error: Check messages from the SQL Server [1934] (severity 16) [(null)]

推荐答案

原来问题与 SET 参数有关.我使用了从 这里.确定在 SQL Server Management Studio(插入工作的地方)中设置了哪些选项.然后在我的插入语句之前将每个放在 exec 中导致事情再次工作.我不需要保留所有选项，所以下面我展示了使其工作所需的选项.

Turns out the problem had to do with the SET parameters. I used the code below obtained from Here. To determine which options were set in SQL Server Management Studio (where the insert worked). Then placing each of those in an exec prior to my insert statement caused things to work again. I didn't need to keep all the options, so below I show the ones which were required to get it working.

DECLARE @options INT
SELECT @options = @@OPTIONS

PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK' 
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS' 
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT' 
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS' 
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING' 
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS' 
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT' 
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER' 
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT' 
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON' 
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF' 
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL' 
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT' 
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

以下是我最终需要的选项:

Here are the options I ended up needing:

$dbPDO->exec("SET ANSI_WARNINGS ON");                                                                               
$dbPDO->exec("SET ANSI_PADDING ON");
$dbPDO->exec("SET ANSI_NULLS ON");
$dbPDO->exec("SET QUOTED_IDENTIFIER ON");
$dbPDO->exec("SET CONCAT_NULL_YIELDS_NULL ON");

更新:似乎 FK 约束导致了以下错误.以上也修复了这个错误.

Update: It seems FK constraints resulted in the following error. The above fixed this error as well.

SQLSTATE[HY000]:一般错误:8624 一般 SQL Server 错误:检查来自 SQL Server 的消息 [8624](严重性 16)[(null)]

SQLSTATE[HY000]: General error: 8624 General SQL Server error: Check messages from the SQL Server [8624] (severity 16) [(null)]

这篇关于SQL Server 错误 1934 发生在 INSERT 到带有计算列 PHP/PDO 的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！