Where there is a will there is a way. I applaud you for wanting to wean yourself off the Microsoft tit and break the hideous upgrade cycles that are designed to empty your pockets. I am also in the beginning stages of going from a MSSQL to SAPDB and will soon be faced with the task of data porting.
While I cannot give you a stead-fast proven method to port your data I would like to keep in touch with you during our conversion experiences. I am going to share the following ideas that may not even come close to being correct or even lead you down the right path but they are what I would investigate if I ran into the situation where everything is failing. 1) I do know that the MSSQL DTS engine *does not* like to export to other DBs (except Access) via ODBC but is very forgiving when importing data from others. (Do not think for one minute that this is an accident) 2) It seems to me that SAPDB only supports the unicode code page (everyone please correct me if I am wrong...please!) so this may have something to do with it if you are going from non-unicode code page to a unicode code page...Again, I am not sure this is relevant or correct ... it is just a guess of something I would look into. 3) Try using the SAPDB Tools to connect to MSSQL and try a direct cross-server fetch and insert from there. If this isn't a go try using another tool that speaks MSSQL's language (OLEDB or ODBC) but also talks SAPDB ... this one may work (???): http://www.dbone.info/dbone/index.php ... (someone posted this link a couple of weeks ago) 4) Does your MS DB use any proprietary 'anything' (data-types, etc.) that is screwing the export up? If so, then this may be an issue but damn hard to find if you never get any error messages to guide you. (ex:, mysql varchar max length = 255 -> mssql varchar max length = 8000) 5) I know for a fact that you can get data from MSSQL to MySQL via the ODBC Import feature pretty quick in the tool MySQL-Front (latest version). This screws ya for your FKs and such that MySQL doesn't support but it may come to that. From there you would think that an upload of data from the MySQL db to SAPDB would be a little easier given the open-source nature of both DBs...again, just a guess. 6) You may need to employ a several step process whereby you clone the db structure in the target db and do incremental imports whatever way you can to get the data over there. Sure this is a labor intensive approach but it may come down to that. **************************************************************************** * Most of these suggestions may seem absolutely off-base to the more DB savvy members of the list but when things go wrong, the docs and tools let you down I tend to start thinking outside-the-box and see if the absurd makes sense. This off-the-wall brainstorming method has worked for me in the past so as crazy or lame as these suggestions may seem, one never knows when dealing with MS products. Most of all ... DON'T GIVE UP ... that is what MS wants you to do so they can keep sucking the money out of your pockets. Keep me up to date with your experiences as I will you with mine. If you can't get SAPDB to work for the life of you then go PostgreSQL with Linux ... just don't let the bastard win. Kevin -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Yves Trudeau Sent: Wednesday, December 11, 2002 7:22 PM To: [EMAIL PROTECTED] Subject: RE: Questions & Chronicles of someone trying to move from Microsoft DB's to SAPDB Hi, I have migrated a database from MS Access to SapDB sucessfully a year and a half ago. Personnally, I don't like the DAO or ADO style for data manipulation, I prefer plain SQL. Here is the part of my exporting code that transfert the data. lbTable is a listBox containing a list of table to export and conn is a connection to Sapdb. Tables are created by another Sub. I hope this help. Good luck, Yves Trudeau Private Sub BT_Transfert_data_Click() Dim i As Integer, iPos As Long, iTotal As Long Dim rstIn As Recordset, rstOut As Recordset Dim strTable As String, strChamps As String, strValeurs As String For Each ListeItem In Me.lbTables.ItemsSelected wrk.BeginTrans strTable = Me.lbTables.Column(0, ListeItem) conn.Execute ("delete from " & strTable) strSql = "select count(*) as total from " & strTable Set rstOut = db.OpenRecordset(strSql, dbOpenSnapshot) iTotal = rstOut!total rstOut.Close Set rstOut = db.OpenRecordset(strTable, dbOpenSnapshot) iPos = 1 While Not rstOut.EOF strChamps = "" strValeurs = "" For i = 0 To rstOut.Fields.count - 1 If Not IsNull(rstOut.Fields(i).Value) Then strChamps = strChamps & rstOut.Fields(i).Name & "," Select Case rstOut.Fields(i).Type Case 1 'Booleen If rstOut.Fields(i).Value = True Then strValeurs = strValeurs & "TRUE," Else strValeurs = strValeurs & "FALSE," End If Case 2, 3, 4, 7 strValeurs = strValeurs & rstOut.Fields(i).Value & "," Case 8 If strGetProp("format", rstOut.Fields(i).Properties) = "Short Date" Then strValeurs = strValeurs & "'" & CStr(Format(rstOut.Fields(i).Value, "yyyy-mm-dd")) & "'," Else strValeurs = strValeurs & "'" & CStr(Format(rstOut.Fields(i).Value, "yyyy-mm-dd hh:mm:ss")) & "'," End If Case 10, 12 strValeurs = strValeurs & "'" & adhHandleQuotes(rstOut.Fields(i).Value, "'") & "'," End Select End If Next i DoEvents strSql = "INSERT INTO " & strTable & " (" & Left(strChamps, Len(strChamps) - 1) & ") Values (" _ & Left(strValeurs, Len(strValeurs) - 1) & ")" Debug.Print strSql conn.Execute (strSql) 'Meter Me.tbTableEnCours = strTable & ": (" & iPos & "/" & iTotal & ")" iPos = iPos + 1 rstOut.MoveNext Wend rstOut.Close wrk.CommitTrans Next End Sub -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Sergio Samayoa Sent: 11 d�cembre, 2002 18:08 To: [EMAIL PROTECTED] Subject: Re: Questions & Chronicles of someone trying to move from Microsoft DB's to SAPDB I'm not VB expert (I'm Delphi developer) but what is the default behaviour with transactions when closing ADO connection without commiting? Change your VB code and start explicit transaction in the destination connection and commit after the end/while. "jd" <[EMAIL PROTECTED]> escribi� en el mensaje 004301c2a15d$66551e40$6401a8c0@fhswsxp01">news:004301c2a15d$66551e40$6401a8c0@fhswsxp01... >From Jon: Fountainhead Software Solutions, Canby Oregon Forgive the lengthy message here but I have so many questions, issues, and experiences I don't know where to start. If you can answer or address any of these questions please do as one answer may lead to solving many more of them. Also, please don't simply reply back with "it works here" because I'm sure it does work other places, I just need to know how to get to that place. I have developed several systems using VB6 initially tied to MS Access db's using DAO. I converted one of these systems a year and a half ago to work with MS Sql Server using ADO. This was after trying for a couple of weeks to get it to work well with MySQL. I was able to make that work, but performance in the MS environment using VB6 & ADO was very sluggish and everything I read warned me of that MySQL just wasn't ready for this environment. I recently converted another system likewise to MS Sql Server only to find they balked at spending the thousands of dollars in Microsoft licenses. I happened across SAPDB last week and am confident that it will prove to be the answer as I'd like to get out of Microsoft db products all together. Here's been my experience with SAPDB: I successfully installed the server, the test database, SQL Studio, the DB Manager GUI, the SAPDB ODBC package. I read User Guide pages and Repmcli pages and User Reference pages on my computer screen until my eyes hurt, bad. (I sure wish I could find a printed book on this stuff in English) I was able to get MS Access to link tables I created via ODBC and I was able to add records and do most anything you'd expect. I was able to create a VB6 program that used the ADO data control, added it to an Infragistics grid and was merrily able to manipulate data to my hearts content. I figured I was ready to tackle porting the data from the MS Access db to SAPDB. I decided to do a little test. This is where the effort jumped off the track. Using repmcli I was able to import data into a table from a csv file providing the file only had one field. Once I added a second field repmcli complained that it couldn't find data for the second field. After scouring the archives I stumbled upon the information that I needed to have all fields encased in double quotes whether they were text fields or not. Hmmm. that's a pain but I can deal with it. Once I was able to add more than one column via repmcli I decided to make a table with all the data types I'd have to deal with. Here's how I figured I'd have to convert from MS Access types to SAPDB types: number(long) -> integer, date/time -> date, text -> varchar, memo -> long(ascii), currency -> float. Do you see any potential problems with this? I'm particularly nervous about the data/time fields and memo field conversions. I built a table using all these types. I created a csv file with all fields enclosed in double quotes by changing all of my Access fields to type text so Access would export the data with the double quotes. The data file looked to me exactly as I'd want it. Despite this I could never get repmcli to get past the first line of data as I kept getting the No data supplied error message on one of the memo fields. Figuring I was now creating more problems than I was solving with all these gyrations I decided to abandon repmcli and search for another method. I tried Microsoft DTS. It looked very promising as I was able to connect using ODBC to both the Access table and the SAPDB table that I was to move data from and to. When I clicked on the transfer button it all looked like it was going to work. Hours later it was obvious it wasn't working as nothing had changed and it should've only taken a couple of minutes. I finally killed the DTS process. I connected to the SAPDB database using SQL Studio (as I had many time before), but now when I tried to look at any table using ALTER Table, SQL Studio would lock up. There had been no records added to the SAPDB table either. I performed a complete backup of the database as I'd read that this was necessary after using repmcli to get it out of readonly mode, thinking maybe this was the same issue. In the end there was nothing I could do the salvage this situation and decided to create a new test database. HERE's a question: how do I get rid of these test database I seems to be accumilating on my server so that it appears they never existed? So I created a new database and another new table. I decided I'd write a VB6 program to simply transfer data from Access using ADO recordsets. Here's the code: Dim strSQL As String Dim i As Long Dim rsO As New ADODB.Recordset Dim cnO As New ADODB.Connection Dim rsS As New ADODB.Recordset Dim cnS As New ADODB.Connection cnO.CursorLocation = adUseClient cnO.Open "DSN=OriginalData;" cnS.CursorLocation = adUseClient cnS.Open "DSN=SAPDBTst1;Password=DMSPWD;User ID=DMS;" strSQL = "select * from tbltst1Export order by ProjId" rsO.Open strSQL, cnO, adOpenForwardOnly, adLockReadOnly strSQL = "select * from ATBLProject" rsS.Open strSQL, cnS, adOpenStatic, adLockBatchOptimistic Do While Not rsS.EOF Debug.Print rsS.Fields("TITLE") rsS.MoveNext Loop rsS.MoveFirst Do While Not rsO.EOF rsS.AddNew For i = 0 To rsO.Fields.Count - 1 rsS.Fields(rsO.Fields(i).Name) = rsO.Fields(i) Next i rsS.Update rsO.MoveNext Loop With this code I was able to APPARENTLY move all the data from the Access table to the SAPDB table as I got NO ERRORS. When I went to SQL Studio to look at the data none was added. When I used ADO to look at the data nothing appeared. I then linked to the SAPDB table using Access and added 1 record. This one record showed up but I never was able to add data to the table using an ADO recordset. THE QUESTION MOST IMPORTANT TO ME is why didn't the above code work? Why didn't the data get added to the SAPDB table and why didn't I get any error messages when it didn't work?!??!? This one is really disturbing. I then went to MS Access again, linked the SAPDB table. Created an append query to move data from the Access table to the SAPDB table. this query locked up as well. I ended up killing the Access process. Now the new test database appears to be toast just like the last one. Why does this keep happening? I'd really like to use SAPDB and knew there'd be a learning curve just as there was for SQL Server and MySQL. But in my experience one worked for me (SQL Server) and one didn't work (MySQL) for me. I need to decide quickly if SAPDB will work for me. Any guidance anyone can give me to help me through this crawling over broken glass period would be greatly appreciated. Or simply being told that what I'm trying to do just ain't going to work would also be usefull. Cheers, Jon _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
