Linked Servers to Lotus Notes

[NickBlooruk]

Hello,

I have successfully linked a Lotus Notes server to our SQL Server
database using an ODBC connection.
This works fine when wanting to select records
eg openquery(LOTUSNOTES2, 'select * from Person' )

The problem I have is when I try to update the record I get an error
eg
update openquery(LOTUSNOTES, 'select * from Person where
Ma**********************@sandh-ltd.com''' )
set JobTitle='Test'

Produces the following error
==========
OLE DB provider 'MSDASQL' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
===========

I have tested the ODBC connection using an Access database. The link
tables facilities enable me to select what I think should be the
primary key. If I select a primary key then I can use Access to update
the lotus notes database, however if I don'k select a primary key, the
table is not updateable.

I believe that if I can somehow create an index on the linked table
within SQL Server, I should be able to update the Lotus Notes database
from SQL Server, but I cannot find a way of doing so.

As the Lotus Notes software is third party, I cannot actually change
anything on that server.

Thanks for any help.

Regards

Nick Bloor

[Erland Sommarskog]

NickBlooruk (ni*********@yahoo.com) writes:

The problem I have is when I try to update the record I get an error
eg
update openquery(LOTUSNOTES, 'select * from Person where
Ma**********************@sandh-ltd.com''' )
set JobTitle='Test'
I don't know Lotus Notes, nor do I have much experience of updating
through OPENQUERY. But what happens if you move the WHERE to the
SQL Server side of things?
I have tested the ODBC connection using an Access database. The link
tables facilities enable me to select what I think should be the
primary key. If I select a primary key then I can use Access to update
the lotus notes database, however if I don'k select a primary key, the
table is not updateable.
Is Person.MailAddress the primary key? If it's not it seems like a
difficult case.
I believe that if I can somehow create an index on the linked table
within SQL Server, I should be able to update the Lotus Notes database
from SQL Server, but I cannot find a way of doing so.

You cannot create indexes on linked tables.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[nickblooruk]

Hi Erland,
Thanks for your response.

Strictly speaking Lotus Notes is not a relational database. The Person
table therefore does not have a primary key.
There are a few records will null values in the MailAddress within
Lotus notes.
However the data I am interested in is unique within Lotus notes.

Access 2000 allows me to say that the MailAddress field is unique (even
though it isn't strictly). This allows me to update the Lotus Notes
table using Access' Query.

I cannot find a way of doing the same with MS SQL SERVER 2000, so the
table is currently not updateable.

I have tried using the WHERE on the SQL SERVER side
eg:
update openquery(LOTUSNOTES2, 'select * from Person ' )
set [JobTitle]='Test'
FROM
openquery(LOTUSNOTES2, 'select * from Person ' ) a
where a.************************@yahoo.com'

but the error is still the same.

Thanks for your advise.

Nick
Erland Sommarskog wrote:

NickBlooruk (ni*********@yahoo.com) writes:

The problem I have is when I try to update the record I get an error eg
update openquery(LOTUSNOTES, 'select * from Person where
Ma**********************@sandh-ltd.com''' )
set JobTitle='Test'

I don't know Lotus Notes, nor do I have much experience of updating
through OPENQUERY. But what happens if you move the WHERE to the
SQL Server side of things?

I have tested the ODBC connection using an Access database. The link tables facilities enable me to select what I think should be the
primary key. If I select a primary key then I can use Access to update the lotus notes database, however if I don'k select a primary key, the table is not updateable.

Is Person.MailAddress the primary key? If it's not it seems like a
difficult case.

I believe that if I can somehow create an index on the linked table
within SQL Server, I should be able to update the Lotus Notes database from SQL Server, but I cannot find a way of doing so.

You cannot create indexes on linked tables.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

(ni*********@yahoo.com) writes:

Strictly speaking Lotus Notes is not a relational database. The Person
table therefore does not have a primary key.
I kind of suspected that.
Access 2000 allows me to say that the MailAddress field is unique (even
though it isn't strictly). This allows me to update the Lotus Notes
table using Access' Query.

I cannot find a way of doing the same with MS SQL SERVER 2000, so the
table is currently not updateable.
Access and SQL Server are not really the same sort of animals. Access
is a GUI and DBMS in once. SQL Server is only a DBMS.

Yet one more thing to try is something like:

UPDATE LOTUSNOTES2...Person
SET JobTitle = 'Test'
WHERE MailAdress = '...'

You would need to find out what to place between the dots in the four-
part notation, which is not always that easy.
I have tried using the WHERE on the SQL SERVER side
eg:
update openquery(LOTUSNOTES2, 'select * from Person ' )
set [JobTitle]='Test'
FROM
openquery(LOTUSNOTES2, 'select * from Person ' ) a
where a.************************@yahoo.com'

I was more thinking of

update openquery(LOTUSNOTES2, 'select * from Person ' )
set [JobTitle]='Test'
FROM where Ma**********************@yahoo.com'

But I would guess you've already tried that.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[NickBlooruk]

Thanks Erland for your help.

I have discovered that there is a unique reference number in Lotus
Notes called NoteID. This has all the characteristics of a primary
key, however as it is not a primary key in the strict sense of the
work as there is no built in integrity checks, I cannot update using
the openquery statement.

I think I will post a note on the lotus notes forum and also try to
find a different solution.

Thank you very much though

Regards

Nick Bloor.

[Erland Sommarskog]

NickBlooruk (ni*********@yahoo.com) writes:

I have discovered that there is a unique reference number in Lotus
Notes called NoteID. This has all the characteristics of a primary
key, however as it is not a primary key in the strict sense of the
work as there is no built in integrity checks, I cannot update using
the openquery statement.

I think I will post a note on the lotus notes forum and also try to
find a different solution.

Good luck! :-)

I can only say that I'm glad that I don't have fight that battle.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp