Using NotesSQL 3.0.2f, I defined an ODBC connection to a notes database. I can use that connection in MS Access to query the Notes database, including joins to an Access MDB. In the MS SQL 2000 Server Executive, however, when I try to create a “Linked Server,” I get this message:
Error 7399: OLE DB provider ‘MSDASQL’ reported an error,
Data source name not found and no default driver specified.
The DSN is a System DSN. Anybody had any success doing this?
Thanks.
Randy
Subject: NotesSQL To MS SQL Server
Is it set up like the following?
Thanks,
Ron
Subject: RE: NotesSQL To MS SQL Server
Except for the name of the data source and linked server:
Thanks.
Randy
Subject: RE: NotesSQL To MS SQL Server
With the config in the prior post, I get this:
Subject: RE: NotesSQL To MS SQL Server
A few things, it’s possible that the SQL Server user doesn’t have the rights to the registry entry in ODBC. Run regedit and go to the HKEY_LOCAL_MACHINE\Software\ODBC node and then go to the Edit\Permissions menu (this isn’t available in versions less then XP… Do do this in say Win2000, you have to go into regedt32.exe, not regedit…) and make sure the user that SQL Server is logging in as has the correct rights to see that part of the registry.
Unlikely, but possible, make sure that user has read rights to the Windows and Windows\System dir. The error you are seeing is not a NotesSQL error, it’s an ODBC error. Since you’ve yet to get into NotesSQL, make sure that the SQL Server user has rights to the Notes dir and the NotesSQL dir as well as the correct rights in HKLM\Software\Lotus registry key. For NotesSQL, you can check the Event Viewer to see if there are any rights errors when it’s loading. SQL Server might log some information in there as well.
One last thing, try turning on SQL Tracing in the Data Source Administrator. Because it looks like you’re using XP (because of your cool skin), this will be easier as there is a setting in XP’s Data Source Administrator that wasn’t in previous versions of the OS. Make sure to turn on Machine-Wide tracing for all user identies and push the button Start Tracing Now. Then view the outputted sql.log file to see where the error is coming from.
Good luck!
Ron
Subject: RE: NotesSQL To MS SQL Server
Actually, are you saying that NotesSQL has to be on the SQL Server box? Is that the problem? The SQL Server box does not have Notes loaded at all. Do I have to load a minimal Notes client there, install NotesSQL, then set up the DSN from there?
What I’ve been doing is running SQL Executive from my client, using a system DSN on my machine, then trying to link to Notes, using the Executive, on my client machine. Client is WinXP, and both server boxes are Win2K.
I certainly appreciate the help you’ve given.
Subject: RE: NotesSQL To MS SQL Server
Oh, you don’t have this stuff installed on the SQL Server machine? Yes, Notes and NotesSQL need to be installed on the SQL Server machine! Please read the NotesSQL documentation, it tells you the requirements. Yes, a minimal Notes client needs to be installed AND set up so that it can talk to the server you’re trying to access…
Doc is available in the NotesSQL Program group or you can view it online here:
Subject: RE: NotesSQL To MS SQL Server
My sincere apologies. I scanned the docs but didn’t pay close attention and didn’t catch that. RTFM! Randy
Subject: RE: NotesSQL To MS SQL Server
This is interesting: I get no output from the ODBC trace at all. Just to be on the safe side, I fired up Access and used the same DSN to access Notes. No problem there. Retrieved 18,000 records.
My domain Admin guru did check the credentials SQL Server is running and gave that ID admin rights to the Notes box. Still no joy. But given that I got no ODBC trace output, does that say the problem is somewhere in MS SQL Server? I hope not! Trying to get tech support from them…
Thanks again.
Subject: NotesSQL To MS SQL Server
Randy,You should be using OLEDB to talk to MS SQL Server 2000 not ODBC.