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'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.

Children
No Data