Export

[OuTCasT]

Can anyone please tell me how to export data from a sql database to an ascii text file ?

[code green]

Via what medium.
Is this a one-off or a regular task.

[OuTCasT]

Via what medium.
Is this a one-off or a regular task.

Well i am building a payroll program and need to export data from the db to an ascii file to get sent to the bank. This will be done every month.
can this be done. ?

[ck9663]

For a simple technique, use this .

You have to run the bcp through xp_cmdshell, so watch out for proper access rights. You might also want to consider making this command dynamic to have a more dynamic file name of your text file.

And watch for existing filename, non-existing destination folder, etc...

-- CK

[OuTCasT]

For a simple technique, use this .

You have to run the bcp through xp_cmdshell, so watch out for proper access rights. You might also want to consider making this command dynamic to have a more dynamic file name of your text file.

And watch for existing filename, non-existing destination folder, etc...

-- CK

Thankyou very much, i will take a look at this. :D

[OuTCasT]

Thankyou very much, i will take a look at this. :D

when i use the bcp it says

Error=[MICROSOFT][SQL Native Client]Unable to open BCP host data file

this is the code

[CODE=sql]declare @filename varchar(50),
@bcpCommand varchar(2000)

SET @filename = REPLACE('c:\pro ducts_'+convert (char(8),GETDAT E(),1)+'.txt','/','-')

SET @bcpCommand = 'bcp "SELECT * FROM PUBS..Authors" queryout "'
SET @bcpCommand = @bcpCommand = @filename + ' " -T -c -S [servername]'

exec master..xp_cmds hell @bcpCommand[/CODE]

[OuTCasT]

Thankyou very much, i will take a look at this. :D

I figured out the the c:\ was not given permission to write the .txt file even though im administrator
created a folder and gave it permissions and its working fine now.