January 4, 2018 at 1:11 pm
I need to copy a subset of rows from a table on a (sql2016) server, to a table on a (sql2008r2) server.
Old approach was SSIS; I wanted to see if I could do it in PS more simply.
I found this article & tried the code submitted by Gungnir (included inline below)
https://community.spiceworks.com/topic/2076861-simpler-way-to-copy-sql-data-between-servers
It works from the PS ISE (on a Windows 10 desktop).
But when I paste it into a SQL JOB step on the SQL2016 box, it fails with a syntax error on this line:
"'$($Row.$Prop)'"
I'd prefer a solution that works for both the ISE & in a SQL JOB step, but if that cannot be done, so be it!
If you suggest troubleshooting steps, assume I am fairly PS-ignorant.
# Here is the code:
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process
Import-Module SQLPS
$SourceServer = 'svrname1'
$SourceDatabase = 'dbname1'
$SourceTable = 'tblname1'
$DestServer = 'svrname2'
$DestDatabase = 'dbname2'
$DestTable = 'tblname2'
$SelectQuery = "SELECT * FROM $SourceTable WHERE (some restriction..not important to discussion)"
$SelectRows = Invoke-Sqlcmd -ServerInstance $SourceServer -Database $SourceDatabase -Query $SelectQuery
$Properties = $SelectRows |
Get-Member -MemberType Property |
Select-Object -ExpandProperty Name
$InsertValues = foreach( $Row in $SelectRows ){
$Values = foreach( $Prop in $Properties ){
"'$($Row.$Prop)'" # Works in PS ISE; But fails with syntax error in SQL JOB STEP *************************************************
}
$Values -join ','
}
$Insert = $InsertValues -join '), ('
$InsertQuery = "INSERT INTO $DestTable ( $( $Properties -join ', ' ) ) VALUES ( $Insert )"
Invoke-Sqlcmd -ServerInstance $DestServer -Database $DestDatabase -Query $InsertQuery
January 4, 2018 at 4:33 pm
Resolved; replaced problem row with this: "'" + $Row.$Prop + "'"
Also needed to modify these rows:$Insert = $InsertValues -join '), ('
$InsertQuery = "INSERT INTO $DestTable ( $( $Properties -join ', ' ) ) VALUES ( $Insert )"
Invoke-Sqlcmd -ServerInstance $DestServer -Database $DestDatabase -Query $InsertQuery
To this:$Insert = $InsertValues -join '), ('
$Cols = $Properties -join ', '
$InsertQuery = "INSERT INTO $DestTable ( $Cols ) VALUES ( $Insert )"
Invoke-Sqlcmd -ServerInstance $DestServer -Database $DestDatabase -Query $InsertQuery
This code runs correctly in the ISE & in the SQL JOB.
January 4, 2018 at 4:59 pm
mstjean - Thursday, January 4, 2018 4:33 PMResolved; replaced problem row with this: "'" + $Row.$Prop + "'"Also needed to modify these rows:
$Insert = $InsertValues -join '), ('
$InsertQuery = "INSERT INTO $DestTable ( $( $Properties -join ', ' ) ) VALUES ( $Insert )"
Invoke-Sqlcmd -ServerInstance $DestServer -Database $DestDatabase -Query $InsertQuery
To this:$Insert = $InsertValues -join '), ('
$Cols = $Properties -join ', '
$InsertQuery = "INSERT INTO $DestTable ( $Cols ) VALUES ( $Insert )"
Invoke-Sqlcmd -ServerInstance $DestServer -Database $DestDatabase -Query $InsertQuery
This code runs correctly in the ISE & in the SQL JOB.
Thank you very much for posting back - it really helps everyone out, especially those who come across your post when searching on similar issues.
Sue
March 10, 2018 at 10:30 pm
The reason this fails in a PowerShell step of SQL Agent is due to the tokens with Agent jobs. A token, when used in Agent job, is wrapped using "$()", so anything within that is picked up by SQL Agent as a token and it will try to resolve it as one. These are used differently in PowerShell and just don't mix well when you try to use them in a SQL Agent PowerShell job step.
It is best to use a CmdExec step in SQL Agent as it will give you the most consistent experience to running scripts in normal PowerShell or PowerShell ISE.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 10, 2018 at 11:20 pm
If your requirements allow for the table to be the same name on your destination as it is on the source, you can utilize the dbatools module to help make maintaining (and in some cases performing) the process much easier. The command that would allow you to easily perform this task is: Copy-Dbatable.
Import-Module dbatools
$SourceServer = 'svrname1'
$SourceDatabase = 'dbname1'
$Table = 'tblname1'
$DestServer = 'srvname2'
$DestDatabase = 'dbname2'
$SelectQuery = "SELECT * FROM $SourceTable WHERE (<some restriction>)"
Copy-DbaTableData -SqlInstance $SourceServer -Destination $DestServer -DestinationDatabase $DestDatabase -Table $Table -Query $SelectQuery
Now, if it does not and you still need to keep all the same parameters, the module can still help to simply your code:
# Here is the code:
Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process
Import-Module dbatools
$SourceServer = 'svrname1'
$SourceDatabase = 'dbname1'
$SourceTable = 'tblname1'
$DestServer = 'svrname2'
$DestDatabase = 'dbname2'
$DestTable = 'tblname2'
$SelectQuery = "SELECT * FROM $SourceTable WHERE (some restriction..not important to discussion)"
$SelectData = (Connect-DbaInstance -SqlInstance $SourceServer).Databases[$SourceDatabase].Query($SelectQuery)
<# As long as your data types are supported well in BulkInsert, this should work #>
$SelectData | select-object -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors |
Write-DbaDataTable -SqlInstance $DestServer -Table $DestTable
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 12, 2021 at 9:58 am
Hi,
I am running below code, running fine in PS console and ISE. When i set in SQL agent job it's failing,
#Create folder
$f = New-Item "E:\Archive_backups\$(get-date -f yyyy-MM-dd-hh-mm-ss)" -ItemType Directory -Force
#Copy all files inside E:\MainBackups to new folder (if you need to include files from subfolders, add -Recurse parameter)
Copy-Item E:\MainBackups\* $f.FullName
Please suggest.
January 12, 2021 at 4:57 pm
Just to point out - you posted to a 2 year old thread. This really should be a new topic...
With that said, this issue was explained very well by Shawn - the problem is how SQL Server agent processes tokens which are identified by $(token). Your code has this: $(get-date -f yyyy-MM-dd-hh-mm-ss) - which agent will attempt to process as a token.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply