对包含计算列PHP/PDO的表执行INSERT时会发生SQL Server错误1934 [英] SQL Server error 1934 occurs on INSERT to table with computed column PHP/PDO

问题描述

将计算列添加到SQL Server 2005中的表后，我在INSERT上收到以下消息，仅通过PHP(使用PDO)在SQL Server Managment 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 Managment 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) [(null)]

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

推荐答案

原来问题出在SET参数上.我使用了以下代码，这些代码是从

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)]

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