SQL Stored Procedure to Log Updates, Independent of Database

Have you ever needed a quick, retroactive solution to track changes on your database?
Well, if youre a good system designer you wouldn't, but when contracting for clients you may find often that the characterization of the project is ever changing, and there is only so much you are able to foresee.

When working with multiple clients, with only a certain amount of hours dedicated to each project, you may want your patches to be applicable for multiple projects. The following is such a patch. It functions to track updates done through the website, storing information such as the user information, the page on which the action took place, the old value and the new value of the changed information and a date time stamp.

Please check out this article to get the code on how to develop the SQL Stored Procedure for creating a log on all updates.

Please visit my other blogs too: http://edwardanil.blogspot.com for information and http://netsell.blogspot.com for internet marketing. Thanks !!

Free Web Hosting

Can you really get FREE web hosting?

Yes, there are hundreds of free hosting web sites, as far as not having to pay any money to have your website hosted. Generally they either cost you in time, web hosting restrictions, or modifying your free web pages by adding pop ups, banners, or other adverts. When looking for free web hosting (especially on search engines), you should beware that there are also a large number of commercial web hosts that claim to offer free hosting services, but those often have a catch, such as paying an excessive amount for a domain name or other service, and therefore aren't really free. The free free hosting guide below will give you some tips for finding the right free web hosting company for you.

How do the free web hosts make money?

The free website hosts often make money in other ways, such as putting banners, pop ups, or pop unders ads on your free web pages. Some free web hosting companies do not put ads on your site, but require you as the webmaster to click on banners in their control panel or sign up process, or just display banners in the file manager in hopes you will click them. Some lure visitors with free hosting in hopes you will upgrade and pay for advanced features. A few send you occasional emails with ads, or may even sell your email address. A new method that is becoming popular is requiring a certain number of "quality" forum posting, usually as a means of getting free content for them and thereby being able to display more ads to their website visitors.

Are free web hosts reliable?

Generally no, although there are a few exceptions. If the free host is making money from banner ads or other revenue sources directly from the free hosting service, then they likely will stay in business, provided someone doesn't abuse their web hosting server with spam, hacking, etc., as often happens to new free web hosting companies with liberal signup policies. If the freehost accepts just anyone, especially with an automated instant activation and it offers features such as PHP or CGI, then some users invariably try to find ways to abuse it, which can cause the free server to have a lot of downtime or the free web server to be slow. It is best if you choose a very selective free hoster which only accepts quality sites (assuming you have one).

Uses for free webspace

Free web hosting is not recommended for businesses unless you can get domain hosting from an ad-free host that is very selective. Other reasons for using free hosting websites would be to learn the basics of website hosting, have a personal website with pictures of your family or whatever, a doorway page to another web site of yours, or to try scripts you have developed on different web hosting environments.

How to find the right free web hosting site

The best place to search for free web hosting is on a free webspace directory website (i.e. a web site which specializes in listing only free web hosting providers). There are some which add new free hosts pretty much every week (and if it is updated often, has usually had to delete about as many). There are also many which almost never update their web site, and a huge percent of their links and info are outdated. Unfortunately that includes most of the directories that were the best several years ago. The problem is free hosts change so often, and most fold up in less than a year (often even after only a day or two), that it is hard to keep such a free hosting directory up-to-date.

For a more selective list of the best free hosts, there are also these free webspace hosting directories:
Free WebHosts (http://www.free-webhosts.com)
Best Free Webspace (http://www.100-Best-Free-Webspace.com/)
Free Hosting (http://www.Absolutely-Free-Hosting.com/)
Free Webspace (http://www.free-webspace.org/)
Other (usually less useful) resources include subcategories of freebies sites, search engines and directories, and forums. Your ISP might also supply you with free webhosting.

Hints for finding the best free web hosting service

Generally it is best not to choose a free hosting package with more features than you need, and also check to see if the company somehow receives revenue from the free hosting itself to keep it in business. As already mentioned, it is best to try to get accepted to a more selective free host if possible. Look at other sites hosted there to see what kind of ads are on your site, and the server speed (keep in mind newer hosts will be faster at first). Read the Terms of Service (TOS) and host features to make sure it has enough bandwidth for your site, large webspace and file size limit, and any scripting options you might need. Read free webspace reviews and ratings by other users on free hosting directories. If you don't have your own domain name, you might want to use a free URL forwarding service so you can change your site's host if needed.

Recommended free web hosts

It would be awfully hard to recommend any free web space host and someone not like it, as different people need different web hosting features and have different priorities, and the webhosting quality may change over time. Also some people want free domain hosting (you own the domain), and others might not be able to buy a domain name. Here are some of the most recommended free web hosts, and their main features.

• Tripod - http://www.tripod.lycos.co.uk/
• 100webspace - http://www.100webspace.com/web_hosting/free_plan.html
• AwardSpace - http://www.awardspace.com/signup.html
• php0h - http://www.php0h.com/
• XlPhp - http://www.xlphp.net/signup_form.php
• Hp-h - http://hp-h.us/signup.htm
• Gig4free - http://www.gig4free.com/registration_form.php
• 50webs - http://www.50webs.com/signup.html?domain_type=new&plan=0
• JayWebDesigns - http://www.jaywebdesigns.com/modules.php?name=Your_Account&op=new_user
• Somee - https://somee.com/Signup.aspx
• Domaindlx - http://members.domaindlx.com/otmain.aspx
• 1ASPHost - http://members.1asphost.com/otmain.aspx
• websamba - http://www1.websamba.com/registration/
• Free Cpanels - http://www.freecpanels.com/hsignup.php?pt=&plan=free
• 100-Best-Free-Webspace - http://www.100-best-free-webspace.com/
• Absolutely-Free-Hosting - http://www.absolutely-free-hosting.com/
• free-webspace - http://www.free-webspace.org/
  

You could read the original article in this link: http://www.free-webhosts.com/free-webhosting-article.php

More Lists of Free Web Hosts

• http://www.free-webhosts.com/webhosting-01.php
• http://www.free-webhosts.com/php-hosting-comparison.php
  
• http://www.free-webhosts.com/search-webhosts.php?ORDER=14&CO=1
  

Please visit my other blogs too: http://edwardanil.blogspot.com for information and http://netsell.blogspot.com for internet marketing. Thanks !!

Manipulating Data in TEXT Type Columns

Its always tricky to do string manipulation in TEXT datatype fields.

For many SQL Server 2000 DBAs working with text columns in T-SQL is no different than any other datatype. But there are some tricks when you work with very large values that you need to know. Leo Peysakhovich brings us some advanced queries that you might need if you work with large XML documents as he does.

Please visit this link to know more about how to manipulate data in Text type columns.

Please visit my other blogs too: http://edwardanil.blogspot.com for information and http://netsell.blogspot.com for internet marketing. Thanks !!

Contract Coding

Thanks to Damon Armstrong for this article.

Contracts and scope definition

You’re called a "contractor", right? As such, it seems that you should have an actual contract, for the sake of propriety if nothing else. The reality is, however, that most contractors start contracting without ever actually making an official contract, or even fully defining the scope and deliverables for the project they are undertaking (guilty as charged!). Clearly, this can lead to serious problems down the line.

Project scope definition

One of the first things you need to do is to define a clear set of objective deliverables for the project. Normally, you define deliverables in a 'scope' document, which outlines the extent of the work you are agreeing to complete. You can also think of the scope document as a high-level 'design' document on which your client signs off – just like a contract.

When you create your scope document, you are essentially defining what needs to be done in order for the project to be considered 'complete'. Outline each item that you are going to complete, and attempt to be specific about what 'complete' means. If you are building a website, make sure to identify all of the pages in the site and the specific functionality for each page. The more detailed you are, the better off you are if you have a disagreement with your client over part of the project.

Also, remember to include a statement that indicates you are only responsible for items specifically designated as deliverables in the scope document. This helps protect you from any assumptions the client makes, but which he forgets to tell you about. For example, if you deliver an e-commerce site and your client comes back and says that you are responsible for all the product data entry, you can point them to this clause in the scope document and tell them that, since the data entry was not a deliverable, you are not responsible for its completion. If you want to be doubly-protected, you should also include a section outlining specific project items you are not going to deliver. This may seem a bit redundant, but specifics are always better than generalities.

Alongside the scope document, you also need to prepare a formal contractual agreement for the work you are about to undertake.

Writing up the contract

There are many tangible benefits to be gained from establishing a written contractual agreement. Hashing out a contract forces you and your client to outline all of the terms of your engagement, and it legally binds both to the fulfillment of those terms (at least, it does if you do it right). If you complete all the work outlined in the contract and your client fails to remunerate you in the method specified, then you have a means by which to pursue payment in court. Of course, it also means that you need to do a good job of outlining specifics, because ambiguity can only harm you.

So, the question is: what do you need to have in a contract? Here are some of the things that you will definitely want to consider:

Project deliverables

As described in the previous section, you and the client should formally agree and sign off on a scope and deliverable document, which can then be explicitly referred to in the contract.

Project timelines

You need to define how both sides handle time-sensitive situations and deadlines. Is there a deadline for finishing the project? Is there a penalty for finishing the project late? A bonus for finishing the project early? And, perhaps most importantly, what happens when the project timeline shifts because the client fails to provide a time-sensitive deliverable?

I’ve seen too many clients say they need something in a month, wait three weeks to give you what you need to start on the project, and expect you to have it done in a week. Always include something about delaying the project timeline in response to delays for items on which you depend, but the client fails to provide. You will be amazed at how quickly you can coax your client into getting things done when they know there are defined consequences for delays. You may also want to specify how you are going to bill clients during a period when you are waiting on a dependency.

Compensation

You need to define all aspects of how you will be compensated for your work on the project. Are you paid based on an hourly rate? Are there limitations on the number of hours you can bill in a given time frame? Is it a fixed-bid project? How, when, and to whom do you submit invoices? How much time does the client have to pay an invoice once it is received? What happens if the client fails to pay an invoice?

Travel time / expenses

If you need to travel out to the client for any reason, can you bill your travel time? Can you expense the mileage? And how do you handle other ad hoc expenses that come up during the course of the project?

Prerequisite needs

If there is something that you know you will need, in order to be successful on the project, make sure you include it in the contract. For example, if you expect to have VPN access to their network, or need a specific piece of hardware or software, then outline it specifically. This helps you avoid assumptions about your environment that may hinder your ability to complete a job on time and on budget.

Third-party software / licenses

If you know that you need to use any third-party software, you should outline who is going to pay for those tools, and who will own the licenses for them when the engagement is over. Even if you are not planning on using third-party software, it may help to have a clause in your contract that states that the client is responsible for paying for any third-party tools that are deemed necessary for the project. And then you will need to define how 'deemed necessary' is determined, because you do not want it to be ambiguous.

Communication

If you want to keep your sanity, be sure to define the process by which the client contacts you with questions, concerns or additional information about a project. If you do not want the client calling you during the day while you’re at your real job, then put it in the contract. To drive home the point, you can even put in a clause that allows you to charge them a premium if they do call you during a restricted time.

Maintenance

Almost every project you work on will have bugs, but you cannot provide indefinite support for an application. Make sure that you outline, in your contract, how you plan on handling maintenance with your client. This is a touchy subject, because clients quickly find issues with an application once it is deployed, and usually want those fixed as part of the original cost of the project. Normally, you will want to include the cost of a few maintenance hours in the original cost of the project, so you can stick around and fix problems for a little while after deployment. But you also want to protect yourself from a ceaseless stream of minor requests like changing the text of a label from this to that, moving a textbox from here to there, using a different shade of blue as a background, etc.

Liability

Cover your butt. I’ve heard of clients going after developers for a myriad of reasons, like lost employee productivity or even lost revenue due to glitches in software. There are a lot of things that can happen on a project and you need to have a broad-sweeping statement that attempts to cover the unforeseen. The more specific you can be about the things that can go wrong, the better off you are. For example, if you are building a billing system, then you should probably have a clause indemnifying you of the cost of any lost revenue due to billing errors, system down time, and so on.

Anything is better than nothing when it comes to contracts, so writing a contract yourself is a far better option than having nothing to go on at all. But you should be keenly aware of the fact that a professional contract lawyer is far more qualified than you are at writing contracts, so you should seek assistance from one, to help you write a solid contract. Writing contracts is a mysterious journey into the complex art of legal prose, most aptly scribed by a professional who actually understands the ramifications of what they are putting to ink. You may think your contract writing skills are akin to the works of Thoreau, but when you come up against a really good lawyer, you will quickly find they are more on par with Curious George gets his Ass Handed to Him In Court. Normally, you can ask a lawyer to write up a fairly generic contract that you can use for most of your engagements, so it’s well worth the investment.

Layers of Protection

A wise man also once told me that a contract is just an invitation to a fight. If you get to the point where you need to enforce something in your contract, then you need the help of the court system. And that course of action normally requires spending a lot of time, energy, and money on the legal process. So it’s also good to avoid a protracted legal battle by protecting yourself in other ways.

Full up-front payments

One way to make sure you get paid for your work is to get paid before you start working! However, negotiating a full up-front payment is a fairly difficult task, because it creates a risk reversal for the client. Instead of you taking on the risk of not getting paid for your work, the client takes on the risk of not getting the work for which they have paid. Some companies are willing to take that risk if you have a good reputation and they trust you. Some companies are so desperate for help that they will agree to anything. You should always check into a desperate client to see the source of their desperation. It may be that they need someone quickly and are willing to pay up-front to secure a qualified contractor. It may be that the project is a complete mess and they cannot get anyone in their right mind to touch it. Knowing their reasoning can help you determine if a project is really worth your time and effort.

One downside to accepting a full up-front payment is that some clients may feel cheated when you declare a project complete while there are lingering issues which the client feels you should resolve. But it’s far better, in this situation, to have the money in hand, because this is the point in time when some clients would refuse to pay you until you fixed those issues. It also illustrates the need for appropriate project scope definition, outlining what deliverables are covered in your fees and how to handle maintenance after finishing the project.

Partial up-front payments

A more common alternative to a full up-front payment is a partial up-front payment. This reduces the overall feeling of risk for a client, because they are not paying for everything up front. Most businesses understand the importance of an initial investment in a project, as a way of demonstrating a commitment, both to the project itself and to maintaining a healthy client-contractor relationship. When defining partial payments, you need to outline the specifics of how such payments are to be made. Normally, the client pays an up-front amount and then makes additional payments as you provide them with project deliverables. This creates a cycle in which you finish a part of the project and are paid for that part. It also sets up an easier environment for billing your client for additional work that arises in the middle of a project. If they want to add something to the project, or keep you on longer for maintenance purposes, then you can simply schedule additional payments for the additional work.

Maintenance and buckets of time

Most contractors accept a project, thinking that they are going to write some software, send it to the client, get a paycheck, and be done with it forever. But that is rarely the case. Business processes change over time and, when they do, your client may need to update your application to account for that change. And guess who they are going to call! Sometimes the requested changes are so significant that you can simply treat it as a completely new project. Often, however, you will get a client who has lots of little changes that come up sporadically. One tactic for dealing with this situation is to establish a maintenance time-bucket.

In this situation, the client pays you for a certain number of maintenance hours (say 5 hours). They can call you and request any changes that they want and you can deduct the time you spend on the fix from the time-bucket. When it starts getting low, the client simply refills the time-bucket by paying you for another set of hours.

Another suggestion is to establish an initial maintenance time-bucket for your project, and explain the concept to your client. Tell them that you will be available for this many hours after the project, and that, if they want to retain you for minor fixes, they can refill that bucket as needed. It’s a seamless way to move from project completion into a maintenance billing cycle.

Binary-only deployments

You may not be able to protect yourself by negotiating an up-front payment on each project, but you can still protect your development efforts with other strategies. When you compile a project, your easily readable source code is reduced to an economically indecipherable jumble of machine language. So, one option for protecting your code is to give your clients only the compiled form of your work. Binary deployments allow the client to interact with your work and even deploy it into a production environment, but also allow you to retain a bargaining chip if the client fails to pay you for the work you have completed. Most applications have bugs and eventually need changes, so the source code is important for the client to ultimately acquire.

Licensing and trial periods

Although source code is important, some clients can still find ways to abuse a binary-only deployment scenario. I spoke with one contractor who deployed his solution, only to have the client turn around and demand that he add additional reporting functionality to the application before they would pay him. It put him in a bad situation because, if he walked away, the client could continue to use his application as it was, or until they found someone else to rebuild the system, using his work as an example. You may even encounter some clients who lose their sense of urgency and, once they have a working solution in place, drag their feet when it comes to paying you. So how do you protect your binary-only deployment? By programming your application to quit working after a certain trial period!

Adding trial-period support into your application can be as simple or complex as you choose. You could add a full-blown licensing system to your application that checks a license key to determine if your software has been paid for or should run in trial mode. You could throw if-then statements around important sections of code that disable the application after a certain date. If you choose the latter, which is the cheapest and least difficult route, I highly recommend centralizing your date-checking logic so you can easily disable the date checks. If you have scattered checks throughout your application, then you are bound to miss one of them and incur the wrath of an angry client when the application they paid for suddenly stops working.

You may also want to consider targeting specific pieces of functionality instead of the application as a whole. For example, if you build a web application that has a customer-facing front-end and a back-end system that the business uses to manage the website, consider disabling the back-end system before disabling the front-end. Disabling the back-end system does not affect the customer’s ability to make purchases, but it does affect the client’s ability to process those orders. When you re-enable the back-end system, the client can process the backlog of customer purchases without losing any sales during the downtime. It’s an effective means of getting the client’s attention, without making too much of a negative impact on the client’s bottom line.

Never use trial period protection as a last-ditch act of revenge against a client who fails to pay you for your services. You will damage your reputation, and you may leave yourself legally liable for losses the client may incur as a result of their application suddenly ceasing to function. Always outline the trial period in your contract to let the customer know, in advance, that a trial period exists and exactly when the trial period expires. You should also refrain from displaying inappropriate comments to the client when your trial period expires. It may seem like fun to write a nasty message about how they should have paid you, but simply informing the user that the application is unavailable is much more advisable. Remember, if you mess up and accidentally display the expired trial message, it’s much easier to explain away an 'Application Unavailable' message than an expletive-laced tirade.

Informing your clients of all protective measures

You should expressly outline any measures you plan to take to protect your work (such as binary deployments, trial periods etc) in the contract, and discuss them with your client at the beginning of a project. By outlining the specifics, you help protect yourself from legal liability in the event that the client feels your protective measures damaged their business in some way. If you suddenly inform the client of such measures at the end of the project, then you are much more likely to get yourself into trouble and upset your client.

In conclusion

Remember, the most important thing you can do as a contractor is to establish an actual contract with your client. Effective communication is essential for a project to run smoothly. The process of hashing out a contract forces you to communicate openly with your client about topics most people seem too squeamish to bring up in normal conversation. Money and failure seem to be taboo subjects, but the finality of a contract makes it easy to discuss delicate financial matters and what happens if anyone fails to live up to their side of the bargain. Every client you encounter is going to be a bit different, so use your judgment in determining the best approach for payment options and source code protection.

You could read the original article by Damon Armstrong, in this link:
http://www.simple-talk.com/opinion/opinion-pieces/contract-coding-ensuring-your-client-pays-up/
Thanks to Damon Armstrong for providing this wonderful insight on this subject.

Check Your SQL Server Identity

We expect developers to be able to create stored procedures, write moderately complex SQL statements, and even the occasional trigger where needed. One question I like to ask goes something like this:

"Let's take a scenario using SQL Server 2000 where I'll be passing in two variables (firstname and lastname) to a stored procedure. That procedure should insert them into a table called TEST that has columns also called firstname and lastname. Table TEST has a primary key column named ContactID which is an integer and is also marked as an identity column. How would I obtain and return the primary key for the row just inserted?"

Stop for a moment and see if you know the answer. Do you know how to create the stored procedure? Obtain the value? Return it to the calling application?

A fair question to ask me is - why is this important? For me, it's a fundamental test to see if someone has worked with data in anything beyond a trivial way. Take the standard order/order detail scenario - how do you insert the details if you don't have the primary key of the order? And while you may have had the luck (good?) to work on a system with natural keys, not every system uses them and identities are the most common way of solving key generation in SQL. More importantly, if you ever do work on a system where identities are used and you rely on @@Identity, you could get some very unusual results at some point in the future when someone adds an auditing trigger. It's not a deal breaker question, but it's an interesting one to lead them into a conversation about dealing with related tables.

I get a variety of answers and most of them are shall we say less than optimum. Almost everyone figures how to insert the values and knows to use either an output or return value, but almost everyone trips on the identity portion.

Wrong Answer #1 - Select max(contactid) from Test. This is wrong because it assumes that no one else will be inserting a row. I suppose you could make it work if you used the right isolation level, but doing that will most likely reduce your concurrency. It's also doing more than you need to.

Wrong Answer #2 - Select top 1 contactid from test order by contactid desc. This is wrong for the same reasons described above.

Wrong Answer #3 - Select the row back by querying on other data you inserted into the table, essentially saying that you inserted an alternative primary key made of one or more columns. This would work if your data supported it and guaranteed that those values were indeed unique. Still not a good idea.

Wrong Answer #4 - In this one they almost get it right. They suggest using @@Identity which will work of course (with caveats), but when I ask them if they are any concerns with this technique, I usually get one of the following:

- No, there are no concerns

- You have to query it quickly because it is a database wide setting and you have to get the value before someone else inserts a row into any table in the database.

- Yes, it retrieves the last identity value for the session which is usually the value you want, but could be incorrect if you had a trigger on TEST which inserted rows into another table that also had an identity column. In that case you'd get the identity value from that table instead of TEST (Note: this correctly describes the behavior @@identity exhibits).

Right Answer - Use Scope_Identity() because it's SQL 2000, use @@Identity in SQL 7, and return the result as an output parameter (return value typically reserved for error conditions). Using @@Identity represents a possible bug in the future if auditing were deployed and it used an identity column as well.

Now let's run a couple tests to prove that the right answer is really correct:

create database IdentityTest

use identitytest
create table TEST (ContactID int not null identity (1, 1), firstname varchar(100) null, lastname varchar(100) null)

insert into TEST Default Values
select @@Identity

This will return the value 1. Repeating it will return 2.

insert into TEST Default Values
select Scope_Identity()

This will return a value of 3.

Now let's start by proving that @@Identity can cause strange behavior. We'll create a history table first that has a new identity column, then we'll add an insert trigger to TEST.

create table TESTHISTORY (HistoryID int not null identity (1, 1), ContactID int not null, firstname varchar(100) null, lastname varchar(100) null)

create trigger i_TEST on dbo.TEST for insert as

set nocount on

insert into TESTHISTORY (ContactID, FirstName, LastName) select ContactID, FirstName, LastName from Inserted

Now let's test what happens:

insert into TEST Default Values
select @@Identity

Returns a value of 1. Inspecting TEST shows that the last row we inserted had a value of 4, the only row in TESTHISTORY has a historyid = 1.

insert into TEST Default Values
select Scope_Identity()

( Corrected as correctly pointed out by adk in the comments)
Returns a value of 5.
Inspecting TEST confirms this, and confirms we inserted a second row into TESTHISTORY. Now let's start testing what happens if someone else inserts a row into TEST while we're busily working away in our stored procedure. Using the existing connection, we execute the first part:

insert into TEST Default Values

If we check the table we see that we just inserted row 6. Now open a second connection and execute the same statement:

insert into TEST Default Values

Check the table reveals we just inserted row 7. Now go back to the original connection. We start with someone we know should return the "wrong" result and it does, the value 3.

select @@Identity

Now let's try scope_identity(). If all went well, it should return 6, not 7!

select Scope_Identity()

And it does, supporting the Right Answer detailed above. I know this is SQL trivia, the kind of stuff I think you shouldn't have to delve into, but if you're going to use the platform you have to know how it works. Take this back and quiz your developers, you'll be treating them to some professional development and you may save yourself a large headache one day too.

You could read the original article in this link:
http://www.sqlservercentral.com/columnists/awarren/checkyoursqlserveridentity.asp
Thanks to Andy Warren for this article