How to check if a javascript global variable is defined or not in a javascript function?

We can do it with following code,

if (variablename == undefined){
        variablename = 'XYZ';
}

But for some reason, firefox will throws error with above code. So the solution to that problem is,
if(window.variablename == undefined){
       window.variablename = 'XYZ';
}

How to insert NULL value in PostgreSQL date column?

The simple query is like,
INSERT INTO table (date_field) VALUES (NULL);

But when we try to run this insert query through PHP application, where we don't know whether the variable is NULL or contain some value, it starts giving error. Here we need to do some programming and query manipulation as shown below,

<?
if(empty($dbDate)) $dbDate = '0000-00-00';
$myQry = "INSERT INTO table (date_field) VALUES (CASE $dbDate WHEN 0 THEN NULL ELSE TO_DATE('{$dbDate}', 'YYYY-MM-DD') END)";

/*Remaining code comes here */
?>

How to Enable Macro in Microsoft Office Excel 2007?

• Open Microsoft Office Excel 2007 Click on Office logo at the very Left hand Top
• Then select "Excel Options", select "Trust Centre" on left hand side and click on "Trust Centre Settings"
• Select "Macro Settings" and then select "Enable all Macros", if Macros are also written in Visual Basics programming, put check before "Trust access to the VBA project object model"

How to find Serial number of machine?

To find out the serial number of the machine, go to windows command prompt and type following command on the command prompt,

1. Start > cmd
2. C:\Users\XYZ > wmic csproduct get identifyingnumber,vendor,name

It will give you the output like
IdentifyingNumber    Name          Vendor
L3ZZ896                  5897X7Y     LENOVO

Where IdentifyingNumber is Serial number of machine, Name is Productid and Vendor is the name of manufacturer

CONCAT_WS for PostgreSQL

PostgreSQL do not have function like CONCAT_WS of MySQL, but writing the query as given below we can do much more...


SELECT ARRAY_TO_STRING(ARRAY[initial, firstname, lastname] , ' ') AS user
FROM users

Same as CONCAT_WS function, the above query will ignore fields which are having NULL values, and return the concatenation of remaining fields. But if your fields contains blank value, modify the above query as given below, to get the proper result

SELECT ARRAY_TO_STRING(ARRAY[
CASE title WHEN '' THEN NULL ELSE title END,
CASE firstname WHEN '' THEN NULL ELSE firstname END,
CASE lastname WHEN '' THEN NULL ELSE lastname END], ' ') AS user
FROM users

Migrate Data from Access 97 to PostgreSQL

To migrate data from Access to PostgreSQL, so many tools are available online. I did try to migrate data with few tools, but no success. I encounter issues with the tables which has boolean fields and with the tables in which access date() and time() function are being used to set default value of a column.

The best way to migrate data from Access to PostgreSQL database, I found is through PostgreSQL ODBC driver. To check how to install and setup PostgreSQL ODBC driver go to Install & Setup PostgreSQL ODBC Driver . Once finished with installation and setup of PostgreSQL ODBC driver, you are ready to migrate the data.

How to migrate data from Access to PostgreSQL using PostgreSQL ODBC?

• Open the access database, select the table, then click on file, Save As/Export, a “Save As” popup will appear, select “To an external file or database” radio button and click ok.
• Then in the new popup for “Save as type” select “ODBC database”.
• An Export window will appear, change the table name if required, or just click ok.
• “Select Data Source” window will appear, select your PostgreSQL DSN, that you have created as mentioned above. And click ok.
• Selected table will get exported to your postgres database.

Install & Setup PostgreSQL ODBC Driver

Install PostgreSQL ODBC Driver

• Download the latest PostgreSQL 32-bit ODBC Driver by choosing a mirror from http://wwwmaster.postgresql.org/download/mirrors-ftpand then navigating to the odbc/versions/msi/ folder. The current version is psqlodbc_08_03_0100.zip which was released Jan-22-2008.
• Unzip psqlodbc_08_03_0100.zip
• Run the psqlodbc.msi file

Setup PostgreSQL ODBC Driver

• Click Start, Control Panel, then switch the control panel to “Classical View”, if it's already not in classical view or select "System and Security" category.
• ODBC located inside Administrative Tools folder. Double click ODBC Data Sources. ODBC Data Source Administrator window displays.
• Select File DSN tab and click Add button, select “PostgreSQL Unicode” from the popup opened.
• Type in a name “pgCon” for "Create New Data Source” and Click Next
• Then Click Finish and fill in relevant server, db, username and password.
• Click the Connection button and uncheck Bools as char as shown in Manager Connection Page 1
• Click the Page 2 button and check True is -1, and uncheck updateable cursors as shown ODBC Manager Connection Pg 2 and then click OK
• A DSN (Data Source) with name “ pgCon ” is created and ready to use...

How to Fix Corrupted Vista Profile?

Follow the following steps in order to recreate a user’s Vista profile when it gets corrupted.

1. log into user’s workstation with your admin account
2. Delete user’s local profile on C: drive under users folder
3. Click on Start and run and type “Regedit”
4. This will open Registry Editor Window
5. Click on + sign besides HKEY_LOCAL_MACHINE
6. Click on + sign besides SOFTWARE
7. Click on + sign besides MICROSOFT
8. Click on + sign besides WINDOWS NT
9. Click on + sign besides CURRENT VERSION
10. Click on + sign besides PROFILELIST and delete the SID number points to user name in Right side Pane
11. Close Registry Editor window.
12. Now RDP into the Server hosting that user’s profile.
13. Go to Profiles folder and locate the user’s windows vista profile
14. Rename this profile folder
15. Ask the user to login and then logoff
16. Now copy the user’s favorites and Desktop icons from the renamed profile folder to the new profile folder
17. Ask user to login again and this will create a new profile for the user successfully.

How to fix network printer dirver error for HP printers

1. Delete the printer having printing problem from the printer window.
2. Click on Start and Run and type in Regedit.
3. This will open Registry editor window.
4. Click on + sign besides HKEY_LOCAL_MACHINE
5. Click on + sign besides SYSTEM
6. Click on + sign besides CurrentControlSet
7. Click on + sign besides Control
8. Click on + sign besides Print
9. Click on + sign besides Environments
10. Click on + sign besides Windows NT x86
11. Click on + sign besides Drivers
12. Click on + sign besides Version-3
13. Right click on the printer name and delete it
14. Click on + sign besides Monitors and delete the folder for that printer
15. Close the Registry Editor
16. Go to System32 folder under C:\Windows\System32
17. Click to open spool folder and click to open drivers folder
18. Click to open w32x86 folder and click on 3 folder
19. Under 3 folder delete all the instances for the printer giving problems
20. Reboot computer and install the printer again
21. It should work fine now.

Important PSql commands

1. Command to connect to postgres

• psql -h localhost -U postgres

2. Few general command that required when connected to Postges though shell

• \h for help with SQL commands
• \? for help on internal slash commands
• \g or terminate with semicolon to execute query
• \q to quit

3. Command to view the database assign to this user

• \l to List databases
• SELECT datname FROM pg_database;

4. Command to connect to a database

• \c database-name  

5. Command to list tables in current database

• \d to List tables in database
• SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'

6. Command to describe a table structure

• \d table-name

Important Linux commands for PostgreSQL

• createdb
  createdb tmpdb -h localhost -U postgres
  Create a new PostgreSQL database 
• createuser
  createuser ekta –s -h localhost -U postgres –P
  Create a new PostgreSQL user. Above command will create a new user ekta with the permission of superuser (-s) . And –P will prompt you for the password for new user. If we write this command without –P, it will create used which will not require password authentication
  
• dropdb
  dropdb tmpdb -h localhost -U postgres
  Remove an existing PostgreSQL database
  
• dropuser
  dropuser ekta -h localhost -U postgres
  Drops (removes) a PostgreSQL user
  
• pg_dump
  pg_dump -h localhost -U postgres tmpdb > tmpdb.sql
  pg_dump --insert -h localhost -U postgres tmpdb > tmpdb.sql
  All above command are used to take database dump. It will extract a PostgreSQL database into a script file. --insert option is used to dump data as insert command.
  
• pg_dumpall
  pg_ dumpall -h localhost -U postgres > tmpdb.sql
  Extract all PostgreSQL databases into a script file
  
• psql
  psql -h localhost -U postgres
  Open PostgreSQL interactive terminal
  
• vacuumdb
  vacuumdb tmpdb -h localhost -U postgres
  Clean and analyze a PostgreSQL database

Note : For information on above command check Linux help using man command, for example man pg_dump

Windows Apache PHP and Postgres (WAPP Server)

BitNami WAPPStack includes Apache 2.2.16, PostgreSQL 9.0.0-1, PHP 5.2.14 and phpPgAdmin 4.2.2. phpPgAdmin is a web interface for administration of PostgreSQL. Steps to install and configure WAPP Server are given below,

Installation

•  Download free copy of WAPP Server from http://bitnami.org/stack/wappstack
•  Locate the downloaded file and double click the file to launch the installation.
• The WAPP Setup Wizard will appear. Click Next.
• At the Select Destination Location you can change the install directory. The default is C:\Program Files\BitNami WAPPStack. We will use the default directory. Click Next.
• Then you will be asked for PostgreSQL postgres user password. Enter the Password. A Postgres user account will be created with user name “postgres” and the password you have entered. This information will be then used to connect with PostgreSQL server, through command prompt or through phpPgAdmin. Click Next
• The default listening port for Apache is 80 and for PostgreSQL is 5432. If those ports are already in use by other applications, you will be prompted for alternate ports to use. Enter the alternate port if prompted. Click next
• You are now ready to begin the installation, screen will appear. Click next
• When installation is complete, “Completing the BitNami WAPP Stack Setup Wizard” page will appear. Click on Finish
• You can now launch your browser with http://localhost/

Folder to create PHP files

• The installation process will create several subfolders under the main installation directory i.e. C:\Program Files\BitNami WAPPStack
• Create your PHP files in C:\Program Files\BitNami WAPPStack\apache2\htdocs

How to access phpPgAdmin

• You can access your phpPgAdmin by opening a browser and typing http://127.0.0.1/phppgadmin. You will be asked for a username and password. As user name, use "administrator" and as password use the value specified during installation
• Once you are connected with phpPgAdmin, to connect with default Postgres database, click on the Postgres on left bar, it will again ask for database user name and password. As the user name, use “postgres” and as password use the value specified during installation

Implementing Aggregate Queries

AGGREGATE FUNCTIONS
Aggregate functions perform calculations on a set of data and return a scalar value. Following aggregate functions are available in SQL Server 2008,

AVG : Returns the average of the values in a data set
CHECKSUM_AGG : Returns the checksum of the values in a data set
COUNT : Returns the number of the values in a data set COUNT(*) returns the number of rows in a set. COUNT(Column Name) Returns number of rows which contain data in specified column. COUNT(DISTINCT ) returns number of unique not null values in data set
COUNT_BIG : it work same as "COUNT" but it returns Bigint data type, while "COUNT" return Int data type.
GROUPING : It returns 0 or 1, the value 1 indicate aggregate while 0 indicate detail result
MAX : Returns the highest value in a data set
MIN : Returns the lowest value in a data set
SUM : Returns the total of the values in the data set
STDEV : Returns the statistical standard deviation of all values in given data set
STDEVP : Returns the statistical standard deviation for the population for all values in data set
VAR : Returns the statistical variance of all values in the specified expression
VARP : Returns the statistical variance for the population for all values in the specified expression

GROUP BY CLAUSE
The "Group By" clause is used in the query with Aggregate functions. When an Aggregate function is used in Select, all other expression in Select must be either Aggregate function or included in Group By clause. The "Group By" allow to find out subtotal of the aggregate data. For example, I have a table like given below

Table A : MonthlySalary

Now look at the following query, it will return Average, Minimum and Maximum salary of each employee

SELECT Employee, AVG(Salary) Average,
MIN(Salary) Minimum, MAX(Salary)Maximum
FROM MonthlySalary
GROUP BY Employee

WITH ROLLUP OPERATOR
The ROLLUP operator is useful in generating quick result set that contain subtotals and total. This operators provides grand total and subtotal based on the column included in GROUP BY clause. On "MonthlySalary" table execute the given query that uses WITH ROLLUP Operator and check the output,

SELECT Employee, Month, SUM(Salary) AS Salary
FROM MonthlySalary
GROUP BY Employee, Month WITH ROLLUP
OR
SELECT Employee, Month, SUM(Salary) as Salary
FROM MonthlySalary
GROUP BY ROLLUP(Employee,Month)

The result set contain subtotal of the salary for all the employees, and the grand total.

WITH CUBE OPERATOR
The CUBE operator should be used when the summary information needed for more then one column. It generates a result set that contain subtotal for all the column specified in GROUP BY clause. Look at the query and the result given below

SELECT Employee, Month, SUM(Salary) AS Salary
FROM MonthlySalary
GROUP BY Employee, Month WITH CUBE
OR
SELECT Employee, Month, SUM(Salary) as Salary
FROM MonthlySalary
GROUP BY CUBE(Employee,Month)

Don't miss the row number 3, 7 and 11 which shows subtotal by month as well, when using WITH CUBE operator. While using WITH ROLLUP operator this rows will not be displayed.

Things to remember while using WITH ROLLUP and WITH CUBE operator

• CHECKSUM_AGG aggregate function is not compatible with ROLLUP, CUBE and GROUPING SETS
• Avoid using WITH CUBE operator on large table where their are more then 3 columns in GROUP BY clause, for batter performance.

ROLLUP() AND CUBE()
ROLLUP() and CUBE() are equivalent to WITH ROLLUP and WITH CUBE and use the same query plans. But note that the new ROLLUP and CUBE syntax is only available in compatibility level 100.

GROUPING AGGREGATE FUNCTION
How can a NULL generated by the CUBE operation be distinguished from a NULL returned in the actual data? This can be done by using the GROUPING function. The GROUPING function returns 0 if the column value NULL came from the fact data, and 1 if the column value NULL is generated by the CUBE operation. It will be useful when we have to further use the result set generated, as it is tough to find out which data is summary data and for which column, so using GROUPING function we can easily find it out. Look at the query and the result given below

SELECT Employee, Month, SUM(Salary) AS Salary,
GROUPING(Employee)ForEmployee, GROUPING(Month)ForMonth
FROM MonthlySalary
GROUP BY Employee, Month WITH Cube

GROUPING SETS
It allows us to specify which aggregations do we want in our result set.
For Example:
1 - Following query will give us same output as we get with ROLLUP() or WITH ROLLUP operator

SELECT Employee, Month, SUM(Salary) as Salary
FROM MonthlySalary
GROUP BY GROUPING SETS((Employee,Month), (Employee), 0)

This query aggregates the Salary by Employee and Month, then it aggregate by Employee only, at last it compute the total for all Employees for all Months. The () syntax with no GROUP BY columns display the total.

2 - Following query will give us the same output as we get with CUBE() or WITH CUBE operator

SELECT Employee, Month, SUM(Salary) as Salary
FROM MonthlySalary
GROUP BY GROUPING SETS((Employee,Month), (Employee), (Month), 0)

This query aggregates the Salary by Employee and Month, then it aggregate by Employee only, then it aggregate by Month only, at last it compute the total for all Employees for all Months.

SQL Server Database Recovery Models in Brief

SQL Server database recovery models gives backup and restore flexibility. The model used to determine how great your risk of data loss will be when a breakdown occurs. There are three types of database recovery models available

1. Full Recovery
2. Bulk_Logged Recovery
3. Simple Recovery

Full Recovery Model :
In the Full recovery model, the database engine logs all operations onto the transaction log, and the database engine never truncates the log. The Full recovery model lets you restore a database to the point of failure
Simple Recovery Model :
In the Simple recovery model, the database engine minimally logs most operations and truncates the transaction log after each checkpoint. In the Simple recovery model, you cannot back up or restore the transaction log. Furthermore, you cannot restore individual data pages.
Bulk-Logged Recovery Model:
In the Bulk-Logged recovery model, the database engine minimally logs bulk operations such as SELECT INTO and BULK INSERT. In this recovery model, if a log backup contains any bulk operation, you can restore the database to the end of the log backup, not to a point in time. The Bulk-Logged recovery model is  intended to be used only during large bulk operations.

Important: The Simple recovery model is not appropriate for databases in which the loss of recent changes is unacceptable.

What Deterministic and Nondeterministic Functions are?

Deterministic Function: Functions which always return the same result any time they are called with a specific set of input values. For example square(2) will always return 4.


Nondeterministic Function: Functions that may return different results each time they are called with a specific set of input values. For example getdate() function will return one result today, but the same function will return different result tomorrow.

What is a FILESTREAM in SQL Server 2008?

A FILESTREAM is a new feature in the SQL Server 2008. It allows structured data to be stored in the database and associated unstructured data to be stored directly in the NTFS file system. Transactional consistency is maintained at all times between the structured and unstructured data. FILESTREAM combines the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system.
It is recommended to use the FILESTREAM when any of the following conditions are met:

• When the objects that are being stored in the FILESTREAM have a size greater than 1 MB.
• When fast read access is an essential requirement.
• When an application is developed that uses a middle tier for application logic.

Database Normalization with Example

In this article, we'll introduce the concept of normalization as it is very difficult to visualize these concepts using words only, I will try to explore first 3 normal form with example  

What is normalization?
Normalization is the process of efficiently organizing data to minimize redundancy in a database. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.

What are different normalization forms?
1NF - No repeating elements or groups of elements
2NF - No partial dependencies on a concatenated key
3NF - No dependencies on non-key attributes
BCNF - Boyce-Codd Normal Form
4NF - Isolate Independent Multiple Relationships
5NF - Isolate Semantically Related Multiple Relationships
ONF - Optimal Normal Form
DKNF - Domain-Key Normal Form

Understanding first 3 normal form with example
For a database to be in 3NF, it must first satisfy all the criteria of a 2NF and 1NF database. Now to understand 1NF, 2NF and 3NF we will take an example of below given table structure with given data and will implement first 3 normal forms on it.
Our table structure is as given below, with highlighted repeating groups of element. Note down that here primary key is “ ordid ”

The data in the table will be like given below

First Normal Form: No Repeating Elements or Groups of Elements
Now after applying 1NF, our table structure will be as given below.

And the data in the table will be like

Here as you can see from there is no single column that uniquely identifies each row. However, if we put a number of columns together, we can satisfy this requirement. The two columns that together uniquely identify each row are ordid and itemid , no two rows have the same combination of ordid and itemid. Therefore, together they qualify to be used as the table's primary key.  

Second Normal Form: No Partial Dependencies on a Concatenated Key
Next we test each table for partial dependencies on a concatenated key . This means that for a table that has a concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence. If any column only depends upon one part of the concatenated key, then we say that the entire table has failed Second Normal Form and we must create another table to rectify the failure.

Now we will analyze the column other then concatenated key column one by one,

• orddt is the date on which the order was made. Obviously it relies on ordid, an order date has to have an order otherwise it is only a date. But can an order date exist without an itemid ? The answer is no, therefore orddt fails Second Normal Form.
• custid is the ID number of the customer who placed the order. Does it rely on ordid ? No: a customer can exist without placing any orders. Does it rely on itemid ? No: for the same reason. This is interesting: custid (along with the rest of the customer columns) does not rely on either member of the primary key. What do we do with these columns? We don't have to worry about them until we get to Third Normal Form.
• itemname is the next column that is not itself part of the primary key. This is the name of the inventory item. Obviously it relies on itemid . But it can exist without an ordid, therefore itemname fails the test.
• qty refers to the number of items purchased on a particular invoice. Can this quantity exist without an itemid? The answer is no. Can the quantity exist without an ordid? Again the answer is no. So this column does not violate Second Normal Form, qty depends on both parts of our concatenated primary key.
• price is similar to itemname . It depends on the itemid but not on the ordid , so it does violate Second Normal Form.

What should we do with a table that fails Second Normal Form? First we take out the second half of the concatenated primary key ( itemid ) and put it in its own table.
All the columns that depend on itemid - whether in whole or in part - follow it into the new table. We call this new table OrderItems. Now our table structure will look like given below

There are several things to notice:

• We have brought a copy of the ordid column over into the OrderItems table. This allows each OrderItem to "remember" which order it is a part of.
• Now the order table has fewer rows than it did before.
• The order table no longer has a concatenated primary key. The primary key now consists of a single column, ordid .
• The OrderItems table does have a concatenated primary key.

Remember, NF2 only applies to tables with a concatenated primary key. Now that Order has a single-column primary key, it has passed Second Normal Form. OrderItems , however, still has a concatenated primary key. We have to pass it through the NF2 analysis again. We ask the same question we did before,
Now consider the columns that are not part of the primary key...

• itemname relies on itemid , but not ordid . So this column once again fails NF2.
• qty relies on both members of the primary key. It does not violate NF2.
• price relies on the itemid but not on the ordid , so it does violate Second Normal Form.

So now after applying NF2 on OrderItems table, the table structure should look like

So now our table structure fulfill NF2

Third Normal Form: No Dependencies on Non-Key Attributes
At last, we return to the problem of the repeating Customer information. Right now if a customer places more than one order then we have to input all of that customer's contact information again. This is because there are columns in the order table that rely on "non-key attributes".

To better understand this concept, consider the orddt column. Can it exist without ordid column? No, so the orddt is said to depend on a key attribute ordid

What about custname , can it exist on its own? Yes , it can without referring to an order. The same goes for custaddr . These column actually rely on custid , which is not a key in this table

So now after applying NF3 our table structure will look like,

So, we are done with NF3. Is this information useful to you? Let me know if have any doubt...

Important date queries in SQL Server 2005

Query to fetch current date in '2010-02-23 00:00:00.000' format
SELECT DATEADD(d, DATEDIFF(d, 0, GETDATE())+0, 0)
OR
SELECT DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()) )

Query to fetch current date in '2010-02-23 11:59:59.000' format
SELECT DATEADD(ss, +43199, DATEADD(d, DATEDIFF(d, 0, GETDATE())+0, 0))
OR
SELECT DATEADD(s, -1, DATEADD(d, 0, DATEDIFF(d, 0, GETDATE()) ) + 1)

Few days back I had a requirement that, I have to find out start date of the week, considering Monday as first day of the week. I have search on the internet for available solution and found the following query,
SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
Output is : 2010-10-04 00:00:00.000

Soon I have noticed a problem with above query that instead of using GETDATE() if I pass any date when the day is Sunday, for eg. I pass '2010-10-03' then it should return me ‘2010-09-27’ but it is returning me '2010-10-04'. With the following query, you will notice that output  is not meeting our requirement
SELECT DATEADD(wk, DATEDIFF(wk, 0, '2010-10-03'), 0)
Output is : 2010-10-04 00:00:00.000

So, I have used the following query which will help to solve above mentioned problem
SELECT (CAST('2010-01-05' AS DATETIME) - DATEPART(dw, CAST('2010-01-05' AS DATETIME)-1)) +1
Output is : 2010-09-27 00:00:00.000

What is short_open_tag?

Well short_open_tag allows you to use <? and <?= in addition to <?php while writing the code. All that you have to do for this setting is

1. Open your php.ini file
2. Search for ‘short_open_tag’ directive
3. The default value of ‘short_open_tag’ directive usually Off, now you can change it to On
4. Save the php.ini
5. Restart your PHP stacks to reflect the changes

Now you can write PHP code starts with <? … ?>. This is not recommended but you may need it if don’t want to make changes in your code.

How to install and configure WAMP Server?

Steps to install and configure WAMP Server are given below,
Installation

• Download free copy of WAMP Server from http://www.wampserver.com/en/download.php
• Locate the downloaded file and double click the file to launch the installation.
• The WAMP Setup Wizard will appear. Click Next.
• The License Agreement screen will appear - Accept the agreement and click Next.
• At the Select Destination Location you can change the install directory. The default is C:\wamp. We will use the default directory. Click Next.
• Select the Start Menu Folder. We will use the default WAMP Server folder. Click Next.
• You can set WAMP to automatically start each time Windows start. Batter to not check this option because of security issues. You should manually start your WAMP/Apache server when you need it. Click Next.
• At the Ready to Install screen, click Next.
• The Installing screen will appear indicating the installation process.
• You will need to select the default root folder. This is the folder that you store your web site in. The path to the folder is C:\wamp\www. Click OK.
• In the next step you will select your default web browser. We will have Internet Explorer as the default browser so no changes made to this screen. Click Open.
• If necessary, check Launch WAMP now and click Finish to complete the Installation.
• A new icon will appear in the task bar to the left of the clock. When you click the WAMP menu icon a menu will appear.

Configuration

• To start the server click Start > All Programs > WAMP Server > Start WAMP Server. Then click the WAMP Server icon in the task bar and select Start All Services.
• If WAMP Server is not getting start, or when you click on WAMP5 Server icon in the task bar and select Localhost, if it show IIS then as IIS and Apache/ WAMP are both web server and might conflict in some way, so you have to disable IIS in order for WAMP to work
  Disabling IIS in Vista/W7: Control Panel > Uninstall Programs > Turn Widows Features On or Off > uncheck Internet Information Services
  Disabling IIS in XP: Control Panel > Add/Remove Programs > Add/Remove Windows Components > uncheckInternet Information Services (IIS)
•  Restart computer, then restart WAMP

Writing a recursive query to find out all dates between two given date

--Declaring the required variables
DECLARE @fromdate DATETIME
DECLARE @todate DATETIME
SET @fromdate = '2010-01-01'
SET @todate = '2010-01-10'

--Query to fetch all the dates between two given date
;WITH tmpinfo(tmpdt) AS
(SELECT @fromdate
UNION ALL
SELECT tmpdt + 1
FROM tmpinfo
WHERE tmpdt < @todate)
SELECT * FROM tmpinfo OPTION (MAXRECURSION 0);

Equivalent of Group_Concat function in MS-SQL

--Query to create the table given in the example
CREATE TABLE [dbo].[categoryproductlink](
[categoryproductlinkid] [int] IDENTITY(1,1) NOT NULL,
[categoryid] [int] NOT NULL,
[productid] [int] NOT NULL,
[endeffdt] [smalldatetime] NULL,
CONSTRAINT [PK_categoryproductlink] PRIMARY KEY CLUSTERED
(
[categoryproductlinkid] ASC
)) ON [PRIMARY]

GO
--Query to insert data in above table
SET IDENTITY_INSERT [dbo].[categoryproductlink] ON
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (1, 1, 1, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (2, 1, 2, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (3, 1, 3, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (4, 2, 4, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (5, 2, 5, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (6, 2, 6, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (7, 3, 2, NULL)
INSERT [dbo].[categoryproductlink] ([categoryproductlinkid], [categoryid], [productid], [endeffdt]) VALUES (8, 3, 6, NULL)
SET IDENTITY_INSERT [dbo].[categoryproductlink] OFF

Query 1 – Equivalent to Group_Concate function in MySQL
_________________________________________________________
SELECT CAST(productid AS varchar(20)) + ', ' AS [text()]
FROM categoryproductlink
WHERE categoryid = 2
FOR XML PATH('')
_________________________________________________________

Query 2 – Equivalent to Group_Concate function in MySQL
_________________________________________________________
DECLARE @prodidstr VARCHAR(MAX)

SELECT @prodidstr = COALESCE(@prodidstr+', ','')+CAST(productid AS varchar(8))
FROM categoryproductlink
WHERE categoryid = 2

SELECT @prodidstr
_________________________________________________________

Query to change collation of a column

ALTER TABLE categorydesc
ALTER COLUMN ldesc text COLLATE SQL_Latin1_General_CP1_CI_AS NULL

Query to fetch description of all tables and columns in current database

SELECT major_id, minor_id, t.name AS [Table Name],
c.name AS [Column Name], value AS [Extended Property]
FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id
AND ep.minor_id = c.column_id
WHERE class = 1
ORDER BY t.name, c.name

Query to get Collation of database and columns of a table

To get dafault collation of database
1 - SELECT DATABASEPROPERTYEX('GoldenTours', 'Collation')
2 - SELECT collation_name FROM sys.databases WHERE name = 'GoldenTours'

Query to get Collation of all the columns of a table
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID FROM sys.objects
WHERE type = 'U'
AND name = 'categorydesc')

Query to generate create index statement for all the indexes in a perticular database. Here it will find only those index which start with 'IX_'

SELECT 'CREATE NONCLUSTERED INDEX [' + si.name + '] ON ' + so.name + '( [' + sc.name + '] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]' test
FROM sysobjects so, syscolumns sc , sysindexkeys sik , sysindexes si
WHERE sc.id = so.id
AND sik.colid = sc.colid
AND sik.id = sc.id
AND si.indid = sik.indid
AND si.id = sik.id
AND si.name like 'IX_%'
ORDER BY si.name

To create index statement with "IF NOT EXIST" check

SELECT 'IF NOT EXISTS (SELECT * FROM sysindexes WHERE name = ''' + si.name + ''')
BEGIN CREATE NONCLUSTERED INDEX [' + si.name + '] ON ' + so.name + '( [' + sc.name + '] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] END' test
FROM sysobjects so, syscolumns sc , sysindexkeys sik , sysindexes si
WHERE sc.id = so.id
AND sik.colid = sc.colid
AND sik.id = sc.id
AND si.indid = sik.indid
AND si.id = sik.id
AND si.name like 'IX_%'
ORDER BY si.name

Query to fetch all duplicate indexes in current database

SELECT si.name
FROM sysobjects so, syscolumns sc, sysindexkeys sik, sysindexes si
WHERE sc.id = so.id
AND sik.colid = sc.colid
AND sik.id = sc.id
AND si.indid = sik.indid
AND si.id = sik.id
GROUP BY si.name
HAVING COUNT(DISTINCT(si.id)) > 1
ORDER BY si.name

Query to fetch all indexes on all user defined tables in current database

SELECT so.name TbName, sc.name ColName, si.name IxName
FROM sysobjects so, syscolumns sc, sysindexkeys sik, sysindexes si
WHERE sc.id = so.id
AND sik.colid = sc.colid
AND sik.id = sc.id
AND si.indid = sik.indid
AND si.id = sik.id
ORDER BY TbName, ColName

Query to fetch all user defined tables in current database along with it's column name, datatype, length and collation

SELECT ts.TABLE_NAME, cs.COLUMN_NAME, cs.DATA_TYPE,
cs.CHARACTER_MAXIMUM_LENGTH, cs.COLLATION_NAME
FROM INFORMATION_SCHEMA.TABLES ts, INFORMATION_SCHEMA.COLUMNS cs
WHERE ts.TABLE_NAME != 'sysdiagrams'
AND cs.TABLE_NAME = ts.TABLE_NAME
AND cs.COLLATION_NAME IS NOT NULL
ORDER BY ts.TABLE_NAME, cs.COLUMN_NAME

Query to fetch all user defined tables in current database along with total number of rows in it

SELECT so.name, MAX(si.rows)
FROM sysobjects so, sysindexes si
WHERE so.xtype = 'U'
AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY 2 DESC

Query to fetch all user defined tables in current database

1 - SELECT * FROM sys.tables
2 - SELECT table_name FROM INFORMATION_SCHEMA.TABLES

Query to check wheather tamporary table exist or not?

IF
object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
DROP
TABLE #MyTempTable
END