How to Repair a SQL Server 2005 Suspect database

Sometimes when you connect to your database server, you may find it in suspect mode. Your database server won’t allow you to perform any operation on that database until the database is repaired.

SQL server database can go in suspect mode for many reasons; some of them are given below:

Improper shutdown of the database server
Corruption of the database files
Unavailable device files
Unavailable database files
Database resource used by operating system
SQL Server incorrectly asserts free data page space when a row is inserted

To get the exact reason of a database going into suspect mode can be found using the following query,

DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

Output of the above query will give the errors in the database.

To repair the database, run the following queries in Query Analyzer,

EXEC sp_resetstatus ‘yourDBname’;

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(‘yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

and you are done. 

You should keep one thing in mind while using the above queries that the repair mode used here , REPAIR_ALLOW_DATA_LOSS, is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries. 
Source : http://sapost.blogspot.in/

Select Edit All Records In SQL Server 2008

In SQL Server 2008, the default numbers of rows for selecting and editing records are 1000 and 200 respectively. Sometimes if the records are greater than 200 and you want to edit some record then you have to change the query to get all the records. This is time consuming and you have to repeat this exercise whenever the records are greater than 200. The solution for getting all the records for selecting and editing is given below in steps:

1- Open SQL Server 2008 Management Studio
2- Goto Tools
3- Click "Options"
4- Expand the tree of "SQL Server Object Explorer"
5- Click "Commands"
6- Change "Value for edit top < n > rows command" to 0
7- Change "Value for select top < n > rows command" to 0
8- Click "OK"

SQL SERVER INTERVIEW QUESTIONS - PART 1

SQL SERVER INTERVIEW QUESTIONS - PART 1

What is the significance of NULL value and why should we avoid permitting null values?

Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

What is SQL whats its uses and its component ?

The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. It enable us to retrieve the data from based on our exact requirement. We will be given a flexibility to store the data in our own format.

The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables

What is DTS in SQL Server ?

Data Transformation Services is used to transfer the data from one source to our required destination. Considering am having some data in sql server and I need to transfer the data to Excel destination. Its highly possible with dialogue based tool called Data Transformation services. More customization can be achieved using SSIS. A specialized tool used to do such migration works.

What is the difference between SQL and Pl/Sql ?

Straight forward. SQL is a single statement to finish up our work.Considering, I need some data from a particular table. “Select * from table” will fetch the necessary information. Where as I need to do some row by row processing. In that case, we need to go for Procedural Logic / SQL. 

What is the significance of NULL value and why should we avoid permitting null values?

Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

Difference between primary key and Unique key?
Both constraints will share a common property called uniqueness. The data in the column should be unique. The basic difference is,
· Primary key won’t allow null value. Whereas, unique key will accept null value but only one null value.
· On creating primary key, it will automatically format the data inturn creates clustered index on the table. Whereas, this characteristics is not associated with unique key.
· Only one primary key can be created for the table. Any number of Unique key can be created for the table.

SELECT STATEMENT IN SQL SERVER

 

STRING FUNCTIONS IN SQL SERVER

Substring/Len/replace/Ltrim/Rtrim

Courtesy : http://mrsupport.blogspot.com/

SQL SERVER INTERVIEW QUESTION - PART 2

SQL SERVER INTERVIEW QUESTION - PART 2

What is normalization?

Normalization is the basic concept used in designing a database. Its nothing but, an advise given to the database to have minimal repetition of data, highly structured, highly secured, easy to retrieve. In high level definition, the Process of organizing data into tables is referred to as normalization.

What is a stored procedure:

Stored procedures are precompiled T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements. As, its precompiled statement, execution of Stored procedure is compatatively high when compared to an ordinary T-SQL statement.

What is the difference between UNION ALL Statement and UNION ?
The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.
Example for Stored Procedure?
They are three kinds of stored procedures,1.System stored procedure – Start with sp_2. User defined stored procedure – SP created by the user.3. Extended stored procedure – SP used to invoke a process in the external systems.Example for system stored proceduresp_helpdb - Database and its propertiessp_who2 – Gives details about the current user connected to your system. sp_renamedb – Enable you to rename your database

What is a trigger?
Triggers are precompiled statements similar to Stored Procedure. It will automatically invoke for a particular operation. Triggers are basically used to implement business rules.

What is a view?
If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.

What is an Index?
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.

What are the types of indexes available with SQL Server?
There are basically two types of indexes that we use with the SQL ServerClustered -
1. It will format the entire table, inturn physically sort the table.
2. Only one clustered index can be created for a table.
3. Data will be located in the leaf level.
4. By default, primary key will create clustered index on the table.
Non-Clustered Index
1. It wont touch the structure of the table.
2. It forms an index table as reference to the exact data.
3. A reference to the data will be located in the leaf level.
4. For a table, we can create 249 non clustered index.


Courtesy : http://mrsupport.blogspot.com/

EMO DATABASE - HOW TO REPAIR CONSISTENCY ERROR IN DB

Problem in Printing eMO (Database Corrupted)

While printing the eMOS, an error occurs. The
screen shot is shown. The error shows

Attempt to fetch logical page in database eMO belongs to object eMO Inward not to object eMO Offices

While checking the databases. The database is not in suspect state. While checking the Check db it shows There are some consistency errors.. Then I tried to repair the database with query. This worked for me

The query for corrupted database(Not for Suspected database) with minimum data loss is given below

DBCC checkdb(‘yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

while While reporting the matter with PTC Mysore They give a solution. While running the exl file It completed with on error. While consulting the matter with PTC they replied thatIt can occur to some reasons like, network issue and excess size of log files. Please check for network connectivity. if everything is fine please try to shrink the database and check. While clearing the log file and restarting the server the query worked.

COURTESY : SAPARAVUR

MANAGEMENT STUDIO IN SQL SERVER 2005

On Yesterday I got a call from one of my post office saying that no applications are working . It shows an error that server is not exists. Firstly I thought that it is problem with network connections and I asked themto restart the server machine and try again but then also this error exists. I came to the office and tries to run the application. But seeing the error I thought that this problem with SQL server. On tryingto open the SQL server 2005 management studio I got an error. The error is as follows

“A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server wasnot found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.NetSqlClient Data Provider) “

First I wondered to see the error while connecting to server 2005. I never come up on such type of error during period as SA. While Contacting fellow Sys Admins they told that they had never come across such errors. While searching for the solutions on net But that not worked for me.

The main check points are

• Make sure your database engine is configured to accept remote connections

Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration

Click on Surface Area Configuration for Services and Connections
Select the instance that is having a problem > Database Engine > Remote Connections
Enable local and remote connections

Enable the TCP/IP in surface area configuration

Check the fire wall status and off the firewall if it is on and allow  exception for programsSQL server and SQL server browser

Enable SQL Server Browser (Start the SQL server browser n service)

But then also I am not able to connect to SQL server management studio. But while restarting the server machine I found that the windows server administrator password was missing. Earlier it requires a password for opening the windows server. Then I gotto users (By right clicking computer---Manage----Local User) and set pass word for Administrator (Recreated the earlier Password) and restarted the server.

After this SQL Server is working and I can open management studio correctly.

 

via-saparavur.blogspot.in

SQL: Query to find Duplicate Rows in a Table

If you would like to list duplicate rows with the count for a table, the following query would be helpful.

Query: Duplicate Rows

SELECT Column1,Column2,Column3,Count(*)
FROM dbo.TableName
GROUP BY Column1,Column2,Column3
HAVING ( COUNT(*) > 1 )

And there are scenarios where we need to find the combination of Columns in a table occurred only once. In such case the following query could be used.

Query: Unique combination of columns

SELECT Column1,Column2,Column3,Count(*)
FROM dbo.TableName
GROUP BY Column1,Column2,Column3
HAVING ( COUNT(*) = 1 )

Hope it helps.
Source : http://sapost.blogspot.in/

SQL Server Not Starting ( SQL 2000)

One day when I started the server I got a message. Microsoft SQL server not starting. While Restarting the SQL server it is not possible. Mostly SQL Server is not starting because master database is corrupted. Since SQL server is not started we cant restore the master database.

If you have SQL Server 2000 CD then you can restore the master database. Insert the SQL SERVER 2000 CD. Then go to
MY Computer ->
C:\  DRIVE -> Program Files -> Microsoft SQL Server -> 80 -> Tools -> -Binn -> rebuildm.exe

Double click on rebuildm.exe then you have to show the path of DATA folder from the CD

(ie Browse the CD open the folder---*86--------DATA)

After this you can connect/ start SQL server. The restore the master database with the backup(Previous Days). Then you can re start Sql server 2000. and start working all the Applications
---------------
Related Post :

• How to Rectify Consistency Problem in SQL Tables

• Some basic information about SQL

• Scheduling of Database Backup in SQL Server 2008 Express

• Error: MMC cannot open the file C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC.

• Set SA Password in SQL Server Management Studio Express

• Compatibility With SQL Server 2005

• How to change the SQL sa password - For Security measure ( for SAs only)

How to Rectify Consistency Problem in SQL Tables

Solution 1:

sp_dboption 'dbname', 'single user', 'true'
go

dbcc checkdb ('dbname', REPAIR_REBUILD)
go

sp_dboption 'dbname'single user', 'false'
go



Solution 2:
The second option is to run the query given by us through query analyser.This solution given may result in a certain degree of data loss proportionate to the corruption of data.The extent and amount of data loss cannot be determined before running the query. Run the query given below from SQL Query Analyser after taking a backup.

sp_dboption 'databasename', 'single user', 'true'
go

dbcc checkdb ('databasename', REPAIR_ALLOW_DATA_LOSS)
go

sp_dboption 'databasename, 'single user', 'false'
go

Sources:sapost,blogspot.com

Some basic information about SQL

SQL ESSENTIALS

With just a dozen commands and functions, a developer is able to perform most activities related to querying and manipulating a database.

Commands like Select , insert , delete , update , create Function like Sum () Avg () Max() , Min() Count()

The SQL declarations, or commands, are divided into two main categories: DDL and DML

DDL, or data definition language, is the part of SQL used to define the data and objects in a database. When these commands are used, entries are made in a data dictionary in the SQL server. Following are some DDL commands: Create Table-, Create Index , Alter Table, Drop Table, Drop Index

DML, the language for manipulating data, is the part of SQL used to recover or manipulate data. Its commands are responsible for the queries and changes made to tables. These are some of the most important commands in this category: Select , insert , update rollback

Data Type in SQL Char Character data type with fixed size of up to 254 characters. Date Stores only the date. Datetime or Timestamp When part of the input argument is omitted, SQLBase assumes the default 0, which converts the date to 30/12/1899 and 12:00 a.m. Decimal or Dec Supports up to 15 digits (999999999999999 to +999999999999999). If nothing is specified, the precision 5 and scale 0 are assumed. Double precision Numeric data type of floating-point and double precision. Float When the precision is between 1 and 21, the data type will have simple precision. Between 22 and 53, the precision is double. Integer or Int An integer data type with precision of up to 10 digits (2,147,483,648 up to + 2,147,483,647). Long Varchar Stores characters or binary objects. This is equivalent to the data type blob. Number This is a super set from other data types. It supports precision of up to 22 digits. Real Numeric data type of floating-point and simple precision. Smallint This data type has no fractional digits. The digits to the right of the decimal point are truncated. You can have precision of up to five digits (from 32,768 to +32,767). Time Stores only the hour. Varchar Character data type with fixed size of up to 254 characters.

Select command is the essence of the SQL language

Basic syntax: SELECT [*] [ALL | DISTINCT] [name =] expression [expression [AS name]] FROM [correlation_name] WHERE [GROUP BY ] ] [HAVING ] [ORDER BY [ASC] [DESC]] ]

Selects all the columns in a table. ALL The default in a SELECT command is to recover all the rows. DISTINCT Does not show duplicated rows. expression A selection list of expressions separated by commas. An expression can be a column name, a constant, a checked variable, the result of a function, or a system keyword. FROM Contains the names of the tables or views resulting in a set of rows. correlation_name A related name can be used to designate a preceding table or view. WHERE Specifies a search condition for the basic tables or views. The search condition cannot contain aggregate functions. GROUP BY This clause groups the resulting rows of a query, according to the column names. When the column by which the grouping occurs is an expression with more than one column, you must specify the number that indicates its relative position in the selection list. HAVING This clause allows you to establish a search condition with a group of rows resulting from a GROUP BY clause or by grouped columns. ORDER BY Specifies the order of rows in a result table. The rows can be ordered by more than one column. When the order column is derived from a function or arithmetic expression, the column must be specified by an integer that indicates the relative number of its position in the SELECT command. Optionally, ASC or DESC, indicating the ascending or descending order, can follow each column name or number. Instead of showing all the columns, you can request a list of specific columns This list must contain the names of the columns separated by commas. The order of the columns is not important. The code below selects the columns containing the first name, last name, and telephone number of each author. SELECT au_fname, au_lname, phone FROM authors; Collected and prepared by S Jayachandran,

Scheduling of Database Backup in SQL Server 2008 Express

Scheduling of backups not possible in SQL server 2008 express editiion, because sql server agent is not present in SQL express eidition. The daily back up of database is important in post office environments. A disaster plan is the type of document you hope you never need. But a disaster plan is a key element of systems administration strategy because of the risk of disaster presented by hardware failures, natural catastrophes, network intrusions, and human error. The risk of disaster is particularly high for databases.

Simple tool for scheduling of database in SQL server 2008 can be downloaded from the following site

Download : http://sqlbackupandftp.com/

Error: MMC cannot open the file C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC.

Error: MMC cannot open the file C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC.

Upon clicking on the SQL Server Enterprise Manager shortcut the following error occurs:

MMC cannot open the file C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC.
This may be because the file does not exist, is not an MMC console, or was created by a later version of MMC. This may also be because you do not have sufficient access rights to the file.

The msc file did exist and I definately has permissions to it. So I can only assume it some how got corrupt.

The solution to this is fairly simple... either get another copy of this file, or create one, as follows:

1. Select Start - Run.

2. At the Open prompt enter: mmc

3. Click OK

4. Select File - Add/Remove Snap-in...

5. Click Add...

6. Select Microsoft SQL Enterprise Manager

7. Click Add, then Close

8. Click Ok to return to the mmc.

9. Select File - Save As...

10. Delete or rename the original (offending) file out the way.

11. Save the new msc file as C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC

Now your original shortcut should work.

Alternatively you can always access Enterprise Manager from within the Computer Management mmc.

Set SA Password in SQL Server Management Studio Express

During the installation of SQL Server,we select mixed mode authentication and give complex password for sa. Sometime we may need to change the password of sa.

To change the password of sa follow the below steps.

1.Open SQL Server Express Management Studio.

2.Connect to SQL Server using windows authentication.

3. Expand the server and choose security and expand logins.

4. Right click on sa, from properties modify the password and confirm password

5. Uncheck Enforce password policy.

6.Click OK

Compatibility With SQL Server 2005

The ntwdblib.dll module is associated with the Microsoft SQL Server program. This is an essential file and should not be removed if you have SQL Server installed on your computer.

It is essential for Sanchaypost With SQL2005

Copy ntwdblib.dll into c:\Windows\System32 folder in the server & nodes having SQL 2005.

Download

How to change the SQL sa password - For Security measure ( for SAs only)

1. How to change the SQL sa password in the SQL Server Enterprise Manager

To change the password on the 'sa' account from the graphical interfaces of MSSQL Server 7.0 and 2000 on both Windows NT 4.0 and 2000:

Quote:

1. Open the "SQL Server Enterprise Manager". This is usually under "Start"-->"Programs"-->"Microsoft SQL Server". 2. Navigate to the "Logins" object under the "Security" folder on the SQL Server you wish to administer. Then, right click on the 'sa' account and select "Properties". 3. Now, enter a new password in the "Password" field under the "Authentication" options.

For More details SQL 2000 / 2005 - Visit : MRSupport
Note:

It is for the Use of System Administrators only for maintaining the Security