Usage Note 52403: How to generate your connection string to an OLE DB datasource
 |  |  |  |
To generate the correct syntax for your OLE DB connection options, complete a prompted connection by using the Data Link Properties dialog box, invoked with the following code:
libname mydb oledb;
%put %superq(sysdbmsg);
Respond to the dialog box prompts, supplying the information necessary to connect using the specific OLE DB provider.
After the above code has been submitted, and you have responded to the prompts of the Data Link Properties dialog box, the SAS® log then contains line(s) similar to the following:
OLEDB: Provider=SQLOLEDB;Password=sqlpwd;Persist Security Info=True;
User ID=sqluser;Initial Catalog=users;Data Source=ServerName;
Copy the entire string from the SAS log (excluding "OLEDB:") and paste it into your SAS/ACCESS® Interface to OLE DB code as a quoted init_string, like this:
libname mydb oledb init_string="Provider=SQLOLEDB;Password=sqlpwd;
Persist Security Info=True;User ID=sqluser;
Initial Catalog=users;
Data Source=ServerName";
The same init_string can be used in SQL procedure pass-through code. Here is an example:
proc sql;
connect to oledb(init_string="Provider=SQLOLEDB;Password=sqlpwd;
Persist Security Info=True;User ID=sqluser;
Initial Catalog=users;Data Source=ServerName");
create table work.test as select * from connection to oledb
(select * from MyTable);
disconnect from oledb;
quit;
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to OLE DB | Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M3 SP4 | |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.1 TS1M3 SP4 | |||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.1 TS1M3 SP4 | |||
| Microsoft Windows XP 64-bit Edition | 9.1 TS1M3 SP4 | |||
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M3 SP4 | |||
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M3 SP4 | |||
| Microsoft Windows 2000 Server | 9.1 TS1M3 SP4 | |||
| Microsoft Windows 2000 Professional | 9.1 TS1M3 SP4 | |||
| Microsoft Windows NT Workstation | 9.1 TS1M3 SP4 | |||
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M3 SP4 | |||
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M3 SP4 | |||
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M3 SP4 | |||
| Microsoft Windows XP Professional | 9.1 TS1M3 SP4 | |||
| Windows Vista | 9.1 TS1M3 SP4 | |||
| Windows Vista for x64 | 9.1 TS1M3 SP4 | |||
| Type: | Usage Note |
| Priority: |
| Date Modified: | 2014-03-12 15:28:10 |
| Date Created: | 2014-02-21 10:10:20 |