Running bcp from powershell

Hi,

I have the below job step, which I need to convert into powershell, because the security policies do not allow xp_cmdshell to be used.

How can I change the below TSQL into powershell?

Thanks

 

DECLARE  

 @saveas VARCHAR(2048)

,@query VARCHAR(2048)

,@bcpquery VARCHAR(2048)

,@bcpconn VARCHAR(64)

,@bcpdelim VARCHAR(2)

,@tablename sysname

,@s NVARCHAR(500)

SET @bcpdelim   = '|'

SET @bcpconn    = '-T' -- Trusted

select @tablename = MIN(name) from MyDb_Archive..sysobjects (nolock) where name like 'MyTableStaging_______'

while @tablename is not null 

begin

   SET @query      = 'select * from MyDb_Archive.dbo.' + @tablename

   SELECT @saveas = [value] FROM [SupportDB].[dbo].[PartitioningConfiguration] (nolock) WHERE name = 'ArchivePath'

   SET @saveas     = @saveas + REPLACE (@@Servername, '\', '_') + '_MyDb_Archive_' + @tablename + '.txt'

   SET @bcpquery = 'bcp "' + replace(@query, char(10), '') + '" QUERYOUT "' + @saveas + '" -n -t^' + @bcpdelim + ' ' + @bcpconn + ' -S ' + @@servername

   EXEC master..xp_cmdshell @bcpquery  

   select @tablename = MIN(name) from MyDb_Archive..sysobjects (nolock) where name like 'MyTableStaging_______' and name > @tablename

end 

  • Hey Roust,

    Good news is you don't have to.  Instead, you can use .NET functionality from PowerShell, which allows you to pass your TSQL query in as a variable.  I've not run your specific query, but this is the format I use whenever I'm wanting to run a query against SQL server :

    (Set the variables you see below accordingly, such as the name of the SQL server, and you should be okay.)

     

    $query = @"
    #I'm not going to bother pasting in your query here
    #but do that.
    "@
               
               
    $SqlConnection = New-Object  -TypeName System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=$SQLServer;Database=`'$Database`';Integrated Security=SSPI;"
    $SqlCmd = New-Object  -TypeName System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $query
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object  -TypeName System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object  -TypeName System.Data.DataSet
    $nSet = $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    $Tables = $DataSet.Tables
    $Tables

  • I don't think you understand, I can't just pass the above query to powershell, as it contains xp_cmdshell, used to run bcp command.

    This is what I managed to create:

    SL SQLSERVER:\SQL\"myserver\myinstance"\Databases\"MyDB_Archive"\Tables

    $ServerName = Invoke-Sqlcmd -query "SELECT @@ServerName" 

    $timestamp = Get-Date -Format yyyy-MM

    $bcpconn = '-T'

    $TableList = Invoke-Sqlcmd -query "select name from MyDB_Archive..sysobjects (nolock) where name like 'MyTable%' and type = 'U'" 

    foreach($item in $TableList) {

      $table = $item.name

      $Query = 'select * from MyDB_Archive.dbo.' + $table

      $Saveas = Invoke-Sqlcmd -query "SELECT [value] FROM [SupportDB].[Support].[PartitioningConfiguration] (nolock) WHERE name = 'ArchivePath'" 

      $ReplaceServername = $ServerName.Column1 -replace '\\', '_'

      $Saveas.value = $Saveas.value + $ReplaceServername + '_MyDB_Archive_' + $table + '_' + $timestamp + '.txt'

      bcp $Query  QUERYOUT $Saveas -n $bcpconn -S $ServerName.Column1

    }

     

    It looks like it outputs some data, however, the files don't get created in the folder:

    Starting copy...

    1000 rows successfully bulk-copied to host-file. Total received: 1000

    1000 rows successfully bulk-copied to host-file. Total received: 2000

    1000 rows successfully bulk-copied to host-file. Total received: 3000

    1000 rows successfully bulk-copied to host-file. Total received: 4000

    1000 rows successfully bulk-copied to host-file. Total received: 5000

    1000 rows successfully bulk-copied to host-file. Total received: 6000

    1000 rows successfully bulk-copied to host-file. Total received: 7000

    1000 rows successfully bulk-copied to host-file. Total received: 8000

    1000 rows successfully bulk-copied to host-file. Total received: 9000

     

    9322 rows copied.

     

     

     

  • I'm talking about putting the parts of the SQL script that you can execute in that format of code. 

    Personally, I prefer using the .NET functionality instead of the SQL cmdlets, particularly when SELECT type operations are being returned, but guess it comes down to what's best for you.