MSSQL - A Simple Way to Create A Temp Demo Table

This is an interesting way to build a SQL table.  It might be useful when writing some sample code.

Instead of using create table and insert rows this simple sql does it all to create a temp table:

SELECT col1, col2

FROM (VALUES ('January', 1),('Feburary', 2),('March', 3),('April', 4),('May', 5)) AS tbl(col1, col2)

See the WHERE clause to see a way to referencing the temp table:

SELECT col1, col2

FROM (VALUES ('January', 1),('Feburary', 2),('March', 3),('April', 4),('May', 5)) AS tbl(col1, col2)

WHERE tbl.col1 = 'January'

Windows - Resolve External HD Readonly Issue

My external HD became readonly all of a sudden.  Here is the solution:

Disable Diskpart

If using a Windows computer, connect the WD hard drive and take note of its name in Windows Explorer. Work in the command-line utility in Windows to access and disable diskpart. To do this, open the command window from the Start menu, type "diskpart" in the command utility, and press enter. Then, type "list disk" to access all available disks on the computer. Locate the write-protected disk and type "select disk X," with X referring to the number associated with the disk. Finally, type "attributes disk clear readonly" and press enter. If write protection exists, then this removes the protection on the computer.

http://www.ebay.com/gds/How-to-Remove-Write-Protection-From-a-WD-External-Hard-Drive-/10000000205277025/g.html

SSRS - To Drill Through A Report in A Popup Window

="javascript:void(window.open('" + "http://SharePointServer/_vti_bin/reportserver?http%3A//SharePointServer/ReportFolder/ReportName.rdl" + "','Title','height=400,width=150,scrollbars=1'))"

BizTalk - AS2 Encryption/Signing

Message or MDN	Direction	Certificate Type	Certificate Owner	Public or Private	Certificate Location	Where to configure
Message	Outbound	Signing	Home Org	Private	Personal certificate store of in-proc host user	BizTalk Group / Properties / Certificate
Message	Outbound	Encryption	Partner	Public	Other People certificate store of local computer	Send port / Certificate
Message	Inbound	Signing	Partner	Public	Other People certificate store of local computer	Party / Certificate
Message	Inbound	Encryption	Home Org	Private	Personal certificate store of in-proc host user	Isolated Host / Certificates
MDN	Outbound	Signing	Home Org	Private	Synch MDN: Personal certificate store of isolated host user Asynch MDN: Personal certificate store of in-proc host user	BizTalk Group / Properties / Certificate
MDN	Inbound	Signing	Partner	Public	Other People certificate store of local computer	Party / Certificate

Forgot where this one from (probably a MS doc).  A simple description about AS2 protocol.

PowerShell - A Few Programming Tips

The first thing you need to do is make sure that Powershell is set to execute Powershell scripts, instead of only allowing interactive commands to be run in the Powershell environment.

set-executionpolicy RemoteSigned

To run the script from command line:

powershell -command "& c:\temp\test.ps1"

To send mail:

$SMTPClient = new-object System.Net.Mail.smtpClient

$SMTPClient.host = "localhost"

$MailMessage = new-object System.Net.Mail.MailMessage

$MailMessage.From = "me@email.com"

$MailMessage.To.add("you@email.com")

$MailMessage.Subject = "Test from powershell"

$MailMessage.Body = "Text body"

$SMTPClient.Send($MailMessage)

MSSQL - BulkLoad an External CSV File

1. Load a portion of file into a temp DB table to produce FMT file

-- Use this to read the CSV file and create a table; see the schema.ini design below to correctly load the file

SELECT * INTO MyTable FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Text;Database=c:\temp;HDR=Yes;', 'SELECT * FROM [External.csv]') temp

--If Text driver doesn't work then load the file into an Excel then read the file from Excel using OPENROWSET

SELECT * INTO LU_PennzoilBillingOLD FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\temp\External.xlsx;HDR=Yes;', 'SELECT TOP 10 * FROM [Sheet1$]') t

2. Produce external FMT file for future bulkload

-- Use this to create a format file from the DB table IN A COMMAND WINDOW

bcp DataWarehouse.dbo.TempTable format nul -T -c -f "c:\temp\External.fmt"

3. Use the following pieces of code for data refresh process

-- Use this to bulkload the CSV file into DB table

TRUNCATE TABLE LU_GE_Billing

BULK INSERT TempTable FROM 'c:\temp\External.csv' WITH (FORMATFILE = 'c:\temp\External.fmt', FIRSTROW=1, MAXERRORS=10)

Schema.ini is a file used to describe the External.csv file used by MS text driver.  Put the following content in a file named c:\temp\Schema.ini (same directory as your External.csv located):

[Temp.csv]

ColNameHeader=False

Format=Delimited(|)

MaxScanRows=0

Col1=Col1 Long

Col2=Col2 Long

Col3=InvoiceNum Text Width 20

Col4=Manufacturer Text Width 50

Col5=Distributor Text Width 50

Microsoft Text File Driver - https://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx

Windows - Move Windows Folders off Drive C:

This note is normally OK until you need to rescue your systems.  I normally burn a clean image and use that for restoring purpose so I move these folders away from C: as they grow too big.  


To move C:\Windowsd\SoftwareDistribution folder to D:

1. To disable hiberfil.sys by turning hineration off (if is it enabled)

• powercfg.exe /hibernate off

2.      To remove c:\windows\SoftwareDistribution

• net stop wuauserv and press enter
• copy C:\windows\SoftwareDistribution "D:\From C Drive"
• net start wuauserv and press enter

3.      Re-enable hibernation

• powercfg.exe /hibernate on

To move C:\Windowsd\Installer folder to D:

1.      Remove Installer folder

• move C:\Windows\Installer to "D:\From C Drive\Installer"

2. Make registry links to D:

• mklink /d C:\Windows\Installer  "D:\From C Drive\Installer"

Clean up Service Packs

1. DISM /online /Cleanup-Image /SpSuperseded