Month: June 2011

Use PowerShell with C# to get any file ‘ContentType’

Posted on by admin

Here’s an example of how you can use a piece of C# code to create an Static method for getting a file ‘ContentType’.  It just took me a few minutes to look for the sample C# code in the internet that would give me the expected result.

I modify the C# code from this blog ( http://codeasp.net/blogs/raghav_khunger/microsoft-net/531/how-to-get-content-type-of-a-file-in-c ) to create my own “”GetContentType” static method in my PowerShell script.

[sourcecode language=”powershell”]

$CSharpCode = @"
using System;
using Microsoft.Win32;
public class Win32file
{
public static string GetContentType(string getfileName)
{
string contentType = "application/octetstream";
string ext = System.IO.Path.GetExtension(getfileName).ToLower();

RegistryKey registryKey = Registry.ClassesRoot.OpenSubKey(ext);

if (registryKey != null && registryKey.GetValue("Content Type") != null)

contentType = registryKey.GetValue("Content Type").ToString();

return contentType;
}
}
"@
Add-Type -TypeDefinition $CSharpCode

[/sourcecode]

Usage: [Win32file]::GetContentType(“C:\Temp\GraphicImage.png“)

Result: image/png

Now, this piece of code serve a purpose, and I’m going to be shown it in my next blog post on “How to import a Document into a table BLOB field?”.  YES!! Using PowereShell and much easier than using SSIS.

Stay tuned!

Quick Export SQL Data to a Excel CSV file…

Posted on by admin

Here’s a quick way to get data off on of your SQL Server table and dump it to a CSV file.  Only 4 steps:

1. Make sure you are in SQLPS console, or have the community SQLPS (or SQLPSv2) module loaded ( ie. Import-Module SQLPS ) in either your PowerShell Console or ISE.

2. Have you query ready, then save it into a PowerShell variable:

$sql = “SELECT * FROM [AdventureWorks].[Production].[Location]”

3. Next one-liner will build the PowerShell object and exported to a *CSV file: (Execute in localhost only. Use -ServerInstance with the -database parameter if is needed t0 execute query)

Invoke-Sqlcmd -query $sql | Export-Csv -Path c:\temp\excelfile.csv -NoTypeInformation

4. Last line will open the file in Excel:

ii c:\temp\excelfile.csv

This surely beats going to SSIS for quick results!

For more information about the “Invoke-SQLcmd” use the help in PowerShell:

Help Invoke-SQLcmd -detailed

FLPSUG Next Livemeeting – July 13th 6:00PM

Posted on by admin

Florida PowerShell User Group Virtual Meeting.

Next livemeeting, Wednesday July 13th, starting at 6:00PM (45min-1hr), I will be presenting about “Customizing your PowerShell Environment”. Here’s the link if you want to join me:

https://www.livemeeting.com/cc/mvp/join?id=9GGSBP&role=attend&pw=cb*GD587c

Download Presentation here: <will be provided later> (rename *txt file to *zip)

Just released SQLDevTools Module (Alpha 0.1.0)

Posted on by admin

I’m excited to release this new version of SQLDevTools today Monday June 6th.  Here’s list of the updates:

##- SQLDevTools Alpha 0.1.0 Released 06/06/2011:
##-
##- Functions fixed;
##   1. Get-TSQLTableRecordCount dual syntax issue not working properly.
##  2. Write-TSQLScriptIndex parameters ‘DropIndexOnly” and ‘CreateIndexOnly’ was mispelled.
##  3. Get-TSQLTableTriggers not work, add modify_date field as last_modified.

## – Updated two function to include rename of destination table: (need to all help)
##  1. Write-TSQLScriptDFConstraint – Help updated
##  2. Write-TSQLScriptInsertSelect – Help updated

## – Add two new functions to change destination table name in the PS Object: (Help included)
##  1. Rename-TSQLScriptedTableName
##  2. Rename-TSQLScriptedTableIndexes
##  3. Get-TSQLUserDefinedDataTypes
##  4. Get-TSQLXmlSchemaCollections
##   5. Get-TSQLUserDefinedFunctions
##   6. Write-TSQLScriptXmlSchemaCollections
##  7. Write-TSQLScriptUserDefinedFunctions
##  8. Write-TSQLScriptUserDefinedDataTypes

Next release -> will include Views, User Stored-Procedures, and Database triggers.

Basically, this completes the basic need to script out most object needed to rebuild a table, or series of tables.  It takes into account “Identity”, Computed, and User Defined objects.  Also, I included the ability to alter(rename) your scripted destination tablename, and index objects.

For testing these functions, I’ve use Microsoft “AdventureWorks” which is full of scripting surprises.  Keep in mind, I’ve use a mix of both SMO and embed-TSQL that assist in generating the results.

Here’s a teaser sample using new function to script User Defined Data Types:

[sourcecode language=”powershell”]
## – Setting variables:
$SQLInstanceName = "YourSQLinstanceName"
$SourcedbName = "AdventureWorks"
$DestinationdbName = "Developer"

## – Sample – Write a UserDefinedDataTypes script, and display results in console:
Write-TSQLScriptUserDefinedDataTypes -SQLInstanceName $SQLInstanceName -DatabaseName $SourcedbName `
-DestinationdbName $DestinationdbName -DropUDDTOnly
Write-TSQLScriptUserDefinedDataTypes -SQLInstanceName $SQLInstanceName -DatabaseName $SourcedbName `
-DestinationdbName $DestinationdbName -CreateUDDTOnly

## Save PS Object to a file, and then view it:
[string] $ScriptToFile = Write-TSQLScriptUserDefinedDataTypes -SQLInstanceName $SQLInstanceName -DatabaseName $SourcedbName `
-DestinationdbName $DestinationdbName
$ScriptToFile | Out-File c:\Temp\SQLScript01.sql
ii c:\Temp\SQLScript01.sql
[/sourcecode]

Sample executing Write-TSQLScriptUserDefinedDataTypes

Stay Tuned!!