Dan Walmsley Dan Walmsley - 7 months ago 29
SQL Question

INSERT failed because the following SET options have incorrect setting only in Perl Sybase

I have the following perl script.

use strict;
use warnings;
use DBI;

my $boris_db_connect = 'dbi:Sybase:server=10.42.2.8\BOBSTER1;charset=utf8;database=PorseDev';
my $boris_db_username = "porseuser";
my $boris_db_password = "porseuser";

my $boris_dbh = DBI->connect($boris_db_connect, $boris_db_username, $boris_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
';

$boris_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 GUI's 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 no where. Any information would be much appreciated.

Note: I assume that because the query runs in other tools that set options are correct

Answer

Please refer this link you 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.