Finding Duplicates in MS SQL Server

Here is a quick snippet on how to find duplicates:

SELECT a.[dr_id]

      ,a.[sch_type]

      ,COUNT(*) as '# records'

  FROM [LZ].[dbo].[lz_src_address1] a

  where a.sch_type = '23'

  group by a.dr_id, a.sch_type

  having COUNT(*) > 1

You only include the fields that you need to determine if its a duplicate and put them in the GROUP BY clausing.  the HAVING clause tells you if there is more than 1 record.

There are several ways to delete duplicates.  They way you choose will depend on if you have a unique key for each row.  If so, you can delete them with a single statement.  If not, you may need to use a cursor and/or temp tables.

MS SQL UPDATE with a JOIN

This describes how to UPDATE a table when you have to JOIN another table.  This is for MS SQL Server, so the syntax varies with other versions.

In this example, I am updating the [aspnet_Membership] table, but I want to join the [aspnet_Users] table so I can do it user UserName.  This script allows me to set the password of a user to my password so I can log in as that user.  This is done by updating the ASP.NET Membership table.  I would need to store the [Password] and [PasswordSalt] of this account if I wanted to change it back.

UPDATE m 

SET   m.[Password] = 'mrhLMUfIWCuTHDwOtm1s/I9ABMQsS=='

    , m.[PasswordSalt] = 'suyp+Nsd2lmMPpQ=='

FROM [dnnDB].[dbo].[aspnet_Membership] m

JOIN [dnnDB].[dbo].[aspnet_Users] u on m.UserId = u.UserId

WHERE 1=1

  AND u.UserName in 

  (

      'FirstUser'

    , 'SecondUser'

    , 'ThirdUser'

  )

View the Contents of a DataTable or DataView While Debugging

I always forget this because I use four different programming languages(3 different IDEs), so I'll put it up here in case I forget ...... again!  Also, credit to Rob Prouse (on StackOverflow).

The Visual Studio debugger comes with four standard visualizers. These are the text, HTML, and XML visualizers, all of which work on string objects, and the dataset visualizer, which works for DataSet, DataView, and DataTable objects.
To use it, break into your code, mouse over your DataSet, expand the quick watch, view the Tables, expand that, then view Table[0] (for example). You will see something like {Table1} in the quick watch, but notice that there is also a magnifying glass icon. Click on that icon and your DataTable will open up in a grid view.

ASPX Hyperlinks: Opening automatically and in New Windows

Here's a couple quick tips that took me some time to work out.

1. Opening a Hyperlink in a new window:

ASPX

<asp:HyperLink ID="jiraLink" 

runat="server">&nbsp;&nbsp;&nbsp;&nbsp;Click here to Expand Window </asp:HyperLink>

Code-behind:

String jiraURL = "http://" + jiraServerURL +"/jira/secure/IssueNavigator.jspa?sId=" + sessionGUID;

jiraLink.NavigateUrl = jiraURL;

jiraLink.Target = "_blank";

And, to automatically open a Hyperlink, put this in the Page_Load:

Response.Write("<script type='text/javascript'>window.open('" + serverURL+ "','_blank');</script>");

DotNetNuke Update Custom Profile fields

I had a good bit of trouble with this, but I was able to get it working.

You may need to make sure the custom property you have created is marked Visible, but this is the code that ended up working for me in DNN 6.1.2.

UserInfo _currentUser = DotNetNuke.Entities.Users.UserController.GetCurrentUserInfo();

// Create Token

string sessionGuid = TokenLibrary.TokenSupport.CreateToken(_currentUser).ToString();

if (sessionGuid == null || (sessionGuid.Trim().Length < 1 )

sessionGuid = "00000000-0000-0000-0000-000000000000";

// Set User Property called "sessionGUID"

_currentUser.Profile.SetProfileProperty("sessionGUID", sessionGuid);

UserController.UpdateUser(_currentUser.PortalID, _currentUser);

Bulk File Renaming Utility

I found a great free tool and thought I'd share it here. It's called "Bulk Rename Utility" and can be downloaded from http://www.bulkrenameutility.co.uk/Command.php.

This utility is GUI based, but the link above is the command-line version. I also highly recommend you download the manual to see all the features it has.


When you download the command line version, it is called BRC64.exe ( and they have a 32bit version). I just dropped this into the Windows system directory so its available from the command line no matter what directory I am in.

If you type: BRC64.exe /? , you will get all the command line options.
The main concept is if you run the command with the options, it WILL NOT MODIFY the filenames until you add the /EXECUTE option. So, you can play around with all the options and it will show you what WOULD HAPPEN, then you add /EXECUTE to make it happen.

Examples:

-- This will insert ACME in front of all .X01 files, then remove the .X01 and add .txt as the suffix
brc64.exe /PATTERN:*.X01 /INSERT:ACME-:0 /REMOVEEXT /SUFFIX:.txt /EXECUTE


-- This will replace 2012 with 2011 in all .txt file and change .txt to .asc
brc64.exe /PATTERN:*.txt /REPLACECI:2012:2011 /REMOVEEXT /SUFFIX:.asc /EXECUTE



There are a lot more features, but you get the idea. Just remember, it WILL NOT perform the rename until you add the /EXECUTE, so you can experiment all day.

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"