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

Question

I have the following Perl script:

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 *** 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
            ,ContributionContractorRate
            ,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
            ,NonChildContributionAmount
            ,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= 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.

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

If you use `SQL Server` please have a look at my answer on [Failed because incorrect arithabort setting](http://stackoverflow.com/questions/35140159/failed-because-incorrect-arithabort-setting/35750269#35750269) page. Hope this helps... — Murat Yıldız, Mar 02 '16 at 14:41

score 9 · Accepted Answer · edited May 23 '17 at 11:54

9

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.

edited May 23 '17 at 11:54

Community

1
1

answered Jun 04 '14 at 05:20

Chankey Pathak

21,187
12
85
133

1

Thanks, yes that is how it should work. However this does not help. – Dan Walmsley Jun 04 '14 at 05:25
1

If you execute insertContractSQL2 statements directly on db do you get any error? – Chankey Pathak Jun 04 '14 at 05:34
No I don't get any error there. This missing peice was that the set had to be the first thing executed. Putting this first $boris_dbh->do(' 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, XACT_ABORT , NUMERIC_ROUNDABORT OFF'); fixed it. – Dan Walmsley Jun 04 '14 at 05:34
Ah I see. Yes, SET should be the first thing after you connect to DB. – Chankey Pathak Jun 04 '14 at 05:35
1

Now I have a simple example that works. But when I try to do the same thing in the real code it breaks. This is not my day... – Dan Walmsley Jun 04 '14 at 05:39
Do you use same `$dbh` throughout the script or do you connect again at some place in real code? Or is there any other module which does this and you're not aware of? – Chankey Pathak Jun 04 '14 at 05:42
Same $dbh, the only difference is a prepare a few statements before running the query. – Dan Walmsley Jun 04 '14 at 21:34
I gave up and now use a separate db handle for the query that has issues. This works but is nasty. Thanks for your help. – Dan Walmsley Jun 04 '14 at 23:33

score 1 · Answer 2 · answered Apr 27 '16 at 06:13

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...
')

score 1 · Answer 3 · answered Sep 26 '19 at 03:54

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'"

My solution is to add the keyword "GO" and the script worked.

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.

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

3 Answers3