I have this powershell script function. The first time it starts up, it works flawlessly, the second time it starts up, I get an error that the .csv cannont is accessing "because it is being used by another process. Any idea which part of the script is" holding " file and how can i make it go at the end?
clear
set-executionpolicy remotesigned
cd d:\directory
$accountNumbers = (Get-Content input.txt) | Sort-Object
$accountID=0
$numAccounts = $accountNumbers.Count
$outString =$null
$file = New-Item -ItemType file -name sql.sql -Force
if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerProviderSnapin110'}))
{
if(Get-PSSnapin -registered | ?{$_.name -eq 'SqlServerProviderSnapin110'})
{
add-pssnapin SqlServerProviderSnapin100
Write-host SQL Server Provider Snapin Loaded
}
else
{
}
}
else
{
Write-host SQL Server Provider Snapin was already loaded
}
if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerCmdletSnapin100'}))
{
if(Get-PSSnapin -registered | ?{$_.name -eq 'SqlServerCmdletSnapin100'})
{
add-pssnapin SqlServerCmdletSnapin100
Write-host SQL Server Cmdlet Snapin Loaded
}
else
{
}
}
else
{
Write-host SQL Server CMDlet Snapin was already loaded
}
$outString = "SELECT stuff FROM table LIKE '%"
IF ($numAccounts -gt 0)
{
For ($i =1; $i -le ($AccountNumbers.Count - 1); $i++)
{
$outString = $outstring + $AccountNumbers[$accountID]
$outString = $outString + "' OR ca.accountnumber LIKE '"
$accountID++
}
$outString = $outString + $AccountNumbers[$AccountNumbers.Count - 1]
}
else
{
$outString = $outString + $AccountNumbers
}
$outString = $outString + "%'more sql stuff"
add-content $file $outString
Write-host Sql query dynamically written and saved to file
Invoke-Sqlcmd -ServerInstance instance -Database database -Username username -Password password -InputFile sql.sql | Export-Csv -Path output.csv
$emailFrom = "to"
$emailTo = "from"
$subject = "test"
$body = "test"
$smtpServer = "server"
$filename = "output.csv"
$att = new-object Net.mail.attachment($filename)
$msg = new-object net.mail.mailmessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg.from = $emailFrom
$msg.to.add($emailto)
$msg.subject = $subject
$msg.body = $body
$msg.attachments.add($att)
$smtp.Send($msg)
source
share