Connect via ODBC

[cmoore]

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

[SuryaKiran]

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.

[Reeza]

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.

[cmoore]

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

[SASKiwi]

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

[ChrisNZ]

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