a way to export data from table to flat file

Hi there!
I know bulk insert for importing data into tables and bcp for both
importing, exporting data between tables..from flat files...ok..
But is there any way to export data using a SQL statement and a format
file (need to export in kind of csv) just like bcp ? a kind of *bulk
output*...:)
any idea'
thanks a lot
++
Vincelook at DTS...|||Vince
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
DefaultDir=D:\FolderName;','select * from Text1.txt')
"Vince .>" <vincent@.<remove> wrote in message
news:ro7751hjpvc9l0lks58pds2dda1prok8lu@.
4ax.com...
> Hi there!
> I know bulk insert for importing data into tables and bcp for both
> importing, exporting data between tables..from flat files...ok..
> But is there any way to export data using a SQL statement and a format
> file (need to export in kind of csv) just like bcp ? a kind of *bulk
> output*...:)
> any idea'
> thanks a lot
> ++
> Vince
>

a question for using bcp in stored procedure..

background: sql2k, nt5
is it possible to write a sp to do the following:
within the sp, create a #temp table
bcp a table from a mdb (Access) file to that #temp table directly (assuming
table structure would be the same).
or one has bring the Access table into sql server as a static table first?
would appreicate some sample (or psudo code). thank youYou could create a atored procedure that executes a DTS package via DTSRUN. The DTS package would be setup with the Access DB and specific Access table as the dats source and the SQL table as the destination

A problem with bcp tool

I need to transfer some data from a file .txt to a SQL Server.
I use bcp tool. The problem is about a column named 'FILE', that I've
declared as:
[FILE] VARCHAR(150) NOT NULL

The command I launch is the following:
bcp.exe EC_CUNEO01.PROJECT.ANTRACCIATO in data_file.txt -f
format_file.fmt -e error_file.err -m 10 -t\t -r\n -k -b1000 -U user -P
password -S server

In the format_file I've declared the format of the columns, referring to the
'FILE' one as:
3 SQLCHAR 0 150 "\t" 3 [FILE]

But when I try to import the data, the system returns the error:
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
the keyword 'FILE'.

Does anybody know why?

Thank you."Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message
news:ckok38$1hs$1@.atlantis.cu.mi.it...
>I need to transfer some data from a file .txt to a SQL Server.
> I use bcp tool. The problem is about a column named 'FILE', that I've
> declared as:
> [FILE] VARCHAR(150) NOT NULL
> The command I launch is the following:
> bcp.exe EC_CUNEO01.PROJECT.ANTRACCIATO in data_file.txt -f
> format_file.fmt -e error_file.err -m 10 -t\t -r\n -k -b1000 -U user -P
> password -S server
> In the format_file I've declared the format of the columns, referring to
> the
> 'FILE' one as:
> 3 SQLCHAR 0 150 "\t" 3 [FILE]
> But when I try to import the data, the system returns the error:
> SQLState = 37000, NativeError = 156
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
> near
> the keyword 'FILE'.
> Does anybody know why?
> Thank you.

I would guess it's because FILE is a reserved keyword in MSSQL. According to
BOL, the correct column name is not required in a format file anyway, so you
could try replacing it with a non-reserved word to see what happens.

Alternatively, try using DTS, which is often a bit 'smarter' about these
things, but you should probably change your column name to a non-reserved
word anyway. I appreciate that you might have no control over that, however.

Simon|||> I would guess it's because FILE is a reserved keyword in MSSQL. According
to
> BOL, the correct column name is not required in a format file anyway, so
you
> could try replacing it with a non-reserved word to see what happens.
> Alternatively, try using DTS, which is often a bit 'smarter' about these
> things, but you should probably change your column name to a non-reserved
> word anyway. I appreciate that you might have no control over that,
however.
> Simon
I would maintain the name of the column because my application already runs
on a DB2 preexisting Database, which contains columns with this name; I
would make less change as possible...

Thank you, I will try DTS!
Bye
Fede