Tuesday, December 14, 2010

Outlook 2007 Tip: Color Code Messages

This is something I've always wanted in Outlook.  You can 'Categorize' emails and assign a color, but this does not change the color in the Outlook Inbox view.

The work around for me was to create a 'Custom Flag' with a 'Due Date' in the past.  This will turn the text of the email in the Inbox view to Red.

You can also set up some Advance Formatting Rules to change the Font(color, size, underline, bold) based on the Category, but this takes some time.  Here is the link for that: http://www.howto-outlook.com/howto/coloremailadvanced.htm

Here is a sample of what it looks like:

Monday, November 29, 2010

@schedule_uid is not a parameter for procedure sp_add_jobschedule

I received this error when trying to copy a SQL job from one server to another, by trying to script out the create statement:

Msg 8145, Level 16, State 1, Procedure sp_add_jobschedule, Line 0

@schedule_uid is not a parameter for procedure sp_add_jobschedule



The problem is that the parameters for the stored procedure, sp_add_jobschedule, have changed from SQL2005 to SQL2008, so you need to change the script to match.

- SQL 2005 uses @schedule_id (an integer) and SQL 2008 uses @schedule_uid (a uniqueidentifier)


- All you have to do is change the parameter name and data-type to the correct value
    If it's @schedule_uid, change it to @schedule_id.
    Then, find an un-used integer value in: select * from sysjobschedules (MSDB database)

- If you are going the other way, you can change the parameter to @schedule_uid, then create a new
   uniqueidentifier by running:  select newid()

Friday, November 26, 2010

Grails Bootstrapping Issue

I'm sure this is a newbie issue, but it's interesting to me.

I've created the Race applications from the book, "Getting Started With Grails, 2nd Edition" by Jason Rudolph.  After creating the Runner domain class, I add "package racetrack", then I try to bootstrap some data.



   1:  class BootStrap {
   2:  def init = { servletContext ->
   3:  def jane = new Runner(firstName:'Jane',lastName:'Doe')
   4:  jane.save()
   5:  }
   6:  def destroy = {}
   7:  }

But, when I run the application, I don't get any data populated and no error.

The answer was in #GRAILS-3842.  In order to create the data, you MUST set the values for "ALL PROPERTIES THAT ARE NOT NULLABLE".

So, when you create a new Runner(), you must supply values for all properties or mark the properties as "nullable:true".  Also, there is a new parameter for the save() method, save(failOnError:true).  This will cause the compilation to fail if the save() method in Bootstrap does not work.

Here is the new code:


   1:  class Runner {
   2:   
   3:  static constraints = {
   4:      firstName(blank:false)
   5:      lastName(blank:false)
   6:      dateOfBirth(nullable:true)
   7:      gender(inList:["M", "F"])
   8:      address(nullable:true)
   9:      city(nullable:true)
  10:      state(nullable:true)
  11:      zipcode(nullable:true)
  12:      email(email:true)
  13:  }
  14:  static hasMany = [registrations:Registration]
  15:      String firstName
  16:      String lastName
  17:      Date dateOfBirth
  18:      String gender
  19:      String address
  20:      String city
  21:      String state
  22:      String zipcode
  23:      String email
  24:      String toString(){
  25:          "${lastName}, ${firstName} (${email})"
  26:      }
  27:   
  28:   
  29:   
  30:   
  31:   
  32:   
  33:  class BootStrap {
  34:   
  35:      def init = { servletContext ->
  36:          def jane = new Runner(firstName:'Jane', lastName:'Doe', gender:'F',city:'Atlanta',state:'GA',email:'jane@toto.com')
  37:          jane.save(failOnError:true)
  38:          def joe = new Runner(firstName:'Joe', lastName:'Blow', gender:'M',city:'Atlanta',state:'GA',email:'joe@toto.com')
  39:          joe.save(failOnError:true)
  40:          def larry = new Runner(firstName:'Larry', lastName:'Eisenstein', gender:'M',city:'Lilburn',state:'GA',email:'larrye@toto.com')
  41:          larry.save(failOnError:true)
  42:   
  43:      }  
  44:   

Tuesday, November 16, 2010

Delete files based on Last Modified Date using FORFILES.exe

Deleting files based on last modified date or running some command on these files has been a real sticking point for Windows users.  Unix/Linux people would always hold some of those cool command line features over our heads to prove why Linux is better than Windows.

Windows Server 2000/2003/2008 has a command called ForFiles.exe (http://technet.microsoft.com/en-us/library/cc753551(WS.10).aspx).  This is a great tool once you get used to the sytnax.  You can find files based on Last Modified Date, then run a command(like del) on each of those files.


FORFILES  /P  E:\MSSQL\Backup  /S  /M  *.full.zip   /D -30   /C  “cmd  /c  del  @path”


/S – look at subdirectories
*.full.zip – that is the file pattern, you could have it be like *.txt for example
/D -30 = files greater than 30 days
/C – thats the command you want to run





Here is what I use in my SQL script:

   1:    declare @full_location varchar(8000)
   2:    SET @full_location = @backup_location + @backup_db_name 
   3:    SET @command = 'forfiles -p "' + @full_location + '" -s -m *.full.zip -d -1 -c "cmd /c move /y @path "' + @archive_destination
   4:    print @command
   5:    EXEC xp_cmdshell @command, NO_OUTPUT
   6:    --print @command
   7:   

I don't know why this isn't in Windows XP.  This is a great tool

Sunday, November 14, 2010

Deactivate Netflix on PS3

This has nothing to do with programming, but I just found a couple great tricks if you are having issues with your Netflix account on your PS3.


Deactivate Netflix from your PS3
1. Click the Netflix application
2. As soon as you do, hold Start & Select at the same time
3. This will allow you to deactivate Netflix from the PS3


Reset your Netflix account on the PS3
1. Start the Netflix account
2. You will be in the movie selections area(don't worry, this will work)
3. With the DPAD, hit these buttons:
UP, UP, DOWN, DOWN, LEFT, RIGHT, LEFT, RIGHT, UP, UP, UP, UP

4. VIOLA!!!  You can now log on as a different user.

Friday, November 12, 2010

Java Sorting: Comparator vs Comparable

I thought I'd repost this because it helped me a lot.  This article is by Kamal Mettananda and the link is: http://lkamal.blogspot.com/2008/07/java-sorting-comparator-vs-comparable.html

I just wanted to copy it in case this link disappears.  Thanks Kamal!


Java Comparators and Comparables? What are they? How do we use them? This is a question
we received from one of our readers. This article will discuss the java.util.Comparator
and java.lang.Comparable in details with a set of sample codes for further
clarifications.




Prerequisites


  • Basic Java knowledge



System Requirements


  • JDK installed



What are Java Comparators and Comparables?

As both names
suggest (and you may have guessed), these are used for comparing objects in Java. Using
these concepts; Java objects can be

sorted according to a predefined order.



Two of these concepts can be explained as follows.


Comparable

A comparable object is capable of comparing
itself with another object. The class itself must implements the java.lang.Comparable
interface in order to be able to compare its instances.




Comparator

A comparator object is capable of comparing
two different objects. The class is not comparing its instances, but some other
class's instances. This comparator class must implement the
java.util.Comparator interface.




Do we need to compare objects?

The simplest answer is yes. When there is a
list of objects, ordering these objects into different orders becomes a must in some
situations. For example; think of displaying a list of employee objects in a web page.
Generally employees may be displayed by sorting them using the employee id. Also there
will be requirements to sort them according to the name or age as well. In these
situations both these (above defined) concepts will become handy.




How to use these?

There are two interfaces in Java to
support these concepts, and each of these has one method to be implemented by
user.

Those are;



java.lang.Comparable: int compareTo(Object o1)

This method compares this object with o1 object. Returned int value has the following
meanings.

  1. positive : this object is greater than o1
  2. zero : this object equals to o1
  3. negative : this object is less than o1


java.util.Comparator: int compare(Object o1, Objecto2)

This method compares o1 and o2 objects. Returned int value has the following meanings.

  1. positive : o1 is greater than o2
  2. zero : o1 equals to o2
  3. negative : o1 is less than o1


java.util.Collections.sort(List) and java.util.Arrays.sort(Object[])
methods can be used to sort using natural ordering of objects.

java.util.Collections.sort(List, Comparator) and
java.util.Arrays.sort(Object[], Comparator) methods can be used if a Comparator
is available for comparison.



The above explained Employee example is a good candidate for explaining these two
concepts. First we'll write a simple Java bean to represent the
Employee.




public class Employee {
    private int empId;
    private String name;
    private int age;

    public Employee(int empId, String name, int age) {
        // set values on attributes
    }
    // getters & setters
}


Next we'll create a list of Employees for using in different sorting
requirements. Employees are added to a List without any specific order in the following
class.




import java.util.*;

public class Util {
    
    public static List<Employee> getEmployees() {
        
        List<Employee> col = new ArrayList<Employee>();
        
        col.add(new Employee(5, "Frank", 28));
        col.add(new Employee(1, "Jorge", 19));
        col.add(new Employee(6, "Bill", 34));
        col.add(new Employee(3, "Michel", 10));
        col.add(new Employee(7, "Simpson", 8));
        col.add(new Employee(4, "Clerk",16 ));
        col.add(new Employee(8, "Lee", 40));
        col.add(new Employee(2, "Mark", 30));
        
        return col;
    }
}



Sorting in natural
ordering

Employee's natural ordering would be done according to
the employee id. For that, above Employee class must be altered to add the comparing
ability as follows.




public class Employee implements Comparable<Employee> {
    private int empId;
    private String name;
    private int age;
    
    /**
     * Compare a given Employee with this object.
     * If employee id of this object is 
     * greater than the received object,
     * then this object is greater than the other.
     */
    public int compareTo(Employee o) {
        return this.empId - o.empId ;
    }
    
}


The new compareTo() method does the trick of implementing the natural ordering of the
instances. So if a collection of Employee objects is sorted using
Collections.sort(List) method; sorting happens according to the ordering done inside
this method.



We'll write a class to test this natural ordering mechanism.
Following class use the Collections.sort(List) method to sort the given list in natural
order.




import java.util.*;

public class TestEmployeeSort {
    
    public static void main(String[] args) {     
        List coll = Util.getEmployees();
        Collections.sort(coll); // sort method
        printList(coll);
    }
    
    private static void printList(List<Employee> list) {
        System.out.println("EmpId\tName\tAge");
        for (Employee e: list) {
            System.out.println(e.getEmpId() + "\t" + e.getName() + "\t" + e.getAge());
        }
    }
}


Run the above class and examine the output. It will be as follows. As you can see, the
list is sorted correctly using the employee id. As empId is an int value, the employee
instances are ordered so that the int values ordered from 1 to 8.




EmpId Name Age
1 Jorge 19
2 Mark 30
3 Michel 10
4 Clerk 16
5 Frank 28
6 Bill 34
7 Simp 8
8 Lee 40



Sorting by other fields

If we need to sort using other
fields of the employee, we'll have to change the Employee
class's compareTo() method to use those fields. But then
we'll loose this empId based sorting mechanism. This is not a good
alternative if we need to sort using different fields at different occasions. But no
need to worry; Comparator is there to save us.



By writing a class that implements the java.util.Comparator interface, you can sort
Employees using any field as you wish even without touching the Employee class itself;
Employee class does not need to implement java.lang.Comparable or java.util.Comparator
interface.


Sorting by name field

Following EmpSortByName class is used to sort
Employee instances according to the name field. In this class, inside the compare()
method sorting mechanism is implemented. In compare() method we get two Employee
instances and we have to return which object is greater.




public class EmpSortByName implements Comparator<Employee>{

    public int compare(Employee o1, Employee o2) {
        return o1.getName().compareTo(o2.getName());
    }
}


Watch out: Here, String class's compareTo() method is used in
comparing the name fields (which are Strings).



Now to test this sorting mechanism, you must use the Collections.sort(List, Comparator)
method instead of Collections.sort(List) method. Now change the TestEmployeeSort class
as follows. See how the EmpSortByName comparator is used inside sort method.




import java.util.*;

public class TestEmployeeSort {
    
    public static void main(String[] args) {
        
        List coll = Util.getEmployees();
        //Collections.sort(coll);
        //use Comparator implementation
        Collections.sort(coll, new EmpSortByName());
        printList(coll);
    }
    
    private static void printList(List<Employee> list) {
        System.out.println("EmpId\tName\tAge");
        for (Employee e: list) {
            System.out.println(e.getEmpId() + "\t" + e.getName() + "\t" + e.getAge());
        }
    }
}


Now the result would be as follows. Check whether the employees are sorted correctly by
the name String field. You'll see that these are sorted
alphabetically.




EmpId Name Age
6 Bill 34
4 Clerk 16
5 Frank 28
1 Jorge 19
8 Lee 40
2 Mark 30
3 Michel 10
7 Simp 8

Sorting by empId field

Even the ordering by empId (previously done using
Comparable) can be implemented using Comparator; following class

does that.




public class EmpSortByEmpId implements Comparator<Employee>{

    public int compare(Employee o1, Employee o2) {
        return o1.getEmpId() - o2.getEmpId();
    }
}



Explore further

Do not stop here. Work on the followings
by yourselves and sharpen knowledge on these concepts.


  1. Sort employees using name, age, empId in this order (ie: when names are equal,
    try age and then next empId)
  2. Explore how & why equals() method and compare()/compareTo() methods must be
    consistence.

Thursday, September 23, 2010

Truncate Log Files and Recover Space

We have had many issues on our database server with space. The databases are growing faster than we can purchase space and there is also a lot of issues with people wanting to store backups locally for quick restores.

There is a really cool(free) tool that helps you analyze which file and directories are taking up all your space. It's called WinDirStat and you can download it here: http://sourceforge.net/projects/windirstat/

It will help you find where all your space is going and if there are any obscure files laying around (temp files) that no one uses, but are taking Gigs of space.




Using this tool, I was able to find that TempDB was taking over 94GB and it's log file was 20GB. We just use a Simple backup scheme and don't really need to recover databases with transaction files. We just restore the previous day's information.

I also noticed a few other database logs had grown large. Now, I know this tool doesn't take the place of a proper database maintenance plan, but we don't have any good DBAs. So, this will have to do.




   1:  USE DatabaseName
   2:  GO
   3:  BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
   4:  DBCC SHRINKFILE(<TransactionLogName>, 1) --LOGICAL NAME FOUND IN PROPERTIES
   5:  GO 

Wednesday, September 22, 2010

Deleting a Single Duplicate Row

I had a coworker just ask me this and I remember having to do this a while ago.

Case: You have a table that has 2 identical rows because there are no constraints to prevent this. You want to delete one of the entries (not both).




   1:  SET COUNT 1
   2:  DELETE FROM ProductTable WHERE product_name = 'Widget Model 3000'
   3:  SET COUNT 0


It will only delete one of the products with the name 'Widget Model 3000'

Friday, September 17, 2010

SqlServer 2008 Connection Refused to Grails

I was setting up a test application on my development machine and I had everything configured, but the application would not start.  After reviewing the error message, somewhere near the bottom, it said "Connection Refused".

The machine was running Windows 7, Sql Server 2008, and Grails 1.3.4. I was using the jTDS JDBC Driver 1.2.5 to connect to the database (just drop the jar in the lib directory).  After searching for about 30 minutes, I found that TCP/IP access in SQL Server 2005/2008 is disabled by default.

To enable it, you can do this:



SQL Server Configuration Manager
-->SQL Server 2005 Network Configuration
-->TCP/IP
-->IP  Address
-->TCP port


You'll need to enable it, set the port, set the IP, then restart the SQLServer service.


Here is what my DataSource.groovy looks like:


dataSource {
    pooled = true
    driverClassName ="net.sourceforge.jtds.jdbc.Driver"
    dialect = "org.hibernate.dialect.SQLServerDialect"
}

hibernate {
    cache.use_second_level_cache=true
    cache.use_query_cache=true
    cache.provider_class="com.opensymphony.oscache.hibernate.OSCacheProvider"
    connection.pool_size=10
}

// environment specific settings
environments {
    development {
        dataSource {
            url = "jdbc:jtds:sqlserver://laptop_win7:1433;databaseName=gpay_DEV"
            username = "GPayAdmin"
            password = "MyPassword123!"
            
        }
    }
}

native2ascii error in Grails

When I create a new Grails app, I always get the native2ascii error.

In the past, I've always on into Config.groovy and changed this line to false:

grails.enable.native2ascii = true


Now, I've found a way to make sure the error does not show up at all.

Copy the tools.jar file from the JDKPATH/lib directory to the JREPATH/lib/ext directory.

copy %Java_Home%/lib/tools.jar to %Java_Home%/jre/lib/ext/tools.jar fixed this problem..


Thanks to grailslog!

Friday, September 3, 2010

SQL NOLOCK Example

A co-worker just gave this to me to illustrate "nolock" and its pretty cool.

Purpose: If you are doing SELECTs and data accuracy is not extremely important, use "nolock" to avoid conflicts with other users.

If you have long running processes that do a lot of SELECTs, this can dramatically improve performance.




   1:  create table testdb.dbo.NoLockTest
   2:  (
   3:      ID int identity(1,1),
   4:      Product     varchar(20),
   5:      SalesDate   datetime,
   6:      SalesPrice  int
   7:  )
   8:   
   9:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('PoolTable', GETDATE(), 200)
  10:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('MyTable', GETDATE(), 500)
  11:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('GeoffTable', GETDATE(), 400)
  12:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('LarryTable', GETDATE(), 250)
  13:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) VALUES ('StephenTable', GETDATE(), 360)
  14:   
  15:  select * from testdb.dbo.NoLockTest
  16:   
  17:  -- Query 1
  18:  BEGIN TRANSACTION 
  19:  INSERT INTO testdb.dbo.NoLockTest (Product, SalesDate, SalesPrice) 
  20:  VALUES ('PoolTable', GETDATE(), 1000)
  21:   
  22:  -- Query 2 will continue to run until ROLLBACK TRANSACTION OR COMMITE does not apply to any INSERT/UPDATE/DELETE stmt.
  23:  -- only way to stop the query is to use "Cancel Executing Query" from SSMS Menu.
  24:  select count(*) from testdb.dbo.NoLockTest 
  25:   
  26:  -- Query 3 will allow  you to pull data even though there is a lock on the table due to INSERT/UPDATE/DELETE stmt running
  27:  -- in this case we have from Query 1 
  28:  select count(*) from testdb.dbo.NoLockTest with(nolock)
  29:   
  30:  -- Query 4 -- execute below statement.
  31:  ROLLBACK TRANSACTION
  32:   
  33:  -- Query 5
  34:  select count(*) from testdb.dbo.NoLockTest 

Wednesday, August 18, 2010

ASP.Net file too large or exceeded buffer (ASP0251)

I've run into this problem a few times.  The problem is ASP's default is to limit the file buffer to 4MB.  If you get a file (or response) larger than that, it will choke.

In IIS6/7, you can run this command:
  • Click Start, click Run, type cmd, and then click OK.
  • cd /d %systemdrive%\inetpub\adminscripts
  • cscript.exe adsutil.vbs SET w3svc/aspbufferinglimit 20000000

I just found the setting in IIS7.5 in IIS Manager:

Sites -> Default Web Site -> Choose your site
Double Click on the "ASP" feature
Expand "Limits Properties"
Change the "Response Buffering Limit"

It defaults to : 4194304 (4MB)
I changed it to 10000000 (10MB) and clicked Apply, but still got the error
Then, I changed it to 20000000 (20MB), and the error went away

Wednesday, August 4, 2010

Database Restore Script

This is a quick database restore script.  It sets the database to SINGLE_USER so you don't get any "database in use" errors, then restores the database, then sets it back to MULTI_USER.

You need to run all the commands together.  It didn't work for me when I tried to run them separately.



   1:  ALTER DATABASE MyAppDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   2:    RESTORE DATABASE MyAppDB FROM DISK = 'E:\DBBackups\MyApp_db_201008032143.BAK' WITH REPLACE
   3:    ALTER DATABASE MyAppDB SET MULTI_USER

Wednesday, July 14, 2010

SQL DateTime Queries

Here are some basic DateTime queries to get the date you need.



declare @BeginDate datetime;
set @BeginDate = getdate(); 
 
select dateadd(dd, datediff(dd, 0, @BeginDate), 0)     -- Beginning of this day
select dateadd(dd, datediff(dd, 0, @BeginDate) + 1, 0) -- Beginning of next day
select dateadd(dd, datediff(dd, 0, @BeginDate) - 1, 0) -- Beginning of previous day
select dateadd(wk, datediff(wk, 0, @BeginDate), 0)     -- Beginning of this week (Monday)
select dateadd(wk, datediff(wk, 0, @BeginDate) + 1, 0) -- Beginning of next week (Monday)
select dateadd(wk, datediff(wk, 0, @BeginDate) - 1, 0) -- Beginning of previous week (Monday)
select dateadd(mm, datediff(mm, 0, @BeginDate), 0)     -- Beginning of this month
select dateadd(mm, datediff(mm, 0, @BeginDate) + 1, 0) -- Beginning of next month
select dateadd(mm, datediff(mm, 0, @BeginDate) - 1, 0) -- Beginning of previous month
select dateadd(qq, datediff(qq, 0, @BeginDate), 0)     -- Beginning of this quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @BeginDate) + 1, 0) -- Beginning of next quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @BeginDate) - 1, 0) -- Beginning of previous quarter (Calendar)
select dateadd(yy, datediff(yy, 0, @BeginDate), 0)     -- Beginning of this year
select dateadd(yy, datediff(yy, 0, @BeginDate) + 1, 0) -- Beginning of next year
select dateadd(yy, datediff(yy, 0, @BeginDate) - 1, 0) -- Beginning of previous year