Connecting to MS SQL Server

[monona]

So is it another story to push sas data set to sql server directly? sorry for many questions...

[SASKiwi]

The same LIBNAME will work for updating SQL Server as well as reading, assuming you have the right database privileges.

[amitk9599]

How to connect SAS online version with Microsoft sql server ?

It is throwing this error:-

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

68

69 libname MY_lib '/home/amitk.pr15/sasuser.v94/SAS_WORK';

NOTE: Libref MY_LIB was successfully assigned as follows:

Engine: V9

Physical Name: /home/amitk.pr15/sasuser.v94/SAS_WORK

70

71 libname My_lib odbc ="{server=01-0234-0920\SQLEXPRESS}; driver=SQLServer; Native Client 11.0; Trusted connection=yes"

71 ! database=Piwikdatabase schema=dbo;

ERROR: Libref MY_LIB is not assigned.

ERROR: Error in the LIBNAME statement.

72

73 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

[ScottBass]

---

@monona wrote:
Thanks for your reply! I got error messages though.

ERROR: CLI error trying to establish connection: [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for
user ''. : [Microsoft][SQL Server Native Client 11.0]Invalid connection string attribute
ERROR: Error in the LIBNAME statement.

---

Does your SQL Server support trusted connections, i.e. your already authenticated network identity (when you logged in) if sufficient to grant access to SQL Server?

Do you use SQL Server Management Console, or some other tool, to connect to SQL Server outside of SAS?

Before posting more questions, you may want to invest some time here:

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001355231.htm

If you haven't yet read this, you should...

[monona]

Yes! I log in with my windows authentication when loggin in ms sql server management studio.

[tampham92]

Hello, I am trying to connect to MS SQL Server data table through SAS and I ran into this issue and not sure what it means.

I ran the following code and got issue 

proc sql;
connect to SQLSVR as mydb (datasrc="SQL_SAS" user="XXX" password="XXX");
select * from connection to mydb
(select * from DATA_TABLE);
quit;
run;

23 GOPTIONS ACCESSIBLE;

24 proc sql;
25 connect to SQLSVR as mydb (datasrc="SQL_SAS" user="D113338" password=XXXXXXXXXX);
ERROR: CLI error trying to establish connection: [DataDirect][ODBC lib] Data source name not found and no default driver specified
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
26 select * from connection to mydb
27 (select * from DATA_TABLE);
ERROR: The MYDB engine cannot be found.
ERROR: A Connection to the mydb DBMS is not currently supported, or is not installed at your site.
28 quit;

I already added that SQL_SAS datasource into the ODBC Datasouce Administrator but it still did not work.

Thank you so much for any help.

[SASKiwi]

@tampham92  - Adding new posts on the end of old answered posts is not a good idea. You should have started a new post, linking in the old post if necessary.

The errors suggest SAS is not finding your Data Source. Where is SAS running? Locally on your PC or remotely on a SAS server? The data source has to be defined on the computer SAS is running on. Also bitness matters. If you are running 64-bit SAS the ODBC data source has to be 64-bit.