SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
Occasional Contributor
Posts: 9

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

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

Occasional Contributor
Posts: 9

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

Posted in reply to 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

Occasional Contributor
Posts: 9

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

Posted in reply to jespersahner

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

Regards,

Jesper

Ask a Question
Discussion stats
  • 2 replies
  • 545 views
  • 0 likes
  • 1 in conversation