|OLE DB Connection Arguments|
|Example: An MS SQL Server CONNECT statement|
|CONNECT TO OLEDB <AS alias > <(<OLE-DB-connection-arguments>)>;|
You use the following arguments with the CONNECT statement:
|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,
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
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.
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=.
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
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.
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=.
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=.
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=.
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.
|dBase III, IV, 5.0|
|Excel 3.0, 4.0, 5.0, 8.0|
|HTML Export, HTML Import|
|Jet 2.x, Jet 3.x|
|Lotus WJ2, WJ3|
|Lotus WK1, WK2, WK3, WK4|
|Paradox 3.x, Paradox 4.x, Paradox 5.x, Paradox 7.x|
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.
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;
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));
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.