Find All SQL Server Instance Running in Local Network

How to list all SQL Server Instance Running in Local Network:
Some time you need to see the List of other running SQL server on your network. It is so simple and easy to call from other application also.

See the example:

-- ######### Query
EXEC master..xp_cmdshell 'osql -L'

-- ############ Result

NULL
Servers:
    (local)
    MAIL_SERVER
    RAGHU
    RAGHU\SQLEXPRESS
    SERVER1
NULL

If you want to get the same list from Command prompt then:

C:\> osql -L

it will show you the same list.

MSSQL Server cursors

How to use MSSQL Server cursors?
SQL Server cursors are database objects used to manipulate data in a set on a row-by-row basis. You can fetch cursor rows and perform operations on them in a loop just like using any looping mechanism found in any other programming language. Before you can use a cursor, you need to declare it.

Example:

declare @row as varchar(20)
DECLARE cur1 CURSOR FOR

SELECT rowid FROM Table1

OPEN cur1

FETCH NEXT FROM cur1 INTO @row
WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT * FROM Table1 WHERE rowid = @row

    FETCH NEXT FROM cur1 INTO @row

END
CLOSE cur1
DEALLOCATE cur1

DECLARE CURSOR

The DECLARE CURSOR command defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the SQL-92 standard and a syntax using a set of Transact-SQL extensions.

Password Encryption and Decrypt

How to Encrypt Password and compare?

MS SQL has its own built in function to Encrypt password or secure text and compare it. This is very simple see the Example:

—1 , Create a Table to insert.

Create table tempPassTable
(
id int,
pwd varbinary(200)
)

—2, Insert encrypted value to a table.

insert into tempPassTable(id,pwd)
select 1, pwdencrypt('abcd')

—3, Compare encrypted value and user input value.

if (select pwdcompare('abcd',pwd,0) from tempPassTable)=0 
select 'Invalid Password'
else
select 'Valid Password'