BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jade_SAS
Pyrite | Level 9

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jade_SAS
Pyrite | Level 9

Contacted with SAS Support, and it turned out there is system setting up problem with the SAS linux.

1)Try to insert the following line to $SASROOT/bin/sasenv_local file:

export EASYSOFT_U NICODE=YES

2)Restart SAS on server

 

Then everything is fixed.

View solution in original post

24 REPLIES 24
Jade_SAS
Pyrite | Level 9

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
Barite | Level 11

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
Pyrite | Level 9

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
Barite | Level 11

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
Pyrite | Level 9

@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
Barite | Level 11

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
Pyrite | Level 9

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
Barite | Level 11

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
Pyrite | Level 9

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
Barite | Level 11

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
Pyrite | Level 9

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

JBailey
Barite | Level 11

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
Pyrite | Level 9

No, get the same error:

 

ERROR: CLI describe error: [

 

JBailey
Barite | Level 11

Hi @Jade_SAS

 

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 24 replies
  • 17357 views
  • 4 likes
  • 4 in conversation