ProgrammingOcean View RSS

Recover the Database in SQL SERVER 10 Jan 2011 1:01 AM (14 years ago)

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 9 Jan 2011 10:14 PM (14 years ago)

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 23 Jun 2010 11:20 PM (14 years ago)

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. 19 Jun 2010 9:05 AM (14 years ago)

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. 19 Jun 2010 9:01 AM (14 years ago)

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 19 Jun 2010 8:50 AM (14 years ago)

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 30 May 2010 8:03 AM (14 years ago)

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() 29 May 2010 6:37 PM (14 years ago)

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 7 May 2010 8:36 AM (14 years ago)

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)

Get the Running Total in Oracle 29 Apr 2010 10:32 PM (14 years ago)

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 15 Apr 2010 1:41 AM (15 years ago)

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 30 Mar 2010 1:08 AM (15 years ago)

 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 26 Mar 2010 10:43 PM (15 years ago)

Blogger Buzz: Blogger integrates with Amazon Associates

Compute By clause in SQL Server 23 Mar 2010 1:39 AM (15 years ago)

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 15 Mar 2010 11:34 PM (15 years ago)

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.

1. SET ROWCOUNT 5
2. 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 15 Mar 2010 11:12 PM (15 years ago)

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

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