Can someone inspect my code and tell me why i am getting the error below when i try to insert more than one value into a table record.
#I created an 'inventory' database in sql server management studio first.
#getting some hardware information from wmi
#Executes successfully when single value is inserted
#throws error when multiple values inserted
Invoke-Sqlcmd -ServerInstance . -Database Inventory -Query $Query2
You're really asking a T-SQL question, not a PowerShell question. I'd suggest asking at StackOverflow.com, or someplace similar – you're much more likely to get a good answer. You'd get this same error if you tried to run the query in a query window of SQL Server Management Studio.
Couple of things.
1 – You are creating the BIOS object with the same WMI class Win32_ComputerSystem, it should be Win32_BIOS. If the serial column does not accept nulls, it could be why you are getting an error
$compsys = Get-WMIObject -Class Win32_ComputerSystem -Property Name | Select Name $bios = Get-WMIObject -Class Win32_BIOS -Property SerialNumber | Select SerialNumber #Executes successfully when single value is inserted $Query1 = @" WITH CTE_T (Name) AS (SELECT '$($compsys.name)') MERGE INTO dbo.Systems1 AS T USING (SELECT * FROM CTE_T) AS S ON (T.Name = S.Name) WHEN MATCHED THEN UPDATE SET T.Name = S.Name WHEN NOT MATCHED THEN INSERT VALUES(S.Name); "@ #throws error when multiple values inserted $Query2 = @" WITH CTE_T (Name,SerialNumber) AS (SELECT '$($compsys.name)', '$($bios.serialnumber)') MERGE INTO dbo.Systems2 AS T USING (SELECT * FROM CTE_T) AS S ON (T.Name = S.Name) WHEN MATCHED THEN UPDATE SET T.SerialNumber = S.SerialNumber UPDATE SET T.Name = S.Name WHEN NOT MATCHED THEN INSERT VALUES(S.Name,S.SerialNumber); "@ $Query1 $Query2
Thanks for the tips Don and Rob. The duplicate class was a typo. The actual problem was with my sql syntax. After executing the statement in sql server management studio I was able to see exactly where the fail was happening. I only needed to specify the UPDATE SET clause once and then separate each column to be updated with commas. Instead I was creating UPDATE SET statements for each column I wanted to update.
WITH CTE_T (Name,SerialNumber) AS (SELECT '$($compsys.name)', '$($bios.serialnumber)')
You must be logged in to reply to this topic.