473,695 Members | 2,187 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Linked Servers to Lotus Notes

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(LOTUS NOTES2, 'select * from Person' )

The problem I have is when I try to update the record I get an error
eg
update openquery(LOTUS NOTES, '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::GetNex tRows
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
Jul 23 '05 #1
5 5011
NickBlooruk (ni*********@ya hoo.com) writes:
The problem I have is when I try to update the record I get an error
eg
update openquery(LOTUS NOTES, '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.MailAddr ess 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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
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(LOTUS NOTES2, 'select * from Person ' )
set [JobTitle]='Test'
FROM
openquery(LOTUS NOTES2, 'select * from Person ' ) a
where a.************* ***********@yah oo.com'

but the error is still the same.

Thanks for your advise.

Nick
Erland Sommarskog wrote:
NickBlooruk (ni*********@ya hoo.com) writes:
The problem I have is when I try to update the record I get an error eg
update openquery(LOTUS NOTES, '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.MailAddr ess 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****@sommarsk og.se

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


Jul 23 '05 #3
(ni*********@ya hoo.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...P erson
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(LOTUS NOTES2, 'select * from Person ' )
set [JobTitle]='Test'
FROM
openquery(LOTUS NOTES2, 'select * from Person ' ) a
where a.************* ***********@yah oo.com'


I was more thinking of

update openquery(LOTUS NOTES2, '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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
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.
Jul 23 '05 #5
NickBlooruk (ni*********@ya hoo.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
4236
by: hrishy | last post by:
Hi I have heard rumors that Lotus notes would be moving to db2 as the datastore..would that mean db2 for windows and Linux have a shot in its arm and become as pervasive as oracle and ms-sql server.. would appreciate your thoughts on this.. regards Hrishy
5
7380
by: Colin Anderson | last post by:
I discovered, with great excitement, this article http://www.davison.uk.net/vb2notes.asp when researching methods for emailing from Access via Notes. Unfortunatly, when I run this I get a Run-time error. When I run it on an XP machine it crashes, but on an NT box it just generates an unknown error, handled by the error handler. I have debugged and stepped through the code and have narrowed the issue to the point at which the...
0
5607
by: PZ Fosbeck | last post by:
I'm not a Lotus Notes developer but thanks to this group's archives have successfully created a function for sending Lotus Notes emails from Access. The follow code works great except I want to remove my name from the 'Sent By' portion of the email. These messages are sent using my client session of Lotus Notes, using a database called 'Tech Team' The resulting email message headers contains (Bold, the From name) 'Tech Team'
0
2327
by: ProgrammerGal | last post by:
I am struggling with moving data to Lotus Notes from SQL Server. I am hoping someone has done this and can point me in the right direction. I have a stored procedure that collects my data into a temp table on sql server and I want to move that data into a Lotus Notes table. I am select rows from #tmpCases on SQL Server and trying to put them into CaseName on Lotus Notes.
9
7329
by: ProgrammerGal | last post by:
I am hoping this is a quick easy question for someone! :) I am trying (struggling) with moving data from Sql Server to a Lotus Notes table. I am using SQL Server 2000, I have a Lotus Notes linked server (using NotesSQL), and I wasnt to clear the table (delete all records) and then reload it from my data on SQL Server. What is the syntax to delete the records?
0
1494
by: Heinz K | last post by:
Hi all, is it possible to access a Lotus Notes database even if Lotus Notes is not installed on current computer? I have an IIS and want to create a Webservice which accesses data from a Lotus Notes Server. I found solutions if Lotus Notes is installed locally, but on my IIS Notes is not and will not be installed. Is it nevertheless possible to read data from the database? Thanks!
1
13044
by: Joe | last post by:
HI Has anyone been able to work with lotus notes automation classes??? Can you post sample code of how to use these classes. I have setup in VB but I am not able to port to C# This is what I have so far - I cannot create a session and not sure how to setup From/Subject
3
8263
by: =?Utf-8?B?SmFtZXNU?= | last post by:
I can create a message and send it via my btopenworld account but is the method the same when using Lotus Notes. I have no experience of Lotus Notes whatsoever. I have never seen it at all. the code I am using is: dim smtp as new smtpclient dim message as new mailmessage( from@cc.com, to@dd.com) message.subject = "test"
2
3965
by: MarkStorer | last post by:
Hi All I need to email a report (with contains graphs) via Lotus Notes. I've tried the 'SendObjectSnp' method (which works with some Lotus Notes clients (but not many others)); so I used the code below: - Public Sub SEND_EMAILS() Dim session As Object Dim db As Object Dim doc As Object Dim rtitem As Object
0
9112
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8822
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8815
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6483
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5827
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4332
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2994
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2251
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1970
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.