none
Does permission creating a file dynamically differs with xp_cmdshell and bcp in SQL Server 2000?

    Question

  • Hi All,

       I am trying to create a text file using "bcp" in a local drive for which I has admin rights. But, I couldn't know why it doesn't work for me. However, when I use a xp_cmdshell it does work. Can somebody help me with this?

    This does work for me creating a file but couldn't do an empty file with this:

    EXEC master..xp_cmdshell 'echo This is a Test Message.>> G:\Customers\Cust_info.txt'
    

    However, running this query gave me the following output without creating any file:

    DECLARE @bcpCommand VARCHAR(255)
    DECLARE @Result INT
    SET @bcpCommand = 'bcp "" queryout "' + 'G:\Customers\Cust_info.txt' + '" -c -t, -T -S '
    EXEC @Result = master..xp_cmdshell @bcpCommand
    

    Thanks
    Bangaaram


    Known is a DROP, Unknown is an OCEAN.
    • Edited by Bangaaram Thursday, January 26, 2012 3:41 PM
    Thursday, January 26, 2012 3:40 PM

Answers

  • Then pls try this -

    DECLARE @bcpCommand VARCHAR(255)
    DECLARE @Result INT
    SET @bcpCommand = 'bcp "Select '' Name" queryout "' + 'G:\Customers\Cust_info.txt' + '" -c -t, -T -S <InstanceNamehere>'
    EXEC @Result = master..xp_cmdshell @bcpCommand


    http://uk.linkedin.com/in/ramjaddu
    Thursday, January 26, 2012 5:29 PM
  • It should not put 1 in the file. It should put a space there. Did you check the file? Also, you can add where 1=0 to the query.

    Finally, what was wrong with the link I gave you before as how to create empty file? Why do you need to use bcp if all you need is to create an empty file?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, January 26, 2012 7:15 PM
  • Any progress?

    Consider using the very excellent SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
    Thursday, February 02, 2012 6:36 PM

All replies

  • In your bcp command I don't see any query at all. You need to query some table.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, January 26, 2012 3:42 PM
  • In your bcp command I don't see any query at all. You need to query some table.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Is it not possible to create any empty file using "bcp" ?
    Known is a DROP, Unknown is an OCEAN.
    Thursday, January 26, 2012 3:44 PM
  • See this  http://stackoverflow.com/questions/210201/how-to-create-empty-text-file-from-a-batch-file
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, January 26, 2012 3:50 PM
  • See this  http://stackoverflow.com/questions/210201/how-to-create-empty-text-file-from-a-batch-file
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Didn't get that.

     However, when I use the same bcp query on my SQL Server 2008 R2 it created an empty file. However, its not showing up the same in SQL Server 2000.


    Known is a DROP, Unknown is an OCEAN.
    Thursday, January 26, 2012 4:09 PM
  • You must supply the instance name If it named instance -
    DECLARE @bcpCommand VARCHAR(255)
    DECLARE @Result INT
    SET @bcpCommand = 'bcp "" queryout "' + 'G:\Customers\Cust_info.txt' + '" -c -t, -T -S <InstanceNamehere>'
    EXEC @Result = master..xp_cmdshell @bcpCommand


    http://uk.linkedin.com/in/ramjaddu
    • Edited by RamJaddu Thursday, January 26, 2012 4:15 PM
    Thursday, January 26, 2012 4:14 PM
  • You must supply the instance name If it named instance -
    DECLARE @bcpCommand VARCHAR(255)
    DECLARE @Result INT
    SET @bcpCommand = 'bcp "" queryout "' + 'G:\Customers\Cust_info.txt' + '" -c -t, -T -S <InstanceNamehere>'
    EXEC @Result = master..xp_cmdshell @bcpCommand


    http://uk.linkedin.com/in/ramjaddu

    Sorry but How to check my instance name?
    Known is a DROP, Unknown is an OCEAN.
    Thursday, January 26, 2012 4:28 PM
  • Try

    SELECT @@SERVERNAME


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, January 26, 2012 4:50 PM
  • Try

    SELECT @@SERVERNAME


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    I have an idea about this but, thought Server Name & Instance Name would be different. Between, I still get the same error even if I mention the Instance Name.

     


    Known is a DROP, Unknown is an OCEAN.
    Thursday, January 26, 2012 5:00 PM
  • Then pls try this -

    DECLARE @bcpCommand VARCHAR(255)
    DECLARE @Result INT
    SET @bcpCommand = 'bcp "Select '' Name" queryout "' + 'G:\Customers\Cust_info.txt' + '" -c -t, -T -S <InstanceNamehere>'
    EXEC @Result = master..xp_cmdshell @bcpCommand


    http://uk.linkedin.com/in/ramjaddu
    Thursday, January 26, 2012 5:29 PM
  • Then pls try this -

    DECLARE @bcpCommand VARCHAR(255)
    DECLARE @Result INT
    SET @bcpCommand = 'bcp "Select '' Name" queryout "' + 'G:\Customers\Cust_info.txt' + '" -c -t, -T -S <InstanceNamehere>'
    EXEC @Result = master..xp_cmdshell @bcpCommand


    http://uk.linkedin.com/in/ramjaddu


    I got this error when I ran the above query:


    Known is a DROP, Unknown is an OCEAN.
    Thursday, January 26, 2012 5:37 PM
  • change the query to be

    select space(1) as Name


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, January 26, 2012 5:46 PM
  • change the query to be

    select space(1) as Name


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    It created the file giving following output too. However, it has writtern 1 in the file. I could do the same using xp_cmdshell echo command. But, all I wanted to do is create an empty file.


    Known is a DROP, Unknown is an OCEAN.
    Thursday, January 26, 2012 7:12 PM
  • It should not put 1 in the file. It should put a space there. Did you check the file? Also, you can add where 1=0 to the query.

    Finally, what was wrong with the link I gave you before as how to create empty file? Why do you need to use bcp if all you need is to create an empty file?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, January 26, 2012 7:15 PM
  • It should not put 1 in the file. It should put a space there. Did you check the file? Also, you can add where 1=0 to the query.

    Finally, what was wrong with the link I gave you before as how to create empty file? Why do you need to use bcp if all you need is to create an empty file?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Sorry I ran the wrong script (Select 1 instead of Select(1)) earlier. Now it created an empty file with a space which is fine.

    Coming to the link you've mentioned, I couldy barely understand it. Thats the reason I've asked again.

     

    Thanks
    Bangaaram


    Known is a DROP, Unknown is an OCEAN.
    Thursday, January 26, 2012 7:29 PM
  • echo ' '

    should work. That link was showing some other methods, such as copy null to textfile.txt I haven't looked too close myself or try them, but I think you can use something simple, no need for bcp for your case.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, January 26, 2012 7:32 PM
  • echo ' '

    should work. That link was showing some other methods, such as copy null to textfile.txt I haven't looked too close myself or try them, but I think you can use something simple, no need for bcp for your case.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    EXEC maste..xp_cmdshell 'echo '  ' >>G:\Customers\Cust_info.txt'  gave me an error saying

    Line 1: Incorrect syntax near ' >>G:\Customer\Cust_info.txt'.

     

     However, running EXEC maste..xp_cmdshell 'echo ' ' >>G:\Customers\Cust_info.txt' created a text file with a ' in it. Its fine if it do so, but, later I copy some windows commands to this file and rename it as a batch file in order to execute it. Thats the reason I wanted it to create as an empty file as the data which is already been written cannot be overwritten when I use this command.

     


    Known is a DROP, Unknown is an OCEAN.
    Thursday, January 26, 2012 8:15 PM
  • Any progress?

    Consider using the very excellent SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
    Thursday, February 02, 2012 6:36 PM
  • Any progress?

    Consider using the very excellent SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

    No. I should do it only using T-SQL as I am using SQL Server 2000. Please help me with T-SQL for this.

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, February 29, 2012 7:19 PM