Computed Columns in SQL Server 2005

A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery.

For example, in the AdventureWorks sample database, the TotalDue column of the Sales.SalesOrderHeader table has the definition: TotalDue AS Subtotal + TaxAmt + Freight.

Users get blocked when access same table while getting Unique IDs

--Table which will have the Unique IDs

CREATE TABLE dbo.CounterTable

(

Counter int IDENTITY(1,1) NOT NULL

)
GO
--Procedure which Inserts unique IDs in table and returns the New ID

Simple cursor Example in Microsoft SQL Server

DECLARE @String nVARCHAR(MAX)
DECLARE @getInputBuffer CURSOR
SET @getInputBuffer = CURSOR FOR
SELECT Text FROM Table

OPEN @getInputBuffer

FETCH NEXT
FROM @getInputBuffer INTO @String

WHILE @@FETCH_STATUS = 0

Get foreign keys of a table in SQL Server

SELECT f.name AS ForeignKey

,OBJECT_NAME(f.parent_object_id) AS TableName

,COL_NAME(fc.parent_object_id

,fc.parent_column_id) AS ColumnName

,OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName

,COL_NAME(fc.referenced_object_id

,fc.referenced_column_id) AS ReferenceColumnName

FROM sys.foreign_keys AS f

INNER JOIN sys.foreign_key_columns AS fc

ON f.OBJECT_ID = fc.constraint_object_id

WHERE OBJECT_NAME (f.referenced_object_id)   = 'TableName'

ORDER BY 2

Find /Remove New Line or Carriage Return Character in String or Text in SQL Server

1)

DECLARE @NewLine char(2)

SET @NewLine=char(13)+char(10)

SELECT *

FROM TABLENAME

WHERE CHARINDEX(@NewLine,ColumnName) > 0

2)

DECLARE @NewLine char(2)

SET @NewLine=char(13)

SELECT *

FROM TABLENAME

WHERE CHARINDEX(@NewLine,ColumnName) > 0

3)

DECLARE @NewLine char(2)

SET @NewLine=char(10)

SELECT *

FROM TABLENAME

WHERE CHARINDEX(@NewLine,ColumnName) > 0

Delete Duplicate Records Using Common Tabel Expression

there are many ways to delete the duplicates in a table but sometimes the group by doesnt seem to work so you can try out the new feature of Microsoft SQL Server "Common Table Expression"

With Dups as 

(

SELECT  row_number() over (

partition by ColumnName 

order by  ColumnName ) as RowNum

FROM Table

)

DELETE 

FROM Dups 

WHERE rownum > 1

Solution for Large volume of backup storage on tape with high performance and security

Linear Tape-Open (or LTO) is a magnetic tape data storage technology originally developed in the late 1990s as an open standards alternative to the proprietary magnetic tape formats that were available at the time. Seagate, Hewlett-Packard, and IBM initiated the LTO Consortium, which directs development and manages licensing and certification of media and mechanism manufacturers. The standard form-factor of LTO technology goes by the name "Ultrium", the original version of which was released in 2000 and could hold 100 GB of data in a single cartridge. The most recent version was released in 2007 and can hold 800 GB in the same size cartridge

http://h10010.www1.hp.com/wwpc/us/en/en/WF05a/12169-304612-3446236-3446236-3446236-3454484.html

http://en.wikipedia.org/wiki/Linear_Tape-Open

Manage web sessions in database with .net 2.0

This is the utility placed in the .net framwork v2.0 folder within the windows directory.

just run the command and you will get a script to make a db and it will automatically create the required tables and procedured in it.
Just run the command and execute the sql script generated by the command on the target server.

aspnet_regsql.exe
-S servername
-U username
-P password
-ssaddd
/*just add -ssaddd parameter to tell sql that you are going to add the functionalities */
-sqlexportonly c:\sqlscript.txt
/* this will generate the script to the specified path */
-sstype p
/* in sstype paramerter specifying P will mention that you are enforcing persistant database*/

command will be like


aspnet_regsql.exe -S myserver -U username -P password -ssaddd -sqlexportonly c:\sqlscript.txt -sstype p

There are some useful articles which you must read to get some detailed knowledge.


Reference Links :
http://www.developer.com/db/article.php/10920_3595766_3
http://msdn.microsoft.com/en-us/library/ms229862(VS.80).aspx
http://msdn.microsoft.com/en-us/library/h6bb9cz9(VS.71).aspx

Windows Restart because RPC Service Restarts Unexpectedly

Dear Fellows !

This is because of a Worm attack on your system, it can be resolved if you take the mentioned below steps carefully.

1. Ensure Latest Service pack for your operating system.
2. Find MBlast.exe in your task manager and stop it
3. Go to Control Panel-> Administrative Tools -> Services and Find "Remote Procedure Call (RPC)", check properties and under Recovery Select "Take No Action" under all failure conditions.
4. Now your computer will not restart but the worm is still there you need to remove it so that your system should come to a normal position.

5. Download Patch from the mentioned link
http://www.symantec.com/content/en/us/global/removal_tool/threat_writeups/FixBlast.exe

6. Follow the instruction of Symantec for Removal of the Worm at the mentioned below link.
http://www.symantec.com/security_response/writeup.jsp?docid=2003-081119-5051-99

Database Comparison Tool

Dear Fellows !

Here is a great tool offered by StarInix which comperes Microsoft SQL Server databases for FREE !

Download Link is mentioned below:

http://dw.com.com/redir?edId=3&siteId=4&oId=3000-10254_4-10614062&ontId=10254_4&spi=4e7e60e4583edfb5928c220e5e087238&lop=link&tag=tdw_dltext&ltype=dl_dlnow&pid=10621794&mfgId=6289143&merId=6289143&pguid=CuOMGAoPjFsAAHarY6YAAAEr&destUrl=http%3A%2F%2Fdownload.cnet.com%2F3001-10254_4-10614062.html%3Fspi%3D4e7e60e4583edfb5928c220e5e087238%26part%3Ddl-StarInixD