Jack,

Since object references within SQL Server normally reference the database schema as well as the name of the object, I would try something like the following, using SQL Passthrough:

connect ... execute ( create view dbo.jack_view as select * from dbo.claims_fy08_dent; ) by odbc;

replacing "dbo" with your actual schema name.

I was successfully able to run an example like this, although with OLEDB rather than ODBC.

Mike Rhoads RhoadsM1@Westat.com

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Jack Clark Sent: Monday, December 20, 2010 10:33 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Create View on SQL Server Database with PROC SQL

Hello,

I am trying to help a colleague with this task. We have a SQL Server database that is updated with a SAS program. The SAS program uses the LIBNAME statement with ODBC to link to the database. This has been working fine.

Now, we want to create a VIEW on the SQL Server database (not in SAS) using SAS code. I tried the syntax below, but get an ERROR which doesn't tell me much. My colleague has also tried the SQL Passthrough approach, but says the view gets created in the WORK area of the database, not as a permanent view.

Can someone suggest how this can be corrected? Thank you.

libname wf2 odbc noprompt="dsn=wfsql2_clm;" bulkload=yes;

proc sql;

create view wf2.jack_view as

select *

from wf2.claims_fy08_dent

;

quit;

369 libname wf2 odbc noprompt=XXXXXXXXXXXXXXXXX bulkload=yes;

NOTE: Libref WF2 was successfully assigned as follows:

Engine: ODBC

Physical Name: wfsql2_clm

370

371 proc sql;

372 create view wf2.jack_view as

373 select *

374 from wf2.claims_fy08_dent

375 ;

ERROR: Create View failed.

376 quit;

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

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

Jack Clark Senior Research Analyst phone: 410-455-6256 fax: 410-455-6850 jclark@hilltop.umbc.edu

University of Maryland, Baltimore County Sondheim Hall, 3rd Floor 1000 Hilltop Circle Baltimore, MD 21250

Please consider the environment before printing this e-mail and/or any attachments.

Confidentiality Notice: This e-mail may contain information that is legally privileged and that is intended only for the use of the addressee(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying of this e-mail, distribution, or action taken in reliance on the contents of this e-mail and/or documents attributed to this e-mail is strictly prohibited. If you have received this information in error, please notify the sender immediately by phone and delete this entire e-mail. Thank you.