Date:         Fri, 18 Mar 2005 13:57:04 -0500
Reply-To:     Mike Rhoads <RHOADSM1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mike Rhoads <RHOADSM1@WESTAT.COM>
Subject:      Re: SQL commands passed from SEG v3 to SQL Dbase
Comments: To: "Worlund, Max" <ZQY3@CDC.GOV>
Content-Type: text/plain; charset="us-ascii"

Max,

I haven't tried this, but I think what you want to do is:

1.  In PROC SQL, use the CONNECT statement to connect to SQL Server
(instead of the
implicit connection with a LIBNAME statement),
2.  Use EXECUTE statements to execute your SET statement, then your
INSERT statement,
3.  Use DISCONNECT to close the connection.

This makes sure that the SET IDENTITY_INSERT gets passed to SQL Server,
which
understands it.  SAS doesn't have a clue, as you found out.

Hope this helps!

Mike Rhoads
Westat
RhoadsM1@Westat.com

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Worlund, Max
Sent: Friday, March 18, 2005 12:59 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SQL commands passed from SEG v3 to SQL Dbase


I'm working with SAS Enterprise Guide and trying to do a SQL Insert to a
SQL Database table.  I need to set IDENTITY_INSERT to ON.  The SQL
command is "Set Identity_Insert <table name> On".   I can't get PROC SQL
to recognize this command or pass it to the SQL database.  I'm trying to
do this via SEG and not SQL Enterprise Manager so that the table
build/maintenance process will be a one button process and not have the
user get the SQL DBA to do the Insert via DTS.  Any ideas? Thanks.


proc sql;
set IDENTITY_INSERT SQLTEST1.sys_df_standard on;
insert into SQLTEST1.sys_df_standard
select standard_uid, field_to_standardize, standard_table_source,
standard_cd, standard_cd_descr, incoming_val
from dfpower.standard_tbl;



Max A. Worlund

CDC Information Technology Support Contract (CITS)

Software Engineer III

NCHSTP/SDMB

Phone:  404.639.4802

Fax:  404.639.8642

email:  zqy3@cdc.gov