Powershell Export SQL results to CSV

I have a bunch of SQL scripts for which I need to export the results to CSV, this is something I need to do on a semi-regular basis and its generally a handy thing to be able to automate without having to create a SSIS package or write a console application.

So here is a Powershell script i have written which will read the SQL queries in from .sql files in the input directory and output the results to csv files with the same name as the source script.

WARNING: This script doesn’t handle SQL comments very well, so make sure you remove them from your SQL scripts before using this script to run them, also for some reason wordpress keeps messing up the connection string in the SqlConnection so Datace should be Data Source.

Load Workflow Class: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.

I ran into an interesting issue today on one of the sharepoint servers we manage, the issue was affecting all workflows across a single site collection, the workflows would just fail immediately and gave a ‘Failed to Start (Retrying)’ message, when I looked at the logs I found these errors:

Load Workflow Class: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.   
 at System.ThrowHelper.ThrowKeyNotFoundException()   
 at System.Collections.Generic.Dictionary`2.get_Item(TKey key)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.IsConfigForSite(SPSite site)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.GetWorkflowConfurationSection(SPSite site, String section)   
 at Microsoft.SharePoint.Workflow.SPWinOeHostServices.EnsurePluggableServices(SPSite site, SPWorkflowExternalDataExchangeServiceCollection services, ExternalDataExchangeService existingServices)   
 at Microsoft.SharePoint.Workflow.SPWinOeHostServices..ctor(SPSite site, SPWeb web, SPWorkflowManager manager, SPWorkflowEngine engine)     -
-- End of inner exception stack trace ---   
 at System.RuntimeMethodHandle._InvokeConstructor(Object[] args, SignatureStruct& signature, IntPtr declaringType)   
 at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)   
 at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.LoadPluggableClass(String classname, String assembly, Object[] parameters)

 

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.   
 at System.ThrowHelper.ThrowKeyNotFoundException()   
 at System.Collections.Generic.Dictionary`2.get_Item(TKey key)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.IsConfigForSite(SPSite site)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.GetWorkflowConfurationSection(SPSite site, String section)   
 at Microsoft.SharePoint.Workflow.SPWinOeHostServices.EnsurePluggableServices(SPSite site, SPWorkflowExternalDataExchangeServiceCollection services, ExternalDataExchangeService existingServices)   
 at Microsoft.SharePoint.Workflow.SPWinOeHostServices..ctor(SPSite site, SPWeb web, SPWorkflowManager manager, SPWorkflowEngine engine)     -
-- End of inner exception stack trace ---   
 at System.RuntimeMethodHandle._InvokeConstructor(Object[] args, SignatureStruct& signature, IntPtr declaringType)   
 at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)   
 at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.LoadPluggableClass(String classname, String assembly, Object[] parameters)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.GetService(SPWorkflowAssociation association, SPWorkflowEngine engine)   
 at Microsoft.SharePoint.Workflow.SPWorkflowManager.RunWorkflowElev(SPWorkflow workflow, Collection`1 events, SPWorkflowRunOptionsInternal runOptions)

When I googled these errors I found this article and I tried his approach but no luck, so I opened up .NET Reflector and had a look at the IsConfigForSite method on the SPWorkflowManager class and found the following:

.net-reflector

The exception is being thrown when it’s trying to access the IisSettings dictionary using the SPUrlZone.Default enum, so I opened up a powershell console and had a look at what was in the IisSettings dictionary:

$site = Get-SPSite http://mysite
$site.WebApplication.IisSettings

This showed that there was no entry for the default zone in the IisSettings dictionary, initially I thought we just needed to reconfigure the alternate access mappings and add an entry for the default zone but this had no affect.

So I started discussing this issue with one of my colleagues who had worked on some issues with this sever the previous week. I knew he had made some pretty significant changes, and as it turned out one of those changes was he had extended the web application to the intranet zone and then deleted the previous web application.

This was the root cause of the problem, this is why there was no default entry in the IisSettings dictionary, so I extended the web application to the default zone and the workflows started working again.

Powershell, adding and connecting webparts

Recently had an issue with a powershell script I was writing for a sharepoint project I was working on, I needed to add two webparts to a page and connect them, they were an XsltListViewWebpart and a Nintex List Form webpart and I was following these articles:

http://social.msdn.microsoft.com/Forums/sharepoint/en-US/1cb661a0-b42f-4ec8-a42a-9a5d1fe1dce1/connect-xslt-list-view-and-query-string-filter-web-part-via-powershell?forum=sharepointdevelopment
http://troyvssharepoint.blogspot.in/2012/08/web-parts-connections-via-powershell.html

I could add the webparts to the page without any problems, but when I tried to connect them the connection wouldn’t save properly, and the problem turned out to be the i just needed to re-get the SPLimitedWebPartManager after adding the webparts, then create the connection.

And here is an example script:

add-pssnapin Microsoft.Sharepoint.Powershell
$url = http://mysiteurl
$web = Get-SPWeb $url
$wpGallery = $web.ParentWeb.Lists["Web Part Gallery"]
$wpManager = $web.GetLimitedWebPartManager($web.Url + “pages/test.aspx”,[System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)

Write-Host ‘clearing page…’
while ($wpManager.WebParts.count -gt 0)
{
    $wpManager.DeleteWebPart($wpManager.WebParts[0])
}
$recordList = $web.Lists['Record List']
$viewFields = New-Object System.Collections.Specialized.StringCollection
$viewFields.Add(“RecordID”)
$view = $recordList.Views | where { $_.title -eq ‘RecordByID’ }
if($view -eq $null){
    Write-Host ‘creating RecordByID view…’
    $parameterBindings = ‘’
    $viewQuery = ‘{RecordID}’
    $view = $recordList.Views.Add(“RecordByID”, $viewFields, $viewQuery, 100, $True, $False, “HTML”, $False)
    $view.ParameterBindings = $parameterBindings;
    $view.Update()
}

Write-Host ‘creating filtered list view webpart…’
$recordByID = New-Object Microsoft.SharePoint.WebPartPages.XsltListViewWebPart
$recordByID.ChromeType = [System.Web.UI.WebControls.WebParts.PartChromeType]::TitleOnly
$recordByID.Title = “Record List”
$recordByID.ListName = ($recordList.ID).ToString(“B”).ToUpper()
$recordByID.ViewGuid = ($view.ID).ToString(“B”).ToUpper()
$recordByID.ParameterBindings = $parameterBindings;
$recordByID.TitleUrl = ‘$url/’ + $view.Url
$recordByID.WebId = $recordList.ParentWeb.ID
$wpManager.AddWebPart($recordByID, “Header”, 1)

Write-Host ‘creating nintex list form webpart…’
$wpl = $wpGallery.Items | where {$_.Title -eq ‘$Resources:NFResource,WebPart_List_Form_Title;’} 
$xmlReader = New-Object System.Xml.XmlTextReader($wpl.File.OpenBinaryStream()); 
$errorMsg = “” 
$webPart = $wpManager.ImportWebPart($xmlReader, [ref]$errorMsg) 
$webPart.Title = ‘List Form’;
$webPart.Mode = ‘Edit’;
$wpManager.AddWebPart($webpart,”Header”,1)

##### you must re-get the webpart manager #####
$wpManager.Dispose()
Write-Host ‘refreshing webpart manager…’
$wpManager = $web.GetLimitedWebPartManager($web.Url + “pages/test.aspx”,[System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)
$consumerWebPart = $wpManager.WebParts | where {$_.Title -eq ‘List Form’}
$providerWebPart = $wpManager.WebParts | where {$_.Title -eq ‘Record List’}

Write-Host ‘getting connection points…’
$providerConnectionPoints = $wpManager.GetProviderConnectionPoints($providerWebPart)
$consumerConnectionPoints = $wpManager.GetConsumerConnectionPoints($consumerWebPart)
# find matching interfaces
foreach($pc in $providerConnectionPoints){
    $consumerCon = $consumerConnectionPoints | where { $_.InterfaceType -eq $pc.InterfaceType }
    if($consumerCon -ne $null){
       $providerCon = $pc
        break
    }
}

Write-Host ‘connecting webparts…’
$newCon = $wpManager.SPConnectWebParts($providerWebPart,$providerCon,$consumerWebPart,$consumerCon)
$wpManager.Dispose()
$web.dispose()

Write-Host ‘done’