I'm trying to create a script that imports data from multiple database servers into one central data repository using the Write-DataTable function (source code here). The script I came up with works if the target table (RPRMBSDEVDB81.dba_rep..DBBakSizes) is empty, however, if there is data in the table it blows up.
What I'm doing:
- I delete everything from target table via SQL Server Mgmt Studio.
- I run the PowerShell script and it runs successfully and data ends up in the table.
- I delete data via SSMS for one server from the target table.
- I run the PowerShell script again, verifying I have unique data in my data table, and it errors out (see complete error below my script).
My script:
#Clear screen (for testing)
Clear-Host
#SQLCMD timeout parameter
$QueryTimeout = 120
#Get list of servers to import data from
$sql_serverlist = "
SELECT sl.HostName
,sl.ServerName + '.' + sl.Domain AS ServerName
,ISNULL(MAX(bs.ReportDate),'1/1/1980') AS ReportDate
FROM dbo.ServerList sl
LEFT OUTER JOIN dbo.DBBakSizes bs ON bs.ServerName = sl.HostName
WHERE sl.Import = 1
AND sl.Active = 1
GROUP BY sl.HostName
,sl.ServerName + '.' + sl.Domain;
"
$servers = Invoke-Sqlcmd -ServerInstance RPRMBSDEVDB81 -Database dba_rep -Query $sql_serverlist
#Define path to Write-DataTable module
$Location = "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\Modules\Write-DataTable"
#Load Write-DataTable module
Import-Module $Location\Write-DataTable.psm1
#Setup DataTable
$dt = New-Object Data.DataTable
$col1 = New-Object Data.DataColumn ServerName,([string])
$col2 = New-Object Data.DataColumn DatabaseName,([string])
$col3 = New-Object Data.DataColumn BackupType,([string])
$col4 = New-Object Data.DataColumn UsedCompression,([int])
$col5 = New-Object Data.DataColumn UsedChecksum,([int])
$col6 = New-Object Data.DataColumn MostRecentFull_Date,([datetime])
$col7 = New-Object Data.DataColumn MostRecentFull_Sec,([int])
$col8 = New-Object Data.DataColumn MostRecentFull_MB,([int])
$col9 = New-Object Data.DataColumn MostRecentOther,([string])
$col10 = New-Object Data.DataColumn MostRecentOther_Date,([datetime])
$col11 = New-Object Data.DataColumn MostRecentOther_Sec,([int])
$col12 = New-Object Data.DataColumn MostRecentOther_MB,([int])
$col13 = New-Object Data.DataColumn ReportDate,([datetime])
$dt.columns.add($col1)
$dt.columns.add($col2)
$dt.columns.add($col3)
$dt.columns.add($col4)
$dt.columns.add($col5)
$dt.columns.add($col6)
$dt.columns.add($col7)
$dt.columns.add($col8)
$dt.columns.add($col9)
$dt.columns.add($col10)
$dt.columns.add($col11)
$dt.columns.add($col12)
$dt.columns.add($col13)
#Loop through servers and pull in bak file data
foreach ($server in $servers)
{
#Retrieve ServerName and MAX(ReportDate) from array
$hostname = $server[0]
$servername = $server[1]
$reportdate = $server[2].ToString()
#Build SQL to retrieve records for import
$sql_bakdata = "
SELECT '$hostname' AS ServerName
,DatabaseName
,BackupType
,UsedCompression
,UsedChecksum
,MostRecentFull_Date
,MostRecentFull_Sec
,MostRecentFull_MB
,MostRecentOther
,MostRecentOther_Date
,MostRecentOther_Sec
,MostRecentOther_MB
,ReportDate
FROM rp_util.dbo.DBBakSizes
WHERE CAST(ReportDate AS SMALLDATETIME) > '$reportdate';
"
#Run SQL and capture results in array
$dt += Invoke-Sqlcmd -ServerInstance $servername -Query $sql_bakdata -QueryTimeout $QueryTimeout
}
#Load data
Write-DataTable -ServerInstance "RPRMBSDEVDB81" -Database "dba_rep" -TableName "DBBakSizes" -Data $dt
The error:
Write-DataTable : System.Management.Automation.MethodInvocationException:
Exception calling "WriteToServer" with "1" argument(s): "Object reference not
set to an instance of an object." ---> System.NullReferenceException: Object
reference not set to an instance of an object.
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataRow[] rows)
at WriteToServer(Object , Object[] )
at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[] arguments, MethodInformation methodInformation, Object[] originalArguments)
--- End of inner exception stack trace ---
at System.Management.Automation.DotNetAdapter.AuxiliaryMethodInvoke(Object target, Object[] arguments, MethodInformation methodInformation, Object[] originalArguments)
at System.Management.Automation.ParserOps.CallMethod(Token token, Object target, String methodName, Object[] paramArray, Boolean callStatic, Object valueToSet)
at System.Management.Automation.MethodCallNode.InvokeMethod(Object target, Object[] arguments, Object value)
at System.Management.Automation.MethodCallNode.Execute(Array input, Pipe outputPipe, ExecutionContext context)
at System.Management.Automation.ParseTreeNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context).Message
At line:2 char:16
+ Write-DataTable <<<< -ServerInstance "RPRMBSDEVDB81" -Database "dba_rep" -TableName "DBBakSizes" -Data $dt
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Write-DataTable
Write-DataTablecomes from here?