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.
Are you sure your HealthShare and the related ODBCdrivers are all 64bit?
the ending U of your $ZV might mean Unicode == 64 bit
And OK. SMP doesn't need any ODBC
Yes, I did verify by doing an @@version in SSMS.
I was able to figure it out though! For anyone else that comes upon an issue like this, it was a Namespace mapping issue on my end so I hope it helps.