write to text file using sql

Question

write to text file using sql

Ali.M.Habib

SSC Eights!

Points: 930
More actions
July 7, 2009 at 2:57 pm

#207890

I want to write sql output of many queries to one file (that will be created by sql)
I use bcp but it so slow and have problems , do uhave any suggestion I searched alot without result any advice
best regards

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003946 More actions · Answer 1

I've never experienced BCP being slow at anything. Usually, it's the associated query that's slow. Please post both the BCP command and the query being executed by BCP.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 2

the other alternative is using SSIS packages.

Regards

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

Ali.M.Habib SSC Eights! Points: 930 More actions · Answer 3

Jeff Moden (7/7/2009)
I've never experienced BCP being slow at anything. Usually, it's the associated query that's slow. Please post both the BCP command and the query being executed by BCP.

this is the query I use

SET @bcpCommand = 'bcp " select '''+ @expFile +'''" queryout "'

SET @bcpCommand = @bcpCommand + @exptionFile + '" -T -c'

EXEC master..xp_cmdshell @bcpCommand

it also give error

Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

how can I fix that

the server is not the pc I work on it and I want to save the output on my pc

Eswin SSCrazy Eights Points: 8320 More actions · Answer 4

SET @bcpCommand = 'bcp " select '''+ @expFile +'''" queryout "'
SET @bcpCommand = @bcpCommand + @exptionFile + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand

i don't think you can use '+' along with xp_cmdshell for multiple commands try using '&' instead.

what is @expfile and @exptionfile

Tanx 😀

Jeff Moden SSC Guru Points: 1003946 More actions · Answer 5

Eswin (7/8/2009)
SET @bcpCommand = 'bcp " select '''+ @expFile +'''" queryout "'
SET @bcpCommand = @bcpCommand + @exptionFile + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand
i don't think you can use '+' along with xp_cmdshell for multiple commands try using '&' instead.
what is @expfile and @exptionfile

He's not. The "+" is being used to build up the @bcpCommand through SQL concatenation of strings and string variables and will not appear in the final command.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003946 More actions · Answer 6

ali.m.habib (7/8/2009)
Jeff Moden (7/7/2009)
I've never experienced BCP being slow at anything. Usually, it's the associated query that's slow. Please post both the BCP command and the query being executed by BCP.
this is the query I use
SET @bcpCommand = 'bcp " select '''+ @expFile +'''" queryout "'
SET @bcpCommand = @bcpCommand + @exptionFile + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand
it also give error
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
how can I fix that
the server is not the pc I work on it and I want to save the output on my pc

Can you post the values for @expFile and @exptionFile as well, please. Also, in order for this to work, you will need to create a share on it your PC that the server can "see" and then the files must be listed as UNC's instead of drive:pathname's.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 7

If you run bcp via xp_cmdshell, it will be executing on the server and can write a file to your PC only if you have set up a writeable share and use a full URL path for the filename.

You can also run scripts in Management Studio in SQLCMD mode, and use the :OUT command in the script to save the results to a local file. You can also run SQLCMD in a command window on your PC and use the "-o filename" parameter (or simple output redirection) to save the results to a local file.

Ali.M.Habib SSC Eights! Points: 930 More actions · Answer 8

Jeff Moden (7/8/2009)
ali.m.habib (7/8/2009)
Jeff Moden (7/7/2009)
I've never experienced BCP being slow at anything. Usually, it's the associated query that's slow. Please post both the BCP command and the query being executed by BCP.
this is the query I use
SET @bcpCommand = 'bcp " select '''+ @expFile +'''" queryout "'
SET @bcpCommand = @bcpCommand + @exptionFile + '" -T -c'
EXEC master..xp_cmdshell @bcpCommand
it also give error
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
how can I fix that
the server is not the pc I work on it and I want to save the output on my pc
Can you post the values for @expFile and @exptionFile as well, please. Also, in order for this to work, you will need to create a share on it your PC that the server can "see" and then the files must be listed as UNC's instead of drive:pathname's.

the @expFile variable carry string depend on select statement