Skip to main content
How Can We Help?

Search for answers or browse our knowledge base.

Import Export Single Wells – Employees Only

Created On
byAndrew Binning
You are here:
< Back

There are 2 scripts for this. Export using powershell with ExportWell.ps1 and import in SSMS using ImportWell.sql

ftp://ftp.gotheta.com/Builds/XSPOC/Releases/xspoc-3-1-0/Database/

 

 Example Use:
.\ExportWell.ps1 -Server ‘doverals01.xspoc.com’ -Database ‘xspoc’ -username ‘SpocWrite’ -password ‘someSecretPassword’ -StartDate ‘20200101 00:00:00’ -EndDate ‘20210625 23:59:59’ -NodeId ‘WellName nodeID’

 

 

he files that you need are:

Instructions

  • On the Source server, create a directory (source directory) to save the exported data
  • Copy ExportWell.ps1 to the source directory
  • Create PowerShell command
    • Ex.: . ‘C:\Temp\WellTransfer\ExportWell.ps1’ -Server ‘kevin01.xspoc.com’ -Database ‘xspoc’ -username ‘KevinUN’ -password ‘KevinPW’ -StartDate ‘20180625 00:00:00’ -EndDate ‘20190625 23:59:59’ -NodeId ‘KevinNode’
    • There is a “.” at the beginning of the line above, please make sure to include that
    • In cases where the value for your variable contains $, please use a backward apostrophe ` to escape it. Ex. ‘1010$1010’ will not work, but ‘1010`$1010’ will work.
  • Run PowerShell command from (source directory) and Review
    • Exported data will be created in the current session’s working directory. Ex. If your Powershell Window starts with this: “PS C:\Temp\WellTransfer>” then your exported data will be located in C:\Temp\WellTransfer
    • Missing columns error can occur. These columns must be removed from ExportWell.ps1 and ImportWell.sql (see Info below)
  • Zip the contents of the source directory and move it to the new server (destination server)
  • Unzip the file to a directory (destination directory) on the destination server
  • Copy ImportWell.sql to the destination directory
  • Edit the ImportWell.sql
    • @NodeId, “sqldir” text
  • Run ImportWell.sql and Review
    • Missing columns error can occur. These columns must be removed from ExportWell.ps1 and ImportWell.sql (see Info below)

 

If the destination server can communicate with the source server then all the work can be done on the destination server. There won’t be a need to zip and transfer the table data.

For ImportWell.sql. In cases where the database being imported to is old, please delete all the columns with red squiggle lines from the INSERT INTO, and delete the same columns from the SELECT below it.

For ImportWell.sql. Another way to modify the @NodeId and sqldir text is to use PowerShell. Example: (Get-Content “C:\Temp\WellTransfer\ImportWell.sql”).Replace(“sqldir”,”C:\Temp\WellTransfer”).Replace(“SET @nodeId = ””,”SET @nodeId = ‘KevinNode'”) | Set-Content “C:\Temp\WellTransfer\ImportWell.sql”

For ImportWell.sql. Another way to run the script is to use PowerShell. The command must run on the destination server, the “-d” command sets the database. Example: sqlcmd -E -d xspoc -I -i “C:\Temp\WellTransfer\ImportWell.sql”

For ExportWell.ps1. To verify if data was exported, we could check tblNodeMaster. Example: $lines = Get-Content -Path “C:\Temp\WellTransfer\tblNodeMaster.dat”; if ($lines.count -eq 0) { Write-Host “`n—No data exported—`n”}