Sometimes we need to get a little creative with our querying. Having some nice, helpful functions on hand can be a great help and a fabulous time saver.  Here is one from our collection that has been useful on a surprising number of occasions.

So what does it do?

Basically it returns true if a certain amount of consecutive string characters contained in any part of @Str1 matches any part of @Str2. The length of the match is controlled be the @Precision parameter.

It is useful where your want to find strings that are similar but not exactly the same.

With a little extra code using the inbuilt TSQL function "SoundEx" this could easily become an extremely powerful word analyser.

In it's current form it could even be used to search for documents with similar phrasing.

Anyway, we hope you find it a useful function to add to your ever expanding TSQL tool belt.

Enjoy.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

– =============================================

– Author:      Proficient IT (http://www.proficientit.com.au)

– Create date: 04/01/2011

– Description: Match two strings if any part of the string of length set by presistion matches the next.

– =============================================

CREATE FUNCTION fn_FuzzyStringMatch

(

     @Str1 Nvarchar(4000)

     , @Str2 Nvarchar(4000)

     , @Precision int = 4

)

RETURNS bit

AS

BEGIN

     DECLARE @Str1Len int SET @Str1Len = LEN(@Str1)

     DECLARE @MaxPosition int SET @MaxPosition = @Str1Len - @Precision

     DECLARE @Str1Extract Nvarchar(4000)

     DECLARE @i int = 1

     WHILE @i <= @MaxPosition BEGIN

           SET @Str1Extract = SUBSTRING(@Str1, @i, @Precision)

           IF @Str2 LIKE '%' + @Str1Extract + '%' BEGIN

                RETURN 1

           END

           SET @i = @i + 1

     END

     RETURN 0

END

GO

Trying to stop people saving their login details on public computers? Often it is done inadvertently by people forgetting to un-tick a “Remember me” or “Keep me logged in”. Although this is only available on sites that give the option. Even if a site doesn’t offer this function, almost all modern browsers will offer to save the password anyway.

To stop this you can simply add the following line to you page load event;

form1.Attributes.Add(“autocomplete”, “off”);

If you are using a master page you can add this to the master page code behind. Remember though if you do it this way then it will stop any text on all your pages to not remember anything when the user come back to the page. This can actually become annoying so only use it when necessary.

Another method is to change the property of the textbox, of the username and password controls, “AutoCompleteType” to “Disable“.

You may need to change both the password textbox and the username textbox properties to “Disabled” in order for this to work as most browsers use the selection of a past username as the trigger point to save/retrieve the password.

The benefit of the second method is that it only affects the targeted fields.

I have not tested this on all browsers. It has worked on IE7, IE8, Safari, Firefox and Chrome.

Enjoy.

The reason for needing the script is that even when you create a new SQL login by the same name on the new server, when you restore or attach the existing database the database user does not map the the new login. Even though they have the same name they have different identifiers.

The best approach is to prepare the move by running the below script on the database in it’s existing location. This will then generate a new script that you save for later use. Do this for all necessary database users.

Next restore/attach the database in it’s new location and delete the users from the database. Yes I said delete them.

Now run the scripts you generated at the start and it will recreate the Logins and map all the role and permissions from the last server. If you have multiple scripts for multiple users run them all sequentially.

Sometimes the script has an issue creating the actually server login. If it does create logins yourself by the same names. The password can be set to what ever you need. Also in the generated script you may need to comment out the part at the top that creates the login to prevent an error.

The script is written by Bradley Morris back in 2002 and I have had no problems, even today, using it on SQL Server 2008.

Find the script here.
http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx

Enjoy.

Even once you have successfully deleted an account any activity on the account will re-create the account.

Well, luckily someone has gone to the effort to let us know how. Read this interesting post. Why does Facebook make it so hard to leave. Do they know something we dont know?

Read it. Get rid of Facebook.

Is it because so may people are leaving, disgusted with how Facebook turned out. They joined just to see what was the fuss. They stayed just to see what changed. Perusing around trying to see who is doing what but all the while missing out on events due to the fact they where too busy looking and drooling over what everyone else was doing. On the the internet. Forgeting that there is is a real life outside of Facebook.

Facebook says it has reached 500 million users but how many of these users simply can’t delete their account?

So and so said so and so to so and so. Who cares you say? If I had no Facebook then my life would be simpler. I would be worrying about real events and getting further in my life.

Facebook is a diversion. Get on with life.

Hope you find as useful as I have.

http://www.pantz.org/software/dd/drivecopywithsshanddd.html

Therefore, if you want to use any value inside of a PHP function that does originate from inside the function itself there are two ways to go about it.

The most simple and most would argue correct is to enter the variable as an input parameter of the function itself.

For example;

<?php

function myFunction($myVariable){

echo $myVariable;

}

myFunction(“myVariableValue”);

?>

While simple and correct, it is not always what you want. If you are writing functions that are using the same variables over and over it just doesn’t make sense as a human to have to input the same information into multiple functions. One example is if you have a large number of functions that need the sites global connection string passed into everyone.

In this case you can allow your function to access the external variable using the global statement.

For example;

<?php

function myFunction() {

global $myVariable;

echo $myVariable;

}

$myVariable = “myVariableValue”;

myFunction ();

?>

As you can see we have given explicit permission for the function to access data from the variable that was set outside the scope of the function.

I would recommend that you limit your usage of the global statement to variables that are unique and used extensively throughout your project. Things like a global database connection strings and similar would be acceptable. Outside this I would strongly recommend using function input parameters only. This will help keep your code clean and understandable for when someone else needs to look at your code or even when you yourself need to come back in the future and make a change. Another reason is that if you reuse your function in another project you rule out any chance of cross variable contamination.

]]> http://www.proficientit.com.au/blog/?feed=rss2&p=127 0 http://www.proficientit.com.au/blog/?p=87 http://www.proficientit.com.au/blog/?p=87#comments Wed, 29 Dec 2010 11:07:27 +0000 Proficient IT http://www.proficientit.com.au/blog/?p=87 Continue reading →]]> This script will help you setup a database that will log all logins into a MSSQL server. This has only been tested on Windows SQL Server 2008 but it should be ok for 2005 as well.

The Script 

USE master