05-05-2016 06:50 PM - edited 05-05-2016 07:48 PM
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)?
05-06-2016 04:08 AM
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.
05-06-2016 11:57 AM
05-07-2016 07:58 PM - edited 05-07-2016 08:08 PM
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;
05-07-2016 10:17 PM
05-08-2016 03:52 PM
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?
05-09-2016 08:15 PM
05-09-2016 08:21 PM