OleDbConnection fails while OdbcConnection works (using C# to write to SAS)

[jespersahner]

Hi,

I am using C# to write to SAS.

The following ODBC-example works:

	OdbcConnection conn = new OdbcConnection();
	conn.ConnectionString = "DSN=sasakt;";
	conn.Open();
	OdbcCommand cmd = new OdbcCommand();
	cmd.Connection = conn;
	cmd.CommandType = CommandType.Text;
	cmd.CommandText = "libname odbc 'C:/AKT/Data/x/test1';";
	cmd.ExecuteNonQuery();
	cmd.CommandText = "create table odbc.odbc (i num, name char(40), age num);";
	cmd.ExecuteNonQuery();
	conn.Close();

- while the identical OLEDB-example doesn't:

	OleDbConnection conn = new OleDbConnection();
	conn.ConnectionString = "Provider=sas.SHAREProvider;Data Source=sasakt;";
	conn.Open();
	OleDbCommand cmd = new OleDbCommand();
	cmd.Connection = conn;
	cmd.CommandType = CommandType.Text;
	cmd.CommandText = "libname oledb 'C:/AKT/Data/x/test1';";
	cmd.ExecuteNonQuery();
	cmd.CommandText = "create table oledb.oledb (i num, name char(40), age num);";
	cmd.ExecuteNonQuery();
	conn.Close();

I am running SAS 9.2, in the example "sasakt" is a local SAS/Share-server.

It seems that OLEDB doesn't recognize the libname-statement.

The SAS/share-log looks like this:

20   options comamid=tcp;

21

22   /* sasshare er specificeret i "C:\Windows\system32\Drivers\etc\services" med port=9453 */

23   proc server id=sasakt authenticate=optional;

24   run;

NOTE: Not all userids connecting to server SASAKT will be authenticated.  This server may be

      available to users who do not have userids on this system.

09MAY2015:20:12:37.164 SAS server SASAKT started.

NOTE: SAH211999I SAS/SHARE Server SASAKT, State, running

09MAY2015:20:12:42.504 User U02105(1) has connected to server SASAKT.

09MAY2015:20:12:42.505 User U02105(1) is executing release 9.02.02M0P01252009 (120) of SAS (r)

                       on DIFFERENT architecture host type: .

09MAY2015:20:12:42.512 U02105(1) in "ERREB"(0) has connected to SQLVIEW.

09MAY2015:20:12:42.520 U02105(1) in "ERREB"(0) has disconnected from SQLVIEW.

09MAY2015:20:12:42.521 User U02105(1) has disconnected from server SASAKT.

09MAY2015:20:12:42.605 User U02105(2) has connected to server SASAKT.

09MAY2015:20:12:42.605 User U02105(2) is executing release 9.02.02M0P01252009 (120) of SAS (r)

                       on DIFFERENT architecture host type: .

09MAY2015:20:12:42.608 U02105(2) in "ERREB"(0) has connected to SQLVIEW.

09MAY2015:20:12:42.610 Server library OLEDB ('C:\AKT\Data\x\test1' V9) accessed as OLEDB by

                       "ERREB"(0) of user U02105(2).

09MAY2015:20:12:42.612 U02105(2) in "ERREB"(0) has disconnected from SQLVIEW.

09MAY2015:20:12:42.613 Server library OLEDB (accessed as OLEDB) released by "ERREB"(0) of user

                       U02105(2).

09MAY2015:20:12:42.613 User U02105(2) has disconnected from server SASAKT.

09MAY2015:20:12:42.683 User U02105(3) has connected to server SASAKT.

09MAY2015:20:12:42.684 User U02105(3) is executing release 9.02.02M0P01252009 (120) of SAS (r)

                       on DIFFERENT architecture host type: .

09MAY2015:20:12:42.687 U02105(3) in "ERREB"(0) has connected to SQLVIEW.

09MAY2015:20:12:42.689 When U02105(3) in "ERREB"(0) tried to EXECUTE create table oledb.oledb

                       (i num, name char(40), age num); BY SQLVIEW:

                       ERROR: SQL passthru expression contained these errors: ERROR: Libname

                       OLEDB is not assigned..

Any ideas?

Regards,

Jesper

[jespersahner]

It should be noted that while writing using OLEDB doesn't work, reading works fine.

The following OLEDB-example works:

	OleDbConnection conn = new OleDbConnection();
	conn.ConnectionString = "Provider=sas.SHAREProvider;Data Source=sasakt;";
	conn.Open();
	OleDbCommand cmd = new OleDbCommand();
	cmd.Connection = conn;
	cmd.CommandType = CommandType.Text;
	cmd.CommandText = "libname oledb 'C:/AKT/Data/x/test1';";
	cmd.ExecuteNonQuery();
	cmd.CommandType = CommandType.TableDirect;
	cmd.CommandText = "oledb.test5";
	OleDbDataReader myReader = cmd.ExecuteReader();
	while (myReader.Read())
	{
	}
	myReader.Close();
	conn.Close();

Regards,

Jesper

[jespersahner]

The problem has now been reproduced by SAS Global Support and a track has been opened.

Regards,

Jesper