Writing to SQL tables

by wilbudl at 2012-09-14 10:41:09

I hope this in the correct forum… I’m a long time listener, first time caller… Using some code I’ve manage to compile from various sources, I’m attempting to gather hard disk information from a text list of systems and write that into a SQL table. I do not have any problems when writing the value for a single system, however when attempting to write values for multiple systems, I receive a “Violation of PRIMARY KEY constraint” and "Cannot insert duplicate key in object ‘dbo.Disks’. I do not yet know enough about SQL to understand why I am getting this error. Can anyone please give me some direction? I’ve modified the code a couple of different ways, but this is the latest iteration: (there are things commented out that were part of the original code) BTW, I’m using PowerGUI to build, so some of the script is simply visual queues to see where it is. (helpful when multiple errors scroll up the screen)

Write-Host “Starting script…”
#Get Current Time
$time2 = Get-Date -Format g

$srvArray = Get-Content C:\Reports\servertest.txt
#$dp = Get-WmiObject win32_logicaldisk -ComputerName LHDPPRM1 | Where-Object {$.drivetype -eq 3}

#$ServerName = Get-Content env:ComputerName

#Define and open the connection to the SQL database
$dbconn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=localhost\sqlexpress1; Initial Catalog=ConfigurationMaintenanceSQL; Integrated Security=SSPI”)

#Read through the servers and write disk info to SQL table
Foreach ($srv in $srvArray)
$dp = Get-WmiObject win32_logicaldisk -ComputerName $srv |
Where-Object {$
.drivetype -eq 3}
Foreach ($disk in $dp)
$driveLetter = $disk.DeviceId
$freeSpace = [Math]::Round(($disk.FreeSpace/1073741824),2)
$driveCapacity = [Math]::Round(($disk.size/1073741824),2)
$percentFree = ($freeSpace/$driveCapacity)*100
## Write Values to SQL
$dbwrite = $dbconn.CreateCommand()
$dbwrite.CommandText = “INSERT INTO dbo.Disks (ScanDateTime,systemName,driveLetter,freeSpace,capacity,percentFree) VALUES (’$time2’,’$srv’,’$driveLetter’,’$freeSpace’,’$driveCapacity’,’$percentFree’)”
Write-Host "Script complete"

by poshoholic at 2012-09-14 10:43:15
Hi Darryl,

I’m going to move this question to the PowerShell for SQL Server forum on this site. It will still appear in both locations, but it will help get the attention of the SQL Server MVPs who monitor that forum.
by wilbudl at 2012-09-14 10:44:52
Ok. I had originally put it there, but was really undecided where the best place on where it should go. Some boards get really “wound up” if you post in the wrong area! :slight_smile:

by poshoholic at 2012-09-14 11:09:05
It’s not really a big deal. I usually go with who would I want to answer a question. If it’s related to a specific domain (SQL, AD, SharePoint, Exchange, etc.), I’d post it in the appropriate forum so that the right people see it. That usually helps get better answers. But PowerShell scripters touch a lot of different products, so you’ll probably get good help in the general forum just the same. In this case I wanted the SQL guys to see the question which is why I moved it.
by Klaas at 2012-09-14 11:37:51
Hi Darryl

I think the problem is not in your script, but in the constraints on your table. What column(s) is/are the primary key of the dbo.disks table?

by RichardSiddaway at 2012-09-14 12:38:17
The error message means that you are trying to insert multiple records with the same primary key - not allowed - primary key values must be unique

Can you show the table structure

Couple of minor points

You can make your WMI statement simpler - change it from

$dp = Get-WmiObject win32_logicaldisk -ComputerName $srv |
Where-Object {$_.drivetype -eq 3}


$dp = Get-WmiObject win32_logicaldisk -ComputerName $srv -Filter "Drivetype = 3"

That way the filtering is done on the server which makes things a bit more efficient

PowerShell recognises GB, MB etc so use 1GB instead or 1073741824 for your division
by wilbudl at 2012-09-18 06:49:04
Thank you for pointing me in the correct direction. The initial Primary Key was the “ScanDateTime” column. I changed it to the “SystemName” column, which allowed it to work for the initial run, but failed on subsequent runs, I assume because the same system name already existed in the table. It would seem that the next thing I need to do is to move the “Get-Date” into the “Foreach” step to acquire a unique time stamp and also change the Primary Key back to “ScanDateTime”. Does that seem like the best approach?

Richard, I also change my variable to your recommendation. I can see it is much more efficient when I run the code through the debugger. Thank you!

by Klaas at 2012-09-18 07:21:27
I wouldn’t pick the datetime as primary key.
If you choose a datetype with a precision of 0.0000001 sec, the probability is very very very high that you won’t have any duplicates, but it’s not guaranteed. It’s also not economic to save time with such a precision if you run this script once a day, or once an hour?
Do you want to keep the history of diskspace? If not you can delete previous entries, or use an UPDATE instead of INSERT. If you do, you can choose an ID column (an auto incrementing int) or a combination of systemname, driveletter and date as primary key.

The best choice depends on the entire set up of your database and the way you want to use it. Will you implement foreign keys to this table, for instance?
If you really want a very precise scan time or the script runs a long time (if you have many servers or you add more checks in the loop,…) you can move the Get-Date in the loop. The cost is of course that this cmdlet has to run as many times as there are servers. Depending on yuor choice, you should also choose a fitting datatype.
by wilbudl at 2012-09-18 14:01:08
The end goal of this database/script, is to run weekly to build historical disk usage to assist in planning for future storage needs. It will most likely be run weekly. The ScanDateTime does not need to be precise at all, in fact, just the date will be sufficient for my needs. I have added an additional column to the table as the “Identity”, and things are now moving along.
by Omshanti at 2012-11-16 16:32:38
Hi Wilbudl

i have taken your script and tried to change it using central management servers. i am sure someone will further simplify this.

##Prerequisites servers are registered in Central Management Servers in SQL Management Studio
write-host “Starting script…”
#Get Current Time
$time2 = Get-Date -Format g
#Get server name using central management servers
foreach ($RegisteredSQLs in invoke-sqlcmd -query “select
when charindex(’’,server_name) >0 then left(server_name,charindex(’’,server_name)-1)
else server_name
end srv,
from msdb.dbo.sysmanagement_shared_registered_servers_internal” -database msdb -serverinstance “.”)
{write-host $RegisteredSQLs.Server_Name
#Get Space Information
$dp = Get-WmiObject win32_logicaldisk -ComputerName $srv -Filter "Drivetype = 3"
Foreach ($disk in $dp)
$driveLetter = $disk.DeviceId
$freeSpace = [Math]::Round(($disk.FreeSpace/1073741824),2)
$driveCapacity = [Math]::Round(($disk.size/1073741824),2)
$percentFree = ($freeSpace/$driveCapacity)*100
#Format Percentfree take decimal off
$percentFree = “{0:N0}” -f $percentFree
$srv = $RegisteredSQLs.srv
invoke-sqlcmd -query “INSERT INTO dbo.Disks (ScanDateTime,systemName,driveLetter,freeSpace,capacity,percentFree)
VALUES (’$time2’,’$srv’,’$driveLetter’,’$freeSpace’,’$driveCapacity’,’$percentFree’)” -ServerInstance “.” -Database test
Write-Host “Script complete”