SAS to SQL Error via ODBC

[Jade_SAS]

Hi All,

   I am using SAS server in linux and tried the connection to MS SQL server via ODBC, I got errors. The connection could not be established successfully.

  1) When using Libname, no real error message in the log file, but the table I see from SAS library is not the real table in SQL studio (the Table in SAS library seems to have a chopped off name of the real table in SQL studio)

A: SAS statement:

LIBNAME sqltest odbc Password=** User=** DATASRC=** schema=**;

B: Table in SAS library, see attached word file.

C: Table in SQL studio, see attached word file.

   2) When using pass through, got error message.

A: SAS statement:

proc sql;

connect to odbc (noprompt = "dsn=**;DRIVER=SQLServer;uid=**;pwd=**;");

create table Prod02test as

select * from connection to odbc

(SELECT *

FROM [**].[**].[**]

)

;

disconnect from odbc;

quit;

B: Error message

ERROR: CLI describe error: [

What's the possible reason for that? Is there something wrong from SAS side or SQL side? Thank you!

Jade

[Jade_SAS]

Hi All,

   I am using SAS server in linux and tried the connection to MS SQL server via ODBC, I got errors. The connection could not be established successfully.

  1) When using Libname, no real error message in the log file, but the table I see from SAS library is not the real table in SQL studio (the Table in SAS library seems to have a chopped off name of the real table in SQL studio)

A: SAS statement:

LIBNAME sqltest odbc Password=** User=** DATASRC=** schema=**;

B: Table in SAS library, see attached word file.

C: Table in SQL studio, see attached word file.

   2) When using pass through, got error message.

A: SAS statement:

proc sql;

connect to odbc (noprompt = "dsn=**;DRIVER=SQLServer;uid=**;pwd=**;");

create table Prod02test as

select * from connection to odbc

(SELECT *

FROM [**].[**].[**]

)

;

disconnect from odbc;

quit;

B: Error message

ERROR: CLI describe error: [

What's the possible reason for that? Is there something wrong from SAS side or SQL side? Thank you!

Jade

[JBailey]

Hi @Jade_SAS

select * from connection to odbc

(anything in these parenthesis is being passed directly to the ODBC driver for translation. SAS has nothing to do with it. It is called explicit pass-through);

Best wishes,
Jeff

[Jade_SAS]

So you mean it is an error from SQL side? Not SAS server parameter sut up problem?

It seems all the tables extract via ODBC only has initail letter left.

[JBailey]

Yes, the problem is being reported by SQL Server. 

Can you provide a more readable version of your SQL code? There are too many "**" in there. It is hard to understand. What is your goal here? List of tables?

[Jade_SAS]

@JBailey

1)LIBNAME sqltest odbc Password=yourpassword User=yourID DATASRC=yourdatasource schema=yourschema;

 2) When using pass through

proc sql;

connect to odbc (noprompt = "dsn=yourdatasource;DRIVER=SQLServer;uid=yourID;pwd=yourPWD;");

create table Prod02test as

select * from connection to odbc

(SELECT *

FROM [database].[schema].[table]

)

;

disconnect from odbc;

quit;

[JBailey]

Hi @Jade_SAS

The following code uses SAS/ACCESS Interface to ODBC to connect to Microsoft SQL Server. It may help you (then again, it may not):

libname mssqlodb odbc dsn=mssql user=sasxjb password=mypassword;

/* Determine my default database so that I can use it in the query */
proc sql;
   connect using mssqlodb;
   select * from connection to mssqlodb
      (select name, default_database_name from sys.server_principals);
quit;

/* Can be written like */
proc sql;
   connect using mssqlodb;
   select * from connection to mssqlodb
      (select name, default_database_name from [sys].[server_principals]);
quit;

/* List all databases */
proc sql;
   connect using mssqlodb;
   select * from connection to mssqlodb
      (select name from [master].[dbo].[sysdatabases]);
quit;

/* Or... like this */
proc sql;
   connect using mssqlodb;
   select * from connection to mssqlodb
      (select name from master.dbo.sysdatabases);
quit;

/* Create a test table */
data mssqlodb.mytable;
   x=1; output;
run;

/* Read data from mytable. My default database is test */
proc sql;
   connect using mssqlodb;
   select * from connection to mssqlodb
      (select * from test.sasxjb.mytable);
quit;

/* Or... like this */
proc sql;
   connect using mssqlodb;
   select * from connection to mssqlodb
      (select * from [test].[sasxjb].[mytable]);
quit;

If I enter an invalid value I get something like this:

146  /* there is no database named bogus */
147  proc sql;
148     connect using mssqlodb;
149     select * from connection to mssqlodb
150        (select * from [bogus].[sasxjb].[mytable]);
ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
       'bogus.sasxjb.mytable'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s)
       could not be prepared.
151  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds

Hope this helps,

Jeff

[Jade_SAS]

Thank you, all these are using MSsql. We do not have that license.

We only can connect to MSSQL via ODBC at this moment. Thank you!

[JBailey]

Hi @Jade_SAS

The examples I provided use SAS/ACCESS Interface to ODBC to connect to a Microsoft SQL Server database.

Here is the LIBNAME statement:

libname mssql ODBC dsn=mssql user=sasxjb password=mypassword;

I will edit the code to make it more clear.

[Jade_SAS]

Thank you, my libname coding is the same as yours.

There is no problem with my libname and pass through coding, I do not know why the table name showed in SAS get chopped off, only initial letter of the table name left.

[JBailey]

Are all your queries failing?

Can you get something simple, like this, to work? Have you gotten any query to work?

libname mssqlodb odbc dsn=mssql user=myuser password=mypassword;

/* Create a test table */
data mssqlodb.mytable;
   x=1; output;
run;

proc sql;
   connect using mssqlodb;
   select * from connection to mssqlodb
      (select * from myuser.mytable);
quit;

If you can't get any query to work, it may be time to call Tech Support. If other queries work, there may be a hidden control character in the query text.

[Jade_SAS]

Thank you, I do not have writing right to the SQL server 😞

[JBailey]

Hi @Jade_SAS

OK, no problem.

Are you able to read any table from Microsoft SQL Server using SAS? Can any of your co-workers read a Microsoft SQL Server table using SAS?

Does a query similar to this work?

libname mssqlodb odbc dsn=mssql user=myuser password=mypasswd;

proc sql;
   connect using mssqlodb;
   select * from connection to mssqlodb
      (select * from sysobjects where xtype='U');
quit;

er 

[Jade_SAS]

No, get the same error:

ERROR: CLI describe error: [

[JBailey]

Hi @Jade_SAS

Are your co-workers able to successfully submit Microsoft SQL Server queries using SAS/ACCESS Interface to ODBC?