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
  • 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

Children
No Data