LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (March 2005, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


Back to: Top of message | Previous page | Main SAS-L page