none
Executing from SSMS or from CMD? which is better for the follwoing?

    Question

  • which APPROACH of the following two is better?

    why?

    set @sql = 'bcp "select * from DATABASE.dbo.TABLE " queryout C:\t1.csv -c -T -S' + convert(varchar,@@servername);

    exec @a = master..xp_cmdshell @sql

    OR

    SET @Query ='sqlcmd  -S "'+@@SERVERNAME+'" -d "'+DB_NAME()+'" -Q "select * from DATABASE.dbo.TABLE " -s "," -o " C:\t1.csv "’

    THANKS IN ADVANCE

    EBRO


    ebro

    Tuesday, July 30, 2013 2:06 PM

Answers

  • Hi ebrolove,

    BCP is a utility to bulk copy data and SQLCMD is a utility to run any kind of SQL script. If we want to export data from SQL Server to a CSV file, we can use both of them. With both of your codes, we need to run them with master..xp_cmdshell. Since xp_cmdshell can run Windows command shell on SQL Server side, for example:

    EXEC xp_cmdshell 'dir *.exe';
    GO

    This may cause some of security issue, for example, some SQL Server users delete some system files, etc. I suggest running either of the following commands via Command Prompt to export data from SQL Server to a CSV file directly.

    sqlcmd  -S "xxxxxxxxx" -d "DBName" -Q "select * from DATABASE.dbo.TABLE" -s "," -o " C:\t1.csv "
    bcp "select * from DATABASE.dbo.TABLE " queryout C:\t1.csv -c -T –S xxxxxxxxx

    If you need to pass the server name and database name to the commands, we can use either of your codes, please only give xp_cmdshell execution permission to the users who need to run these codes.


    Allen Li
    TechNet Community Support

    Thursday, August 01, 2013 3:49 AM

All replies

  • both processes will give you a different outcome. bcp is best suited to export base data, but please don't use xp_cmdshell!!!

    Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Tuesday, July 30, 2013 2:11 PM
  • Hey body,

    I am not getting you. Can you elaborate on that?

    why not xp_cmdshell? and

    what do you mean by base data?

    Regards,

    ebro


    ebro

    Tuesday, July 30, 2013 2:29 PM
  • Hi ebrolove,

    BCP is a utility to bulk copy data and SQLCMD is a utility to run any kind of SQL script. If we want to export data from SQL Server to a CSV file, we can use both of them. With both of your codes, we need to run them with master..xp_cmdshell. Since xp_cmdshell can run Windows command shell on SQL Server side, for example:

    EXEC xp_cmdshell 'dir *.exe';
    GO

    This may cause some of security issue, for example, some SQL Server users delete some system files, etc. I suggest running either of the following commands via Command Prompt to export data from SQL Server to a CSV file directly.

    sqlcmd  -S "xxxxxxxxx" -d "DBName" -Q "select * from DATABASE.dbo.TABLE" -s "," -o " C:\t1.csv "
    bcp "select * from DATABASE.dbo.TABLE " queryout C:\t1.csv -c -T –S xxxxxxxxx

    If you need to pass the server name and database name to the commands, we can use either of your codes, please only give xp_cmdshell execution permission to the users who need to run these codes.


    Allen Li
    TechNet Community Support

    Thursday, August 01, 2013 3:49 AM