Solved

NotesSQL table create into SQL database

Posted on 2009-05-14
14
1,164 Views
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.
0
Comment
Question by:VanessaTamora
  • 9
  • 3
  • 2
14 Comments
 
LVL 75

Expert Comment

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

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 100 total points
ID: 24385379
0
 

Author Comment

by:VanessaTamora
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.

Thanks!
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:VanessaTamora
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 ...

Thanks!
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"
connSQL.Open
 
'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;"
connNotes.Open
 
'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
'Next
 
'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
 
Stop
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
  rsNotes.MoveLast
  rsNotes.MoveFirst
End If
 
count = rsNotes.RecordCount
 
'Do Until rsNotes.EOF
For j = 0 To count
rsSQL.AddNew
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
rsSQL.Update
Next
'Loop
 
rsNotes.Close
rsSQL.Close
connSQL.Close
connNotes.Close
 
End Sub

Open in new window

0
 
LVL 75

Accepted Solution

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

Author Comment

by:VanessaTamora
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
               (name)
VALUES (LicensingNotes.Licensing_Profile.Name)
'it didn't like referencing my other connection ... said it's not permitted

Also tried
INSERT INTO LicensingTest
               (name)
VALUES (SELECT Name
                   FROM  Licensing_Profile)

I'll keep trying ...
Thanks!
0
 
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
0
 

Author Comment

by:VanessaTamora
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.
NotesToSQLpics.pdf
0
 

Author Comment

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

Author Comment

by:VanessaTamora
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.
----SQL CREATE TABLE CODE----
USE CPAS
GO
DROP TABLE CPAS..Licensing
----Create Licensing Table
CREATE TABLE CPAS..Licensing 
(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)
 
----VB CODE TO INSERT RECORDS - 1 QUIRK WITH THIS CODE ----
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"
connSQL.Open
 
'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"
connNotes.Open
 
'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
        .AddNew
        !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
        .Update
        rsSQL.MoveNext
    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"
Else
    Stop
    GoTo Done
End If
 
Wend
 
Done:
 
MsgBox "Licensing is now populated", vbOKOnly, "Process Complete"
 
rsNotes.Close
rsSQL.Close
connSQL.Close
connNotes.Close
Set rsSQL = Nothing
Set rsNotes = Nothing
Set connSQL = Nothing
Set connNotes = Nothing
 
End Sub

Open in new window

0
 

Author Closing Comment

by:VanessaTamora
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!
0
 
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.
0
 

Author Comment

by:VanessaTamora
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.

Thanks!
0
 

Author Comment

by:VanessaTamora
ID: 24400486
I went ahead and posted a new question with points since it seems like it makes sense to do that??
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24413807.html

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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 2 53
SQL Query with Sum and Detail rows 2 50
Query to return total 6 19
Why i am getting a star, SSMS does not show me any error. Division Error 5 22
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

777 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