send output to txt file

Question

Post reply

send output to txt file

Rehan

SSC Enthusiast

Points: 134
More actions
August 5, 2004 at 9:59 am

#109365

Hi,
I want to send the output of select statement to a txt file . I tried it with bcp utility but it throw me this error
Sqlstate s1000, nativeeoor 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
My code
declare @filename varchar(150)
declare @bcpcommand varchar(2000)
set @filename = replace ('c:\report' + convert (char(8), Getdate(),1) + '_authors' + '.txt','/','-')
set @bcpcommand = declare @filename varchar(150)
-- declare @errorfilename varchar(150)
declare @bcpcommand varchar(2000)
set @filename = replace ('c:\report' + convert (char(8), Getdate(),1) + '_authors' + '.txt','/','-')
--set @errorfilename = replace ('c:\Siebel_' + convert (char(8), Getdate(),1) + '_authors' + '.err','/','-')
set @bcpcommand = 'bcp pubs..authors out'
--set @bcpcommand = @bcpcommand + ' ' + @filename + ' ' + '-U abure -P twinkle1995 -c -S d2ls1'
set @bcpcommand = @bcpcommand + ' ' + @filename + ' ' + '-T -c -S d2ls1'
PRINT @bcpcommand
Exec master..xp_cmdshell @bcpcommand
declare @filename varchar(150)
-- declare @errorfilename varchar(150)
declare @bcpcommand varchar(2000)
set @filename = replace ('c:\report' + convert (char(8), Getdate(),1) + '_authors' + '.txt','/','-')
--set @errorfilename = replace ('c:\Siebel_' + convert (char(8), Getdate(),1) + '_authors' + '.err','/','-')
set @bcpcommand = bcp "SELECT name, b.last_run_date, b.last_run_duration FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 1"
--set @bcpcommand = @bcpcommand + ' ' + @filename + ' ' + '-U abure -P twinkle1995 -c -S d2ls1'
set @bcpcommand = @bcpcommand + ' ' + @filename + ' ' + '-T -c -S d2ls1'
PRINT @bcpcommand
Exec master..xp_cmdshell @bcpcommand
Please help me to bug free this code or if you have any other solution to get the issue resolved

Thnaks in advanced

Viewing 7 posts - 1 through 6 (of 6 total)

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

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 1

I think first get the BCP program going from a DOS prompt and then try to code it in TSQL

Jean-Pierre PONS SSC-Addicted Points: 497 More actions · Answer 2

I can imagine a solution based on the xm_cmdshell system proc like this :

declare @FetchCustomerID varchar(5),

@FetchCompanyName varchar(40),

@WkCommand varchar(100)

declare cursor_test cursor for

select CustomerID, CompanyName

from Customers

order by 2

EXEC master..xp_cmdshell ' @echo CusID CompanyName > c:\temp.txt', NO_OUTPUT

EXEC master..xp_cmdshell ' @echo ----- ----------------------------------------------- >> c:\temp.txt', NO_OUTPUT

Open cursor_test

Fetch NEXT FROM cursor_test into @FetchCustomerID, @FetchCompanyName

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

Begin

Set @WkCommand = ' echo ' + @FetchCustomerID + ' ' + @FetchCompanyName + ' >> c:\temp.txt'

EXEC master..xp_cmdshell @WkCommand, NO_OUTPUT

Fetch NEXT FROM cursor_test into @FetchCustomerID, @FetchCompanyName

End

END

Close cursor_test

DEALLOCATE cursor_test

... but it's a bit heavy...

Rehan SSC Enthusiast Points: 134 More actions · Answer 3

Hi try this. it gives command process sucessfully but its not creating any temp.txt at c: is ther any things else I have to do to work on your script.

Thanks for your time. In oracle I know its simple "set spool on" give the drive and filename with spool command , run the select statement and its done. Why its not simple in sql server I don't know.

Thanks

Linda Johanning SSCertifiable Points: 6727 More actions · Answer 4

I agree to start simple. Try running this in QA

exec master..xp_cmdshell 'bcp pubs..authors out c:\authors.txt /c /SYourServer /T'

I've seen that error before when there was a problem creating the file... either because the path was incorrect (or didn't exist) or permissons were restricted on a folder, etc.

Linda

Rehan SSC Enthusiast Points: 134 More actions · Answer 5

Hi Linda,

It runs without error. giving message 13 rows effected but it doesn't create the author.txt file in the c: where I am doing wrong. help me

thanks

Farhad-138839 SSC Enthusiast Points: 115 More actions · Answer 6

It depends on the server that your QA is connected to. If the QA that you are using is conected to a remote server, then you can find the text file under that server's C:\.

If you want to have the text file under your local machine, connect the QA to your local SQL Server and use Linda's command with your server's IP:

exec master..xp_cmdshell 'bcp pubs..authors out c:\authors.txt /c /SYourServer /T'

Canada DBA,