I have set up and configured NotesSQL on a Windows 2003 server. I was able to add a system DSN that points to a test database on my Domino development server. So far so good.
What I ultimately want to do is to add a trigger to a SQL Server database table that monitors for updates and then update a corresponding Notes database.
I basically am wondering how to talk to the datasource through SQL code that will run on the SQL server. I found this article on Database Journal:
It outlines how to set up the datasource as a “Linked Server”. I did this and was able to query the datasource via the “openquery” statement. However, I am wondering how I can just to a simple statement like this:
select * from vwTest
Management Studio is unaware of the database unless I use the “openquery” statement. I guess the problem is that I don’t know how to reference the datasource from SQL Server Management Studio. It seems simple and I would appreciate it if anyone could lend a hand.
Subject: Accessing a NotesSQL Datasource from MS SQL Server
Here’s a little more detail:
I am able to query data from the NotesSQL datasource from Mangement Studio in both of the following ways:
select * from openquery(NotesSQL, ‘Select * from frmTest’)
select * from NotesSQL…frmTest
Both work, but what I am still unable to use an SQL update statement. And what I really need is to be able to update Notes data from SQL Server.
When I try an update statement, I get the following message:
Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider “MSDASQL” for linked server “NotesSQL” does not support the required transaction interface.
From everything I can tell in the NotesSQL documentation, update statements are supported. So, I assume it’s just a matter of finding the right syntax here.
Again, if anyone could shed some light here, I’d appreciate it.