Zipping Using Commands Through terminal

Here's the command to zip a folder or file using the terminal in OSX

zip -r ToBeNameOfZipFile.zip FolderName

Getting Just Date from DateTime

Here it is...

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

Thanks Mr. Lubarsky

Triggers

So, learned something interesting the other day about triggers.

Lets say you have a stored procedure that receives a handful of rows as mine did via XML. These rows could contain entries that need to be inserted into table A, others rows may contain entries that should update already existing entries in table A. So net net there could be 3 situations case where all rows are updates, all rows are new entries and must be inserted, or some rows are updates and some are inserts. My initial approach to this was as follows.

Insert Transactions
(
ID
, Date
, Qty
, Price
, Commission
)
select ID, Date, Quantity, Price, Commission,
from
@SomeTempTable tmp
where
ID not in (select ID from Transactions t where t.Date = tmp.date)

update Transactions
set
Price = tmp.Price
, Qty = tmp.Quantity
, Commission = tmp.Commission
from
@SomeTempTable tmp
, Transactions t
where
t.ID = tmp.ID and t.Date = tmp.date

Pretty simple stuff right? A simple update for existing entries and an insert for new ones. The complication came when the triggers came into play. One for Insert, another for update of the transaction table. I assumed that when there were no rows returned for the select (that feeds the insert) that no entries would be added, and thus there would be nothing for the trigger to do, if it in fact fired at all.

Turns out I was wrong.

My trigger looked something like this.

Declare @ID as int
select @ID = (Select ID from Inserted)
Declare @Date as datetime
select @Date = (select Date from Inserted)
Declare @Qty as float
select @Qty = (select Qty from Inserted)
Declare @Price as money
select @Price = (Select Price from Inserted)

(I then inserted these variables into another table.)

The trigger still fired, and because there was nothing in the INSERTED table to feed the trigger, those values returned as null. And because none of the columns in my table allowed nulls it threw a sql exception. For the longest time I couldn't admit to myself that the trigger was indeed firing w/ nothing to fill the variables, and the problem took a while to figure out.

So the lesson learned here is that if you have a trigger on a db event, it always fired when that keyword is executed, even if the execution of the keyword doesn't actually "do" anything.

IF...ELSE Blocks

If Else Blocks are incredibly simple and can be rather helpful at times. This is the general syntax.

IF [Boolean Expression]
   BEGIN
  Print 'in if'
END
ELSE
  IF [Boolean Expression]
   BEGIN
  PRINT 'in else'
END

Cursors

You need a variable to hold the cursor value each time, so declare that first. Afterwards, we declare the cursor and set a one column returning select statement. These are teh values we will curse through... after this we'll do a couple things that assure the list of things we're cursing through hasn't ended, then we'll get into contents of what happens at each itteration, and then we'll wrap up by deallocating the cursor.

DECLARE @AuthorID char(11)

DECLARE c1 CURSOR FOR
SELECT au_id
FROM authors

OPEN c1

FETCH NEXT FROM c1
INTO @AuthorID

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @AuthorID

FETCH NEXT FROM c1
INTO @AuthorID

END

CLOSE c1
DEALLOCATE c1

And that's the gist of it. Source is here.

Assigning Variables With Select

declare @tablename varchar(100)
select @tableName = (select distinct TableName from aTable)

select @tablename

PGP inside a SSIS Package

So, you've got an SSIS package that moves data from one source, let's say an FTP site, and move it into your database. Because the data you're grabbing from the FTP is "sensitive" it's encrypted... well kinda. It's encrypted using PGP (Pretty Good Privacy). PGP is great for stuff that's pretty important, but not worth being too hardward intensive.

So Download This file

This will give you the GNU's PGP instal file.

This link will walk you through setting it up, adding needed bits to the PATH system variable, creating a key, exporting that key, and then sending the new key to someone who's going to be encyrpting data with this key.

Now that it's installed, you've got a key, and you know the steps you need to take to encrypt or decrypt the file.

Step One. Create an Execute Process Task.Drag one of these puppies onto the work area.

A double click will bring up the General Details.
A click on the Process Item on the left, below the highlighted General.
Here we find some things that are actually interesting.
It should look as follows.
The fields we're going to be looking at are as follows:
Executable - the gpg.exe executable, can be hard coded, default location is
C:\Program Files\GNU\GnuPG\gpg.exe

Arguments - this will the the command line args that you would use if you were running this
command in the DOS prompt. This will follow this general form.
--passphrase-fd 0 --decrypt-files F:\filepath\file.asc
"--passphrase-fd 0" indicates that the passphrase is going to be coming in as
an input variable.
"--decrypt-files" obviously tells us that we're going to decrypt a file at the following
location.
StandardInputVariable - this is your password. Feel free to make this a variable to allow
the password to be passed in from a config file.
FailTaskIfReturnCodeIsNotSuccess - I recomend setting this to true, it makes things slightly
easier to debug.
SuccessValue - I kinda cheat here and set this to 2. This is because the process returns a
warning. The warning is issued because gpg cannot check the signature, and because
the message was not integrity protected. (If anyone out there has any thoughts on this
I'm open to suggestions)
WindowStyle - this can stay normal or you can make it hidden, I don't know when this will
actually be seen by a person, but who knows...

Your Process section Should look as follows:
One last thing to investigate is the Expressions section. In my case the ftp file that I was downloading every day had a changingfilename such as "filename20070101". Where 1/1/2007 was today's date. This meant I needed some logic to change that filename, so I used expressions here. I also could have altered this variable from the location it was called in the code. But I chose to do everything in the SSIS package.

I defined the Arguments expression as follows:

In this case I've got an ugly variable called @FormattedDate which is defined in an abnoxous way as follows:

DATEPART("weekday", GETDATE()) == 2 ? (DT_WSTR,10)(DatePart("yyyy", DATEADD( "day", -3, getdate() ) ) ) + ( DatePart("mm", DATEADD( "day", -3, getdate() ) ) < 10 ? "0" + (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -3, getdate() ) ) ) : (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -3, getdate() ) ) ) ) + ( DatePart("dd", DATEADD( "day", -3, getdate() ) ) < 10 ? "0" + (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -3, getdate() ) ) ) : (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -3, getdate() ) ) ) ) : (DT_WSTR,10)(DatePart("yyyy", DATEADD( "day", -1, getdate() ) ) ) + (DatePart("mm", DATEADD( "day", -1, getdate() ) ) < 10 ? "0" + (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -1, getdate() ) ) ) : (DT_WSTR,10)(DatePart("mm", DATEADD( "day", -1, getdate() ) ) )) + ( DatePart("dd", DATEADD( "day", -1, getdate() ) ) < 10 ? "0" + (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -1, getdate() ) ) ) : (DT_WSTR,10)(DatePart("dd", DATEADD( "day", -1, getdate() ) ) ) )

if it's January 1st, 2007 the variable would return: 20070101 (it does...just trust me...)

At this point, we've got a ready to roll decryption task. Enjoy.

SQL Conditional Logic

Here's my preferable way to do if else statements in T-SQL. The syntax is pretty simple.

select
case when conditional
then 'true case'
else 'false case'
end
as conditionalValue

Of course the comparisson opporators are <, =, <=, >, <>, >=, IS NULL, and IS NOT NULL.

Enjoy..

Inserting based on Select

I often forget this syntax.

INSERT tablename (value1, value2)
SELECT value1, value2
FROM someothertable
WHERE somewhereclause

I often forget this syntax. It of course inserts the row or rows returned by the select into the table given on the insert line.

Selecting Column Names, (and Info)

I've been working a lot with ugly CSV files with about 4 trillion rows. (Ok, not 4 trillion, but enough to get sick of them) I found myself looking for rows w/ given names, only to scroll through them when I wasn't going slowly enough. So to fix that I found this

select column_name, data_type, character_maximum_length from information_schema.columns
where table_name = 'myTable'

Which makes things much easier :)

NVARCHAR vs. VARCHAR

I always wondered what the difference was:

NVARCHAR is used to hold unicode characters used to hold multilingual data. It also takes up twice as much space to hold 1 character as it's VARCHAR counterpart, due to the need for those extra bits.

So for the most part...unless you've got a db in multiple languages, USE VARCHAR!

Disabling your Isight

To get rid of the creepy camera that you see at the top of your new shinny mac?

This link gives a good description
http://techslaves.org/index.php?page=10

Still looking for a way to disable the built in microphone in a similar way.

SQL Server 2005 DB Restore

I found myself getting the following error

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'MyServer'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: RESTORE cannot process database 'MYDB' because it is in use by this session. It is recommended that the master database be used when performing this operation. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

So after some googleing I found this.

USE master
RESTORE DATABASE MYDB
FROM DISK = 'C:\Documents and Settings\username\Desktop\myBackUpFile.bak'

this sets the table to master, and runs the same querry the wizard would. (Although this is a deprecated function)