Retrieve unsaved SQL query Scripts

So, the worst has happened and your elaborate work was lost because you didn't press save and then your session was unexpectedly closed? or SSMS crashed and you cannot restore the script?

Well, there is a solution to retrieve unsaved SQL Scripts, if you have executed the script within the last 24 hours:

http://www.sql-programmers.com/retrieve-unsaved-sql-query-scripts.aspx

SELECT execquery.last_execution_time AS [Date Time],

execsql.text AS [Script] 

FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC

sp_MSforeachdb procedure to iterate through each database

Handy SQL Server system stored procedure to iterate through each databse on a server. I used to get a list of all databases that have sysssislog table present

DECLARE @DB TABLE (DBName sysname);
INSERT INTO @DB
EXEC sp_msforeachdb 'select ''?'' from [?].sys.tables t WHERE t.name = ''sysssislog'''
SELECT * FROM @DB
WHERE DBName NOT IN ('master','tempdb','model','msdb')


The undocumented sp_MSforeachdb procedure

PowerShell: Importing CSV into SQL Server

http://www.sqlteam.com/article/fast-csv-import-in-powershell-to-sql-server

I was working on the CSV import script in PowerShell, following the first example in the article above: Simple CSV Import using PowerShell.

I had to use Out-DataTable function  from here:
http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd#content

Simple CSV Import using PowerShell

Simple CSV Import using PowerShell

Simple CSV Import using PowerShell

to get DataTable from CSV file. This part worked very well.

But then I started getting an issue at the bulk insert - "The given value of type String from the data source cannot be converted to type int of the specified target column."

After some time of looking for the culprit data row, I realized that this error happens when we try to insert an empty value in the int column.

Thus, I had to change the Out-DataTable function in the following way:

 replace

     $DR.Item($property.Name) = $property.value

by

if($property.value -eq "")
{
        $DR.Item($property.Name) = $null
}
else
{
         $DR.Item($property.Name) = $property.value
}

now, Bulk Insert is satisfied and inserts NULLs when the data is not available in the CSV

Simple CSV Import using PowerShell

PowerShell: searching in the first 2 levels of the directory

Let's say you need to find a certain folder in the Directory, but you don't want to go deeper than 2 or 3 levels in your search?

Obviously, we will have to use get-childitem

However, -recurse option cannot help, since it has no stopping limit - it will recurse to the bottom of the directory

Here is a solution that I found that's short and simple, just the way I like it:



$search1 = "C:\temp\*"
$search2 = "C:\temp\*\*"
$search3 = "C:\temp\*\*\*"

$folders =  Get-ChildItem  -path $search1, $search2, $search3 | where-object {$_.PSIscontainer -and $_.name.StartsWith("Blue")}

...


Continue SSIS Package execution after failure and ignoring the error

Finally found what I needed to avoid SSIS from failing the package and ignoring the error in the Task:

http://www.timmitchell.net/post/2013/08/05/continue-package-execution-after-error-in-ssis/

Just one tiny hidden System variable "Propagate" was able to resolve my issue on custom error handling in the SSIS Package.

Indexed Views | Creating and Optimizing Views in SQL Server | InformIT

Indexed Views | Creating and Optimizing Views in SQL Server | InformIT

Looks like not is all that simple with indexed views.
If we are using Standard Edition, then indexed views will be used only when we specifically use SELECT with NOEXPAND hint

Enterprise edition's query optimizer decides himself whether to use data in the indexed view, or in its underlying tables, depending on what is faster for him.

In the Standard edition, however, it doesn't bother and goes to underlying tables right away, unless you add this NOEXPAND hint.

At least this is my understanding of the articles below.

http://technet.microsoft.com/en-us/library/ms181151%28v=sql.105%29.aspx

Improving Performance with SQL Server 2005 Indexed Views

Converting CSV files into XML files using PowerShell

At work I have to  resort sometimes to PowerShell to provide a quick solution to a problem.

For example, I had to do some CSV conversions and manipulations, which seemed to be easier done in PowerShell rather than in SQL Server.

1. I will document here a CSV to XML file conversion, which looked to me short and sweet:

Idea inspired from http://blogs.msdn.com/b/powershell/archive/2007/05/29/using-powershell-to-generate-xml-documents.aspx

#create xml file - export_xml 
$file = "C:\tmp\file.csv"
$fileoutxml = "C:\tmp\file.xml"
$xml=Import-Csv $file -Delimiter ";" -Header "c1","c2","c3" |
 foreach { " <row c1=`"{0}`" c2=`"{1}`" c3=`"{2}`"/>`n" -f
 $_.c1,$_.c2,$_.c3 } 
$xml="<export key_field=`"c1`">`n " + $xml + "</export>" | Out-File $fileoutxml -Encoding UTF8 

2. and a replace with Regular Expressions:
here, I am first replacing ; by | and trimming up the data at the same time
and then changing the swapping the order of the two last columns. it is important that $2$1 is surrounded by single quotes - script stops working if we use double quotes for some reason.

(Get-Content $file ) |
Foreach-Object {$_ -replace "(\s*;\s*)", -replace "(?<=^[^|]*\|[^|]*)(\|[^|]*)(\|[^|]*$)", '$2$1'; } |
Set-Content $fileout -Encoding Unicode