SQL Function to get desired characters

We had a requirement to check the data in a table by stripping off special characters since the match in the filter was getting too much (ex. Robin Jr. wont match Robin Jr but we wanted to match it). So searched for a generalized function which would do the job and found this:

CREATE FUNCTION dbo.GetCharacters(@myString varchar(500), @validChars varchar(100))
RETURNS varchar(500) AS
BEGIN

 While @myString like '%[^' + @validChars + ']%'
 Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

 Return @myString
END
Go

So while writing the where clause I used this function and it works like a charm.


Select ColumnA, ColumnB From TableA WHERE dbo.GetCharacters(ColumnB,'a-z') = dbo.GetCharacters(@SearchItem,'a-z')

You can use the above function to get just characters (a-z) or just numbers (0-9) or alpha numeric (0-9a-z) or specific characters like (abcd). This function is pretty effective since it uses regular expression to do the job.

Hope this helps.

Happy Programming!!!

Cheers,
Raja

Javascript – Getting URL Values

I found this nice function at Snipplr. This is a simple function but the thought behind such a simple function amazed me. I used to get the query string values using <% %> and adding the values to a hidden span. This is much more effective since it gets the values in name value pairs.


// Read a page's GET URL variables and return them as an associative array.
function getUrlVars()
{
 var vars = [], hash;
 var hashes = window.location.href.slice(window.location.href.indexOf('?') + 1).split('&');

 for(var i = 0; i < hashes.length; i++)
 {
 hash = hashes[i].split('=');
 vars.push(hash[0]);
 vars[hash[0]] = hash[1];
 }

 return vars;
}

Hope this helps.

Happy Programming!!!

Cheers,

Raja

Windows 7 – Cannot Connect to Wireless Router

Several weeks back I purchased a new HP NetBook for my wife to use for school. I set everything up and it worked flawlessly … except for the wireless. I could not get a dynamic IP address from the router. It was late, so I plugged in a static IP address and forgot about it. That is, until my wife tried to use her NetBook with the static IP on another network.
Ooops.
I started looking around and saw this particular question asked many times, with no definitive answer. I am sure that there are several possible solutions to this problem, but here is what worked for me. I simply changed the wireless security authentication from “WPA-PSK and WPA2-PSK” to “WPA2-PSK.” The authentication on the router was not what windows was expecting.
AT&T Uverse Wireless Settings

AT&T Uverse Wireless Settings

Old Wireless Security Authentication

Old Wireless Security Authentication

New Wireless Security Authentication

New Wireless Security Authentication

~Ron

Using SSIS to Export a Fixed Width Flat File

To begin, open Business Intelligence Development Studio (or Visual Studio, if you have that installed) and under Business Intelligence projects, create a new Integration Services Package. You will also need a directory at c:\export to contain the output flat file.
Start by adding a new database connection on the Connection Manager tab (this is usually at the bottom of the SSIS Package Designed). For the purposes of this tutorial I will be using the AdventureWorks database my local machine. This connection will be the source of the data for the flat file.
Database Connection

Database Connection

After you create the database connection, go ahead and create the flat file destination connection. I used the c:\export directory and named my export file Export.txt, but anything will work. On the format drop down, select “Fixed width.”

Data Destination

Data Destination

Please ignore the “Column” sub-menu on the right for now. Select “Advanced” from the sub-menu on the right. Now we will create all of our columns. For this tutorial I created a mail merge type export using First Name, Last Name, Address, City, State and Zip. Add one column to handle the line feed/carriage return. Without this column the package output will concatenated into on one row.

Data Destination Columns

Data Destination Columns

On the Control Flow tab of the SSIS Package, add a data flow task. After adding the task, select the Data Flow tab. Add an OLE DB Source. On the OLE DB Connection Manager, the database connection we created earlier should already be selected. If it is not, select the connection we created at the beginning of the tutorial. The T-SQL used to select the data is below. Please note that there is no data formatting in the T-SQL, only the inclusion the column to handle the line feed/carriage return.

ANSI decimal character 10 is line feed, CHAR(10) in T-SQL, and ANSI decimal character 13 is a carriage return, CHAR(13) in T-SQL. Sometimes these characters are used in conjunction, depending on the format, but for our purposes we must use only one or the other.

SELECT
    pc.LastName,
    pc.FirstName,
    pa.AddressLine1,
    pa.City,
    psp.StateProvinceCode,
    pa.PostalCode,
    CHAR(10) CRLF
FROM
    Person.Contact pc
INNER JOIN HumanResources.Employee hre
    ON pc.ContactID = hre.ContactID
INNER JOIN HumanResources.EmployeeAddress hrea
    ON hre.EmployeeID = hrea.EmployeeID
INNER JOIN Person.Address pa
    ON hrea.AddressID = pa.AddressID
INNER JOIN Person.StateProvince psp
    ON pa.StateProvinceID = psp.StateProvinceID
WHERE
    psp.CountryRegionCode = 'US' ;
Data Source

Data Source

One of the issues that you will face with any fixed width export are columns whose width is greater than the fixed width destination. We are going to handle this with a Data Conversion task. Be sure to give the converted column a friendly name, other than “Copy of ” and the column and set the length of the converted field to the same length of the destination fixed width column.

Data Conversion

Data Conversion

To make sure that the package succeeds if the width of the origin column is greater than the destination column, we will set the conversion to ignore all truncation errors. For these purposes it is important not to confuse width and length. A column with a data type of nchar(4) has a width of 4 but may have a length of 0 to 4. The data length may be less than or equal to destination, but if the width is greater it may prove problematic.

Error Handling

Error Handling

After adding the data conversions and error handling, let’s map the converted columns to our flat file destination columns.

Flat File Mapping

Flat File Mapping

That’s it. Run the package and you will see the fruits of your labor.
~Ron

Google Wave Invitations

I have Google Wave invitations to hand out. Please post in the comments if you want one.

~Ron

*crossposted

Automatic redirect upon session timeout – using Javascript

We had a requirement that the logged in user should be automatically logged out after 5 minutes of inactivity. We tried the session_end event in global.asax but it didnt work as it should have. After thinking a bit me and bryan came up with a simple solution using Javascript which works really nice.


var wintimeout;

function SetWinTimeout() {
 wintimeout = window.setTimeout("window.location.href='../weblogin/default.aspx';",300000); //after 5 mins i.e. 5 * 60 * 1000
 }
 $('body').click(function() {

window.clearTimeout(wintimeout); //when user clicks remove timeout and reset it

SetWinTimeout();

});
 SetWinTimeout();

That is it. Just put the above code in document load ($) and it works like a charm. Just 5 mins away from keyboard and monitor would do a world of good.

Hope this helps.

Happy Programming!!!

Cheers,

Raja

Visual Studio 2008 keyboard shortcuts

Sometime back Bryan posted a blog in which he detailed his favorite shortcuts. While browsing through the net I found this good poster which details all the shortcuts.

Keyboard Shortcuts in Visual Studio (C#)

Hope this helps.

Happy Programming!!!

Cheers,

Raja

NetworkCredential in SMTP mail (Forms Authentication)

In the project I am currently working on we have to send email confirmation to the registered user giving them their User ID and Password (yup…..you saw that right password….not secured but that is the requirement :-( ). we created a no-reply account and to our amazement all emails sent inside our domain were reaching fine but external domains were not. My hardware tech lead (Mihai) helped me out in checking exchange server(since I didnt have access to it). We saw that no mails to external domain was even queued in exchange. Then Mihai came up to me and asked me to check the credentials which was used for sending email and that was the missing piece :-) . Prior to this application all our apps were windows authenticated so there was no need to specify the Network Credentials but in this app we have to use forms authentication since it is an internet site not an intranet site. So added the following line and it worked like charm :-)


System.Net.NetworkCredential objAuthInfo = new NetworkCredential("<username>", "<password>");

objSMTP.Credentials = objAuthInfo;

Hope this helps.

Happy Programming!!!

Cheers,

Raja

ASP.Net Hybrid Authentication

We are working on a large .Net website that will have users both on and off of our domain.

  • We want to use the ASP.Net Membership framework
  • We want Windows authentication for people on our network
  • All users will be required to have an account (If a windows user on our domain does not have and account (A generic log on ‘kiosk01’) they should receive the Forms logon page
  • We do not want people with Windows accounts to be able to log on outside of our domain. (An employee here should not be able to log on as an employee from home)

This was actually very easy to setup.

First we setup the website. It should have two folders for authentication.

image

The WebLogin folder should take the default security setting from the websites web.config

image

As you can see we are using Forms authentication for the whole site and setting the logon URL to the WinLogin folder.

We create an HTML file (401-2.htm) containing a redirect to the WebLogin page. This will handle the 401-2 access denied error thrown when a user who can’t authenticate because the are off the domain or don’t have a ASP.Net account.

image

(You may want to do your redirect with aspx and code behind instead of html, if you don’t want to hard code the redirect address)

The ‘Weblogin/Default.aspx’ page should be a aspx page with a standard or customized Asp.Net Login control.

In IIS the site level authentication should be set like this…

image

 

Now for the WinLogin folder only, we set the Authentication like this…

image

Right click WinLogin and edit its properties alone

Now while still on WinLogin properties set a custom error HTTP Error 401;2 pointing to the 401-2.htm file created earlier.

image

 

Now all that is left is to write the code for the users who where able to Authenticate to the WinLogin page.

image

Add what ever custom logic you want for your windows authentications.

As you can see we don’t need to do anything with the password. If this code is executing then the user has Authenticated and we are calling the FormsAuthentication.RedirectFromLoginPage method with just the username and the createPersistantCookie boolean variable alone. The call to the RedirectFromLoginPage method  is also actually logging the user in. This confused me at first.

If the asp.net user account is not found (as in our generic log on ‘domain\kiosk01’) the user will be directed to the forms logon.

Now you can create asp.net membership accounts for the domain users assigning them roles etc. Just make sure the user name includes the domain and userid, ‘mydomain\j.user.01’ You can create a randomly generated password for the membership account preventing the domain user from logging in without the Windows authentication. (You would also have to have logic to prevent these users from resetting there password.)

And that’s it. The best of both worlds without making things to complicated.

-ctrlShiftBryan

Impressed with Jquery UI image manipulation

We are using JQuery UI for our current project. I am particularly impressed with their image manipulation using CSS. I am talking about the Framework Icons which is one image ( 5  Kb) but it can be manipulated in such a way that the coordinates are changed to just bring a particular image portion up front. They are using a pretty effective technique using the background-position. I have heard of this technique before but was really excited to see that in action. Let me give you an example:  If you want to use the delete image (trash can) then you can use the following class:

“ui-icon ui-icon-trash” (CSS : .ui-icon-trash { background-position: -176px -96px; }).

so just mouseover the framework icon in the link above, get the class name and use it. The required image would appear. Pretty sweet.

Hope this helps.

Happy Programming!!!

Cheers,

Raja

Next Page »