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