> Gil, > > Could he justify why he was using c(80) for everything...and CHAR > instead of VARCHAR at that? Sheesh.... >
Actually, I know how that came about. Last October, when they first undertook their Data Warehouse project I had suggested they use PostgreSQL, but they were already entrenched into SQL Server Light/Express/Whatever 2000 (I think). Okay, fine, I suggested they simply connect to my VFP tables via ODBC, read my records and use their logic to populate their SQL tables. Well, they did not want to do that, so I was asked to provide .csv files with my logic to output altered and appended records from the previous CSV output routine. I have an app that does that, luckily, already, into CSV, Fixed Record Length or XML format (next is to add TAB). As they tried to import the CSV files they ran into a lot of problems. They kept asking me to see what "I" was doing wrong, but eventually I was able to prove my process was working properly. They moved to SQL Server Express 2005, and that "fixed" all their import problems, until they ran into them again <g>. In an effort to fix the Express 2005 import problem their programmer reset all fields to c(80), despite my telling them we ought to go back to them accessing my tables via ODBC. Last month I went out to Puerto Rico to work with these folks a bit, as it was clear they were lost. It was a Working Vacation, and I carved out a few days for them. Within 20 minutes I found out why their ODBC efforts had been failing, they were installing and configuring ODBC on the wrong machine! I configured ODBC on the correct machine, set up a view into my VFP dbc, and they could see my tables. But, they preferred to have me send the records still. So I set up the updateable RVs into their SQL database, saw what they had done with the c(80) on all fields (about puked), and told them I could indeed append and populate into their tables from VFP. But I would either need them to redefine their fields to match mine, or I would have to create the code to convert my fields into all PADR(<converted values into character, 80) in my SQL-SELECT code. I also warned them about running out of database room in SQL Express 2005, and how I would likely have to use parameterized views in VFP to prevent getting SQL cursors > 2Gb in size. It was like spitting into the wind. So I said I would do that kind of coding back home, as it was too complex for me to create and implement with the last 1 1/2 hours I had on site, before their offices were going to be closed for the day. I was flying home the next day... So, that is the background. Until I hit the RV cursor > 2Gb upon import, and hit their SQL Express database limit of 4Gb max size, they did not want to alter their tables. Well, they have been doing it since this past Monday, as I did indeed hit the wall(s), as predicted. They really are nice folks, and I love working with them. I just hope that when they hit the 4Gb max limit again, with their rebuilt fields, they will finally look at PostgreSQL as opposed to kicking out > $50k for full blown SQL Server (3,300 employees, 43 dealerships and an export operation). I really wish they had listened to me last October instead of their hired Expert Consultant, who did not know how to write SQL-SELECT code using SQL Express 2005, as she had only used Access in the past! When their IT Director told me that the other week I about fell out of my chair! I thought I was the one on a learning curve, but I guess not as much as they are. Okay, time for me to redefine my RVs to match the new SQL table structures! Gil > -----Original Message----- > From: profoxtech-boun...@leafe.com [mailto:profoxtech- > boun...@leafe.com] On Behalf Of MB Software Solutions General Account > Sent: Wednesday, April 08, 2009 2:26 PM > To: profoxt...@leafe.com > Subject: Re: vfp9 and sqlserver express > > Gil Hale RR wrote: > > Funny you would ask that question at this moment in time. I am > working > with > > a client who is (trying) using SQL Server Express 2005 for a data > warehouse > > project (yikes!), and I am using my VFP tables to populate his SQL > temp > > tables used to update his SQL production tables. I created > Updateable > > Remote Views of his tables, and have been successful in importing > data into > > said RVs from my VFP tables, as long as the field names and types > match of > > course. I did try to do a record-by-record SCATTER-APPEND BLANK- > GATHER > from > > VFP into SQL, and it worked. I did that to show their one programmer > an > > alternate method of populating his SQL tables as he got edgy about me > doing > > a straight import (don't ask... <g>). Anyway, the SCATTER-APPEND > > BLANK-GATHER worked fine. I did have the Remote View cursor set to > record > > level buffering, so every time the record pointer moved via APPEND > BLANK it > > would fire an update for the target SQL table. He did end up > "allowing" me > > to do the import process, but I am certain the SCATTER-APPEND BLANK- > GATHER > > for > 1 million records would have worked fine, just taken a tad > longer > than > > IMPORT FROM at worst. > > > > BTW, the SQL tables were set up with all field types as c(80), > whether my > > VFP table fields were Numeric, Date, DateTime, Character, or Logical. > He > > wondered why he could not get all the tables populated. SQL Server > Express > > 2005 has a 4Gb max database size limit, and he hit it. So for the > past 3 > > days he has been rebuilding his SQL tables to match my layouts (I had > given > > him those specs previously). I told him how he could use some DISP > STRU > > created layout files for my VFP tables to translate and alter his > tables > via > > SQL, but he opted to do it all manually I used that DISP STRU file > > code to > > "convert" my fields through SQL-SELECT output to c(80) for SQL import > > purposes. What a PITA, but better than manually doing it). Some of > my > > tables have over 220 fields! Uggghhh... But once he gets away from > c(80) > > squared layouts (not VarChar, he used Char!) he ought to be well > under 4Gb > > in his database. If not, he can split the tables between two > databases. > > Better yet, he ought to move to PostgreSQL! I bought him a book on > > PostgreSQL for Windows the other week as a gift. He put it on a > shelf, and > > I bet it is still there. Shame... > > > > Hard to knock it, I know what it is like to be a newbie with database > > solution creation. At that many in the ProFox group would shudder to > see > > some of my designs (all for good reason, not mainstream). I know > back in > > late 2000 I likely spooked Ed pretty badly when he saw ny core app > for the > > first time! Heh-heh. I made changes based on his recommendations, > and it > > still runs great! But I do not recall doing c(80) squared field > designs > > throughout all tables! I hope my client moves to PostgreSQL. The > sooner > > the better... > > > Gil, > > Could he justify why he was using c(80) for everything...and CHAR > instead of VARCHAR at that? Sheesh.... > > > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/02e101c9b87c$54aa9250$fdffb6...@rr.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
