How to generate script for data present in database

This tool works for only Microsoft SQL Server 2000 / 2005 Only.

Open the command prompt. Go to the following folder. This folder may vary based on where you installed Sql Server.

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2

Type the following at the command prompt and press enter. This will take Windows Authentication. Change the databasename and file path as required.

sqlpubwiz script -d DatabaseName C:\FileName.sql

Type the following at the command prompt and press enter. The following command will script the FooDB database from the default instance on a machine named MYSERVER using SQL Server authentication with the username "Alice" and the password "7h92-v6k3" to the file C:\FooDB.sql:

sqlpubwiz script -d FooDB -S MYSERVER -U Alice -P 7h92-v6k3 C:\FooDB.sql


You can also check another free tool "SQL Scripter" at the following site.

http://www.sqlscripter.com/

How to eliminate duplicate rows in a table



/*Create table DupRecords*/
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_NAME='DUPRECORDS' AND t.TABLE_SCHEMA='dbo')
BEGIN
Create Table dbo.DupRecords
(
id INT not null
,Name varchar(40)
,Salary money
)
END

/*Insert duplicate data*/
insert into DupRecords values
(1,'A', 1000)
,(1,'A', 1000)
,(2,'B',2000)
,(3,'C',3000)
,(2,'B',2000)
,(2,'B',2000)
,(3,'C',3000)
,(4,'D',4000)


If we want to eliminate duplicate rows in a table use the following syntax


/*Display Non duplicate data*/
SELECT distinct * FROM dbo.duprecords


The following code is used to eliminate duplicate rows based on selected columns by displaying all the columns. The columns on which duplicate is decided need to be mentioned after Partition by in the Over() function


SELECT Id,Name,Salary FROM
(
select Id,Name,Salary, ROW_NUMBER()over(partition by id,name,salary order by id) as [RowNumber] from dbo.DupRecords
) t
WHERE RowNumber=1


If we want to display what are the records we need to delete to eliminate duplicate rows in a table we can use the following sql statement.

SELECT Id,Name,Salary FROM
(
select *, ROW_NUMBER()over(partition by id,name,salary order by id) as [RowNumber] from dbo.DupRecords
) t
WHERE RowNumber>1


How to calculate running total using t-sql

/*Using correlated subqueries*/
select e.empno,e.ename, e.sal,(select sum(d.sal) from emp d where d.empno <= e.empno) as running_total
from emp e
order by e.empno

/*using joins*/
select e.empno,e.ename, e.sal, sum(d.Sal) as running_total
from emp e
inner join emp d
on e.empno >= d.EmpNo
group by e.empno,e.ename, e.sal
order by e.empno

String operations using C#.Net

/*
Following are the methods in C#.Net on String for some of the interviews
1. Reverse a string
2. Remove duplicate words in a give string
3. Remove duplicate chars
*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
namespace Interview
{
  class StringOperations
  {
    /*Reverse the string -- Method 1*/
    public string ReverseString(string inputString)
    {
      StringBuilder sbTemp = new StringBuilder("");
      /*Check either the string is empty or null*/
      if (String.IsNullOrEmpty(inputString))
      {
         return sbTemp.ToString();
      }
      /* If it is a valid string take a single charcter from last till start of the
         string*/
      for (int i = inputString.Length - 1; i >= 0; i--)
      {
        sbTemp.Append(inputString.Substring(i, 1));
      }
      return sbTemp.ToString();
    }

    /*Remove duplicate words in a give string.*/
    public string RemoveDuplicateWords(string stringToRemoveDuplicates)
    {
      if (String.IsNullOrEmpty(stringToRemoveDuplicates))
      {
        return "";
      }
      ArrayList alist = new ArrayList();
      foreach (string word in stringToRemoveDuplicates.Split(' '))
      {
        if (!alist.Contains(word))
        {
          alist.Add(word);
        }
      }
      return string.Join(" ", (string[])alist.ToArray(typeof(string)));
    }

     /*Remove duplicate chars*/
     public string RemoveDuplicateChars(string removeDups)
     {
       StringBuilder sb = new StringBuilder(string.Empty);
       foreach (char c in removeDups.ToCharArray())
       {
        if (sb.ToString().IndexOf(c) == -1)
        {
          sb.Append(c);
        }
       }
       return sb.ToString();
      }
  }
}

How to restore master (system) database

• How to restore master database backup.
o Stop the Sql Server
o Start the sql server in Single user mode using the command sqlservr.exe – m from command prompt.
o Restore the master database

• How to restore master database when the master database is corrupted.
o Execute the following command by placing setup disk with the following options by navigating the folder where setup.exe is present.
start /wait \setup.exe /qn
INSTANCENAME= REINSTALL=SQL_Engine
REBUILDDATABASE=1 SAPWD=
o After executing the above command, stop the sql server.
o Start the SQL Server in single user mode using the command sqlservr.exe –m
o Restore the master database from the backup.

SQL Server 2005: Features

SQL Server 2005 Features
• XML data Type
• Recursive Queries with CTE’s (Common Table Expressions)
• PIVOT and UNPIVOT
• APPLY
• TRY….CATCH error handling
• Database Mirroring
• Common Language Runtime (CLR) .Net Framework Integration
• Dynamic Management Views – Provides information on database performance.
• System Catalog Views
• SQL Server Management Objects (SMO)
• Dedicated Administrator Connection (DAC).
• Database Mail
• Online Index and Restore Options
• Table and Index Partitioning
• Multiple Active Result Sets (MAR)
• Indexes can be modified using Alter Index statement.
• Non-key columns can be added into Covered Index to improve performance.
• T-SQL Enhancements
o Common Table Expressions (CTE)
• Security Enhancements
o Execute As
• Tools
o SQLCMD – command tool to execute sql statements in batch mode.
o TableDiff – To compare data in two tables.
o Dta—Database engine Tuning Advisor – Tool to improve sql statements performance.
Tools introduced with SQL Server 2005
• SQL SERVER Configuration Manager
• SQL SERVER Surface Area Configuration
• SQL Server Management Studio

How to find columns with/without default values in a table

How to find the default values of all the columns in all the tables in a database.


SELECT SO.name AS [Table Name],SC.name AS [Column Name],SM.TEXT AS [Default Value],SC.colorder AS [Column Order]
FROM sysobjects SO
INNER JOIN sys.syscolumns SC
ON SO.id = SC.id
INNER JOIN sys.syscomments SM
ON SC.cdefault = SM.id
WHERE SO.xtype = 'U'
UNION
SELECT SO.name AS [Table Name],SC.name AS [Column Name],'' AS [Default Value],SC.colorder AS [Column Order]
FROM sysobjects SO
INNER JOIN sys.syscolumns SC
ON SO.id = SC.id
LEFT JOIN sys.syscomments SM
ON SC.cdefault = SM.id
WHERE SO.xtype = 'U'
AND SM.id IS NULL
ORDER BY SO.name,SC.colorder


How to find only the columns which contains default value in all the tables in a given database.

SELECT SO.name AS [Table Name],SC.name AS [Column Name],SM.TEXT AS [Default Value],SC.colorder AS [Column Order]
FROM sysobjects SO
INNER JOIN sys.syscolumns SC
ON SO.id = SC.id
INNER JOIN sys.syscomments SM
ON SC.cdefault = SM.id
WHERE SO.xtype = 'U'
ORDER BY SO.name,SC.colorder


How to find the columns and tables does not contain default values in a database.

SELECT SO.name AS [Table Name],SC.name AS [Column Name],SC.colorder AS [Column Order]
FROM sysobjects SO
INNER JOIN sys.syscolumns SC
ON SO.id = SC.id
LEFT JOIN sys.syscomments SM
ON SC.cdefault = SM.id
WHERE SO.xtype = 'U'
AND SM.id IS NULL
ORDER BY SO.name,SC.colorder


How to list all Sql Server "Server Name's" using C#.Net

using System.Data.Sql;

class Program
{
static void Main()
{
// Retrieve the enumerator instance and then the data.
SqlDataSourceEnumerator instance =
SqlDataSourceEnumerator.Instance;
System.Data.DataTable table = instance.GetDataSources();

// Display the contents of the table.
DisplayData(table);

Console.WriteLine("Press any key to continue.");
Console.ReadKey();
}

private static void DisplayData(System.Data.DataTable table)
{
foreach (System.Data.DataRow row in table.Rows)
{
foreach (System.Data.DataColumn col in table.Columns)
{
Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
}
Console.WriteLine("============================");
}
}
}

How to list Database names in Sql Server

To Get all the list of DataBase names in SqlServer, Execute the Stored Procedure "sp_databases"

sp_databases

How to know what are the triggers present in the Database

Execute the following statements in Sql Server Management Studio.


SELECT * FROM SYSOBJECTS
WHERE XType = 'tr'

To view the trigger code execute the following statement. Replace the <Trigger_Name> with actual trigger name


SP_HELPTEXT <Trigger_Name>


How to enable CLR in SqlServer

To use CLR(.Net) defined objects in SQL Server. We need to enable CLR functionality in SQL Server.

To enable CLR functionality in SQL Server, execute the following command in Sql Server Management Studio.

sp_configure 'clr enabled', 1
GO
Reconfigure
GO


How to retrieve data recursively

There are many times where we need to retrieve hierarchy data in sql. Like given a manager displaying all the employees below him for many levels. Or given a product, to find all the sub-parts and sub-parts for the Sub-parts. We need a recursive function to display the data. In Sql server 2005 we can acheive this using Common Table Expression. For understanding this check the following example.

Create the employee table with manager id and insert few records.


CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
CONSTRAINT PK_Employees PRIMARY KEY(empid),
)


INSERT INTO Employees VALUES(1 , NULL, 'Nancy' )
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' )
INSERT INTO Employees VALUES(3 , 1 , 'Janet' )
INSERT INTO Employees VALUES(4 , 1 , 'Margaret')
INSERT INTO Employees VALUES(5 , 2 , 'Steven' )
INSERT INTO Employees VALUES(6 , 2 , 'Michael')
INSERT INTO Employees VALUES(7 , 3 , 'Robert' )
INSERT INTO Employees VALUES(8 , 3 , 'Laura' )
INSERT INTO Employees VALUES(9 , 3 , 'Ann' )
INSERT INTO Employees VALUES(10, 4 , 'Ina' )
INSERT INTO Employees VALUES(11, 7 , 'David')
INSERT INTO Employees VALUES(12, 7 , 'Ron' )
INSERT INTO Employees VALUES(13, 7 , 'Dan' )
INSERT INTO Employees VALUES(14, 11 , 'James' )
GO

/*The following displays all the employees who are under that manager till the last level of employee. */

Declare @empid int
set @empid = 3 /*Any manager's employee id*/

WITH Employees_Subtree(empid, empname, mgrid, lvl)
AS
(
-- Anchor Member (AM)
SELECT empid, empname, mgrid, 0
FROM Employees
WHERE empid = @empid /* Managers employee Id */
UNION all
-- Recursive Member (RM)
SELECT e.empid, e.empname, e.mgrid, es.lvl+1
FROM Employees AS e
JOIN Employees_Subtree AS es
ON e.mgrid = es.empid
)
SELECT * FROM Employees_Subtree
GO

If we need to get the manager Name just add a self join to employees table.

Sql Server Reporting Services - Errors and Solutions

Error - 1

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)

Solution:
Check the microsoft KB article at the following link.
http://support.microsoft.com/kb/842421

Error -2
The permissions granted to user are insufficient for performing this operation. (rsAccessDenied)

Solution:
Apply the permissions to the user as mentioned in the below article.
http://msdn2.microsoft.com/en-us/library/aa337471.aspx
If you are using Team Foundation Server, check the following link.
http://team-foundation-server.blogspot.com/2007/09/permissions-granted-to-user-userid-are.html

How can we supress successful messages in the EventLog

When running backup batch for each successfull backup a successfull message is logged in the EventLog and and log file.

We can supress this message by using DBCC trace flag at the start of the batch and end of the batch. If a DBA wants to backup around 100 databases and not interested in success messages this is more helpfull.

You can add your backup statements in between DBCC statements as shown below.


DBCC TRACEON (3226)
GO
BACKUP DATABASE AdventureWorks to disk = N'C:\AdventureWorks.BAK'
GO
BACKUP DATABASE AdventureWorksDW to disk = N'C:\AdventureWorksDW.BAK'
GO
DBCC TRACEOFF (3226)
GO


How to open a Command Prompt from the current folder

When working on batch files, most of the people faced the problem of openning a command prompt and changing to the current directory.

The command prompt can be opened from any folder, and when openning the command prompt displays the current folder. To achieve this follow the following steps

1. Open Windows Explorer
2. Go to Tools -->Folder Options in the menu
3. In the Folder Options dialog go to "File Types" tab
4. Select the "Folder" under FileTypes
5. Click on Advanced button
6. In the "Edit File Type" dialog click on "New" button
7. In the "Action" text box enter " Command Prompt"
8. In the "Application used to perform action" text box enter C:\WINDOWS\system32\cmd.exe
9. Click ok
10. open the Windows folder you want to open the command prompt
11. Right click on that folder, you can see the Command Prompt in the popup menu.
12. Click on "Command Prompt" it will open the command window with the current folder.