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 

Parents
No Data
Reply
  • 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.

     

     

     

Children
No Data