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
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 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
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****;");
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 );
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.