SAS to SQL Error via ODBC

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 106
Accepted Solution

SAS to SQL Error via ODBC

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

 


Accepted Solutions
Solution
‎02-27-2017 09:24 AM
Frequent Contributor
Posts: 106

Re: SAS to SQL Error via ODBC

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


All Replies
SAS Super FREQ
Posts: 291

Re: SAS to SQL Error via ODBC

Hallo Jade,

 

The error message seems to be missing some info. What comes behind the error:[ ?

 

Are you using Access to ODBC, and if yes, what bitness?

What is the bitness of your OS?

 

Thanks

Anja

 

Frequent Contributor
Posts: 106

Re: SAS to SQL Error via ODBC

@ Sorry I missed this reply.

 

That's all the Error said.

 

yes, I am using access ODBC to connect to Microsoft sql tables, I think it is 32 bit.

Talking OS, I may need to ask the SAS admin, as we using SAS server.

 

Thank you!

Frequent Contributor
Posts: 106

SAS Connection to SQL error via ODBC

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

 

SAS Employee
Posts: 198

Re: SAS Connection to SQL error via ODBC

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

 

 

Frequent Contributor
Posts: 106

Re: SAS Connection to SQL error via ODBC

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.

SAS Employee
Posts: 198

Re: SAS Connection to SQL error via ODBC

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?

Frequent Contributor
Posts: 106

Re: SAS Connection to SQL error via ODBC

@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;

SAS Employee
Posts: 198

Re: SAS Connection to SQL error via ODBC

[ Edited ]

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

 

Frequent Contributor
Posts: 106

Re: SAS Connection to SQL error via ODBC

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!

SAS Employee
Posts: 198

Re: SAS Connection to SQL error via ODBC

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.

Frequent Contributor
Posts: 106

Re: SAS Connection to SQL error via ODBC

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.

SAS Employee
Posts: 198

Re: SAS Connection to SQL error via ODBC

[ Edited ]

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.

Frequent Contributor
Posts: 106

Re: SAS Connection to SQL error via ODBC

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

SAS Employee
Posts: 198

Re: SAS Connection to SQL error via ODBC

[ Edited ]

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 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 24 replies
  • 218 views
  • 0 likes
  • 4 in conversation