SQL Server 2008 Linked Servers

Here's a quick little query for getting a look at your linked servers in SQL Server 2008

SELECT *
FROM SYS.LINKED_LOGINS L
JOIN SYS.SERVERS S
 ON S.SERVER_ID = L.SERVER_ID
ORDER BY S.PRODUCT, S.PROVIDER, S.DATA_SOURCE

Getting Started with SQL Server SMO (Server Management Objects)

A few commands to get you started exploring SQL Server SMO:

#load SMO into your Powershell session
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
#instantiate the server object against the instance of your choice
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
#see what the object can do
$sqlServer | get-member
#get a list of linked server entries on the server
$sqlServer.LinkedServers | format-table

Bulk importing CSV to SQL Server on a remote command line

Simple bulk insert from the command line to import a csv

SQLCMD -E -S myserver\myinstance -Q "BULK INSERT myDatabase.dbo.myTable FROM 'C:\rdbms_local_path\myFile.csv' WITH(FIELDTERMINATOR=',', ROWTERMINATOR='\n')"

speeding up Powershell Get-ChildItem

This Windows Powershell blog entry explains why Get-ChildItem is so slow, especially over networks. The fast workaround for now is to use the old cmd.exe

cmd.exe /c dir

The disadvantage is that your output comes back as one string per line, so you'll need to parse out the results. Here's an example including a network path and output that makes the results a little more helpful after noting that the header and footer information from the command all starts with whitespace

cmd.exe /c dir \\myserver\myshare\*.csv | where {$_[0] -ne ' '}
12/09/2012 02:06 PM 879,703 SOMEFILE.csv
07/08/2013 09:16 AM 61,293 SOMEOTHERFILE.csv

SSIS Expression for parsing out dateparts

My colleague Peter asked about embedding a date in the filename of an SSIS output. I gave him some general guiance about a couple of strategies, which he used to find this StackOverflow article(http://stackoverflow.com/questions/4372009/add-datestamp-to-a-txt-file-in-an-ssis-package) on the topic. The general idea is to use an expression in the connection's expressions like this:

"filename_" + RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("mm", GETDATE())), 2) + RIGHT("0" + (DT_STR, 2, 1252) (DATEPART("dd", GETDATE())), 2) + (DT_STR, 4, 1252) (DATEPART("yyyy", GETDATE())) + ".txt"

You can easily modify the above example to rearrange the various components to your needs, however us technical types often look at a format like YYYY-MM-DD, zero padded on month and day, of course, as a natural solution because sort alphabetically also sorts by date. You might also need to do that without dashes, so why not use a format that comes that way and remove the dashes?

"filename_" +  Replace(Substring((DT_WSTR, 50) GetDate(), 1, 10), "-", "") + ".txt"

I think the answer will be in localization settings for other regions, but if you're using a typical US installation, this will give you a nice sortable output.

Formatting a USB drive in FreeBSD

from http://forums.freebsd.org/showthread.php?t=19087

Create 1 slice covering the entire disk:
# fdisk -I /dev/devN

Create 1 partition covering the entire slice:
# bsdlabel -w devNs1

Create a filesystem on the "a" partition (soft-updates enabled):
# newfs -U /dev/devNs1a

Example for a device recognized as da0

# fdisk -I /dev/da0
# bsdlabel -w da0s1
# newfs -U /dev/da0s1a