· Apr 15

SQL Server Management Studio Can Do ODBC Connection But Doesn't Return Rows

Hey everyone,

I'm currently running into a very weird issue to where I am trying to connect with a 64 bit version of SQL Server Management Studio (SSMS) to a HealthShare instance. I have created a System DSN using the Drivers (image below) that were downloaded with the Client version of the install and I'm able to successfully connect using my credentials.

After I have my System DSN (image below) configured, I go into SSMS and add a Linked Server that is referencing the System DSN that was created. Also, I change the provider to be Microsoft OLE DB Provider for ODBC Drivers. I go into Security and set it to Be made using this security context and enter in my own credentials to HealthShare.

After entering in this information, I am able to see the correct databases in the namespace I provided, but I try the following query with little luck:

SELECT * FROM OPENQUERY([Linked Server Name], 'SELECT * FROM Schema.Table')

Doing this gives me the correct columns for the SELECT statement, but it doesn't return any rows for some reason. I can switch it to a different table and I will get the correct columns, but again I won't receive and rows of data. 

I have also tried to limit the query to where I do a TOP 20 and it is the same behavior. It doesn't seem that this due to a timeout. I'm also doing this with an account that has %ALL permissions so I don't believe it is access related.

Additionally, I can login to Management Portal and execute SQL commands without any issues.

Does anyone have any experience with connecting SSMS to a Cache database? Any help or guidance would be appreciated.

Product version: HealthShare 2017.2
$ZV: 2017.2.2 Build 865_3_19668U
Discussion (2)1
Log in or sign up to continue