August 2, 2009 at 3:19 pm
It ain't just less code, it's faster too. And yeah, internally SQL server does have to go row-by-row, but that's faster than an explicit while loop.
Have a look at Jeff's article on tally tables — it's a good introduction to set based problems, look at the first couple of examples of generating a tally table, one shows an explicit loop (RBAR) solution, the second shows a set based solution which completes much quicker.
August 2, 2009 at 5:48 pm
@ Jeff: Thanks 🙂
@ Allister: Yes, I read it. That article on Tally table is a real eye opener.
Thanks for the help guys
August 2, 2009 at 7:25 pm
As a sidebar... you might be a "novice_coder" in T-SQL but, from what I can see in this thread, you are not a "novice developer". Well done.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2009 at 9:24 pm
haha thanks Jeff. Yeah, I have been acknowledged by my professors and managers in the past for my coding skills. Hopefully I will become as good as you guys in T-SQL one day 🙂
August 3, 2009 at 9:18 am
Having used FoxPro for a long time, there was a good reason to use RBAR: step-wise code in FoxPro on an index could execute extremely fast. It was intended as a file server application to support a handful of users, but with clever coding our app. supported 30 users in a networked environment easily. BUT: many well-formed, set-based SQL queries that provide better performance in a true DB engine (SQL Server, MySQL, Oracle) ran dog slow in FoxPro.
No more SET ORDER TO, SEEK, SKIP commands to locate specific values in a table!!
There will be a small number of things that will be more work to code in SQL than in FP code b/c tables in a RDMS don't have any intrinsic order to them. But the code that executes in SQL on a set basis will be much faster than using cursors.
I liked working with FoxPro b/c it included a lot of tools/functions I wish SQL Server had, such as 30 string manipulation functions, for example. SQL Server 2005 doesn't even have a true TRIM() function - you have to LTrim(RTrim(mystring)), for example. But where FoxPro fell down was in SQL execution. It had full support for ANSI syntax, but it just couldn't execute the SQL quickly. And you can easily create your own versions of those missing string functions in SQL and use them as you would in FoxPro.
One thing you might find useful in FP if you aren't already aware of it is the ability to execute pass-through SQL on your SQL Sever from within FP code. I found this extremely useful when error-checking data migration, b/c the same code in FP could query the SQL Server. Sounds like you've done all that already in SSIS, but thought I'd mention it.
August 3, 2009 at 10:02 am
Hello rmechaber,
Thanks for the information. Yes, I have managed to migrate the data using SSIS but I am facing problems with Logic/Bit fields. In my FoxPro table these fields have null value for some records. When I port the data in SQL Server using SSIS, it changes all these Null values to False (which is what I dont want). Do you know how to fix this? Also, How do you verify that all the records have been ported correctly? Right now I am just manually checking/matching random records present at the two locations(mdf and SQL table).
Thanks for the help.
August 3, 2009 at 11:59 am
Not sure why NULL would import as False with SSIS. If you create a linked server from SQL Server to your FoxPro database, you can run queries from within SQL Server Management Studio against your FoxPro tables to see how those NULLs appear to SQL Server.
You should know that SQL Server does not have a true Boolean/Logic data type: the BIT is just a small integer data type, either 0 or 1.
Statements like SELECT * FROM MyTable WHERE myBool;
will fail.
You have to put WHERE myBool=1 in the WHERE clause.
You'll definitely want this paper:
http://www.craigbailey.net/articles/article_vfpintosql1.htm
As for verification, that's a general question others may have suggestions about. General stuff like number of rows imported is basic. Does your import routine catch and report any errors it finds? Have you cleaned up your VFP data as much as possible beforehand? One of the best ways to confirm the data migration is to run a series of comprehensive reports on your data in VFP and then immediately do the same in SQL after importing. Select reports that touch lots of tables, records, and columns - year end financial statements by department, 5 year order summaries, whatever. These should match exactly.
August 3, 2009 at 2:59 pm
Are you changing anything in the database schema or just "copying" the tables and data to SQL Server? It should be fairly straitforward if you are copying the Foxpro data. The other question is are you moving from free tables to SQL Server or from a FoxPro database?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 3, 2009 at 4:03 pm
rmechaber (8/3/2009)
As for verification, that's a general question others may have suggestions about. General stuff like number of rows imported is basic. Does your import routine catch and report any errors it finds? Have you cleaned up your VFP data as much as possible beforehand? One of the best ways to confirm the data migration is to run a series of comprehensive reports on your data in VFP and then immediately do the same in SQL after importing. Select reports that touch lots of tables, records, and columns - year end financial statements by department, 5 year order summaries, whatever. These should match exactly.
Thanks for the reply. Yes, my SSIS package does give me all the information including number of rows imported. I am not getting any errors and all the rows are being imported. Looking at the records, it "seems" like I am getting all the information correctly. I was wondering if theres a program like diff file1 file2 thats present in unix to compare to files or something else on these lines, that I could use to verify that everything is being imported correctly rather than looking myself at records/reports.
August 3, 2009 at 4:09 pm
Jack Corbett (8/3/2009)
Are you changing anything in the database schema or just "copying" the tables and data to SQL Server? It should be fairly straitforward if you are copying the Foxpro data. The other question is are you moving from free tables to SQL Server or from a FoxPro database?
Hello,
I am just copying the tables and data to SQL Server. I am moving free tables. Everything is importing/exporting fine. I am just having trouble with Logic fields of FoxPro. Its getting converted to BIT field and is imported as False for all the null fields. If I change the field type of this column to tinyint in my SQL server table then all the null values are imported as 0 for this field.
August 3, 2009 at 4:40 pm
Hi,
I just tested a SSIS import of a Foxpro free table, with integer and logical fields, both nullable. I added a few rows of data leaving a few fields of each type null.
Using SQL server Import Wizard I successfully imported the foxpro table to a new table in a test SQL database nulls imported intact.
I then re-ran the import as an append, again the data was imported correctly (nulls intact).
I changed the default value of the fields to zero, and re-ran append, records with null values had a zero in place of the null.
You will need to drop the constraint on your bit field which is defaulting the value to zero.
August 3, 2009 at 8:13 pm
Allister Reid (8/3/2009)
Hi,I just tested a SSIS import of a Foxpro free table, with integer and logical fields, both nullable. I added a few rows of data leaving a few fields of each type null.
Using SQL server Import Wizard I successfully imported the foxpro table to a new table in a test SQL database nulls imported intact.
I then re-ran the import as an append, again the data was imported correctly (nulls intact).
I changed the default value of the fields to zero, and re-ran append, records with null values had a zero in place of the null.
You will need to drop the constraint on your bit field which is defaulting the value to zero.
I've tried running SSIS package but I am still getting False value for bit field. Heres what the issue is I think. Say I have a test table that I have created using
use Test
--===== If the test table already exists, drop it
IF OBJECT_ID('Testing','U') IS NOT NULL
DROP TABLE Testing
--===== Create the test table with
CREATE TABLE Testing
(
BITFIELD BIT default(NULL),
)
Now I get different results when I execute the following two statements:
insert into testing SELECT NULL
insert into testing SELECT ''
For the first one, the table has value NULL and for second one the table has value False. I think the second case is whats happening when I try to import my FoxPro tables to SQL Server with blank logical values. SQL Server is taking blank values for Logical field as '' rather than NULL and hence I am getting False in my SQL table. Any ideas on how to fix this? I have tried changing the type in SQL table for this field to varchar also but I still get false.
August 3, 2009 at 8:20 pm
BTW, I am not using Import/Export wizard. I am building a SSIS package and for source I am picking OLE DB Source and for the Provider in connection Manager, I am choosing Microsoft Jet 4.0 OLE DB Provider.
August 3, 2009 at 8:28 pm
Why are you using the JET provider when there is a FoxPro OLE DB driver? This could be your problem.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 3, 2009 at 8:33 pm
Jack Corbett (8/3/2009)
Why are you using the JET provider when there is a FoxPro OLE DB driver? This could be your problem.
hmm Beacsue I read somewhere on the internet that JET provider will work just fine for importing data. And me being lazy didnt try installing the FoxPro driver 🙁
Lemme install it and see what happens with it. Thanks for the tip
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply