topic Re: SAS Connection to SQL error via ODBC in SAS Programming

SAS to SQL Error via ODBC

Jade_SAS — Wed, 15 Feb 2017 17:10:40 GMT

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 Connection to SQL error via ODBC

Jade_SAS — Wed, 15 Feb 2017 17:09:19 GMT

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.

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

Re: SAS Connection to SQL error via ODBC

JBailey — Wed, 15 Feb 2017 18:13:45 GMT

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

Re: SAS Connection to SQL error via ODBC

Jade_SAS — Wed, 15 Feb 2017 18:47:20 GMT

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.

Re: SAS to SQL Error via ODBC

anja — Wed, 15 Feb 2017 21:05:45 GMT

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

Re: SAS Connection to SQL error via ODBC

JBailey — Wed, 15 Feb 2017 21:34:15 GMT

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?

Re: SAS to SQL Error via ODBC

Kurt_Bremser — Thu, 16 Feb 2017 06:41:54 GMT

I merged the identical questions into one thread in General SAS Programming.

Please post ERROR messages and such as text, using the {i} button. MS Office documents can contain malware and are therefore blocked in many places. For screenshots, use the png format, but text is always preferred.

Re: SAS to SQL Error via ODBC

Jade_SAS — Thu, 16 Feb 2017 13:54:30 GMT

@anja 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!

Re: SAS Connection to SQL error via ODBC

Jade_SAS — Thu, 16 Feb 2017 13:57:44 GMT

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

Re: SAS Connection to SQL error via ODBC

JBailey — Thu, 16 Feb 2017 14:48:34 GMT

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

Re: SAS Connection to SQL error via ODBC

Jade_SAS — Thu, 16 Feb 2017 14:39:12 GMT

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!

Re: SAS Connection to SQL error via ODBC

JBailey — Thu, 16 Feb 2017 14:44:34 GMT

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.

Re: SAS Connection to SQL error via ODBC

Jade_SAS — Thu, 16 Feb 2017 14:51:18 GMT

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.

Re: SAS Connection to SQL error via ODBC

JBailey — Thu, 16 Feb 2017 15:02:07 GMT

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.

Re: SAS Connection to SQL error via ODBC

Jade_SAS — Thu, 16 Feb 2017 15:08:18 GMT

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

Re: SAS to SQL Error via ODBC

Jade_SAS — Thu, 16 Feb 2017 15:09:53 GMT

@Kurt_Bremser

Could you please let me know the link of the thread you talked about? Thank you!

Re: SAS Connection to SQL error via ODBC

JBailey — Thu, 16 Feb 2017 15:39:28 GMT

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;

Re: SAS Connection to SQL error via ODBC

Jade_SAS — Thu, 16 Feb 2017 15:31:05 GMT

No, get the same error:

ERROR: CLI describe error: [

Re: SAS Connection to SQL error via ODBC

JBailey — Thu, 16 Feb 2017 15:41:28 GMT

Hi @Jade_SAS

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

Re: SAS Connection to SQL error via ODBC

Jade_SAS — Thu, 16 Feb 2017 16:28:21 GMT

No, they can not