July 30, 2003 at 1:24 am
I'm using MS SQL Server 7.0 and I've been looking for answers in several newsgroups about how to export a table that contains null values into a plain text file with fixed fields. The end result is that the output files columns aren't aligned/fixed. Some suggest to use the ISNULL command when selecting the values, but isn't there an easier way of doing it?
Thanks in advance!
July 30, 2003 at 7:40 am
When defining the destination file you have the option of fixed or delimited. If you pick fixed then the nulls will come out as spaces and your columns will be aligned.
July 30, 2003 at 9:20 am
I wish that was true, but alas. It doesn't work! I've selected the Fixed field radio button as you suggested, but with no success. I've tried the same thing on a SQL Server 2000 database and it works fine, but on our SQL Server 7.0 database...no, no.
I can solve this problem by writing my own select statement.
SELECT ISNULL(COL1, SPACE(10)) AS COL1,
ISNULL(COL2, SPACE(12)) AS COL2
FROM table1
Nevertheless I don't want to write select statements for all my tables that I need to export.
July 30, 2003 at 9:26 am
Works like a charm for me. I send files to a mainframe all the time with nulls and fixed length. I even wrote a one timer this morning to prove it works. I think there may be a setting in your 7.0 environment that is causing this issue for you.
Does anyone out there know of a switch that would cause NULLS to not become spaces when transforming to a flat file defined as fixed?
July 30, 2003 at 7:06 pm
That sounds true. I've looked for all kinds of settings that could cause this behaviour, but I havn't found anything yet. songmeister, which SP are you running with you 7.0 installation?
July 31, 2003 at 8:16 am
We are using SP3
August 1, 2003 at 7:17 am
I'm on SQL7 SP4 and it works fine for me.
What are you using to check column alignment (proportional font!)
Far away is close at hand in the images of elsewhere.
Anon.
August 1, 2003 at 7:21 am
Very good point Mr. DavidBurrows! I myself always use courier as it is a non-proportional font.
August 4, 2003 at 7:52 am
Hello again!
Yes, I'm using a plain text editor to get a proportional font. Our SQL Server installation is the Original SQL Server 7.0 release, (7.00.623). We've decided to move or upgrade to SQL Server 2000. But that will happen later on. Meanwhile I've realized that to solve my problem have to write my own select statements.
SELECT ISNULL(COL1, SPACE(10)) AS COL1,
ISNULL(COL2, SPACE(12)) AS COL2
FROM table1
Thanks for trying to help me with this!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply