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

Connect via ODBC

Reply
Contributor
Posts: 48

Connect via ODBC

Hi,

 

I'm connecting to a SQL database via ODBC and run the following code to do so;

 

/*Link via ODBC to the MS Connect Server*/

libname Connect
odbc complete="driver=SQL Server;
database=connect_productionreports ;
server=fcl-sql-v075"
schema=dbo;
run;

 

This piece of code allows me to view all tables in this area. It's come to my attention that some tables have been stored with a name of more 32 characters that I can't see. There is a table called "Contact_Company_ExtSL_ClientGroupGlobalRP " that has 3 columns that I can't see in the database, for example.

 

Please could you tell me how I could amend the code above to pull in all tables regardless of the table length name?

 

Many thanks

Chris

Valued Guide
Posts: 560

Re: Connect via ODBC

If table names are longer than 32 char, then they are truncated to 32 Char. If truncated name already exists then SAS generates unique name by replacing the last char with a number.

Thanks,
Suryakiran
Super User
Posts: 23,321

Re: Connect via ODBC

Posted in reply to SuryaKiran
It used to be that the tables with names longer than 32 chars are not shown. Variables are truncated. The workarounds are:
1) SQL Pass through instead
2) Creating a view on the SQL Server side with a table name that is less than 32 chars. This is usually the easiest solution.
Contributor
Posts: 48

Re: Connect via ODBC

Thanks for your input. I have attempted to write a Pass-through query but it doesn't seem to connect. When I run the following it connects fine and I can see all the tables;

 

libname Connect
odbc complete="driver=SQL Server;
database=connect_productionreports ;
server=fcl-sql-v075"
schema=dbo;
run;

 

When I just want to target the table with more than 32 characters I have tried the following;

 

proc sql;

connect to odbc (noprompt ="dsn=fcl-sql-v075;DRIVER=SQLServer;uid=c***e;pwd=Lo****;");

create table Prod02test as
select * from connection to odbc
(SELECT *
FROM connect_productionreports.dbo.Contact_Company_ExtSL_FormalCRMProgrammesandTargetingLists
)
;
disconnect from odbc;
quit;

 

This doesn't connect. Is there something stupid that I am doing as I've never needed to use these queries before.

 

many thanks

Chris

Super User
Posts: 3,860

Re: Connect via ODBC

Try using SERVER= and correct driver name. Please note connection strings need to be exact, including blanks.

connect to odbc (noprompt ="server=fcl-sql-v075;DRIVER=SQL Server;uid=c***e;pwd=Lo****;");

 

PROC Star
Posts: 2,319

Re: Connect via ODBC

I always use

connect using

these days. So much easier...

 

libname SQLSLIB odbc database= server= schema=  etc;
  

proc sql;

 connect using SQLSLIB ;

 create table XX as select * from connection to SQLSLIB  (  etc  );

Ask a Question
Discussion stats
  • 5 replies
  • 171 views
  • 0 likes
  • 5 in conversation