Powershell (Network Steve Forum)

Powershell

I getting this error. I can't seem to find what the error is.

I have checked my script several times.

Invoke-Sqlcmd : Incorrect syntax near ','.
At line:83 char:14
+ Invoke-Sqlcmd <<<< Query $SQLQuery -ServerInstance localhost
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerS
   hellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShe
   ll.GetScriptCommand

#Add-PSSnapin SqlServerCmdletSnapin100
#Add-PSSnapin SqlServerProviderSnapin100

$DATA=IMPORT-CSV "C:\Users\pzd74f\Desktop\SLAPERFMAT2014.csv"

FOREACH ($LINE in $DATA) {

#$SLADefinition=$Line.("SLA Definition")
#$SLADefinition = $SLADefinition -replace "'", "''"
$SLADefinition = "`'"+$Line.("SLA Definition")+"`'"
$QTR = "`'"+$Line.("Qtr")+"`'"
$S1AppAvail = "`'"+$Line.("SLA1")+"`'"
$S1EnvResHIM = "`'"+$Line.("SLA1HIM")+"`'"
$S2BatProAvail = "`'"+$Line.("SLA2")+"`'"
$S2AppAvailHIM = "`'"+$Line.("SLA2HIM")+"`'"
$S3FileOlAvail = "`'"+$Line.("SLA3")+"`'"
$S3BatProAvailHIM = "`'"+$Line.("SLA3HIM")+"`'"
$S4TestEnvAvail = "`'"+$Line.("SLA4")+"`'"
$S4FileolAvailHIM = "`'"+$Line.("SLA4HIM")+"`'"
$S5AWanAvail = "`'"+$Line.("SLA5A")+"`'"
$S5BWanResp = "`'"+$Line.("SLA5B")+"`'"
$S5TestAvailHIM = "`'"+$Line.("SLA5HIM")+"`'"
$S6AppTranResp = "`'"+$Line.("SLA6")+"`'"
$S6AWanAvailHIM = "`'"+$Line.("SLA6AHIM")+"`'"
$S6BWanRespHIM = "`'"+$Line.("SLA6BHIM")+"`'"
$S7AHelpDeskAvgSpeed = "`'"+$Line.("SLA7A")+"`'"
$S7BHelpDeskAvgAbanRate = "`'"+$Line.("SLA7B")+"`'"
$S7AppTranResHIM = "`'"+$Line.("SLA7HIM")+"`'"
$S8ATimeToNotify = "`'"+$Line.("SLA8A")+"`'"
$S8BTimeToIdentify = "`'"+$Line.("SLA8B")+"`'"
$S8ATimeToNotifyWH = "`'"+$Line.("SLA8AWH")+"`'"
$S8BTimeToIdentifyWH = "`'"+$Line.("SLA8BWH")+"`'"
$S8ChangeMgtHIM = "`'"+$Line.("SLA8BHIM")+"`'"
$S9ATimeToRespondRepair = "`'"+$Line.("SLA9A")+"`'"
$S9BTimeToRespondRepair1to2 = "`'"+$Line.("SLA9B")+"`'"
$S9CTimeToRespondRepair2to6 = "`'"+$Line.("SLA9C")+"`'"
$S9DTimeToRespondRepair3to48 = "`'"+$Line.("SLA9D")+"`'"
$S9ATimeToRespondWH = "`'"+$Line.("SLA9AWH")+"`'"
$S9BTimeToRespondRepair1to2WH = "`'"+$Line.("SLA9BWH")+"`'"
$S9CTimeToRespondRepair2to6WH = "`'"+$Line.("SLA9CWH")+"`'"
$S9DTimeToRespondRepair3to48WH = "`'"+$Line.("SLA9DWH")+"`'"
$S9ASysIntandSecurityHIM = "`'"+$Line.("SLA9AHIM")+"`'"
$S9BSysIntandSecurityNoRepeatHIM = "`'"+$Line.("SLA9BHIM")+"`'"
$S10FirstTimeResRate = "`'"+$Line.("SLA10")+"`'"
$S11OnTimeKDeliver = "`'"+$Line.("SLA11")+"`'"
$S12ChangeMangt = "`'"+$Line.("SLA12")+"`'"
$S12AHelpDeskAvgSpeedHIM = "`'"+$Line.("SLA12AHIM")+"`'"
$S12BHelpDeskAvgAbanRateHIM = "`'"+$Line.("SLA12BHIM")+"`'"
$S13ASysIntandSecurity = "`'"+$Line.("SLA13A")+"`'"
$S13BSysIntandSecurityNoRepeat = "`'"+$Line.("SLA13B")+"`'"
$S13TimeToNotifyHighSevHIM = "`'"+$Line.("SLA13HIM")+"`'"
$S14NewAppSerDepl = "`'"+$Line.("SLA14")+"`'"
$S14TimeToNotifyHighSevHIM = "`'"+$Line.("SLA14HIM")+"`'"
$S15Backups = "`'"+$Line.("SLA15")+"`'"
$S15ATimeToRespondRepairSev1HIM = "`'"+$Line.("SLA15AHIM")+"`'"
$S16DisasterRecovery = "`'"+$Line.("SLA16")+"`'"
$S16BTimeToRespondRepair1to2HIM = "`'"+$Line.("SLA16BHIM")+"`'"
$S17ImproveCustServ = "`'"+$Line.("SLA17")+"`'"
$S17ImproveCustServWH = "`'"+$Line.("SLA17WH")+"`'"
$S17CTimeToRespondRepair3to48WH = "`'"+$Line.("SLA17CWH")+"`'"
$S18NewAppSerDeplHIM = "`'"+$Line.("SLA18HIM")+"`'"
$S19BackupsHIM = "`'"+$Line.("SLA19HIM")+"`'"
$S20DisasterRecoveryHIM = "`'"+$Line.("SLA20HIM")+"`'"
$S21RecoveryTimeObjHIM = "`'"+$Line.("SLA21HIM")+"`'"
$S22RecoveryPointObjHIM = "`'"+$Line.("SLA22HIM")+"`'"
$S23ImproveCustServHIM= "`'"+$Line.("SLA23HIM")+"`'"
$S24AmazonWebServAvailHIM = "`'"+$Line.("SLA24HIM")+"`'"
$YEAR = "`'"+$Line.("YEAR")+"`'"

#insert into this

$SQLHEADER=INSERT INTO [SP2010_EDCSLA_AppDBHIM].[dbo].[SLAPerfMatrix2014] ([SLADefinition],[QTR],[S1AppAvail],[S1EnvResHIM],[S2BatProAvail],[S2AppAvailHIM],[S3FileOlAvail],[S3BatProAvailHIM],[S4TestEnvAvail],[S4FileolAvailHIM],[S5AWanAvail],[S5BWanResp],[S5TestAvailHIM],[S6AppTranResp],[S6AWanAvailHIM],[S6BWanRespHIM],[S7AHelpDeskAvgSpeed],[S7BHelpDeskAvgAbanRate],[S7AppTranResHIM],[S8ATimeToNotify],[S8BTimeToIdentify],[S8ATimeToNotifyWH],[S8ATimeToIdentifyWH],[S8ChangeMgtHIM],[S9ATimeToRespondRepair],[S9BTimeToRespondRepair1to2],[S9CTimeToRespondRepair2to6],[S9DTimeToRespondRepair3to48],[S9ATimeToRespondRepairWH],[S9BTimeToRespondRepair1to2WH],[S9CTimeToRespondRepair2to6WH],[S9DTimeToRespondRepair3to48WH],[S9ASysIntandSecurityHIM],[S9BSysIntandSecurityNoRepeatHIM],[S10FirstTimeResRate],[S11OnTimeKDeliver],[S12ChangeMangt],[S12AHelpDeskAvgSpeedHIM],[S12BHelpDeskAvgAbanRateHIM],[S13ASysIntandSecurity],[S13BSysIntandSecurityNoRepeat],[S13TimeToNotifyHighSevHIM],[S14NewAppSerDepl],[S14TimeToNotifyHighSevHIM],[S15Backups],[S15ATimeToRespondRepairSev1HIM],[S16DisasterRecovery],[S16BTimeToRespondRepair1to2HIM],[S17ImproveCustServ],[S17ImproveCustServWH],[S17CTimeToRespondRepair3to48WH],[S18NewAppSerDeplHIM],[S19BackupsHIM],[S20DisasterRecoveryHIM],[S21RecoveryTimeObjHIM],[S22RecoveryPointObjHIM],[S23ImproveCustServHIM],[S24AmazonWebServAvailHIM],[YEAR])"

#insert into this

$SQLVALUES=" VALUES ($SLADefinition,$QTR,$S1AppAvail,$S1EnvResHIM,$S2BatProAvail,$S2AppAvailHIM,$S3FileOlAvail,$S3BatProAvailHIM,$S4TestEnvAvail,$S4FileolAvailHIM,$S5AWanAvail,$S5BWanResp,$S5TestAvailHIM,$S6AppTranResp,$S6AWanAvailHIM,$S6BWanRespHIM,$S7AHelpDeskAvgSpeed,$S7BHelpDeskAvgAbanRate,$S7AppTranResHIM,$S8ATimeToNotify,$S8BTimeToIdentify,$S8ATimeToNotifyWH,$S8ATimeToIdentifyWH,$S8ChangeMgtHIM,$S9ATimeToRespondRepair,$S9BTimeToRespondRepair1to2,$S9CTimeToRespondRepair2to6,$S9DTimeToRespondRepair3to48,$S9ATimeToRespondRepairWH,$S9BTimeToRespondRepair1to2WH,$S9CTimeToRespondRepair2to6WH,$S9DTimeToRespondRepair3to48WH,$S9ASysIntandSecurityHIM,$S9BSysIntandSecurityNoRepeatHIM,$S10FirstTimeResRate,$S11OnTimeKDeliver,$S12ChangeMangt,$S12AHelpDeskAvgSpeedHIM,$S12BHelpDeskAvgAbanRateHIM,$S13ASysIntandSecurity,$S13BSysIntandSecurityNoRepeat,$S13TimeToNotifyHighSevHIM,$S14NewAppSerDepl,$S14TimeToNotifyHighSevHIM,$S15Backups,$S15ATimeToRespondRepairSev1HIM,$S16DisasterRecovery,$S16BTimeToRespondRepair1to2HIM,$S17ImproveCustServ,$S17ImproveCustServWH,$S17CTimeToRespondRepair3to48WH,$S18NewAppSerDeplHIM,$S19BackupsHIM,$S20DisasterRecoveryHIM,$S21RecoveryTimeObjHIM,$S22RecoveryPointObjHIM,$S23ImproveCustServHIM,$S24AmazonWebServAvailHIM,$YEAR)"

$SQLQUERY=$SQLHEADER+$SQLVALUES

Invoke-Sqlcmd Query $SQLQuery -ServerInstance localhost
}

Line 83 char 14 is: $SQLQUERY=$SQLHEADER+$SQLVALUES

Edited by Kimo44 Monday, June 22, 2015 3:25 AM

June 22nd, 2015 3:19am

Where does this go?

#Add-PSSnapin SqlServerCmdletSnapin100
#Add-PSSnapin SqlServerProviderSnapin100

$DATA=IMPORT-CSV "C:\Users\pzd74f\Desktop\SLAPERFMAT2014.csv"

FOREACH ($LINE in $DATA) {

$SQLQUERY=$SQLHEADER+$SQLVALUES
Invoke-Sqlcmd Query $SQLQuery -ServerInstance localhost
}

Thank you for your input and suggestions. I have been looking at this for several hours before I contact the forum. I did one earlier but less fields and it work fine. I am not sure what you are talking about moving it to the top? Are you saying that I didn;t need to (

I would also not assign all of the CSV values to variables only to reassign to the statement.

$sql=$SQLTMPLT -f $Line.SLA1, ... etc

The first part of my script is the headering from the csv file and the fields from my sql server table.

I would love to use your patented CSV bulk loader but I wouldn't even know where to start.

Edited by Kimo44 Monday, June 22, 2015 5:35 AM

Free Windows Admin Tool Kit Click here and download it now

June 22nd, 2015 5:33am

This topic is archived. No further replies will be accepted.