Post reply

send output to txt file

  • 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

  • August 5, 2004 at 1:29 pm

    #517902

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

  • August 6, 2004 at 4:06 am

    #517978

    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...

  • August 6, 2004 at 7:50 am

    #518003

    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

  • August 6, 2004 at 11:39 am

    #518059

    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

  • August 9, 2004 at 6:03 am

    #518156

    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

     

     

  • August 9, 2004 at 7:05 am

    #518165

    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,

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

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