VB export data to fixed width text file

There are a few issues to resolve first.
1. Are there any memo fields?
2. Are there any binary data fields?
3. Do you want to start the columns based on the (maximum) size of the field or the largest size of any row for that field?
4. How do you want the numeric and date fields formatted?
5. What do you want to happen if a line exceeds 32K characters?
6. How much space do you want between the end of a field and the start of the next field?
7. What kind of database are you using?

Of course, you should let us know how experienced you are at VB programming, SQL, batch processing, etc.

Not very experienced as a programmer, but here is the sql query that will be run.  I have handled the date issue and no record will ever by over 32k.  

SELECT RM00103.CUSTNMBR, CUSTNAME, RATETPID, CAST(CRLMTAMT AS NUMERIC(19,2))AS CRLMTAMT,
cast(CUSTBLNC as numeric(19,2))as CUSTBLNC, cast(AGPERAMT_1 as numeric(19,2)) as AGPERAMT_1,
cast(TTLSLYTD as numeric(19,2)) as TTLSLYTD, cast(TTLSLLYR as numeric(19,2)) as TTLSLLYR,
convert(varchar(10),LSTTRXDT,101)AS LSTTRXDT, CONVERT(VARCHAR(10), LASTPYDT,101)AS LASTPYDT
FROM RM00101 inner Join RM00103 on rm00101.custnmbr = rm00103.custnmbr
WHERE Lsttrxdt >= dateadd(day, -1, convert(datetime, convert(varchar(10), getdate(), 120), 120))

As you can see, this query (and the two others just like it) are going to export data into a flat file to be later imported into a crm application.  i wrote the connection string and can open the database, I can right the sql string inside of VB, I just don't know how to get this into a text file.

Well, you might not need to write a VB app. I use a stored procedure with a cursor in it that writes the output to file using xp_cmdshell extended stored procedure to execute the echo command to direct output to  a file.

run the following statement block. It will write the header record to a file (c:\testfile.txt') then write a detail record to that same file. All you need to do is write a cursor that selects the data you want, puts it in a string, and you xp_cmdshell each record into the ascii file. You can put the stuff in a stored procedure with a cursor, and wala! you are done!!

------------------------------------------------------------------------------

DECLARE @CommandString SYSNAME;
DECLARE @HeaderRecord SYSNAME;
DECLARE @FileName SYSNAME;

SET @FileName = 'c:\testfile.txt'

SET @HeaderRecord = 'Name     Surname     DateOfBirth        MemberNO';

SET @CommandString = 'echo ' + @HeaderRecord + ' > ' + @FileName;
exec master..xp_cmdshell @CommandString, NO_OUTPUT

SET @CommandString = 'echo Susan  Dimitri     12/12/1975     1112232 >> ' + @FileName;
exec master..xp_cmdshell @CommandString, NO_OUTPUT

is this as a stored procedure?

I have a question:  what I really want to accomplish is in SQL Server 2005 workgroup edition is to have a job agent run a T-SQL query every night and send the results to a fixed width file.  the query i have shown above is just one of three that I need to run.  I am not a very good VB programmer, but I am running out of options to accomplish this for my client.  What I don't know how to do is to get a T-SQL to write to the fixed width file.

I assumed you are using SQL Server....

yes, sql server 2005 workgroup edition that comes as part of MS SBS

No, that is not a stored procedure. Just some statements that execute xp_cmdshel. You can write a stored procedure though. Let me see if I can write something that uses the northwind database as an example

Here is a stored procedure that saves all the customers in the northwind database to disk. Create this stored procedure in the Northwind database and execute it using the following statement. I have tested this stored procedure, and it works fine.

EXEC spNWCustomers 'c:\testfile.txt'

this will dump all the northwind customers in the specified file. I am sure you can take it from here......

--------------------------------------------------

CREATE PROCEDURE spNWCustomers
@File VARCHAR(255)
AS

DECLARE @CommandString SYSNAME;
DECLARE @HeaderRecord  SYSNAME;
DECLARE @RecordData    SYSNAME;
DECLARE @FileName      SYSNAME;

SET @FileName = CAST(@File as SYSNAME);

SET @HeaderRecord = 'CustID              Company Name                  ContactName';
SET @CommandString = 'echo ' + @HeaderRecord + ' > ' + @FileName;
exec master..xp_cmdshell @CommandString, NO_OUTPUT

DECLARE CustomerList CURSOR FOR
SELECT CUSTOMERID + '      ' + CompanyName + '      ' + ContactName
FROM [Customers]

OPEN CustomerList
FETCH NEXT FROM CustomerList INTO @RecordData

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @CommandString = 'echo ' + @RecordData + ' >> ' + @FileName;
    EXEC master..xp_cmdshell @CommandString, NO_OUTPUT

    FETCH NEXT FROM CustomerList INTO @RecordData
END

CLOSE CustomerList
DEALLOCATE CustomerList

GO

I appreciate what you wrote, but I don't understand. how to make my query (below) fit your procedure.
SELECT RM00103.CUSTNMBR, CUSTNAME, RATETPID, CAST(CRLMTAMT AS NUMERIC(19,2))AS CRLMTAMT,
cast(CUSTBLNC as numeric(19,2))as CUSTBLNC, cast(AGPERAMT_1 as numeric(19,2)) as AGPERAMT_1,
cast(TTLSLYTD as numeric(19,2)) as TTLSLYTD, cast(TTLSLLYR as numeric(19,2)) as TTLSLLYR,
convert(varchar(10),LSTTRXDT,101)AS LSTTRXDT, CONVERT(VARCHAR(10), LASTPYDT,101)AS LASTPYDT
FROM RM00101 inner Join RM00103 on rm00101.custnmbr = rm00103.custnmbr
WHERE Lsttrxdt >= dateadd(day, -1, convert(datetime, convert(varchar(10), getdate(), 120), 120))

the results need to be output to a file called customerquery.csv.

If SQL Server reporting services are available, you might be able to create a report with the format you require.

I had an interesting thought about short-cutting the solution.  If you run a query in the Query Analyzer, the results appear in the same format you want.  I'm wondering if we might find a solution that runs the query and then saves the query output results in a text file.

a CSV file is NOT fixed length format

sorry, txt

I figured out where to put my query, but where do I tell the procedure to output the results to a flat file?

GoodAngel;  I am starting to get this (this should terrify the free world).  in the procedure at this point:
SET @FileName = CAST(@File as SYSNAME);  I think I change  the @File to read @customerquery
in the SET @HeaderRecord = 'CustID              Company Name                  ContactName'
I think I put my column headers here.  If my query changes the name to something legible (e.g. CustID as Customer_ID) this won't effect the "set @headerrecord" ?

here is my take on the stored procedure.  i am getting this error: "Server: Msg 16924, Level 16, State 1, Line 23 Cursorfetch: The number of variables declared in the INTO list must match that of selected columns."

here is the procedure as I modified it from goodangel:

DECLARE @customerquery SYSNAME;
DECLARE @CommandString SYSNAME;
DECLARE @HeaderRecord  SYSNAME;
DECLARE @RecordData    SYSNAME;
DECLARE @FileName      SYSNAME;

SET @FileName = CAST(@customerquery as SYSNAME);

SET @HeaderRecord = 'CUSTNMBR CUSTNAME RATETPID CRLMTAMT CUSTBLNC AGPERAMT_1 TTLSLYTD TTLSLLYR LSTTRXDT LASTPYDT';
SET @CommandString = 'echo ' + @HeaderRecord + ' > ' + @FileName;
exec master..xp_cmdshell @CommandString, NO_OUTPUT

DECLARE CustomerList CURSOR FOR
SELECT RM00103.CUSTNMBR AS CUSTOMER_NUMBER, CUSTNAME AS CUSTOMER_NAME, RATETPID AS CREDIT_RATING, CAST(CRLMTAMT AS NUMERIC(19,2))AS CREDIT_LIMIT,
cast(CUSTBLNC as numeric(19,2))as CUSTOMER_BALANCE, cast(AGPERAMT_1 as numeric(19,2)) as CURRENT_OPEN_ORDERS,
cast(TTLSLYTD as numeric(19,2)) as TOTAL_SALES_YEAR_TO_DATE, cast(TTLSLLYR as numeric(19,2)) as TOTAL_SALES_LAST_YEAR,
convert(varchar(10),LSTTRXDT,101)AS DATE_LAST_SALE, CONVERT(VARCHAR(10), LASTPYDT,101)AS DATE_LAST_PAYMENT
FROM RM00101 inner Join RM00103 on rm00101.custnmbr = rm00103.custnmbr
WHERE Lsttrxdt >= dateadd(day, -1, convert(datetime, convert(varchar(10), getdate(), 120), 120))

OPEN CustomerList
FETCH NEXT FROM CustomerList INTO @RecordData

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @CommandString = 'echo ' + @RecordData + ' >> ' + @FileName;
    EXEC master..xp_cmdshell @CommandString, NO_OUTPUT

    FETCH NEXT FROM CustomerList INTO @RecordData
END

CLOSE CustomerList
DEALLOCATE CustomerList

GO

can someone tell me:

1) I don't see the error that query analyzer is can someone point it out to me
2) where do I tell it the name of the file
3) how do I tell it where to save the file

Oh. I see where your error is.

Firstly, the name of the file is a parameter in the stored procedure
-----

CREATE PROCEDURE spNWCustomers
CAST(@File VARCHAR(255)
AS...

@File is the parameter that will contain the file name, and that is the name that will be used to save the file.


In your cursor decleration, you want to concatenate all your columns into one column which will be assigned to header record, so what you want to do is this

------------------------------------

DECLARE CustomerList CURSOR FOR
SELECT
CAST(RM00103.CUSTNMBR AS CHAR(10)) + CAST(CUSTNAME AS CHAR(20)+
CAST(RATETPID AS CHAR(10) +
CAST(CRLMTAMT AS CHAR(21) +
cast(CUSTBLNC as CHAR(21) + cast(AGPERAMT_1 as CHAR(21)) +
cast(TTLSLYTD as CHAR(21)) +
cast(TTLSLLYR as CHAR(21))
convert(char(10),LSTTRXDT,101) + CONVERT(CHAR(10), LASTPYDT,101)
FROM RM00101 inner Join RM00103 on rm00101.custnmbr = rm00103.custnmbr
WHERE Lsttrxdt >= dateadd(day, -1, convert(datetime, convert(varchar(10), getdate(), 120), 120))

------------------------

If you make that modification it should work

when I tried your solution above I got multiple errors.  below is the modified stored procedure.  it gets an error "Error converting data type varchar to numeric."  the procedure is below, I do not see where the "varchar to numeric" issue lies.  only those items that are numeric (per the sql table structure) shouldn't be varchar. In case you haven't already figured this out, I really am quite stupid when it comes to stored procedures. so can someone please fix this so it will work!!

DECLARE @customerquery SYSNAME;
DECLARE @CommandString SYSNAME;
DECLARE @HeaderRecord  SYSNAME;
DECLARE @RecordData    SYSNAME;
DECLARE @FileName      SYSNAME;
DECLARE @FILE          SYSNAME;

SET @FILE = 'C:\CUSTOMERQUERY.TXT'
SET @FileName = CAST(@file as SYSNAME);

SET @HeaderRecord = 'CUSTNMBR CUSTNAME RATETPID CRLMTAMT CUSTBLNC AGPERAMT_1 TTLSLYTD TTLSLLYR LSTTRXDT LASTPYDT';
SET @CommandString = 'echo ' + @HeaderRecord + ' > ' + @FileName;
exec master..xp_cmdshell @CommandString, NO_OUTPUT

DECLARE CustomerList CURSOR FOR
SELECT
CAST(RM00103.CUSTNMBR AS CHAR(10))+ CAST(CUSTNAME AS CHAR(26))+ CAST(RATETPID AS CHAR(10))+
CAST(CRLMTAMT AS NUMERIC(19,2))+ cast(CUSTBLNC as numeric(19,2))+ cast(AGPERAMT_1 as numeric(19,2))+
cast(TTLSLYTD as numeric(19,2))+ cast(TTLSLLYR as numeric(19,2))+
convert(varchar(10),LSTTRXDT,101)+ CONVERT(varCHAR(10), LASTPYDT,101)

FROM RM00101 inner Join RM00103 on rm00101.custnmbr = rm00103.custnmbr
WHERE Lsttrxdt >= dateadd(day, -1, convert(datetime, convert(varchar(10), getdate(), 120), 120))

OPEN CustomerList
FETCH NEXT FROM CustomerList INTO @RecordData

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @CommandString = 'echo ' + @RecordData + ' >> ' + @FileName;
    EXEC master..xp_cmdshell @CommandString, NO_OUTPUT

    FETCH NEXT FROM CustomerList INTO @RecordData
END

CLOSE CustomerList
DEALLOCATE CustomerList

GO