Export to a Text file in SQL Server Express

I have a table in a database has the same filed with a text file:

RBW_AR_BOT_REC

Size

Comment

Justification

Padding

record_id

1

'R'

None

None

date

6

MMDDYY

None

None

time

4

HHMM

(24 hour time)

None

None

cust_num

8

1

- 9999999

Right

Zero

odometer

6

0

- 999999

Right

Zero

receipt

8

1

- 99999999

Right

Zero

exception_customer

1

'0' = No

'1' = Yes

None

None

valid_void

1

'0' = valid

'v' = void

None

None

Justification

Padding

None

None

So that, after export data from the table, the file file must has that struct
Tell me, how can i do?

None

None

None

None

Right

Zero

Right

Zero

Right

Zero

None

None

None

None

Justification

Padding

None

None

None

None

None

None

Right

Zero

Right

Zero

Right

Zero

None

None

None

None

I am going to move this to the sql express group at it is more a problem with using the software.

On another note, you can use the management studio for the express systems (Free Download) and have the query saved or displayed as text, or you could format up the sql script and then use the sqlcmd command line tool to execute that command and save the output as text. But with the information you supplied we are going to need more information, for exable an definition of the table and if you could format the output sample better in the forums message so it is easily read.

|||

hi,

there's no direct way to "export" to file in a defined format like old style random access files...

you can query a table object to return what you are looking for, but the export features are usually performed a client level (thus via an external application) and not at the server level..

you can perhaps use SqlCMD.exe (or oSql.exe), the command line (not graphics tools) provided with SQLExpress, providing the "-oc:\myfile.txt" parameter (-o indicates to output the result to the specified file) (more info and synopsis about SQLCmd at http://msdn2.microsoft.com/en-us/library/ms162773.aspx)... and play with some Transact-SQL function to obtain something similar to the required result..

say you are querying the AdventureWorks database for Person.Address table and you want to return the

[AddressID] (int) formatted as right padded 6 chars and the [ModifiedDate] (datetime) formatted with the yyyy-mm-dd (without time dimension) 8 chars values...

the typical query could be

SET NOCOUNT ON;

USE AdventureWorks;

GO

SELECT RIGHT(REPLICATE(' ' , 6) + CONVERT(varchar, AddressID), 6 ) AS AddressID,

CONVERT(varchar(10), ModifiedDate, 121) AS ModifiedDate

FROM Person.Address

ORDER BY ModifiedDate;

--<--

AddressID ModifiedDate
61 1996-07-24

234 1997-02-19

224 1997-12-05

11387 1997-12-29

1997-12-29

( result abridged )

and want the results to be output to c:\myRes.txt..

create a file to store your query like

<c:\myQuery>
SELECT RIGHT(REPLICATE(' ' , 6) + CONVERT(varchar, AddressID), 6 ) AS AddressID,
CONVERT(varchar(10), ModifiedDate, 121) AS ModifiedDate
FROM Person.Address
ORDER BY ModifiedDate;
</c:\myQuery>

and execute, from the command line, the SQLCmd utility like

c:\..\>sqlcmd -E -S(Local)\SQLExpress -dAdventureWorks -iC:\myQuery -oC:\myRes.txt

this will output the result as

AddressID ModifiedDate
61 1996-07-24

234 1997-02-19

224 1997-12-05

11387 1997-12-29

1 1998-01-04

( result abridged )

you can play with the other SQLCmd parameters as wel, like -h for repeted headers, -s for col separator and the like...

regards