Post reply

Bcp QueryOut

  • March 2, 2011 at 6:25 am

    #235940

    Hi,

    i want to create a xml output file from the result. I can generate the xml file using the below query. But the problem is the file is created in the server. I want to create a file in local machine. Can anyone to help me to solve this problem.

    ----------------------------

    -- Sample export via bcp

    ----------------------------

    DECLARE

    @FileName VARCHAR(50),

    @SQLCmd VARCHAR(500)

    select

    @FileName = 'C:\Temp\SampleXMLOutput1.xml'

    --in this command, we are making sure there is only one ROOT node

    SELECT @SQLCmd = 'bcp '

    + '"SELECT APPLICATION_NAME FROM PARTI.dbo.applications '

    + ' FOR XML PATH(''''), ROOT(''dbappid''), TYPE "'

    + ' queryout '

    + @FileName

    + ' -w -T -S' + @@SERVERNAME

    -- display command, for visual check

    SELECT @SQLCmd AS 'Command to execute'

    -- create the XML file

    EXECUTE master..xp_cmdshell @SQLCmd

    Thanks

    Ram

  • March 2, 2011 at 7:49 am

    #1293510

    If you are running the whole script from the server, then this is the bit you need to change:-

    select

    @FileName = 'C:\Temp\SampleXMLOutput1.xml'

    It needs to refer to the local machine using a UNC name, e.g.

    select

    @FileName = '\\localmachine\c\Temp\SampleXMLOutput1.xml'

    But, beware, you will need to make sure that the SQL Service is running under an account that will be able to make a network connection to the local machine.

  • March 2, 2011 at 9:24 am

    #1293582

    Hi,

    Thanks for your reply. But dont have a network connection to the local machine.

    Is it possible to create a file in local machine using batch file?

    Thanks

    Ram

  • March 2, 2011 at 2:11 pm

    #1293741

    Sure, get out of SSMS and go to a cmd prompt on your local machine...then run the BCP command your T-SQL outputs directly:

    bcp "SELECT APPLICATION_NAME FROM PARTI.dbo.applications FOR XML PATH(''), ROOT('dbappid'), TYPE " queryout C:\Temp\SampleXMLOutput1.xml -w -T -SKSA0356460VD4\SQLEXPRESS_2008

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 3, 2011 at 12:51 am

    #1293874

    If there is no network connection between the Server and the Local machine, then how are you going to move anything from the Server to the Local machine?

  • March 3, 2011 at 3:15 am

    #1293900

    Use USB flash drive 🙂

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

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