Assuming one’s server admin is having trouble keeping up with all the new servers, can one write a SAS LIBNAME using the ODBC engine and noprompt and specify everything needed for the connection? I have tried about 35 variations, and almost all return
ERROR: CLI error trying to establish connection: [OpenLink][ODBC][SQL Server]Unknown host machine name (20013)
I am a bit concerned that the docs seem to require port number and I believe because we use instance names, the port number is dynamic.
Our SQL Server ODBC driver can make use of the instance name for connecting to a SQL Server instance without the need for the port number as detailed in the documentation.
What is the ODBC connect string being passed to make the connection attempt and what is the client OS in use ?
Can you generate an ODBC trace (ODBC driver & driver manager) trace and TDS log for the connection attempt for analysis.
Thank you for the response. My goal was to connect to MS SQL Server before my IT guys added a new entry to the shared odbc.ini for me, and they have won that race. I am still interested, but no longer up against a deadline.
I am working in SAS 9.4 on a Linux box. I switched to a database with a good odbc.ini entry and tried a simple LIBNAME using the data source name. It worked. Following an example in SAS documentation, I converted it into noprompt syntax, still using the DSN. My plan was that that would succeed, and then I would replace the DSN reference with the individual values from the odbc.ini entry. That was why I asked about port vs. instance: I am still not sure whether there is a separate instance parm or I append it to the host with a \ or , as a separator. Contrary to my plan, the second libname below failed. SI disavows their doc and says I cannot use a DSN in a noprompt connect string. I think they are failing to ignore some of the specials I am concealing in those chunks I changed to below. I cannot show the actual strings–I am already on report for a few details I shared with SI. Is it true that I can either provide a full path to the driver, or provide a driver name which is mapped to the driver file in odbcinst.ini? I have tried both. If you would comment on how to express the instance in a noprompt connect string, I would appreciate it. I do not know how to generate the trace and log you mention, and I do not know that I have permission or capability. Did I mention I was already written up for being too forthcoming with technical support on this effort?
/* This worked /
libname static odbc user= pwd= dsn=ECC328_meldb;
/ Nope */
libname static odbc noprompt=“user=l;pwd=;dsn=ECC328_meldb”;
Is the libname static odbc noprompt=“user=l;pwd=;dsn=ECC328_meldb”; connection attempt what is giving the ERROR: CLI error trying to establish connection: [OpenLink][ODBC][SQL Server]Unknown host machine name (20013) error originally reported ?
The libname static odbc user= pwd= dsn=ECC328_meldb; connection that works is using the same ECC328_meldb DSN as the no prompt connection attempt and so assuming it is being read/processed correctly should work I would expect, which is why we would need to see the ODBC trace (ODBC driver & driver manager) trace and TDS log for the connection attempt for analysis as requested previously to see what is actually being passed by the SAS Libname ODBC interface.
No progress, yet. My support staff indicate they can only (will only?) enable logging for a specific odbc.ini entry. As my whole purpose here is not to use an odbc.ini entry, it is not going well. I did get them to enable logging for a separate error which uses an odbc.ini entry, and if I work something out with them for this issue, I will update this thread. Thank you for trying to help and I am sorry I cannot help you help me!
Can you not provide at least the ODBC trace (ODBC driver & driver manager) SQLDriverConnect() or SQLConnect() call being made that is failing, with any sensitive information removed, such that we can see the connecstring being passed ? Calls for the working and failing connections ie PROMPT and NOPROMPT would be useful to see for comparison.
Note, also if a valid support track is in place a support case call also be logged for his issue, such that information is not publicly exposed …