ProgrammingOcean

Recover the Database in SQL SERVER

2011-01-10T02:01:00.000-08:00

1.create the Database with same Name,MDF Name,LDF Name.
2.Stop the Sql Server and then Replace the only new MDF file by old database (Corrupted database) MDF file and delete the LDF File of newly created database.
3.Now Start the Sql Server again.
4.you can notice that database status became 'Suspect' as expected.

5.Then run the given script to know the current status of your newly created datatbase.
(Better you note it down the current status)


SELECT *
FROM sysdatabases
WHERE name = 'yourDB'

6.Normally sql server would not allow you update anything in the system database.SO run the given script to enable the update to system database.


sp_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE

7.After run the above script, update the status of your newly database as shown below. once you updated the status, database status become 'Emergency/Suspect'.


UPDATE sysdatabases
SET status = 32768
WHERE name = 'yourDB'

8.Restart SQL Server (This is must, if it is not done SQL Server will through an error)

9.Execute this DBCC command to create the LDF file.make sure the Name of LDF file which you are giveing is same as deleted LDF file of Newly Created database.


DBCC TRACEON (3604)
DBCC REBUILD_LOG(bmpos,'D:\yourDB_Log.ldf')

DBCC accepts two parameters,
1. parameter is database name and
2. parameter is physical path (where the MDF file is located) of the log file. (*Make sure the path is physical, if you specify the logical file name it will throw an error.)

10.Run the given stored procedure to reset the status of your database.


sp_RESETSTATUS yourDB

11.Do not forget to disable theallow update to system datatbase.


sp_CONFIGURE 'allow updates',0
RECONFIGURE WITH OVERRIDE

12.At last, update the status which you have noted in the 5th step.


UPDATE sysdatabases
SET status = 1073741840
WHERE name = 'yourDB'

Note : During steps 8, 9 , 10 you may encounter any errors if database is in use.
in this case you Set the database to single user.


sp_DBOPTION 'yourDB', 'single user','true'

Once the steps 8,9,10 are completed and database is already single user mode, then run this script.


sp_DBOPTION 'yourDB', 'single user','false'

Reflect in the view after Edited or Newly Added column of a Table

2011-01-09T23:14:00.000-08:00

After changed the name or add new column in the table, that changes would not reflect in the view if that field used in that view.
For that you just run this system stored procedure with view name as parameter rather open the view and update it.


Sp_refreshview yourviewname

For an Example:
I am using Table_A, Table_B and View_C

In the View C I have used Table A and Table B.

After created the View C I added one more column call Status in the Table A and run the View C, you would not see that newly added column as view have not been refreshed yet as shown below.

For this you can simply update the view just using the above stored procedure as shown below,


Sp_refreshview View_C

After run the script you can able to see that added column in the view as shown below,

How get the column names from a particular Table in Oracle

2010-06-24T00:20:00.000-07:00

In Oracle you can retreive the field names as shown below,


DESC Table_Name

How to get the position of a character from a word in SQL server.

2010-06-19T10:05:00.001-07:00

For this there is a function call CHARINDEX(). This is very similar to InStr function of VB.NET and IndexOf in Java. This function returns the position of the first occurrence of the first argument in the record.


SELECT CHARINDEX('r','server')

This would return 3 as it is start from 1.

Working with Cursors in SQL Server.

2010-06-19T10:01:00.000-07:00

Cursors are useful thing in SQL as it is enable you to work with a subset of data on a row-by-row basis. All cursor functions are non-deterministic because the results might not always be consistent. A user might delete a row while you are working with your cursor. Here after a few functions that work with cursors.
When you work with Cursor , you will Have to follow these steps .
1. Declare Cursor
2. Open Cursor
3. Run through the cursor
4. Close Cursor
5. Deallocate the Cursor

1. Declare cursor


Declare Emp_Cur Cursor For
Select Emp_Code From Employee_Details

Declare the cursor with select query for a Table/View as shown above.
2. Open Cursor


Open Emp_Cur
Fetch Next From Emp_Cur
Into @mCardNo

Open the Declared cursor and Fetch them into declared local variables for row-by-row basis.
In given example, open cursor Emp_Cur and Fetch the Emp_Code records and assigned into a local Variable called @mCardNo.
3. Run through the Cursor
For this there is a Cursor function called @@FETCH_STATUs which is a scalar function that works with cursors.
If @@FETCH_STATUS=0 means cursor doesn’t have more records to read otherwise it would be nonzero. Normally we would use this with while loop (While @@FETCH_STATUS=0).


While @@Fetch_Status=0
 Begin
   ----
   --You can write you statements here for update/Insert
   ----

   Fetch Next From Emp_Cur
   Into @mCardNo
 End

Another cursor function is @@CURSOR_ROWS which would returns the number rows in the most recently opened cursor.
4. Close the Cursor.
Once you completed the cursor run through you must close the cursor otherwise when open it again SQL would throw an error.


Close Emp_Cur

5. Deallocate the Cursor
This this for erase the declared cursor from memory as it would consume considerable memory space.


Deallocate Emp_Cur

The full script for Cursor, ( for run this script you must have a table call Employee_Details)


Declare @mCardNo as Varchar(10)

Declare Emp_Cur Cursor For
Select Emp_Code From Employee_Details

Open Emp_Cur
Fetch Next From Emp_Cur
Into @mCardNo

While @@Fetch_Status=0
 Begin
   ----
   --You can write you statements here for update/Insert
   ----

   Fetch Next From Emp_Cur
   Into @mCardNo
 End

Close Emp_Cur
Deallocate Emp_Cur

Working with Stored Procedures

2010-06-19T09:50:00.000-07:00

Stored procedures are stored in SQL Server databases. The simplest implication of stored procedures is to save complicated queries to the database and call them by name, so that users won’t have to enter
the SQL statements more once. As you see, stored procedures have many more applications, and you can even use them to build business rules into the database.
How to create a Stored Procedure,
As shown given below, created a Stored Procedure for Inserting records into Table call Holiday_Details, which has Code and Description fields.
In this Stored Procedure, passing two parametrs as INPUT Parameters and one OUTPUT parameter.
Normally in Stored Procedure we can pass parameters as Input / Output Stored parameters. When you define output parameters, we have to implicitly specify the OUTPUT Keyword.
Here I have shown the simple stored procedure.


CREATE PROCEDURE  [dbo].[SP_Holiday]

@Code char(3),
@Desc varchar(100),
@flag bit,
@Err  Varchar(MAX)=Null OUTPUT

AS

Begin Transaction
if @flag=0
 begin
  INSERT INTO Holiday_Details Values(@code,@Desc)
 end

if @flag=1
 begin
  UPDATE Holiday_Details SET Description=@Desc WHERE Code=@code
 end
 
If @@ERROR <>0
 Begin
    Set @Err=cast(@@Error as varchar(max))
  Rollback Transaction
  print @Err   
  return
 End
Else if @@ERROR<>0
 Begin
  Set @Err='Successfully done!!'
  print @Err
 End 
 
Commit Transaction

Here we are trying Insert/Update the records into Holiday_Details. For diferentiate the Insert and Update, we are using @flag input parameter. So when we execute this parameter we should specify the @flag whether it is Insert/Update.
@Err parametr is Output parameter for get the status of execution whether it is successfully Inserted/Updated or thrown any error.
So, if you execute this procedure, you would get the output of status.
Execute of a Stored Procedure
When you execute a Stored Procedure, you have to use either Execute/EXEC
(Exec is special case, I will explain about in another article) keyword for execute the Stored Procedure.


EXEC SP_Holiday 'bb1','ffff',0


EXECUTE SP_Holiday '332','ffff',0

Here we have not pased value for OUTPUT parameter since we have assined Null as intial value which means in case if you forget topass the paraneter, Variable would take the Intialized value.
So, once you run this stored Procedure, this would return the value of @Err parameter.
Main purposes of Using Stored Procedures
When as SQL statement, especially a complicated one, is stored in the database as stored procedure, its execution plan is designed once, cached, and is ready to be used again.
Moreover, stored procedures can be designed once, tested, and used by many usres and applications. If the same stored procedure is used by more than user, the DBMS keeps only one copy of the procedure in memory, and all users share the same instance of the procedure. This means more efficient memory utilization.
Finally, you can limit user access to database’s tables and force users to access the database throgh stored procedures. This is simple method of enforcing business rules.

Use of Begin, Commit, Rollback Transactions in SQL Server

2010-05-30T09:03:00.001-07:00

The SQL Server provides very useful feature which is Begin, Commit, Rollback Transaction.
When we use Begin Transaction before we use DML Queries, we can Commit or Rollback that Transaction after the confirmation. This is very useful if you update anything wrongly then you can rollback that transaction.
For example,As shown below, I am trying to update the NodeID column data to 5 from 1.


begin transaction

update DownLoad_Data set NodeID=5

But after I updated, You can check whether you have been updated properly. But here, I realised I did not mention the Where clause.


select * from DownLoad_Data

So I have to rollback this transaction. For that I can use Rollback Transaction since I used Begin Transaction.


Rollback transaction

So again I changed the Query and run it.


Begin transaction

update DownLoad_Data set NodeID=5 where RecNo=1

Still you can check whether have been updated properly. If it is updated correctly then, Run the Commit Transaction to make all updates permanently.


Commit Transaction

I hope this would have been very useful for you all.

SQL SERVER – TRIM() Function – UDF TRIM()

2010-05-29T19:37:00.001-07:00

SQL Server does not have Trim() function. So we can create a own UDF (User Defined Function) function for this since SQL Sever does LTRIM(),RTRIM() functions and we can use this any time.
Here I have created a simple Function for this.


Create Function Trim(@mText  varchar(MAX))
Returns varchar(MAX)
AS
Begin
return LTRIM(RTRIM(@mText))
End

You can run this function as shown below here,


Select dbo.Trim(' Test  ')

So this function would return ‘Test’ only as LTRIM() function would cut off the Left side spaces and RTRIM() functiom would cut off the Right side spaces.

SQL Script for take backup of Database in sql server

2010-05-07T09:36:00.000-07:00

In sql server there are 2 built-in stored procedures for drop the already existing backup device and create the new device in the user defined path.
Before create the backup device, must drop the device. Because when you create a backup device, if backup device had already been created, sql server throw a error. So very first time have to create a backup device manually.
Afterwards you can use this script.
This is very use ful as user can take backup where user wants it since this procedure takes the path as parameter.

Create Backup device manually in Sql Server 2008

Go to Server Object where right click on Backup Device, Then choose New Backup Device. if you choose that, sql server let you to create the New Backup Device.
(Please refer the figures as shown below)

Figure 1
Figure 2Figure 3

Figure 4
Figure 5

Script for Drop the Backup Device


EXEC sp_dropdevice 'Time_Attendance'

Script for Create the Backup Device


EXEC sp_addumpdevice 'disk', 'Time_Attendance', @Path

Script for initializes the backup with user specified name


BACKUP DATABASE Time_Attendance
TO Time_Attendance with name= @Name, INIT

Get the Running Total in Oracle

2010-04-29T23:32:00.000-07:00

This very frequent needful thing for developers as they need to create so many reports based on this concept.

For this you will have to use one of the window functions in oracle which is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

This would add each and every vale with previous value and give like Running Total.

Query for this,

SELECT PRODUCT_NO,PL_NO, UNRESTRICTED_QTY,

SUM(UNRESTRICTED_QTY) OVER (ORDER BY PRODUCT_NO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RUNNING_TOTAL

FROM PRODUCT_LOCATION

WHERE CLIENT_C=UPPER(‘MSWG’) AND UNRESTRICTED_QTY>0 AND LOCATION_NO=’RECEIPT_BAY‘

row in the result set and adding up the values with currently reading value which is specified by CURRENT ROW up to last record of the record set.

And ordering results by PRODUCT_NO

The result of the above query shown below.

Get table structure using SQL query in SQL Server

2010-04-15T02:41:00.000-07:00

Here it s the query for retrieve the table structure in sql server,


SELECT Ordinal_Position,Column_Name,Data_Type,Is_Nullable,Character_Maximum_Length
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='table Name'

Delete duplicate records from a Table in SQL Server

2010-03-30T02:08:00.000-07:00


 drop table ##temp
create table ##temp (id char(3) ,marks int )
create table ##temp2 (id char(3) ,marks int )
insert into ##temp(id,marks) ----Here we are inserting duplicate
select '001',50               ----records for each ID
union all
select '001',60
union all
select '002',66
union all
select '002',88
union all
select '003',92
union all
select '003',64
union all
select '004',44
union all
select '005',67
----Here we are getting the distinct records and insert then into another Tempory table
insert into ##temp2 select distinct id,max(marks) from ##temp where id in(
select a.id from
(select id,count(id) cnt from ##temp group by id having count(id)>1) a)
group by id
---And delete those duplicate records from original Table
delete from ##temp where id in(
select a.id from
(select id,count(id) cnt from ##temp group by id having count(id)>1) a)
---And again inser the inserted reocrds from temporary Table
insert into ##temp select * from ##temp2
----Drop the Temporary Table.
drop table ##temp2
---Retrieve the Table which will have only distincts Records
select * from ##temp order by id

Blogger Buzz: Blogger integrates with Amazon Associates

2010-03-26T23:43:00.000-07:00

Blogger Buzz: Blogger integrates with Amazon Associates

Compute By clause in SQL Server

2010-03-23T02:39:00.000-07:00

We can use this clause to sum/count/avg/max/min so on. This clause will give you the output as detail and summary which is based on the fields you want to summarize.


select * 
from #temp 
order by student 
compute sum(marks) by student

in above compute by clause, you must specify the field you want to sum in Compute clause and specify the field in By clause based on which field you need to compute.

Very important thing is you must specify the Order By clause in which specify the fileds whatever you specify in By clause in Compute clause.

The output of above query is,

When we try with max,min,avg, the query and output would be as shown below,

Using Max()


select * from #temp
order by student
compute max(marks) by student

Using Min ()


select * from #temp
order by student
compute min(marks) by student


Using Avg()
select * from #temp
order by student
compute avg(marks) by student

Use of Rowcount in SQL Server

2010-03-16T00:34:00.000-07:00

We can use rowcount sql property to set the number of rows to be shown in the output.
for an example,
lets say there are 10 records in a table, if we set the rowcount to 5 then when retrieve records from that table, only 5 records will be shown.
if you rowcount to 0 then all records will be retrieved and shown in output.

SET ROWCOUNT 5
SELECT ref_num FROM tbl_po_master

in above example only 5 rows have been retrieved and shown in output as we set the rowcount to 5.

Get the Table fileds in SQL Server/Oracle

2010-03-16T00:12:00.000-07:00

In SQL Server you can retreive the field names as shown below,


SELECT  nameFROM syscolumns
WHERE id = (SELECT id FROM sysobjects WHERE name='Table_Name')



Get the parameter list of a Storedprocedure in SQL Server
2010-03-15T23:47:00.000-07:00
There is way find what are the parameter list for a storedprocedure in sql server rather find them by open individually.

SELECT PARAMETER_NAME,DATA_TYPE,PARAMETER_MODE
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME='AddDefaultPropertyDefinitions'



Hope this would be very useful for developers who are working with database.


Procedure for Split the words in SQL Sever
2010-03-15T23:19:00.001-07:00
Here it is the procedure to Split the words using comma seperator. still you can use different character for split instead of comma(','). Here i m using 'E,l,e,p,h,a,n,t' as word with comma characters.
so the output should be  'E','l','e','p','h','a','n','t'.
Declare @name as varchar(20)
Declare @i as int
Declare @char as char
Declare @word as varchar(20)
select @name='E,l,e,p,h,a,n,t'
set @word=''
set @i=1
while @i<=len(@name)     begin         set @char=substring(@name,@i,1)             if @char<> ','
                begin
                    set @word=@word+@char 
                end
            else if (@char=',' and @i<>len(@name))
                begin
                    print @word
                    set @word=''
                end
            ---Print the last word
            if @i=len(@name)
                begin
                    print @word
                end
        set @i=@i+1
    end



















Output of this query would be,


Get the number of the current day of the week in SQL Server
2010-03-09T00:04:00.000-08:00
In SQL Server there is a built-in function called Datepart() which is takes 2 paramaters which are return date option and date value.
for the 1st paramater pass the date option as 'dw' and for second parameter pass the date value as shown below,

 SET dateformat dmy  Select DATENAME(dw,'09/03/2010') Day_Name,datepart(dw,'09/03/2010') which_day_ofWeek

if you execute this query, output will be,


in SQL Server, by default the week start with 'Monday' which is 1. so in this example, the week is Tuesday. So the number of the Tuesday is 2.
You can check, what is default start week number by using @@DATEFIRST.

                        Select @@DATEFIRST

Since SQL Server default start week number is 1(Monday), it is giving 1 in output.
Default Value for Week in SQL Server,

                                  Monday       - 1
                                  Tuesday      - 2
                                  Wednesday - 3
                                  Thursday    - 4
                                  Friday          - 5
                                  Saturday     - 6
                                  Sunday        - 7

But You can change the default start week number as shown below,

              SET datefirst 7

So here we make the start week number to 7 which is sunday.
After set the Datefirst to 7(sunday) and execute the first query you will get as shown below,

SET datefirst 7
SET dateformat dmy Select DATENAME(dw,'09/03/2010')    Day_Name,datepart(dw,'09/03/2010') which_day_ofWeek 

Now if you see the output, it shows 3. because now week start from Sunday instead of Monday. That is the reason why now week number became 3.


Get the Weekday Name in SQL Server
2010-03-08T22:56:00.000-08:00
There is a built-in function call DateName() in SQL Server to get the Weekday Name.
This function takes 2 parameters in which first is return date option whereas second one date value from which you want to get the weekday name.
To get the weekday name you have to specify the date option as 'dw'.

for example,

set dateformat dmy
SELECT DATENAME(dw,'09/03/2010') Weekday

in this example '09' is day of march. so if you use this sql function as i given above, it will return the exact name of the weekday.


in output, it is give you week day name.


How to get the month name in SQL Server
2010-03-08T00:34:00.000-08:00
There is a built-in function called datename() for find the month name from given date.
for example:




Get the Column data in a single row with comma separator in SQL
2010-03-05T00:08:00.000-08:00
In this post i thought to expalin how to get one column data in a single row with comma separator. as i shown shown below a table have one column call Choice which have 4 rows.


So our task is how to bring up all these 4 rows into one single row with comma separator.
We can do this in 2 methods,
Using COALESCE() function
Using ISNULL() Function

Actually both are playing same role.

1. Using COALESCE() function

As shown below in the SQL Query below, COALESCE() function takes 2 parameters.
It is check whether passed field value is Null or not if it is null then place with blank whereas if the field value is not null then concatenate value with comma.

2. Using ISNULL() Function

This query also working similar to COALESCE() function.


here  value 'A' repeating twice. so how to get distinct value from this?
here it is the SQL query for that,
We just write subquery to filter only distinct values and using that as a Table for Outer query.
So here only gives A,B,C.


Get the Server Name in SQL Server
2010-02-26T00:55:00.000-08:00
We can get the Connected Sql Server Name using the Built-in function SQL Server as shown below,






Extract only the Date from DateTime in SQL Server
2010-02-25T21:36:00.000-08:00
Normaly extarcting date only from DATETIME is commaon and serious problem in SQL SERVER.
Here i have given some common methods to extract on the date from datetime

Method 1

------------

SELECT
CAST
(
FLOOR(CAST(GETDATE() AS FLOAT))
AS DATETIME
)

Method 2--but it is not correct 

------------------------------------
This is similar to first method but we have used INT instead of FLOAT . But it will seldom correct. It will not give you the exact date of what you expecting.
So better not to use INT.

SELECT 
CAST
(
FLOOR(CAST(GETDATE() AS INT)) 
AS DATETIME
)

Method 3

----------
i would recommend this method to extract inly date though there are many methods. because this method is very simple and easy.


SELECT 
CAST
(
STR(DAY(GETDATE()))+ '/' +
STR(MONTH(GETDATE())) +'/'+
STR(YEAR(GETDATE())) 
AS DATETIME
)

Method 4

----------
In this we have used CONVERT function with 113 date type.

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(12), GETDATE(), 113))

Method 5

----------
Here we have used DATEDIFF and DATEADD functions together to extract.get the different between specified date and 0 then again add o with output value of DATEDIFF

SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)


Method 6

----------
This is also similar to previous method. But here we have not used DATEADD instead we have used CAST.

SELECTCAST(DATEDIFF(dd,0,GETDATE() as datetime)


PIVOT() Function MSSQL Server
2010-02-10T22:47:00.000-08:00
Introduction

The PIVOT() function in MS SQL Server is very useful function. This is works like Cross-Tab table in Crystal Report.

Explanation

For an instance, as shown below table, we have customer,Yr,Mothname, and TTL columns. The Year column consisting distinct records 2009,2010 year. If you want to show the Year data as each column header name, then you will have to use the PIVOT() function to show the distinct records as column header.

 SELECT user_id Customer,datepart(“yyyy”,order_date) Yr,datename(month,order_date) [Month] ,

isnull(sum(total_value),0) TTL

FROM tbl_po_master group by user_id,datepart(“yyyy”,order_date),datename(month,order_date)






Using PIVOT() function

In PIVOT() function, you must specify the column values as shown below,


select * from

(SELECT user_id Customer,datepart(“yyyy”,order_date) Yr,datename(month,order_date) [Month] ,

isnull(sum(total_value),0) TTL

FROM tbl_po_master group by user_id,datepart(“yyyy”,order_date),datename(month,order_date)  ) s

PIVOT

(

sum(TTL)

FOR yr IN ([2009],[2010])

) p

order by Customer


With our previous query we need to use PIVOT() function and in PIVOT() function have to give the aggregating filed and give the filed/Column name which should show as each column header.

sum(TTL) à This is the Aggregate field

FOR yr IN ([2009],[2010])

Here, give the column name which should show each column header. And give the distinct values of that field as parameter for IN().




After executed the above query you would get this result in which you could notice that 2009,2010 column values became as each column header

In this above query you can give the Month column name instead of Yr column. If try with that you would get the out put as shown below,

Query:


select * from

(SELECT user_id Customer,datepart(“yyyy”,order_date) Yr,datename(month,order_date) [Month] ,

isnull(sum(total_value),0) TTL

FROM tbl_po_master group by user_id,datepart(“yyyy”,order_date),datename(month,order_date)  ) s

PIVOT

(

sum(TTL)

FOR Month IN ([December],[January])

) p

order by Customer


Output:





Now in the output, Month field distinct values (December and January) are showing as column header mean while Yr column values (2009,2010) are showing as row