The LIBNAME Statement for Relational Databases

IGNORE_ READ_ONLY_COLUMNS= LIBNAME Option

Specifies whether to ignore or include columns whose data types are read-only when generating an SQL statement for inserts or updates.

Default value:	NO
Valid in:	SAS/ACCESS LIBNAME statement
DBMS support:	Aster nCluster, DB2 under UNIX and PC Hosts, Greenplum, HP Neoview, Microsoft SQL Server, Netezza, ODBC, OLE DB, Sybase IQ

Syntax
Syntax Description
Details
Example
See Also

Syntax

IGNORE_READ_ONLY_COLUMNS=YES | NO

Syntax Description

YES: specifies that the SAS/ACCESS engine ignores columns whose data types are read-only when you are generating insert and update SQL statements.
NO: specifies that the SAS/ACCESS engine does not ignore columns whose data types are read-only when you are generating insert and update SQL statements.

Details

Several databases include data types that can be read-only, such as the data type of the Microsoft SQL Server timestamp. Several databases also have properties that allow certain data types to be read-only, such as the Microsoft SQL Server identity property.

When IGNORE_READ_ONLY_COLUMNS=NO and a DBMS table contains a column that is read-only, an error is returned indicating that the data could not be modified for that column.

Example

For the following example, a database that contains the table Products is created with two columns: ID and PRODUCT_NAME. The ID column is defined by a read-only data type and PRODUCT_NAME is a character column.

CREATE TABLE products (id int IDENTITY PRIMARY KEY, product_name varchar(40))

Assume you have a SAS data set that contains the name of your products, and you would like to insert the data into the Products table:

data work.products;
   id=1;
   product_name='screwdriver';
   output;
   id=2;
   product_name='hammer';
   output;
   id=3;
   product_name='saw';
   output;
   id=4;
   product_name='shovel';
   output;
run;

With IGNORE_READ_ONLY_COLUMNS=NO (the default), an error is returned by the database because in this example the ID column cannot be updated. However, if you set the option to YES and execute a PROC APPEND, the append succeeds, and the SQL statement that is generated does not contain the ID column.

libname x odbc uid=dbitest pwd=dbigrp1 dsn=lupinss
               ignore_read_only_columns=yes;
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc append base=x.PRODUCTS data=work.products;
run;