Author Posts

May 17, 2015 at 3:46 am

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.
#Create tables in inventory database
#Invoke-Sqlcmd -ServerInstance . -Database Inventory -Query "CREATE TABLE [dbo].[Systems1]([Name] [nvarchar](75) NOT NULL) ON [PRIMARY]"
#Invoke-Sqlcmd -ServerInstance . -Database Inventory -Query "CREATE TABLE [dbo].[Systems2]([Name] [nvarchar](75) NOT NULL,[SerialNumber] [nvarchar](75) NOT NULL) ON [PRIMARY]"

#getting some hardware information from wmi
$compsys = gwmi win32_ComputerSystem
$bios = gwmi win32_ComputerSystem

#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);"

Invoke-Sqlcmd -ServerInstance . -Database Inventory -Query $Query2

Query2 throws:
Invoke-Sqlcmd : A MERGE statement must be terminated by a semi-colon (;).
At line:29 char:1
+ Invoke-Sqlcmd -ServerInstance . -Database Inventory -Query $Query2
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

May 17, 2015 at 6:15 pm

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.

May 18, 2015 at 4:57 am

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
2 – Highly recommend the use of here-strings, see the examples below for the queries

$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

May 18, 2015 at 7:41 am

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.

Correct Syntax:

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) AND (T.SerialNumber = S.SerialNumber)
WHEN MATCHED THEN
UPDATE SET T.Name = S.Name, T.SerialNumber = S.SerialNumber
WHEN NOT MATCHED THEN
INSERT VALUES(S.Name,S.SerialNumber);