topic Connect via ODBC in SAS Data Management

Connect via ODBC

cmoore — Thu, 22 Mar 2018 15:02:35 GMT

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

Re: Connect via ODBC

SuryaKiran — Thu, 22 Mar 2018 15:11:26 GMT

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.

Re: Connect via ODBC

Reeza — Thu, 22 Mar 2018 15:16:28 GMT

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.

Re: Connect via ODBC

cmoore — Fri, 23 Mar 2018 13:43:37 GMT

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

Re: Connect via ODBC

SASKiwi — Fri, 23 Mar 2018 23:00:47 GMT

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****;");

Re: Connect via ODBC

ChrisNZ — Tue, 27 Mar 2018 21:05:30 GMT

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