Can I connect to Cache using SSMS (SQL Server Management Studio)?

Question

Question

Christian Bahnsen · Jun 16, 2016

Can I connect to Cache using SSMS (SQL Server Management Studio) and view schemas and build queries as I do for SQL Server?

If yes, step-by-step instructions for setting up the connection would be greatly appreciated.

I googled the topic but didn't find any results that were on target.

Thanks,

Christian Bahnsen

Discussion (13)1

Log in or sign up to continue

Henrique Dias · Jul 16, 2019

I tried DataGrip in the past, and the software is outstanding.

Nowadays I'm using DBeaver Community Edition. It's an alternative to DataGrip, and it's free.

DBeaver also has code completion for table/field names and standard SQL syntax.

1 0

score 1 · Answer 1 · 2016-06-16T08:50:14-04:00

I use SQL Server quit a lot and I've never seen that you can connect via SSMS to any other databases than MS SQL Server... You can use the Linked Server Feature to use Caché via SQL Server and use the SSMS to query Caché. But this a overkill just to execute some queries I think.

Attention advertising ;-) : You can take a look at Caché Monitor to connect directly to Caché (and other DBs). If you are familiar with SSMS you find many identical UI concepts.

Regards

Andreas

score 0 · Answer 2 · 2016-06-16T09:08:13-04:00

Thanks for the reply.

We already have a linked server to Cache and I run queries against Cache using EXECUTE ... AT or OPENQUERY syntax.

But I can't view namespaces/schemas or write direct queries against Cache.

A recent upgrade messed up the SQL portal in the System Management Portal where I normally start building queries. Our MID department can't seem to fix the problem so I'm looking for workarounds. I know some people use WinSQL to connect to Cache and a Cache tech support person thought SSMS could perform the same role, but he doesn't use SSMS personally so didn't know how to configure the connection.

Hence this post. If it's doable, I'd love to get my instance of SSMS configured so I could write queries directly against Cache. I'd still use the linked server and the EXECUTE or OPENQUERY syntax for queries driving reports I push out to our Report Server.

I became aware of Cache Monitor yesterday and that may be a solution, but if I can accomplish the same end using SSMS that's the route I'd prefer to take.

score 1 · Answer 3 · 2016-06-16T09:08:14-04:00

You can use any ODBC/JDBC database management software. I use DataGrip. It has code completion for table/field names and standard SQL syntax.

score 0 · Answer 4 · 2016-06-16T09:42:14-04:00

Eduard,

Thanks for the reply.

I'm not sure if SSMS is "ODBC/JDBC" compliant so I've opened a parallel thread in the Microsoft SQL Server forum: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ea2bca86-1c23-4c4e-bbca-97130bd2aff8/can-i-connect-to-intersystems-cache-using-ssms-sql-server-management-studio-other-than-as-a-linked?forum=sqltools

Hopefully between these two forums we'll come up with a definitive answer as to whether SSMS can perform the same function as DataGrip or other ODBC/JDBC database management software.

Thanks again

Christian

score 0 · Answer 5 · 2016-06-16T12:42:02-04:00

I should have been more specific in the title. You can (and we do) set Cache up as a linked server. SSMS, however, is not a "vendor-agnostic" tool, therefore you can't use it as a front-end to view schemas and write queries.

score 0 · Answer 6 · 2018-06-28T15:35:25-04:00

Can you post a screenshot of your driver configuration and URL templates. I can get it to connect with test but when it trys to get all details for the tree it starts saying access denied. Not sure if i have my URL template correct.

score 0 · Answer 7 · 2018-06-28T16:00:10-04:00

Here's my URL template

jdbc:Cache://{host}[:{port}]/{database}

If you connect but get Access Denied, try user with %ALL permissions to remove doubts about insufficient privileges.

score 0 · Answer 8 · 2019-07-16T12:30:34-04:00

Evgeny Shvarov · Jul 16, 2019

wow! It has Caché icon? ) cool! Is it possible to add InterSystems IRIS connection option too?

0 0

score 0 · Answer 9 · 2019-07-16T12:51:17-04:00

Henrique Dias · Jul 16, 2019

That's a good question :)

We can add an issue on GitHub

https://github.com/dbeaver/dbeaver

0 0

score 0 · Answer 10 · 2019-07-16T13:03:19-04:00

Evgeny Shvarov · Jul 16, 2019

Did it, thanks!

0 0

score 0 · Answer 11 · 2017-10-03T02:26:00-04:00

SQL Server Management Studio: I'm certain that the answer is No. What your cache post should have said is that you can use any vendor-agnostic application to manage the server/database. SSMS is definitely not vendor-agnostic.

score 0 · Answer 12 · 2019-07-16T11:41:32-04:00

Yes, you can set up a linked server but first, you need to create an ODBC connection.

Create an ODBC connection
Open SQL Server Management Studio
Create linked server using other data source
Select provider: Microsoft OLE DB Provider for ODBC Drivers
Select Product name: same as the ODBC connection name
Select Data source: same as the ODBC connection name
Under Security tab select option “Be made using this is security context”
Enter remote login with password

then select top 1 * from [linked server name]..[schema name].[table name]