Solved

NotesSQL table create into SQL database

Posted on 2009-05-14
14
1,149 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 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

19 Experts available now in Live!

Get 1:1 Help Now