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?
SET @bcpdelim = '|'
SET @bcpconn = '-T' -- Trusted
select @tablename = MIN(name) from MyDb_Archive..sysobjects (nolock) where name like 'MyTableStaging_______'
while @tablename is not null
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
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.