This is a crossover problem -- I'm accessing an MS SQL Server database from SAS on Windows 2000 via OLE DB. I've decided that the problem area is OLE which is Microsoft shite technology, hence placement here.

Some of the following may be specific to SAS, but I'm trying to work out how to get the general properties for an OLE DB connection.

I'm trying to work out the syntax for this, docs and Google offer thin comfort. SAS Tech support doesn't know any better (first tier anyhow) either.

I've an ODBC data source configured to an MS SQL database server. This connection used as an ODBC source works flawlessly.

I've configured an OLE-DB (OLEDB) connection to this ODBC source using a UDL file, foo.udl. This specifies the ODBC connection to use, but nothing else (bless Microsoft's binary configuration formats, there's no useful way I'm aware to dump the settings here).

I can connect to this source interactively, selecting the "provider"\r\n("Microsoft OLE DB Provider for ODBC Drivers") and "data source" ("SQLDB" as configured).

Trying to set up a noninteractive connection has stumped me. Best I've come up with is:

\r\n

\r\n    proc sql;\r\n    connect to oledb as bar\r\n        provider='Microsoft.Jet.OLEDB.4.0'\r\n        properties=('data source'='Desktop/foo.udl')\r\n        );\r\n

\r\n

...which results in an error.

OK, with the data retrieval methods indicated earlier, we've got her licked. Part of my remaining problem was that I was calling the connection by a name other than the alias I'd assigned it. The following works:

\r\n

\r\n484  /* Get OLEDB connection info */\r\n485  proc sql;\r\n486      connect to oledb as bar (\r\n487          Provider=MSDASQL.1\r\n488          properties=("Data Source"="SQLDAT")\r\n489          );\r\n490      select * from connection to bar (select * from sysfiles);\r\n491  quit;\r\nNOTE: PROCEDURE SQL used:\r\n      real time           0:00:00.03\r\n      cpu time            0:00:00.01\r\n

\r\n

Note that the name of the connection in the second statement matches the alias above. I was stumbling on that minor detail....

Connection settings were uncovered using the dd trick above, and with the &SYSDBMSG SAS automatic macrovariable (%PUT &SYSDBMSG; immediately following an interactive connection).