SQL INSERT 失败，因为以下 SET 选项仅在 Perl Sybase 中设置不正确
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24029518/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INSERT failed because the following SET options have incorrect setting only in Perl Sybase
提问by Dan Walmsley
I have the following Perl script:
我有以下 Perl 脚本：
use strict;
use warnings;
use DBI;
my $db_connect = 'dbi:Sybase:server=10.2.2.2\CATDB;charset=utf8;database=Dev';
my $db_username = "*****";
my $db_password = "*****";
my $dbh = DBI->connect($db_connect, $db_username, $db_password,{ RaiseError => 1,
PrintError => 1,
AutoCommit => 1,
syb_chained_txn => 0,
syb_enable_utf8 => 1 } ) || die "Failed to connect to BORIS database: $DBI::errstr\n";
my $insertContractSQL2 = '
BEGIN
DECLARE @ContractID int
UPDATE dbo.Sequences SET NextContractID = NextContractID + 1
SET @ContractID = (SELECT NextContractID FROM dbo.Sequences)
SET ANSI_NULL_DFLT_ON, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON
SET ANSI_NULL_DFLT_OFF, ARITHIGNORE, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS, NOCOUNT, NUMERIC_ROUNDABORT, XACT_ABORT OFF
INSERT INTO dbo.CONTRACTS
(ContractID
,modifieddate
,FranchiseID
,FamilyID
,EducatorID
,StartDate
,EndDate
,ContractTypeID
,PayRate1
,PayRate2
,PayRate3
,PayRate1Hours
,PayRate2Hours
,PayRate3Hours
,WageAdminContractorRate
,PorseContributionContractorRate
,WageAdminAmount
,ACCAmount
,PorseContributionAmount
,WINZSubsidyAmount
,WINZSubsidyAmountChildcareOSCAR
,ACCInvoicedPerQuarterAmount
,FamilyAPAmount
,OtherFortnightPayment
,OtherFortnightPaymentDesc
,ReferralAgencyID
,NextAppraisalDate
,NextAppraisalTypeID
,PendingApproval
,Active
,modifiedby
,BeingEdited
,MOENetworkID
,NewFlag
,ReceivedDate
,FreeECEAmount
,OptionalChargeRate
,OptionalChargeAgreement
,TerminationApproved
,AgreedDeductions
,PayRateEce
,PayRateEceHours
,PreECEClarity
,TotalOptionalCharges
,NonChildPorseContributionAmount
,FreeECETopup
,Donation
,NonChildWinzChildcareAmount
,ManuallyTerminated
,ContractDuplicatedFlag
,CreateDate
,RosteredContractID)
VALUES (
@ContractID
,GETDATE()
,63,22901,9134,\'2014-06-03 00:00:00.0\',\'2014-06-28 00:00:00.0\',2,0,0,0,5,0,0,4.75,0,0,0,0,0,0,0,0,0,null,null,null,null,0,1,\'admin\',1,null,0,\'2014-06-10 00:00:00.0\',0,0,0,0,null,0,0,0,0,0,0,0,0,0,0,\'2014-06-03 15:30:15.037\',4)
END
';
$dbh->do($insertContractSQL2);
When it runs I get:
当它运行时，我得到：
/usr/bin/perl test.pl
DBD::Sybase::db do failed: Server message number=1934 severity=16 state=1
line=10 server=BOBSTER\BOBSTER1 text=INSERT failed because the
following SET options have incorrect settings: 'ANSI_NULLS,
CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that
SET options are correct for use with indexed views and/or indexes on
computed columns and/or filtered indexes and/or query notifications
and/or XML data type methods and/or spatial index operations.
at test.pl line 89.
Now this is a lousy question I know. But I have run the same query via three different GUIs for SQL Server and I don't get this error. I have gone through the first 3 or 4 pages of Google results, and have gotten nowhere. Any information would be much appreciated.
我知道这是一个糟糕的问题。但是我已经通过三个不同的 SQL Server 图形用户界面运行了相同的查询，但我没有收到此错误。我浏览了 Google 结果的前 3 或 4 页，但一无所获。任何信息将不胜感激。
Note:I assume that because the query runs in other tools that set options are correct
注意：我假设因为查询在其他设置选项的工具中运行是正确的
采纳答案by Chankey Pathak
Please refer this linkyou may need to set order to create a table with a persisted, computed column, the following connection settings must be enabled:
请参阅此链接，您可能需要设置顺序以创建具有持久计算列的表，必须启用以下连接设置：
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT ON
SET QUOTED_IDENTIFIER ON
You can set this with the $sth->do() method or with ISQL. You need to execute them first after you connect to the DB, before executing your "SELECT", "UPDATE" or any other command.
您可以使用 $sth->do() 方法或 ISQL 进行设置。在连接到数据库后，您需要先执行它们，然后再执行“SELECT”、“UPDATE”或任何其他命令。
回答by paul popescu
I solved it in this nasty way:
我以这种讨厌的方式解决了它：
EXEC('
IF EXISTS(SELECT * FROM sysindexes WHERE Name = ''IX_GPS_XY'')
DROP INDEX [IX_GPS_XY] ON [dbo].[Cities];
')
EXEC('
INSERT INTO dbo.Cities(Name, CountyID, GPSXY)
VALUES...
')
EXEC('
IF NOT EXISTS(SELECT * FROM sysindexes WHERE Name = ''IX_GPS_XY'') BEGIN
SET ARITHABORT ON;
...
CREATE SPATIAL INDEX...
')
回答by Ray Lionfang
I ran a script generated by SQL Server studio and had the error "INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'"
我运行了一个由 SQL Server Studio 生成的脚本并出现错误“插入失败，因为以下 SET 选项的设置不正确：'ANSI_PADDING'”
My solution is to add the keyword "GO" and the script worked.
我的解决方案是添加关键字“GO”并且脚本有效。
The script with error:
有错误的脚本：
--SOME INSERT STATEMENTS
SET ANSI_PADDING ON
--SOME INSERT STATEMENTS
The script that worked:
有效的脚本：
--SOME INSERT STATEMENTS
SET ANSI_PADDING ON
GO
--SOME INSERT STATEMENTS
I hope it helps.
我希望它有帮助。
