netnerds.net

T-SQL: Find Duplicates Without Using JOIN

Chrissy — Thu, 15 May 2008 19:34:10 +0000

I'd forgotten about this lil trick.

select eUserName from eAttribute GROUP BY eUserName HAVING COUNT( eUserName) > 1

SQL Server: Change the Owner of All Tables to "dbo"

Chrissy — Thu, 08 May 2008 01:32:22 +0000

Reference for me. Reference for you.

sp_MSforeachtable @command1="EXEC sp_changeobjectowner '?','dbo'"

VBScript: Clean Up Imported SIM Card Contacts in Outlook

Chrissy — Fri, 11 Apr 2008 21:11:29 +0000

The other day, I was texting a friend and some guy looked at me and said "Oh hey, 2002 called, it wants its T9 back." Haha, slightly cliche but I laughed anyway. Then I went and bought myself a Blackberry.

I totally love it, I won't lie. So I imported my contacts into Outlook from a Sony Ericsson mobile phone and they came out kinda chu-chu. All the contacts came in as "Work" numbers and were all prefixed by ";\M". I cleaned the M's out manually but then wrote a script to list all the numbers as Mobile numbers. For consistency's sake, I also cleaned up all of the numbers themselves to just be, for instance, 2345551212. Here's the script in case you would like to do a mass cleanse as well.

Set objOutlook = CreateObject("Outlook.Application")

Set objNamespace = objOutlook.GetNamespace("MAPI")

 

Set colContacts = objNamespace.GetDefaultFolder(10).Items ' 10 = olFolderContacts

 

For Each objContact In colContacts

  if len(objContact.MobileTelephoneNumber) = 0 then

    objContact.MobileTelephoneNumber = objContact.BusinessTelephoneNumber

    objContact.BusinessTelephoneNumber = ""

    'Wscript.Echo objContact.FullName, objContact.BusinessTelephoneNumber, objContact.MobileTelephoneNumber 

    objContact.Save

  end if

  

  if len(objContact.MobileTelephoneNumber) > 0 then

    mobileNumber = objContact.MobileTelephoneNumber

    mobileNumber = replace(mobileNumber," ","")

    mobileNumber = replace(mobileNumber,"-","")

    mobileNumber = replace(mobileNumber,"(","")

    mobileNumber = replace(mobileNumber,")","")

    mobileNumber = replace(mobileNumber,"+1","")

    

    if left(mobileNumber,1) = "1" then mobileNumber = right(mobileNumber,len(mobileNumber)-1)

    objContact.MobileTelephoneNumber = mobileNumber

    objContact.Save

  End if

  

Next

Ouu my grammar has gone downhill since I spent a weekend in Louisiana.

SharePoint: Save Publishing Site as Template in MOSS 2007

Chrissy — Sun, 23 Mar 2008 02:01:53 +0000

Seems that Microsoft forgot to add a link to "Save site as Template" for Publishing sites in MOSS 2007, and it appears the problem is not addressed in SP1, either. Makes me wonder if the lack-of-link was intentional.

Nevertheless, in order to save any Publishing Site as a template, you must manually append the site template address "/_layouts/savetmpl.aspx" to the site URL. So for instance, if your site is http://intranet/offices/sandiego, you can save the template by manually browsing to http://intranet/offices/sandiego/_layouts/savetmpl.aspx

Thanks for the tip, Trevs! You and Blue Chip really did a GREAT JORRRB.

Create a Basic SQL Server 2005 Trigger to Send E-mail Alerts

Chrissy — Tue, 05 Feb 2008 18:07:01 +0000

For as many times as I have read about sending e-mails using SQL Server triggers, I've rarely come across actual code samples. After someone asked for a "Triggers for Dummies" example in a Facebook SQL group, I created the following example which uses a trigger to alert a manager that an expensive item has been entered into inventory.

First, if SQL Mail isn't enabled and a profile hasn't been created, we must do so.

Enable SQL Mail/Create Profile

--// First, enable SQL SMail

use master

go

sp_configure 'show advanced options',1

go

reconfigure with override

go

sp_configure 'Database Mail XPs',1

go

reconfigure

go

 

--//Now create the mail profile. CHANGE @email_address,@display_name and @mailserver_name VALUES to support your environment

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'DBMailAccount',

@email_address = 'sqlserver@domain.com',

@display_name = 'SQL Server Mailer',

@mailserver_name = 'exchangeServer'

 

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'DBMailProfile'

 

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'DBMailProfile',

@account_name = 'DBMailAccount',

@sequence_number = 1 ;

Now that SQL will support sending e-mails, let's create the sample table. This is not a useful or well designed table by any means -- it's just a simple example table:

CREATE TABLE

CREATE TABLE dbo.inventory (

item varchar(50),

price money

)

GO

Now that SQL mail and the table are setup, we will create a trigger that does the following:

Creates an AFTER INSERT trigger named expensiveInventoryMailer on the inventory table. This means that the trigger will be executed after the data has been entered.
Checks for items being entered that have a price of $1000 or more
If there is a match, an email is sent using the SQL Mail profile we used above.

The Actual Trigger

CREATE TRIGGER expensiveInventoryMailer ON dbo.inventory AFTER INSERT AS

 

DECLARE @price money

DECLARE @item varchar(50)

 

SET @price  = (SELECT price FROM inserted)

SET @item = (SELECT item FROM inserted)

 

IF @price >= 1000

  BEGIN

    DECLARE @msg varchar(500)

    SET @msg = 'Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '.'

    --// CHANGE THE VALUE FOR @recipients

    EXEC msdb.dbo.sp_send_dbmail @recipients=N'manager@domain.com', @body= @msg,  @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'

  END

GO

The only way to test a trigger is to add actual data, so let's do that here:
insert into inventory (item,price) values ('Vase',100)
insert into inventory (item,price) values ('Oven',1000)

Your email should arrive very quickly. If it doesn't, check the SQL Server mail log in SQL Management Studio by running SELECT * FROM sysmail_allitems.

Have fun!

Whoa.. Microsoft Goes Open Source with the .NET Framework

Chrissy — Tue, 22 Jan 2008 21:24:15 +0000

I'm kinda late on this but arstechnica reports that "Microsoft has opened the source code to the .NET Framework libraries under a read-only reference license. Developers who want to check out the source code need only upgrade to the newly released Visual Studio 2008 to gain access to it."

This is something I've always dreamed about -- learning by a large software firm's example. I can remember how disappointed I was during the first dotcom when companies would go down and take their source code and website designs with them. Woudln't it have been amazing if they would have donated their site templates to OSWD.org? As for the code, I realized the other year after looking at my own dotcom code, that the styles were probably pretty bad and not so good to learn from.

Check out Scott Gu's blog entry for more information on the libraries that are being opened.

FIX: 'Cannot Write Pam Settings' when Joining a Windows Domain in SuSE 10.3

Chrissy — Sat, 19 Jan 2008 19:29:41 +0000

Today I attacked my 2008 technical to do list and setup a subversion server for backups/source control. It was actually pretty darn easy in SUSE 10.3. After I got it going, I wondered if I could have it automatically authenticate against my HOME domain. So, using SuSE's menu driven interface YaST, I easily added my Linux machine to my Windows domain.

Initially, YaST wasn't able to find or join the domain. This happens sometimes in Windows clients too when:
1. In TCP/IP, the DNS settings are pointed to servers outside of the domain
2. The fully qualified domain name (ex. corp.windomain.com) is not given when joining the domain
3. The FQDN is not listed as a DNS search suffix

After adjuting /etc/resolv.conf to reflect my fully qualified domain name, YaST made it surprisingly easy to find and join the domain. But right as it was finishing up, it ran into the error "cannot write pam settings." I looked around the web and saw about 2 other people had the same problem but no solution was offered. After poking around, I noticed that "pam-smb" was not installed. Generally, SuSE will automatically detect when rpms need to be added but in this case it didn't.

In order to get it all working, I added pam-smb, samba-winbind and krb5-client then I easily plugged into my Windows 2003 domain. Years ago, I tried to do something similar and it seemed to work but I was never able to login via SSH. I'm pretty sure I didn't prefix the domain (in proper case, at that) when attempting to login. Knowing that, I was successfully able to login to my Linux machine using a Windows domain login this time around.

login as: HOME\testuser

Using keyboard-interactive authentication.

Password: *****************

Creating directory '/home/HOME/testuser'.

Creating directory '/home/HOME/testuser/public_html'.

Creating directory '/home/HOME/testuser/bin'.

Creating directory '/home/HOME/testuser/Documents'.

Have a lot of fun...

HOME\testuser@subversion:~>

Awesome! This is much easier than doing user mapping with NIS.

If You Dropped Out of College...

Chrissy — Sat, 19 Jan 2008 03:57:51 +0000

For those of you who dropped out of college and want to finish your undergrad degree, you may want to consider the online university, Western Governors University. I'm sharing this on my blog because I actually shared the URL with many of my tech friends who dropped out. I found an ad for WGU in some tech magazine (maybe TechNet?) and it made me curious. Here's what I found:

Its accredited by the same org that accredits traditional universities (such as BYU), Northwest Commission on Colleges and Universities. It also earned some other national accreditation and was the first online and first non-traditional program to do so.
Members of Sun and HP are on the board of trustees and a ton of big companies are listed as corporate collaborators including AT&T, Cisco, Dell, Microsoft, Google, Oracle, Qwest, Sun and HP.
It's only about $5,000 or $6000 a year
The CEO of Google said: "Google has pioneered the idea of access to information. The reason Google thinks WGU is such a good idea is because WGU has pioneered the concept of competency- based education whenever you want it."
You get a bunch of certifications while you are at it.

WGU offers undergrad and graduate degrees in IS, teaching, nursing and business.

Another alternative for those wanting a Masters in IS is Carnegie Mellon's Distance Learning program. It seems pretty awesome.

WinRS: Microsoft's Disappointing Answer to SSH for Remote Administration

Chrissy — Wed, 16 Jan 2008 15:23:09 +0000

I'm currently playing with Windows Server 2008 Core and I'm really at a loss trying to figure out why Microsoft seems to go out of its way not to adopt SSH. SSH seems like such an easy and straightforward answer to remote administration. Unix administrators have long used SSH but Windows administrators are given WinRS, a command line tool that requires that you run it each time you need to execute a command on a remote system. So instead of arriving at a remote prompt as you would with SSH and simply typing "ipconfig", you must type "winrs -r:myserver ipconfig"

winrs -r:myserver every time!

I'm hoping things have changed in Windows 2008, but so far, I can't find any way for WinRS to be interactive. A blog post on TechNet back in 2006 suggests that interactivity is going to be a feature at some point:

Currently any commands you execute can't be interactive or prompt for input. WinRS just executes what you specify and returns the results.

Unfortunately, it's nearly a year and a half later and no progress seems obvious. I hope I'm wrong and someone can show me the light or, even better, perhaps we'll see PowerShell+SSH hit the final version of Windows 2008. Many admins already have an SSH client as part of their toolkit and sure, WinRS runs over HTTP(S) and opening just one port is nice but the same goes for SSH. Port 22 or 80, I don't really care. WinRS seems to have its value, but not as a replacement for SSH. Give me SSH or give me both.

FIX: SQL Server System Configuration Checker cannot be executed due to WMI configuration (Error:2147500034)

Chrissy — Tue, 08 Jan 2008 20:52:45 +0000

I don't know what on Earth AOL developers do in their AIM 6.x setup but they sure manage to mess things up in Vista. Once AIM is installed, my network detection no longer works and it gives an error that reads: "No network - server execution failed." The problem seems to be some kind of COM error and the fix which has worked for me twice is adding localservice to the Administrator group (Net localgroup Administrators localservice /add).

Today, when attempting to install SQL Server 2005 Express, I kept coming across the following error

The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine [computername] Error:2147500034 (0x80004002)

After tons of reboots and WMI resets that didn't work, I found the solution on Chuck Boyer's blog. It was eerily close to the AOL fix: net localgroup Administrators "Network Service" /add.

My SQL install then worked properly right away. I was extremely relieved to find that fix because my experience with faulty SQL Server 2005 installs in the past have not been resolvable AND the reason I was reinstalling SQL and AIM is because I had to format my Vista laptop after a beta Citrix VPN client for Vista completely killed my ability to VPN to any network using any protocol. While I was uninstalling the client, I blue screened and when I came back and tried to establish a VPN connection using Vista's built in client, I was unable to register my computer on the network. It errored out with: "Error 720: No PPP control protocols configured." After a few hours of trying to fix it, I decided to just wipe Vista and start over. It felt kind of good actually. My computer, especially Firefox, runs a lot smoother now that I don't have so many extensions going. Ultimately, though, I was a bit tired and just wanted all of my installs to go well.

Anyway, it took a long time for me to find the solution on Chuck's blog so hopefully this post will help him get some positive PankRanking.

Edit: Already I see a few people have found my site while looking for a solution. Did the above work for you, too?