By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

Export

OuTCasT
100+
P: 374
Can anyone please tell me how to export data from a sql database to an ascii text file ?
May 14 '08 #1
Share this Question
Share on Google+
6 Replies


code green
Expert 100+
P: 1,726
Via what medium.
Is this a one-off or a regular task.
May 14 '08 #2

OuTCasT
100+
P: 374
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. ?
May 14 '08 #3

ck9663
Expert 2.5K+
P: 2,878
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
May 14 '08 #4

OuTCasT
100+
P: 374
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
May 15 '08 #5

OuTCasT
100+
P: 374
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

Expand|Select|Wrap|Line Numbers
  1. declare @filename varchar(50),
  2.             @bcpCommand varchar(2000)
  3.  
  4. SET @filename = REPLACE('c:\products_'+convert(char(8),GETDATE(),1)+'.txt','/','-')
  5.  
  6. SET @bcpCommand = 'bcp "SELECT * FROM PUBS..Authors" queryout "'
  7. SET @bcpCommand = @bcpCommand = @filename + ' " -T -c -S [servername]'
  8.  
  9. exec master..xp_cmdshell @bcpCommand
May 15 '08 #6

OuTCasT
100+
P: 374
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.
May 15 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.