0

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:

  1. I delete everything from target table via SQL Server Mgmt Studio.
  2. I run the PowerShell script and it runs successfully and data ends up in the table.
  3. I delete data via SSMS for one server from the target table.
  4. 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
2
  • Write-DataTable comes from here? Commented Sep 21, 2016 at 18:54
  • Sorry for not including the link but yes, that's where I got it from. Commented Sep 21, 2016 at 19:20

1 Answer 1

0

Gave up on Write-DataTable. My script is now looping through the DataTable and inserting row by agonizing row.

#Load data
#Write-DataTable -ServerInstance "RPRMBSDEVDB81" -Database "dba_rep" -TableName "DBBakSizes" -Data $dt
$SqlConnection = new-object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$DBServer; Database=$DBName; Integrated Security=SSPI;"
$SqlConnection.Open()
$SqlCommand = new-object System.Data.SqlClient.SqlCommand
$SqlCommand.Connection = $SqlConnection

foreach ($dtrow in $dt)
{
  If ($dtrow.ServerName) #Skip NULL/empty records
  {
    $SqlInsert = "INSERT dbo.DBBakSizes VALUES('$($dtrow.ServerName)','$($dtrow.DatabaseName)','$($dtrow.BackupType)',$($dtrow.UsedCompression),$($dtrow.UsedChecksum),'$($dtrow.MostRecentFull_Date)',$($dtrow.MostRecentFull_Sec),$($dtrow.MostRecentFull_MB),'$($dtrow.MostRecentOther)','$($dtrow.MostRecentOther_Date)',$($dtrow.MostRecentOther_Sec),$($dtrow.MostRecentOther_MB),'$($dtrow.ReportDate)')" 
    $SqlCommand.CommandText = $SqlInsert
    $SqlCommand.ExecuteNonQuery()
  }
}

$SqlConnection.Close()
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.