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.
Powered by IDERA