Exporting Blanks

[MMund]

I am attempting to export several SQL Server tables to txt or CSV files using VFP's COPY TO command. Most of them work, but there are a few that instead of writing the data present in the table, it writes a blank line for each record. I'm using SQLEXEC to put the SQL data into a cursor, and then doing the COPY TO after selecting the cursor. One other thing: I'm using Visual Fox Express, but the code is strictly VFP. Ideas, anyone?

TIA,
MMund

 

[Olaf Doschke]

what's in the cursor in those cases?
NULL values?
What's the SQL Servers table structure for those tables?
What's the cursor structure?

Bye, Olaf.

 

[Mike Lewis]

Hi MMund,

If it's only happening in a few cases, a good approach would be to see what's different in those cases. Can you see anything special about the data that's causing the blanks?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[MMund]

Had a co-worker who had a hex reader look at one of the files, and he said there were only CRLFs in the file. The code is the same as for the cases that are working. One thing I should mention: The input SQL tables are built using TSQL stored procedures, but as I said, if you look at the data with SSMS, it's there. As far as the SQL table structures, it's mostly varchars with a few ints and numerics. I don't specify a structure for the VFP cursors, they are built using SQLEXEC.

 

[Olaf Doschke]

Hi MMund,

it doesn't sound like the fault is some datatype like Blob or something of that kind.
Still even though you don't specify a special structure, I assume you execute stored procs in those cases or Select *. I would analyze the intermediate result, browse the cursors that get generated by those sqlexec. And see what field types you get there. Perhaps those are longer varchars, which result in Memo fields on the vfp side.

Bye, Olaf.

 

[JRB-Bldr]

You shouldn't spend much time looking at the back-end of the process - the resultant CSV file.

Begin by looking at the data PRIOR to any output to CSV,,,

"The input SQL tables are built using TSQL stored procedures"
How they are created doesn't matter as long as the SQL tables are successfully created and the records are populated as desired.

"if you look at the data with SSMS, it's there."
So you are saying that the intended records are present and appropriately populated within SQL?

"it's mostly varchars with a few ints and numerics"
Then this should not be a problem.

[I" I don't specify a structure for the VFP cursors, they are built using SQLEXEC. "[/I]
As long as your SQL fields don't exceed 254 char in length or you have BLOB fields, you should be fine. However, in most cases, even if you did have the larger SQL table fields, VFP should receive the fields as MEMO fields - which will not export with a COPY TO command.

Obviously in order to use VFP commands to generate the intended CSV file you have to 'pass' the cursor/table to your Visual Foxpro Express (VFE) somehow.

What does the cursor/table look like immediately after VFE has 'received' it (via Remote View or SQL Pass thru cursor, etc.) before attempting to do the COPY TO?

Are some records as seen in a VFP Browse filled with all NULLs? That might present a problem which can be alleviated by changing NULLs to 0's for numerics or SPACE(0) for characters.

If the VFE 'view', immediately after receipt of the cursor/table is not appropriately populated then you need to investigate how VFE 'receives' the file.

Also you might want to check the SQL output method (SSMS) by sending the results directly to another file type (XLS, TXT, etc.) and checking it there. There could be a problem at that end.

I have never used Visual Fox Express so I do not know if VFE is less functional than VFP (I would hazard a guess that it is), but, assuming everything else checks out, you might want to consider moving up to VFP9 and getting full functionality.

Good Luck,
JRB-Bldr

 

[MMund]

JRB,
You've helped me find the problem. Some of my input fields were concatenations way over 254 characters. Guess I'll have to break them up. A lot to do, but at least I now know what to do.