INSERT failed because the following SET opitions have incorrect settings: 'ANSI_PADDING'

Question

Post reply

INSERT failed because the following SET opitions have incorrect settings: 'ANSI_PADDING'

James Parkhurst

SSC Journeyman

Points: 95
More actions
March 16, 2016 at 4:00 pm

#324960

I am in the process of moving a table of data that stores uploaded files. Within this table, I have binary, image, and a computed column. There are a total of 1,410 records and I'm moving this data to a database on another server, using the 'Generate Scripts' method. While 1,299 of these records went in no problem, I am receiving the error above for the remaining records. ANSI_PADDING is disabled on the DB level but the the binary column within my table has 'ANSI Padding Status' set to TRUE. I've search all over and cannot find a solution to my problem. Any ideas?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

It looks like you are using the wrong syntax for ANSI_PADDING. I believe it should be On or Off...not True or False.

-SQLBill

James Parkhurst SSC Journeyman Points: 95 More actions · Answer 2

I was just explaining what SSMS is showing me. If I right click on the binary column, within General, it shows 'True' for the 'ANSI Padding Status' property but is read only. I'm confused as to why so many of the records went in fine but these last 100 or so are triggering this error. Any more ideas?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Well, let us see what the crystal ball tells us. Hmmm, nothing. Seems we can't see what you see from where we sit. Can you reproduce the error in an empty sandbox database with sample data that mirrors your current environment and data? If so, please post the DDL and sample data and we can try and help you solve the issue.

James Parkhurst SSC Journeyman Points: 95 More actions · Answer 4

Not sure how to setup a sandbox database but here is what the table looks like:

Table Name: BinaryFile

BinarySize int NOT NULL,

BinaryHash binary(16) NOT NULL,

BinaryData image NOT NULL,

MimeTypeId smallint NOT NULL,

CompressedSize as (isnull(datalength(BinaryData),(0))) PERSISTED NOT NULL,

CONSTRAINT Pk_BinaryFile_BinarySizeBinaryHash PRIMARY KEY CLUSTERED (BinarySize ASC, BinaryHash ASC)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY])

ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

A sandbox database is nothing more than an empty database where you can create, modify, and test database objects (tables, views, stored procedures, etc.,...) without affecting production, qa, test, or development databases.