NotesSQL table create into SQL database

Posted on 2009-05-14
Medium Priority
Last Modified: 2012-05-07
I am trying to get everything from a table from a Lotus Domino (R5) table into a new Table in a SQL database.  

I have my NotesSQL ODBC connection set up and can see everything I want to put into the LicensingSQL database using SQL Server Business Intelligence Development Studio and a query of " SELECT * FROM  Licensing_Profile".

My Notes connection I've named LicensingNotes and my SQL database connection I've named LicensingSQL.  I'm fairly new to SQL (I've run select and update queries on existing databases with a few basic date conversions and simple calculations).

I figure it's something simple that I just don't know.  I've created a test table in the LicensingSQL so I should have the proper rights to create a table.

I've tried:
SELECT * INTO  LicensingSQL.TestLicense FROM  Licensing_Profile
but it tells me there is an error around FROM?

I've also tried a button in VB 6.0 too but can't get that right either.

Any suggestions would be appreciated.
Question by:Vanessa
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 3
  • 2
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24384868
SELECT * INTO  LicensingSQL..TestLicense FROM  Licensing_Profile
LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 400 total points
ID: 24385379

Author Comment

ID: 24385706
When I try: "SELECT * INTO  LicensingSQL..TestLicense FROM  Licensing_Profile" I get:
Error Sourc: NSQLV32.DLL
Error Message: ERROR[42000][Lotus][ODBC LotusNotes]Keyword FROM expected
All I can figure is NotesSQL doesn't recognized the "INTO".

I'll try the NotesSQL (v 3.2) documentation (looked briefly at it yesterday) after I read the two links sent.

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Author Comment

ID: 24386501
Now I've got my VB 6.0 creating a table in the correct SQL database but when I try to get the data from Notes, it's giving me -1 record so I'm assuming I'm not opening it right but tried a couple variations to no avail ...

I've attached the code that seems to be almost there ...

Private Sub GetLicensingNotes_Click()
Dim connNotes As ADODB.Connection
Dim connSQL As ADODB.Connection
Dim rsSQL As ADODB.Recordset
Dim rsNotes As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String
Dim count, j As Integer
'Connect to the LicensingSQL Database
Set connSQL = New ADODB.Connection
connSQL.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=LisensingSQL;Data Source=VFRASER\SQLEXPRESS"
'connect to the Lotus Notes License34.nsf database
Set connNotes = New ADODB.Connection
connNotes.ConnectionString = "DSN=Domino cpas license34;Database=cpas\License34.nsf;Server=local;UserName=;EncryptPWD=;MaxSubquery=20;MaxStmtLen=4096;MaxRels=20;MaxVarcharLen=254;KeepTempIdx=1;MaxLongVarcharLen=512;ShowImplicitFlds=0;MapSpecialChars=1;ThreadTimeout=60;"
'delete SQL table if it already exists
connSQL.Execute "DROP TABLE LicensingTest"
'create SQL table
connSQL.Execute "CREATE TABLE LicensingTest(name varchar(25),address1 text, address2 text, city text, state text, zip text)"
'will do something that loops thru the Notes fields to set the SQL fields so I don't have to type all 75 in ...
'For Each fld In rsNotes.Fields
'Debug.Print fld.Value
'rsSQL.field = fld.Value
'direct insert -- worked nicely -- delete when live
'connSQL.Execute "INSERT INTO LicensingTest(name,address1,address2,city,state,zip) values('Name1','1313 Mocking','apt 5','Hickory1','NC','28601')"
'connSQL.Execute "INSERT INTO LicensingTest(name,address1,address2,city,state,zip) values('Name2','123 first','','Hickory2','NC','28602')"
'connSQL.Execute "INSERT INTO LicensingTest(name,address1,address2,city,state,zip) values('Name3','4304 Falls','','Hickory3','ND','28603')"
'set the SQL recordset & make sure it can be added to
Set rsSQL = New ADODB.Recordset
rsSQL.CursorLocation = adUseServer
rsSQL.Open "SELECT * FROM LicensingTest", connSQL, adOpenDynamic, adLockOptimistic
'get the Notes table so that it can be looped thru and added to the SQL table
Set rsNotes = New ADODB.Recordset
rsNotes.CursorLocation = adUseServer
rsNotes.Open "SELECT * FROM Licensing_Profile", connNotes, adOpenDynamic, adLockOptimistic
'my record count is coming out as -1 but I can see all the field names so I must not be opening Notes up right ...
If rsNotes.RecordCount > 0 Then
End If
count = rsNotes.RecordCount
'Do Until rsNotes.EOF
For j = 0 To count
rsSQL!Name = "Test"
'rsSQL!Name = rsNotes!Name -- when going will need to have it loop through fields and fill in "Name" with the field name if possible
End Sub

Open in new window

LVL 75

Accepted Solution

Aneesh Retnakaran earned 1600 total points
ID: 24386570
Why dont you create a table and use a INSERT INTO instead of SELECT into

Author Comment

ID: 24386884
Created table "LicensingTest" then tested that I could add to it this way:
INSERT INTO LicensingTest (name) VALUES ('Testing')
this worked so I moved on to trying to get the Notes connection and table data entered

I tried:
INSERT INTO LicensingTest
VALUES (LicensingNotes.Licensing_Profile.Name)
'it didn't like referencing my other connection ... said it's not permitted

Also tried
INSERT INTO LicensingTest
                   FROM  Licensing_Profile)

I'll keep trying ...
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24387020
INSERT INTO LicensingTest   (name)
SELECT NAME FROM  LicensingNotes..Licensing_Profile  -- note the 2 dotes in between

Author Comment

ID: 24387707
I've added a PDF of what I get when I paste that insert into from your last post.  While at lunch I was thinking that it may have something to do with the way NotesSQL treats all Forms and Views as Tables.  In Lotus the Form is basically just the template that the rows of data are pulled up into using the "Form" field to know which form it needs to use (or use the default db form).  So that could explain why I'm getting -1 document when trying to get it via VB6.  I was able to ODBC the views to Access yesterday and then do a MakeTable from there but RTF fields won't show up in views in Lotus so I'll lose all those fields unless I make a txt field and convert the RTFs by re-saving each of the documents and then show the txt field on my view.  Plus I want to figure out why I can't seem to get it in SQL :=)  

Sort of rambling on that one ... anyhow thought the pdf may show better the error I'm getting plus show if I'm misstating anything due to rookie knowledge.

Author Comment

ID: 24388301
I mis-spoke, Forms are treated as tables and Views are treated as views ... sorry!

Author Comment

ID: 24400106
I never could get a table created by a direct copy from NotesSQL to another database in SQL.

I ended up creating a table in the destination SQL database then using VB script to populate it.  

I've attached the coding in case anyone ever has the same issue.
----Create Licensing Table
(CompanyNo bigint null,
CompanyName Text null,
Contact Text null,
Address1 Text null,
Address2 Text null,
City Text null,
State Text null,
Zip Text null,
WorkPhone Text null,
FaxNumber Text null,
EmailNo Text null,
PrimaryWebsite Text null,
LicenseType Text null,
StatusOfLicenseProfile Text null,
OfficeDaysHours Text null,
LicenseLookup Text null,
LicenseLookupURL Text null,
LicenseDuration Text null,
WorkWLicInProgress Text null,
CostAllLicenses Text null,
TempLicenseAvailable Text null,
TimeToGetLicense Text null,
TimeToGetTempLicense Text null,
MethodsAcceptablePayment Text null,
VerificationFees Text null,
ApplicationAvailableOnWebsite Text null,
LicenseNecessary Text null,
DetailedInformation Text null,
ApplicationAttachment Text null,
RenewalForm Text null,
TimeToGetRenewalLicense Text null,
RenewalFormLink Text null,
CostOfRenewalLicense Text null,
MethodsAcceptablePaymentRen Text null,
RenewalComments Text null,
PostingStatus Text null,
DescriptionUserSeesForMainWebsiteLink Text null,
MainWebsiteLink Text null,
DescriptionUserSeesRenewalLink Text null,
RenewalWebsiteLink Text null)
Private Sub GetLicensingNotes_Click()
Dim connNotes As ADODB.Connection
Dim connSQL As ADODB.Connection
Dim rsSQL As ADODB.Recordset
Dim rsNotes As ADODB.Recordset
Dim count, j As Integer
Dim tmpSQL As String
count = 0
j = 0
'connect to the CPAS SQL Database
Set connSQL = New ADODB.Connection
connSQL.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CPAS;Data Source=VFRASER\SQLEXPRESS"
'connect to the Lotus Notes database
Set connNotes = New ADODB.Connection
connNotes.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=DominoCPASLicense34;Mode=ReadWrite"
'set the SQL recordset & make sure it can be added to
Set rsSQL = New ADODB.Recordset
rsSQL.CursorLocation = adUseServer
rsSQL.Open "SELECT * FROM Licensing", connSQL, adOpenDynamic, adLockOptimistic
'get the Notes table so that it can be looped thru and added to the SQL table
Set rsNotes = New ADODB.Recordset
rsNotes.CursorLocation = adUseServer
Set rsNotes = connNotes.Execute("SELECT * FROM Licensing_Profile")
While Not rsNotes.EOF 'I never could get this to recognize the EOF on my Notes so had to hard code a stop using count
'fill in the fields
count = count + 1 'count how many records have been processed
    With rsSQL
        !CompanyNo = rsNotes.Fields("CompanyNo").Value
        !CompanyName = rsNotes.Fields("Name").Value
        !Contact = rsNotes.Fields("Contact").Value
        !Address1 = rsNotes.Fields("Address1").Value
        !Address2 = rsNotes.Fields("Address2").Value
        !City = rsNotes.Fields("City").Value
        !State = rsNotes.Fields("State").Value
        !Zip = rsNotes.Fields("Zip").Value
        !WorkPhone = rsNotes.Fields("Phone").Value
        !FaxNumber = rsNotes.Fields("Fax").Value
        !EmailNo = rsNotes.Fields("EmailNo").Value
        !PrimaryWebsite = rsNotes.Fields("WebSite").Value
        !LicenseType = rsNotes.Fields("LicenseType").Value
        !StatusOfLicenseProfile = rsNotes.Fields("Status").Value
        !OfficeDaysHours = rsNotes.Fields("OfficeDaysHours").Value
        !LicenseLookup = rsNotes.Fields("YorN").Value
        !LicenseLookupURL = rsNotes.Fields("WebSite_1").Value
        !LicenseDuration = rsNotes.Fields("RenewalTime").Value
        !WorkWLicInProgress = rsNotes.Fields("LicenseProcess").Value
        !CostAllLicenses = rsNotes.Fields("Fees").Value
        !TempLicenseAvailable = rsNotes.Fields("TempLicense").Value
        !TimeToGetLicense = rsNotes.Fields("Time").Value
        !TimeToGetTempLicense = rsNotes.Fields("TempTime").Value
        !MethodsAcceptablePayment = rsNotes.Fields("Payment").Value
        !VerificationFees = rsNotes.Fields("Data1").Value
        !ApplicationAvailableOnWebsite = rsNotes.Fields("YorN2").Value
        !LicenseNecessary = rsNotes.Fields("NeededLicense").Value
        !DetailedInformation = rsNotes.Fields("DetailedInfo").Value
        !ApplicationAttachment = rsNotes.Fields("Comments").Value
        !RenewalForm = rsNotes.Fields("YorN_1").Value
        !TimeToGetRenewalLicense = rsNotes.Fields("Time_1").Value
        !RenewalFormLink = rsNotes.Fields("WebSite_1_1").Value
        !CostOfRenewalLicense = rsNotes.Fields("Fees_1").Value
        !MethodsAcceptablePaymentRen = rsNotes.Fields("Payment_1").Value
        !RenewalComments = rsNotes.Fields("comments_1").Value
        !PostingStatus = rsNotes.Fields("NSPub").Value
        !DescriptionUserSeesForMainWebsiteLink = rsNotes.Fields("txtMainWebsite").Value
        !MainWebsiteLink = rsNotes.Fields("lnkMainWebsite").Value
        !DescriptionUserSeesRenewalLink = rsNotes.Fields("txtWebsite2").Value
        !RenewalWebsiteLink = rsNotes.Fields("lnkWebsite2").Value
    End With
'I have to figure out why Lotus can't realize it is at the end of the recordset ...
If count < 244 Then
    rsNotes.MoveNext            'this gives me the error "[Lotus][ODBC LotusNotes]Data value is not a valid date, time or timestamp"
    GoTo Done
End If
MsgBox "Licensing is now populated", vbOKOnly, "Process Complete"
Set rsSQL = Nothing
Set rsNotes = Nothing
Set connSQL = Nothing
Set connNotes = Nothing
End Sub

Open in new window


Author Closing Comment

ID: 31582126
I wasn't sure how to grade the solution since I ended up combining ideas using the code from the links from sjef_bosman 9( had part of the VB already there just needed the correct connection strings there)
aneeshattingal gave me the idea of inserting it even though the INSERT INTO wouldn't work right with lovely NotesSQL at least that put me on a better track for this situation.  THANKS!
LVL 46

Expert Comment

by:Sjef Bosman
ID: 24400139
Just a hunch... There might actually be more than 244 documents in the view, but the code breaks on one that contains invalid data for your query or code. In Notes, it is entirely possible that a certain field in one document document contains a string value and in another document it contains a numeric value. Or a string value and a date value. It's not the proper way to do things, but it *is* possible.

Author Comment

ID: 24400267
I did show 246 records when I opened it from within Lotus so that would make sense.  I'll see which 2 are missing and work on that premise.


Author Comment

ID: 24400486
I went ahead and posted a new question with points since it seems like it makes sense to do that??

Here's what I did (it's also on the new question if you'd rather read it there ...)
When I remove the two records that appear to be the problem it does fine.  When those two are still gone and I add a new record, it goes back to the error.  So then I removed the two bad ones, copy 2 that I saw had successfully inserted into SQL and pasted them in the Domino database and it still gives me that error.

That's making me think that it's just stopping at 244 rather than record data being bad?  I know that max query results by default in Notes are 250 and there is no max results type of wording in my connection string so that may be the issue but I'm not sure how to word that.I went ahead and posted a new question for this since it's more complicated.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question