Identity_Insert and SAS/Access

Prev: Questions about SAS DDE,
Next: Error on SAS ACCESS TO ORACLE

From: Gerry on 28 Nov 2006 10:13

---

Ken:

Talk about timing -- we just crossed a similar "bridge" ! We needed to
reload already created, though empty tables, in another schema (there were
massive edits done to the tables in the database). Of the 135 tables, about
half used the IDENTITY property.

SI tech support made a few recommendations. The first was to try the option
"IGNORE_READ_ONLY_COLUMNS=YES". This option ignores identity or primary key
columns. This wasn't appropriate for our situation. Unfortunately, we needed
to replicate the tables exactly (the identity column was filled, and needed
to be reloaded in the new schema).

Another suggestion from SI was to create a view of the table(s), excluding
the identity column and insert/update from the view -- we haven't tried this
yet (we were pressed for time to get the test data out, and did it brute
force).

Another, though none-SAS option, is to use a stored procedure to deal with
the column like below. This copies the table, and adds the identity property
(such as can be done manually in the Sql Server EM).

Hope some of this helps !

-Gerry

Gerard T. Pauline
Mgr, Internet Applications &
University Web Master
Computer Systems, DoIT
Pace University

========================================================================

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
COMMIT
BEGIN TRANSACTION

CREATE TABLE dbo.Tmp_Table1
( Test_ID int NOT NULL IDENTITY (5, 1) ) ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO

IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (Test_ID)
SELECT Test_ID FROM dbo.Table1 (HOLDLOCK TABLOCKX)')
GO

SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO

DROP TABLE dbo.Table1
GO

EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO

ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
( Test_ID ) ON [PRIMARY]
GO

COMMIT

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of Barz,
Ken
Sent: Monday, November 27, 2006 7:29 PM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: Identity_Insert and SAS/Access

When using the libname approach for SAS/Access for ODBC, is there some
option that will mimic the database's (SQL Server) SET IDENTITY_INSERT
.... ON runtime statement? I can probably get it to work using RSPT, but
for the moment I'd like to see if it can work this way. (rec_id is the
identity column in the database)

Thanks,

Ken



libname mydblib odbc datasrc=SQL_Test ;



data ali_fact (drop=biddt respdt);

set ali.ali0802 (keep=npa nxx line biddt respdt);

rec_id = .;

bid_date = biddt;

response_date = respdt;

format bid_date response_date datetime21.;

run;



proc sort data=ali_fact;

by bid_date response_date npa nxx line;

run;



proc append base=mydblib.ali_fact (sasdatefmt=(bid_date='datetime21.'


response_date='datetime21.'))

data=ali_fact ;

run;



NOTE: The data set WORK.ALI_FACT has 128625 observations and 6
variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.68 seconds

cpu time 0.39 seconds





16

17

18 proc append base=mydblib.ali_fact
(sasdatefmt=(bid_date='datetime21.'

19
response_date='datetime21.'))

20 data=ali_fact ;

21 run;



NOTE: Appending WORK.ALI_FACT to MYDBLIB.ali_fact.

NOTE: There were 1 observations read from the data set WORK.ALI_FACT.

NOTE: 0 observations added.

NOTE: The data set MYDBLIB.ali_fact has . observations and 6 variables.

ERROR: CLI execute error: [Microsoft][SQL Native Client][SQL
Server]Cannot insert explicit value for

ERROR: CLI execute error: [Microsoft][SQL Native Client][SQL
Server]Cannot insert explicit value for

ERROR: CLI execute error: [Microsoft][SQL Native Client][SQL
Server]Cannot insert explicit value for

identity column in table 'ali_fact' when IDENTITY_INSERT is set
to OFF.

identity column in table 'ali_fact' when IDENTITY_INSERT is set
to OFF.

identity column in table 'ali_fact' when IDENTITY_INSERT is set
to OFF.

NOTE: Statements not processed because of errors noted above.

NOTE: PROCEDURE APPEND used (Total process time):

real time 0.09 seconds

cpu time 0.07 seconds



NOTE: The SAS System stopped processing this step because of errors.

NOTE: SAS set option OBS=0 and will continue to check statements.

This may cause NOTE: No observations in data set.

22



Ken Barz

Software Engineer

Intrado Metrics



Intrado Inc.

1601 Dry Creek Drive

Longmont, CO 80503

direct: 720.494.5868

pager: 303-581-7667

fax: 720.494.6600

email: ken.barz(a)intrado.com



Intrado.(r)

www.intrado.com
<file:///C:\Documents%20and%20Settings\kbarz\Application%20Data\Microsof
t\Signatures\www.intrado.com>



ATTENTION:



The information contained in this electronic message and any attachments
to this message are intended for the exclusive use of the addressee(s)
and may contain confidential or privileged information. If you are not
the intended recipient, please notify Intrado Inc. immediately at
720.494.5800 and destroy all copies of this message and any attachments.

From: "Barz, Ken" on 28 Nov 2006 11:16

---

The IGNORE_READ_ONLY_COLUMNS option worked beautifully. Thank you!

-----Original Message-----
From: Gerry [mailto:gpauline(a)pace.edu]
Sent: Tuesday, November 28, 2006 8:14 AM
To: Barz, Ken; SAS-L(a)LISTSERV.UGA.EDU
Cc: 'Frida Aminova'
Subject: RE: Identity_Insert and SAS/Access

Ken:

Talk about timing -- we just crossed a similar "bridge" ! We needed to
reload already created, though empty tables, in another schema (there
were
massive edits done to the tables in the database). Of the 135 tables,
about
half used the IDENTITY property.

SI tech support made a few recommendations. The first was to try the
option
"IGNORE_READ_ONLY_COLUMNS=YES". This option ignores identity or primary
key
columns. This wasn't appropriate for our situation. Unfortunately, we
needed
to replicate the tables exactly (the identity column was filled, and
needed
to be reloaded in the new schema).

Another suggestion from SI was to create a view of the table(s),
excluding
the identity column and insert/update from the view -- we haven't tried
this
yet (we were pressed for time to get the test data out, and did it brute
force).

Another, though none-SAS option, is to use a stored procedure to deal
with
the column like below. This copies the table, and adds the identity
property
(such as can be done manually in the Sql Server EM).

Hope some of this helps !

-Gerry

Gerard T. Pauline
Mgr, Internet Applications &
University Web Master
Computer Systems, DoIT
Pace University

========================================================================

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
COMMIT
BEGIN TRANSACTION

CREATE TABLE dbo.Tmp_Table1
( Test_ID int NOT NULL IDENTITY (5, 1) ) ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO

IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (Test_ID)
SELECT Test_ID FROM dbo.Table1 (HOLDLOCK TABLOCKX)')
GO

SET IDENTITY_INSERT dbo.Tmp_Table1 ON
GO

DROP TABLE dbo.Table1
GO

EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO

ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
( Test_ID ) ON [PRIMARY]
GO

COMMIT

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of
Barz,
Ken
Sent: Monday, November 27, 2006 7:29 PM
To: SAS-L(a)LISTSERV.UGA.EDU
Subject: Identity_Insert and SAS/Access

When using the libname approach for SAS/Access for ODBC, is there some
option that will mimic the database's (SQL Server) SET IDENTITY_INSERT
.... ON runtime statement? I can probably get it to work using RSPT, but
for the moment I'd like to see if it can work this way. (rec_id is the
identity column in the database)

Thanks,

Ken



libname mydblib odbc datasrc=SQL_Test ;



data ali_fact (drop=biddt respdt);

set ali.ali0802 (keep=npa nxx line biddt respdt);

rec_id = .;

bid_date = biddt;

response_date = respdt;

format bid_date response_date datetime21.;

run;



proc sort data=ali_fact;

by bid_date response_date npa nxx line;

run;



proc append base=mydblib.ali_fact (sasdatefmt=(bid_date='datetime21.'


response_date='datetime21.'))

data=ali_fact ;

run;



NOTE: The data set WORK.ALI_FACT has 128625 observations and 6
variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.68 seconds

cpu time 0.39 seconds





16

17

18 proc append base=mydblib.ali_fact
(sasdatefmt=(bid_date='datetime21.'

19
response_date='datetime21.'))

20 data=ali_fact ;

21 run;



NOTE: Appending WORK.ALI_FACT to MYDBLIB.ali_fact.

NOTE: There were 1 observations read from the data set WORK.ALI_FACT.

NOTE: 0 observations added.

NOTE: The data set MYDBLIB.ali_fact has . observations and 6 variables.

ERROR: CLI execute error: [Microsoft][SQL Native Client][SQL
Server]Cannot insert explicit value for

ERROR: CLI execute error: [Microsoft][SQL Native Client][SQL
Server]Cannot insert explicit value for

ERROR: CLI execute error: [Microsoft][SQL Native Client][SQL
Server]Cannot insert explicit value for

identity column in table 'ali_fact' when IDENTITY_INSERT is set
to OFF.

identity column in table 'ali_fact' when IDENTITY_INSERT is set
to OFF.

identity column in table 'ali_fact' when IDENTITY_INSERT is set
to OFF.

NOTE: Statements not processed because of errors noted above.

NOTE: PROCEDURE APPEND used (Total process time):

real time 0.09 seconds

cpu time 0.07 seconds



NOTE: The SAS System stopped processing this step because of errors.

NOTE: SAS set option OBS=0 and will continue to check statements.

This may cause NOTE: No observations in data set.

22



Ken Barz

Software Engineer

Intrado Metrics



Intrado Inc.

1601 Dry Creek Drive

Longmont, CO 80503

direct: 720.494.5868

pager: 303-581-7667

fax: 720.494.6600

email: ken.barz(a)intrado.com



Intrado.(r)

www.intrado.com
<file:///C:\Documents%20and%20Settings\kbarz\Application%20Data\Microsof
t\Signatures\www.intrado.com>



ATTENTION:



The information contained in this electronic message and any attachments
to this message are intended for the exclusive use of the addressee(s)
and may contain confidential or privileged information. If you are not
the intended recipient, please notify Intrado Inc. immediately at
720.494.5800 and destroy all copies of this message and any attachments.

 | 
Pages: 1
Prev: Questions about SAS DDE,
Next: Error on SAS ACCESS TO ORACLE