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 3 hours 44 minutes ago

June 21st, 2015 11:20pm

Please learn how to read you screen. The error is explicit:

Invoke-Sqlcmd : Incorrect syntax near ','.
At line:83 char:14
+ Invoke-Sqlcmd <<<< Query $SQLQuery -ServerInstance localhost

Your SQL statement is bad. Use SSMS to test the SQL before deploying it. Learn how to format your script so it is readable.

Free Windows Admin Tool Kit Click here and download it now

June 22nd, 2015 12:07am

Here is an example of how to set this up to make it manageable.

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]
)
VALUES(
   	{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},
   	{19},{20},{21},{22},{23},{24},{25},{26},{27},{28},{29},{30},{31},{32},{33},{34},{35},{36},{37},{38},
   	/*<continue to match number of fields>*/
)
'@    	
#'
#insert values into statement
$sql=$SQLTMPLT -f $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

No one is going to count your commas for you.

June 22nd, 2015 12:21am

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

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

Place you single quotes in the template round the holders:

VALUES( '{0}','{1}'

Move the template to the top of the script out of the code loop. It is a static structure now.

You can also auto generate a parameters statement and use a connection to load the data or you can use my patented CSV bulk loader:

https://onedrive.live.com/redir?resid=bf32ab14d23ea238!1031&authkey=!ALvRzH5pF65IAQY&ithint=file%2cps1

Free Windows Admin Tool Kit Click here and download it now

June 22nd, 2015 12:32am

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 1 hour 34 minutes ago

June 22nd, 2015 1:35am

to simplify. You have a bad SL statement. You need to fix it. We cannot do that for you.

I tried to show you heo to dimplify thi but you clearly have no knowledge of scripting so you cannot understand. Just fix your SL statement.

Free Windows Admin Tool Kit Click here and download it now

June 22nd, 2015 1:45am

Thanks!

June 22nd, 2015 1:49am

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