Chapter Contents

Previous

Next

CONNECT Statement

Arguments

You use the following arguments with the CONNECT statement:

alias

specifies an optional alias that has 1 to 32 characters. If you specify an alias, the keyword AS must appear before the alias.

(OLE DB-connection-arguments)

specifies the data source-specific arguments that PROC SQL needs in order to connect to the data source, such as a relational DBMS. These arguments must be enclosed in parentheses. For some data sources, these arguments have default values and, therefore, are optional. The arguments for OLE DB are described in the following sections.

OLE DB Connection Arguments

PROC SQL supports multiple connections to OLE DB. If you use multiple simultaneous connections, you must use an alias to identify the different connections. If you do not specify an alias, the default alias, OLEDB, is used. The functionality of multiple connections to the same OLE DB provider might be limited by a particular provider.

The CONNECT statement is required when connecting to OLE DB providers by way of the SQL Pass-Through Facility.

Note:   Not all of these engine-connection options are supported by all OLE DB providers. Refer to your vendor-supplied documentation for more information.  

The arguments for the data-source connection information arguments can be quoted by using either single or double quotes. Some values may include embedded spaces, semicolons, or quotes and, therefore, must be quoted.

The OLE DB connection arguments are

	AUTOCOMMIT=
	COMPLETE=
	INIT_STRING=
	OLEDB_SERVICES=
	PROMPT=
	PROPERTIES=
	PROVIDER=
	PROVIDER_STRING=
	REQUIRED=

AUTOCOMMIT=YES | NO

indicates whether or not updates are committed immediately to the data source as soon as they are submitted.

Default value: YES.

If AUTOCOMMIT=YES, no rollback is possible. This is the default for the SQL Procedure Pass-Through Facility and read-only connections.

If AUTOCOMMIT=NO, the SAS/ACCESS engine performs the commit automatically when it reaches the end of the file.

COMPLETE=YES | NO

specifies whether the SAS/ACCESS engine for OLE DB tries to connect to the data source with the properties that you specified in PROPERTIES=. If COMPLETE=YES, the engine tries to connect. If enough information is specified for a successful connection, then the connection is made without any prompting for more information.

Default value: NO.

If you specify COMPLETE= NO or do not have enough information to connect, you are prompted for the connection options with a dialog window.

COMPLETE= is used only when you use connect directly to OLE DB using the PROVIDER= and OLEDB_SERVICES=NO options in a SAS/ACCESS LIBNAME statement.

See also these arguments: PROMPT=, PROPERTIES=, PROVIDER=, OLEDB_SERVICES=.

INIT_STRING="<initilization-string>"

using OLE DB Services, INIT_STRING= specifies an initialization string when connecting to a data source. After you are prompted to supply information to connect to your data source, the SAS/ACCESS engine for OLE DB returns the complete initialization string to the macro variable, SYSDBMSG. You can then re-use the initialization string to connect to the same provider and data source.

In this example, you submit the basic connection information so that you will be prompted for the rest of the information. Using OLE DB Services is the default value, so you can omit the OLEDB_SERVICES= option.

libname mydblib oledb;

Through dialog windows, OLE DB Services prompts you for the provider and properties' values. The advantage of being prompted is that you do not need to know any special syntax to set the properties'. Prompting also enables you to set more options than you might when connecting directly to the provider (and not using OLE DB Services).

After connecting to the data source, the SAS/ACCESS engine for OLE DB returns the initialization string to the SYSDBMSG macro variable. To write this string to the SAS log immediately after connecting to the data source, submit the following:

%put &SYSDBMSG;

OLEDB:  Provider=SQLOLEDB;Password=dbmgr1;Persist Security Info=True;
User ID=rachel;Initial Catalog=users;Data Source=DBPC6;

The SYSDBMSG information mirrors all of the options that you chose during your prompted connection. Notice that the initialization string is prefixed with OLEDB:. When you store the string for later use, delete this prefix and any initial spaces before the first listed option.

By storing the initialization string, you can re-use it in the INIT_STRING= option to make automated connections or to specify this option in batch jobs:

init_string="Provider=SQLOLEDB;Password=dbmgr1;
Persist Security Info=True;User ID=rachel;
Initial Catalog=users;Data Source=DBPC6"; 

Using INIT_STRING= enables you to bypass the prompting window but still gives you the advantages of the OLE DB Services, such as performance optimizations.

Specifying INIT_STRING= overrides any values that were set with the SAS/ACCESS connection options PROVIDER= and PROPERTIES=.

By default, the INIT_STRING= option is not set. However, if you specify the option with a null argument, ( INIT_STRING=""), OLE DB connects to ODBC with a default set of properties. See the Microsoft OLE DB documentation for more information about these defaults.

Alias: INIT=.

OLEDB_SERVICES=YES | NO

determines whether the SAS/ACCESS engine for OLE DB uses OLE DB Services. OLEDB_SERVICES=YES causes the engine to use OLE DB Services, and OLEDB_SERVICES=NO causes the engine to use the provider to connect to the data source.

The default value is YES. Generally, OLE DB Services is easier to use and more consistent. When OLEDB_SERVICES=YES and a successful prompted connection is made, the complete connection string is returned in the SYSDBMSG macro variable.

OLEDB_SERVICES= interacts with other connection options. If you have set PROMPT=YES, OLEDB_SERVICES=YES enables you to set more options than you would be able to set by being prompted by the provider's dialog window. If OLEDB_SERVICES=NO, you must specify PROVIDER= first in order for the provider's prompt dialogs to be used. If PROVIDER= is omitted, the SAS/ACCESS engine uses OLE DB Services, regardless of how the OLEDB_SERVICES= option is set.

If the BCP=YES option is set for MS SQL Server data, then OLEDB_SERVICES=NO. OLEDB_SERVICES= also interacts with the PROMPT=, REQUIRED= , and COMPLETE= arguments.

See these arguments for more information:  COMPLETE=, PROMPT=, PROVIDER=, REQUIRED=.

PROMPT=YES | NO

enables you to be prompted for connection information to access the data source. The kind of prompting that you receive depends on how you set the PROVIDER= and OLEDB_SERVICES= options.

If a provider name is specified and OLEDB_SERVICES= NO, the OLE DB provider displays a dialog window that contains the connection information and property attributes. If the provider name is omitted or OLEDB_SERVICES=YES, the OLE DB Services displays a dialog window that enables you to select a provider and to specify connection information and property attributes. The dialog window for OLE DB Services is generally preferred over the provider's dialog window because the OLE DB Services window enables you to set options more easily.

If PROMPT=YES, properties that were set with PROPERTIES= will be displayed in the dialog window. This applies both to the provider dialog window and to the OLE DB Services dialog window. You can edit any field before you connect to the data source.

If the provider name is omitted, the SAS/ACCESS engine for OLE DB tries to prompt you for the connection information by using the OLE DB Services dialog window. This applies even if PROMPT=NO and OLEDB_SERVICES= NO. If the provider name is omitted in batch mode, the connection fails.

If you are unsure what to specify for various provider properties, use the PROMPT= option to guide you through the connection process.

See the following arguments for more information: PROVIDER=, OLEDB_SERVICES=, PROPERTIES=.

PROPERTIES=(<">property-name-1<">=<">property-value-1<"> . . . <">property-name-n <">=<">property-value-n<">)

specifies provider properties that enable you to connect to a data source and to define the attributes of that connection. Each property name is assigned a value using an equal sign (=). If the property name or value contains embedded spaces or special characters, enclose the name in double quotes. Separated multiple pairs with a space. PROPERTIES= is optional in OLE DB.

In this example, you specify a user ID and password to connect to a Microsoft SQL Server data source, you would enter:

libname mydblib oledb provider=sqloledb 
                properties=("User ID"=shala 
                Password="mypw@hr");

Note:   See your provider's documentation for a list and description of all the properties that your provider supports.   

Aliases: PROPS= and PROP=.

PROVIDER=<'> your-provider-name<'>

specifies the OLE DB provider to use in order to connect to the data source. The PROVIDER= option is required during batch processing.

There is no restriction on the length of the name. Put names with non-standard SAS characters (such as spaces, colons, @ signs) in single or double quotations marks.

If you omit this option, you are prompted for the provider name. It is recommended that, if possible, you use the dialog prompts to connect to your data source. The prompts enable you to use an interactive interface to enter the name of the provider, properties, and connection options.

Alias: PROV=.

PROVIDER_STRING=<">provider-name<">

passes additional provider-specific connection information to the provider. The provider-string is enclosed in quotation marks if it contains blank spaces or special characters. The PROVIDER_STRING= option works whether you are connecting directly to the provider or are using OLE DB Services. Microsoft uses this provider-string for its Jet provider in order to determine the type of data source to which it connects. MS Jet currently accepts the following providers; this list is not all-inclusive and is subject to change by Microsoft:

	dBase III, IV, 5.0
	Excel 3.0, 4.0, 5.0, 8.0
	Exchange 4.0
	HTML Export, HTML Import
	Jet 2.x, Jet 3.x
	Lotus WJ2, WJ3
	Lotus WK1, WK2, WK3, WK4
	Outlook 9.0
	Paradox 3.x, Paradox 4.x, Paradox 5.x, Paradox 7.x
	Text

Providers other than MS Jet accept other provider-strings, and the values that MS Jet accepts are subject to change. For example, to connect to an Excel 8.0 spreadsheet using the Microsoft Jet provider, you could issue the following LIBNAME statement:

libname y2kbudget oledb provider="Microsoft.Jet.OLEDB.4.0"  
  properties=('data source'='d:\excel80\Y2Kbudgetworksheet.xls') 
  provider_string="Excel 8.0";

In this example's LIBNAME statement, you use the Microsoft Jet 4.0 provider to access the spreadsheet Y2Kbudgetworksheet.xls. Notice that you must specify the provider-string "Excel 8.0" so that MS Jet recognizes that the file is an Excel 8.0 worksheet.

REQUIRED=YES | NO

indicates whether you specify connection options for your provider.

If you specify REQUIRED= YES, the SAS/ACCESS engine for OLE DB tries to connect to the data source by using the properties that were specified in PROPERTIES=. If you specify enough information to make a connection, then the connection is made without prompting. Otherwise, a dialog window is displayed to prompt you for the connection options. Options in the dialog window that are not related to the connection are disabled.

REQUIRED= is used only when you connect directly to OLE DB using the PROVIDER= and OLEDB_SERVICES=NO options in a SAS/ACCESS LIBNAME statement. The default value is REQUIRED=NO.

For more information, see OLEDB_SERVICES=, PROPERTIES=, PROVIDER=.

Example: An MS SQL Server CONNECT statement

In this example, you use an alias to connect to a Microsoft SQL Server database and select a subset of data from the payroll table. The SAS/ACCESS engine uses OLE DB Services to connect to OLE DB because this is the default action when the OLEDB_SERVICES= option is omitted.

proc sql;
connect to sqlservr as finance
  (properties=('data source'='Microsoft SQL Server Database' 
     'User ID'=isabella password=tester1
   provider=sqloledb));
 
select * from connection to finance (select * from payroll 
                                     where jobcode='FA3');
quit;

In this example, you are prompted for more information because the PROC SQL CONNECT statement has omitted the provider name and properties. See Connecting with OLE DB Services for a sample prompt window. This example also uses OLE DB Services to connect to OLE DB.

proc sql;
connect to oledb;
quit;

CONNECT Example

This example uses OLE DB to connect to a provider that is configured under the data source name User's Data using the alias USER1. Note that the data source name(s) can contain quotes and spaces.

proc sql;
   connect to oledb as user1 
   (properties=("data source"="User's Data" "User id"=adi 
                 password=ghana));

Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.