Post reply

BCPing out a Table without xp_cmdshell

  • February 9, 2011 at 6:11 pm

    #388825

    declare @cmdstr1 varchar(500)

    select @cmdstr1='BCP "select * from AdventureWorks.dbo.Employee" queryout \\'+upper(Substring(@@SERVERNAME,1,1))+lower(Substring(@@SERVERNAME,2,len(@@SERVERNAME)))+'\Information\HR\AdventureWorks.dbo.Employee.csv -c -t"," -S'+@@servername + ' -T'

    exec master.dbo.xp_cmdshell @cmdstr1

    Hii, Currently iam using the above query, but i was advised not use xp_cmdshell because of some security concerns. I still wanted to run it on SSMS, but without xp_cmdshell , can anyone please help me.

    Thanks in advance

    Prasad 🙂

  • February 9, 2011 at 7:23 pm

    #1284920

    Can you not run the query in SSMS

    select * from AdventureWorks.dbo.Employee

    and select "results to file" and specify the location?

  • February 10, 2011 at 2:47 pm

    #1285487

    I think Prasad might be looking for a different way to write a file to disk from a T-SQL programming context without using bcp via xp_cmdshell. This is a commonly sought after function that I think is missing from SQL Server. We have BULK INSERT and OPENROWSET but no in-process EXPORT functionality.

    Using bcp via xp_cmdshell creates the illusion of in-process EXPORT functionality but it forces us to increase the surface area on our instances...not ideal.

    It was not implemented as safely as it could have been but I have seen the "bcp via xp_cmdshell" technique successfully implemented in a large enterprise ETL system.

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

  • February 10, 2011 at 3:00 pm

    #1285496

    Vote for this connect item if you agree that we should have an in-process EXPORT command:

    https://connect.microsoft.com/SQLServer/feedback/details/224026

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

  • February 13, 2011 at 9:57 am

    #1286163

    opc.three (2/10/2011)

    Vote for this connect item if you agree that we should have an in-process EXPORT command:

    https://connect.microsoft.com/SQLServer/feedback/details/224026

    I voted for it. What's amazing is the item was marked as being considered for inclusion way back in 2007 and we've seen nothing of it.

    I also recommended that they retroactively backfit from 2005 and up because it would be a huge help for ETL folks. SSIS just doesn't do it for me.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 13, 2011 at 9:59 am

    #1286164

    prasadau2006 (2/9/2011)

    declare @cmdstr1 varchar(500)

    select @cmdstr1='BCP "select * from AdventureWorks.dbo.Employee" queryout \\'+upper(Substring(@@SERVERNAME,1,1))+lower(Substring(@@SERVERNAME,2,len(@@SERVERNAME)))+'\Information\HR\AdventureWorks.dbo.Employee.csv -c -t"," -S'+@@servername + ' -T'

    exec master.dbo.xp_cmdshell @cmdstr1

    Hii, Currently iam using the above query, but i was advised not use xp_cmdshell because of some security concerns. I still wanted to run it on SSMS, but without xp_cmdshell , can anyone please help me.

    Thanks in advance

    Prasad 🙂

    If you're allowed to use some OLEDB Automation, consider what the following code does and warp it to your needs to execute BCP.

    DECLARE @ShellRefNum INT

    EXEC sp_oaCreate 'wScript.Shell', @ShellRefNum OUT

    EXEC sp_oaMethod @ShellRefNum, 'Run', NULL, 'CMD /c "DIR C:\ /s /a-d /b" > c:\Temp\WScriptTest.txt'

    EXEC sp_oaDestroy @ShellRefNum

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 14, 2011 at 10:51 am

    #1286609

    Jeff Moden (2/13/2011)

    prasadau2006 (2/9/2011)

    declare @cmdstr1 varchar(500)

    select @cmdstr1='BCP "select * from AdventureWorks.dbo.Employee" queryout \\'+upper(Substring(@@SERVERNAME,1,1))+lower(Substring(@@SERVERNAME,2,len(@@SERVERNAME)))+'\Information\HR\AdventureWorks.dbo.Employee.csv -c -t"," -S'+@@servername + ' -T'

    exec master.dbo.xp_cmdshell @cmdstr1

    Hii, Currently iam using the above query, but i was advised not use xp_cmdshell because of some security concerns. I still wanted to run it on SSMS, but without xp_cmdshell , can anyone please help me.

    Thanks in advance

    Prasad 🙂

    If you're allowed to use some OLEDB Automation, consider what the following code does and warp it to your needs to execute BCP.

    DECLARE @ShellRefNum INT

    EXEC sp_oaCreate 'wScript.Shell', @ShellRefNum OUT

    EXEC sp_oaMethod @ShellRefNum, 'Run', NULL, 'CMD /c "DIR C:\ /s /a-d /b" > c:\Temp\WScriptTest.txt'

    EXEC sp_oaDestroy @ShellRefNum

    Thanks a lot for the reply......and i completely agree with your comments. Iam not sure if my manager agrees for the this solution but i will a give a try. Thanks again

  • February 14, 2011 at 11:21 am

    #1286629

    You bet. Thanks for the feedback, Prasad.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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