Solved

NotesSQL table create into SQL database

Posted on 2009-05-14
14
1,155 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
 

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

863 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now