Grails 2.0 SQL Server Configuration

I just created my first test application with Grails 2.0RC1 and used SQL Server 2008 as the database.

Here are the quick steps:

1. Install JDK/JRE (I used 1.6.latest)
2. Unzip Grails 2.0RC1 to an C:\grails2rc1\ directory
3. Create environment variables for JAVA_HOME(point to jdk install)
4. Create environment variables for GRAILS_HOME(point to grails directory)
5. Add the following to PATH environment variable: ";%JAVA_HOME%\bin;%GRAILS_HOME%\bin"
6. Test that you can call "javac" and "grails --help" from C:\

7. Create a database for grails from Management Studio
8. Create a user/pass and give it access to this database(I usually give it dbo and sysadmin during the initial install phase. You should dial it back after that)
9. ** TRICK ** Make sure TCP/IP is ENABLED using SQL Server Configuration Manager, under SQL Server Network Configuration -> Protocols for MSSQLSERVER

10. Create a directory for your app (C:\work\apps)
11. Open a command prompt and "cd" to that directory
12. "grails create-app myapp" (This creates the Grails structure)

13. Download the latest jTDS Driver and extract the files to a temp directory
14. Copy the jtds-1.2.x.jar file to the directory C:\mayapp\lib
15. Your conf/DataSource.groovy should look something like this:
dataSource {
pooled = true
driverClassName = "net.sourceforge.jtds.jdbc.Driver"
dialect = "org.hibernate.dialect.SQLServerDialect"
}
... other stuff
development {
dataSource {
dbCreate = "create-drop" // one of 'create', 'create-drop', 'update', 'validate', ''
url = "jdbc:jtds:sqlserver://127.0.0.1:1433;databaseName=grails2rc1"
username = "grailsadmin"
password = "your-Pass123"
// logSql=true
}
}
... more

16. Create your 1st controller: "grails create-controller dashboard"
17. Save, then run "grails run-app"

Backing Up SQL Database to Network Drive - Operating system error 5(Access is denied)

I ran into this issue the other day while trying to run a backup and store it on a network drive. At first, I thought it was a file permissions issue so I checked in the Security Settings and Audit logs, but no matter what permissions I changed, it still did not work.

The issue ended up being that the account that the SQL Server service runs under needs to be a Domain account and have the proper permissions. There was also a few other configuration issues I stumbled across.

1. Local Security Policy: Under the Local Security Policy(or Group Policy), there is a policy called "Lock pages in memory" and this needs to be enabled for the domain service account that will be used for the SQL Server service (I also changed the SQL Agent service).

2. The next steps were to allow us to reconfigure SQL Server and allow the SQL Agent to run as domain account:

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'awe enabled', 1

RECONFIGURE;

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'Agent XPs', 1;

RECONFIGURE

sp_configure 'show advanced options', 0;

RECONFIGURE

3. Change your SQL Server Service and SQL Agent service Logon account to a domain account with privileges on the network drive.
Open Services -> Choose the Service -> Logon tab -> Choose "This Account" and enter DOMAIN\ACCT and enter the password.

Scheduled Task Not Running a Batch Job

I've recently ran into this several times, so I'm thought I'd document it here.

I've created batch jobs (.bat) that do various things, like copy, move, delete files, download stuff from SFTP servers, etc. In the past, I've just used my credentials to run these jobs, but if I changed my password, the jobs would fail. Now, our IT department created a service account for me to Schedule these tasks, but I've had many issues with Scheduled Tasks not working once I changed the credentials to the service account.

The scheduled task will not show an error code, it just won't work or it will be stuck in a "running" state.

Here is what I have found out:

1. The service account should be a local admin
2. Delete your old Scheduled Task
3. Log onto the server using the service account credentials
4. Re-Create the Scheduled Task while logged on as the service account and use the service account credentials

This applies to Scheduled Tasks that run a .BAT file. All my other scheduled tasks work normally.

Migrating users to DotNetNuke

I've been on a 2 month hiatus, but I've come back with some new skills.  I've been working on a DotNetNuke portal to replace our existing MojoPortal.  I really wanted to like MojoPortal because of it's simplicity and ease of setup, but in the end MojoPortal Core development pretty much came to a halt.  There are just too many standard features that are missing, plus DNN has so much out of the box, I couldn't resist.  Plus, the community behind DNN is huge.

While browsing Mitchel Sellers' blog, I found some SQL that allows me to migrate the 700+ user accounts in our MojoPortal to DotNetNuke.  I've put it in a script and parametized it.

After I created this, I created a table from my old MojoPortal, which contains: UserName, FirstName, LastName, DisplayName, Email, and RoleName (Primary Role, which must exist in DNN before running the script).  Then, I ran it through a cursor and added all the users.

** ADDITION NOTE:  If you are using {owner}{prefix} in DNN, you'll need to modify the script.

-- =======================================================================================================

-- Author:        Larry Eisenstein

-- Create date: 2/15/2011

-- Description:    Creates a DNN User by copying an existing user.

-- This can be used to script a single account creation or migrating users from another system.

-- This works by creating the NewUser from an existing user.

-- The script was pulled from Mitchel Sellers website.  He has a great blog, so you should visit it.

-- http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/84/creating-a-standard-dotnetnuke-user-via-sql.aspx

-- 

-- 

-- Req: 

-- 1. Know the Username/Password of an existing user. The password for the user you create will be the password 

-- of this user

-- 2. If you are assigning Roles, the RoleName MUST existing in the DotNetNuke site

-- 

-- Defaults:  I set up some defaults, but these can be changed via params or just change the defaults in the script.

-- 

-- Notes: 

-- Stored Procs used: aspnet_Membership_CreateUser

-- Tables Updated: users, Roles, UserPortals

-- The password works by copying the encrypted password, passwordsalt of the ExistingUser to your new user.  Then, you can

-- just login with that user's password.

-- ==========================================================================================================

CREATE PROCEDURE [dbo].[AAA_MigrateUser_sp]

@ApplicationName varchar(255) = 'DotNetNuke',  -- Search for applicationName in your web.config

@ExistingUserName varchar(255) = 'TestUser',   -- This must be an existing DNN user

@FirstName varchar(255) = 'Migrated',            

@LastName varchar(255) = 'UserAccount',

@DisplayName varchar(255) = 'Migrated UserAccount',

@NewUserName varchar(255),

@RoleName varchar(255) = 'Registered Users',

@PortalId int = 0,

@Email nvarchar(256) = 'TestUser@email.org'

AS

BEGIN

DECLARE @PasswordQuestion varchar(256),

@PasswordAnswer varchar(256),

@Pw varchar(255),

@PasswordSalt varchar(255),

@PasswordFormat int,

@IsApproved bit,

@CurrentTimeUtc datetime,

@CreateDate datetime,

@UniqueEmail int,

@UserId uniqueidentifier,

@DNNUserId int,

@NumUsers int

SELECT    @PasswordQuestion = '',

@PasswordAnswer = '',

@IsApproved = 1,

@CurrentTimeUtc = GETDATE(),

@CreateDate = @CurrentTimeUtc,

@UniqueEmail = 0

SELECT @NumUsers = COUNT(*)

FROM aspnet_Users

WHERE UserName = @NewUserName

IF(@NumUsers != 0)

return -1

SELECT    @Pw = m.password,

@PasswordSalt = m.passwordsalt,

@PasswordFormat = m.passwordformat

FROM aspnet_users u

INNER JOIN aspnet_membership m  ON (u.userid = m.userid)

WHERE u.UserName = @ExistingUserName

-- Make the stored procedure call

EXEC dbo.aspnet_Membership_CreateUser @ApplicationName, @NewUserName, @Pw,

@PasswordSalt, @email, @passwordquestion, @PasswordAnswer, 

@IsApproved, @CurrentTimeUtc, @CreateDate, @UniqueEmail,

@PasswordFormat, @UserId

-- Insert the record into the DotNetNuke users table

INSERT INTO users (Username, FirstName, LastName, IsSuperUser, Email,

DisplayName, UpdatePassword)

VALUES(@NewUserName, @FirstName, @LastName, 0, @Email, @DisplayName, 0)

-- Get the new userid, from the DNN users table

SELECT @dnnuserid = userid

FROM Users

WHERE username = @NewUserName

-- Now, insert the record into the user portals table

INSERT INTO UserPortals (userId, PortalId, CreatedDate)

VALUES(@dnnuserid, @PortalId, GETDATE()) 

-- Now Give the user permissions to the User Group you specified

IF(@RoleName != 'Registered Users' and @RoleName IS NOT NULL)

BEGIN

INSERT INTO UserRoles (userId, roleId)

SELECT @dnnuserid,

roleId

FROM Roles

WHERE RoleName = @RoleName

END

-- Now Give the user permissions to the REGISTERED Users group

INSERT INTO UserRoles (userId, roleId)

SELECT @dnnuserid,

roleId

FROM Roles

WHERE RoleName = 'Registered Users'

END

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:

@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()

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: