Click here to see PDF file  : Back to Basics

Save a Command’s Output to a File

The use of redirection operators, specifically > and >> operators are used to redirect the output of a command to a file, giving us a saved version of whatever data the command produced in the Command Prompt window.

Redirection Operator

Explanation

Example

>

The greater-than sign is used to send to a file, or even a printer or other device, whatever information from the command would have been displayed in the Command Prompt window had you not used the operator. assoc > types.txt

>>

The double greater-than sign works just like the single greater-than sign but the information is appended to the end of the file instead of overwriting it.

ipconfig >> netdata.txt

<

The less-than sign is used to read the input for a command from a file instead of from the keyboard. sort < data.txt

|

The vertical pipe is used to read the output from one command and use if for the input of another. dir | sort

Copy command output to Windows clipboard

The clip command copies all output from a command to the Windows Clipboard. You can then paste that output into to any Windows application, such as into Windows Notepad or Microsoft Word.

command | clip

Copies the output from the specified command to the Windows clipboard.

Example :

dir | clip Copies the directory listing that is output from the dir command to the Windows Clipboard.

clip < filename

Copies the contents of the filename file to the Windows Clipboard.

Example :

CLIP < README.TXT Places a copy of the text from readme.txt on to the Windows clipboard.

Customize the Command Prompt Title Bar Text

The TITLE command Sets the windows title for the command prompt window

TITLE string

The specified set as command prompt windows.

Example :

Title Virendra Yaduvanshi.

Copy From the Command Prompt

Right-click anywhere in the Command Prompt window and choose Mark. Now, highlight with left mouse button whatever like to copy. Once selection is made, press Enter.

Now we can paste that information into any program.

Open the Command Prompt From Any Location

There’s a super easy Command Prompt trick that will let open a Command Prompt window from whatever folder we’re viewing in Windows.

All it is depend on navigate, in Windows, to the folder where we want to start working from in the Command Prompt. Once there, hold down Shift key while right-click anywhere in the folder. Once the menu pops up, notice there is an entry that’s not usually there: Open command window here.

Drag and Drop For Easy Path Name Entry

There’s a Command Prompt trick that makes this much easier: drag and drop.

Just navigate to the folder you want the path for in Windows Explorer. Once there, drag the folder or file to the Command Prompt window and let go. Like magic, the full path is inserted, saving you a considerable amount of typing depending on the length and complexity of the path name.

Robust File Copy

Robocopy, or “Robust File Copy”, is a command-line directory and/or file replication command. Robocopy functionally replaces Xcopy, with more options. It has been available as part of the Windows Resource Kit starting with Windows NT 4.0, and was first introduced as a standard feature in Windows Vista and Windows Server 2008. The command is robocopy.

View Your Computer’s Important Network Information

Everything we want to know about our network connection is available somewhere in the Control Panel in Windows, but it’s much easier to find, and much better organized, in the results from the ipconfig command.

Map a Local Folder Just Like a Network Drive

The subst command is used to map a local folder just like a Network drive. Just execute the subst command, followed by the path of the folder which need to appear as a drive. For example, let’s say you want your C:\Windows\Fonts folder to appear as the Q: drive. Just execute subst q: c:\windows\fonts and you’re set!

Automatically Complete Commands with Tab Completion

Tab completion is another Command Prompt trick that can save you lots of time, especially if your command has a file or folder name in it that you’re not completely sure of.

Run Commands Simultaneously

We can put && between two commands and execute them one after another. The command on the left will execute first followed by the command on the right of the double ampersand.

Change Windows Explorer’s Starting Location

Right-click on the Windows Explorer icon in taskbar. Right click on it and choose Properties.

Under “Target,” change the path to the folder you want Windows Explorer to display by default

Backspace Key to Go Up

Windows Explorer in Windows XP allowed you to move up a folder using the Backspace key. If you got used to that feature, you’re probably frustrated that it was removed in Windows 7. The Backspace key in Windows 7 moves you Back in the folder browsing history, not to the parent folder of the current folder.

 

Add Copy To / Move To to the Windows Explorer Right Click Menu

A hidden functionality in Windows allows you to right click on a file, select Copy To Folder or Move To Folder, and the move to box will pop up and let you choose a location to either copy or move the file or folder to. The quick registry hack to get this working:-

1) Go to HKEY_CLASSES_ROOT\AllFilesystemObjects\shellex\ContextMenuHandlers

Now you will double-click on the (Default) value and enter the following:

{C2FBB630-2971-11D1-A18C-00 C04FD 75D13}

For Move to – Now when you right click on a file or folder, you should see the following options:

{C2FBB631-2971-11D1-A18C-00C04FD75D13}      Page 16

Use check boxes to select multiple files
In order to select multiple files for an operation such as copying, moving or deleting in Windows Explorer, you generally use the keyboard and the mouse, Ctrl-clicking every file you want to select.
Steps are as below :
1. In Windows Explorer, click Organize, and then select “Folder and search options.”
2 .Click the View tab
3. In Advanced Settings, scroll down and check the box next to “Use check boxes to select items.” Click OK.

Launch taskbar apps without a mouse

Likewise, you can launch any program on the taskbar without the mouse. Press the Windows key and the number that corresponds to the position of the application on the taskbar — for example, Windows key-1 to launch the left-most application on the taskbar, Windows key2 to launch the second left-most application and so on.

Filtering Folder Content Views

Moving your mouse over a Heading will display a drop-down arrow. Clicking on the down arrow will display a set of filtering options (check boxes) that can be set for viewing items in the column.

Ranking Functions – ROW_NUMBER,RANK,DENSE_RANK,NTILE

SQL Server 2005 introduced four new ranking functions: ROW_NUMBER, RANK, DENSE_RANK, and NTILE. These functions allow you to analyze data and provide ranking values to result rows of a query. For example, you might use these ranking functions for assigning sequential integer row IDs to result rows or for presentation, paging, or scoring purposes.

All four ranking functions follow a similar syntax pattern:

function_name() OVER([PARTITION BY partition_by_list] ORDER BY order_by_list)

The basic syntax follows.


ROW_NUMBER() OVER ([<partition_by_clause>] <order_by_clause>)
RANK() OVER ([<partition_by_clause>] <order_by_clause>)
DENSE_RANK() OVER([<partition_by_clause>]<order_by_clause>)
NTILE(integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)


Ranking functions are a subset of the built in functions in SQL Server. They are used to provide a rank of one kind or another to a set of rows in a partition. The partition can be the full result set, if there is no partition. Otherwise, the partitioning of the result set is defined using the partition clause of the OVER clause. The order of the ranking within the partition is defined by the order clause of OVER. Order is required even though partition is optional.

 

ROW_NUMBER: ROW-NUMBER function returns a sequential value for every row in the results. It will assign value 1 for the first row and increase the number of the subsequent rows.

 

Syntax:

SELECT ROW_NUMBER() OVER (ORDER BY column-name), columns FROM table-name

 

OVER – Specify the order of the rows

ORDER BY – Provide sort order for the records


RANK: The RANK function returns the rank based on the sort order. When two rows have the same order value, it provide same rank for the two rows and also the ranking gets incremented after the same order by clause.

Syntax:
SELECT
RANK() OVER ([< partition_by_clause >] < order_by_clause >)

SELECT RANK() OVER ([< partition_by_clause >] < order_by_clause >)

Partition_by_clause – Set of results grouped into partition in which RANK function applied.
Order_by_clause – Set of results order the within the partition


In the above example, based on the sort order Employee Name, the Rank is given.
The first two rows in the list has same Employee Name, those rows are given same Rank, followed by the rank of next for another set of rows because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.


DENSE_RANK: The DENSE_RANK function is very similar to RANK and return rank without any gaps. This function sequentially ranks for each unique order by clause.

Syntax:
SELECT DENSE_RANK() OVER ([< partition_by_clause >] <order_by_clause>)</order_by_clause>

SELECT DENSE_RANK() OVER ([< partition_by_clause >] )

Partition_by_clause – Set of reults grouped into partition in which DENSE RANK function applied.
Order_by_clause – Set of results Order the within the partition

NTILE: NTILE () splits the set of rows into the specified number of groups. It equally splits the rows for each group when the number of rows is divisible by number of group. The number is incremented for every additional group. If the number of rows in the table is not divisible by total groups count (integer_expression), then the top groups will have one row greater than the later groups. For example if the total number of rows is 6 and the number of groups is 4, the first two groups will have 2 rows and the two remaining groups will have 1 row each

Syntax:
SELECT NTILE (integer_expression) OVER ([< partition_by_clause >] <order_by_clause>) </order_by_clause>

 

SELECT NTILE (integer_expression) OVER ([< partition_by_clause >] )

(integer_expression) – The number of groups into which each partition must be divided.


 

 

 

It’s many times observed, a SQL Server Developer commonly use SELECT for assigning values to variables. This was fine and a perfectly valid practice till SQL Server 6.5 But SQL Server 7.0 introduced the new SET statement for initializing and assigning values to variables and SQL Server 7.0 Books Online also stated: It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable.”

There are many Differences between SET and SELECT as

1) SET is the ANSI standard way of assigning values to variables, and SELECT is not.

2) SET can only assign one variable at a time, SELECT can make multiple assignments at once.

Example :

– Declaring variables

DECLARE @Variable1 AS int, @Variable2 AS int

– Initializing two variables at once

SELECT @Variable1 = 1, @Variable2 = 2

– The same can be done using SET, but two SET statements are needed */

SET @Variable1 = 1

SET @Variable2 = 2

3) If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned

4) When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from its previous value)

Please Comments on same J


There may be probability that we have hundreds of SQL Server’s Jobs scheduled to run every day on a SQL Server instance.  Here is a script to find quickly the stored procedure or command text used by SQL Server’s Job.

Select JB.Name [Job Name], JBSTP.Command [Command Text] from MSDB.dbo.Sysjobs JB
INNER JOIN MSDB.dbo.sysjobsteps JBSTP on JB.Job_ID = JBSTP.Job_ID
where JBSTP.Command Like   ‘%SP or Command Name%’

 

Its frequently happens when we need to delete a database or restore a database and few users are still connected to that specific Database, it’s through an error saying that database is used by other users. Here is a script, using this we can drop/kill all connection to a specific database.

– Let Database name is VIRENDRATEST and we want to dropp all connection of this DB.

Use Master
Go 

Declare @dbname sysname
Set @dbname =
‘VIRENDRATEST’

–Write the database name that you want to drop connections

Declare @spid int
Select @spid =
min(spidfrom master.dbo.sysprocesses where dbid = db_id(@dbname)
While @spid is 
not NULL

Begin
Execute (‘Kill ‘ + @spid)
Select @spid
min(spidfrom master.dbo.sysprocesses where dbid =db_id(@dbnameand spid > @spid

End

Print ‘All connection DROPPED!’

In SQL Server Log Shipping process, we can see two files having extension name like .TUF and .WRK. These both files only generate on Log Shipping’s Secondary Server.
Actually SQL server manage internally handle the file copy process and restoration process at Secondary server using LS Copy and LS Restore job which are created at secondary server during log shipping initialization process. To manage the file copy process from Primary server to Secondary server, .WRK files temporarily generated. Means, The .wrk file got generate when the transaction log backups files are being copied from the backup location (Commonly at Primary Server end) to the secondary server by the agent job named as LS-Copy on the secondary, and when file copied completely at secondary server, they renamed to the .trn
extension. The temporary naming using the .wrk extension indicates/ensure that the files will not picked up by the restore job until successfully copied.

The .TUF file is the Transaction Undo File. It got generated only when we have configured the Log Shipping with Stand by Option only. As we know, in Stand by Log Shipping option, Secondary Database is available to user in read mode. In this scenario .TUF file Keeps Pending Transaction Which are in Log File came from Primary server, and when next Log Backup will come from primary server they can be synchronized at Secondary Server.

Happy Reading ! You live several lives while reading  :-) 

 

The below image is a illustrative explanation of LOG SHIPPING concept.