Can someone tell me the code to connect to SQL Server remotely through SAS ODBC so that I can see the schema as well?
I have the IP Addesss, User ID and Password.
Do I also have to set up something at Control Panel\All Control Panel Items\Administrative Tools\Data Source (ODBC)?
Thanks,
Mike
Are you using SAS on your PC to connect to SQL Server or are you using a remote SAS Server? In either case if SAS is running on Windows you can avoid the Control Panel setup by specifying everything in a database connection string.
Is your database userid and password your Windows user account and password or are they different? The reason I ask is its very common to connect to SQL Server using Windows Authentication (your connection is verified using your Windows user account).
The one thing you are missing at the moment is you need the name of a database to connect to.
Then you can construct a test LIBNAME like this (please note syntax may not be quite right as I'm doing this from memory):
libname SQLServr noprompt = 'server=123.456.789;user=MyUser;password=MyPassword' database = MyDatabase schema = dbo;
Once you've confirmed the above questions I can give you a more accurate answer.
OK, since you are connecting from your PC that means you can specify everything to connect to SQL Server in a connection string. Also I forgot to add the DRIVER option in my previous post so it is not surprising it is not working for you. I can check this better at work but that won't be until tomorrow.
If the LIBNAME works OK then the library name should appear in the library list and expanding it should show tables.
libname SQLServr noprompt = 'DRIVER=SQL Server;server=123.456.789;user=MyUser;password=MyPassword' database = MyDatabase schema = dbo;
In theory it should be just the same so I suggest you persevere with your current setup. Also I didn't quite get the LIBNAME right (should specify ODBC engine):
libname SQLServr odbc noprompt = 'DRIVER=SQL Server;server=123.456.789;user=MyUser;password=MyPassword' database = MyDatabase schema = dbo;
Does this help? Does the LIBNAME assign and appear in your library window?
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.