Digicorp

Today, we will examine something very simple and very generic that can apply to hordes of programming languages. Let’s take a common question that is frequently discussed – What is difference between Line Feed (\n) and Carriage Return (\r)?

Prior to continuing with this article let us first look into few synonyms for LF and CR.

Line Feed – LF – \n – 0×0a – 10 (decimal)

Carriage Return – CR – \r – 0×0D – 13 (decimal)

Now that we have understood that we have two different options to get new line, the question that arises is – why is it so?

The reason is simple. Different operating systems have a different way of understanding new line. Mac only understands ‘\r’ as new line, while Unix and Linux understand ‘\n’ as new line character. Our favorite OS windows needs both the characters together to interpret as new line, which is ‘\r\n’. This is the reason why a file created in one OS does not open properly in another OS and makes it messy.

Now, let us see how we can create a new line in SQL Server. It is a very simple script yet very useful when we have to do run print something or generate scripts. I have illustrated two examples below that are very easy to understand. In the first example, there are no new line chars inserted and for the same, everything is displayed in a single line. However, in the second example, new line char is inserted and the lines are separated with a new line.

Example 1: No new line feed char

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL SELECT SecondLine AS SL' )
GO

Example 2: With new line feed char

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL ' +@NewLineChar + 'SELECT SecondLine AS SL' )
GO


I hope my examples make things more clear to you all. Let me have your feedback on this article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

People are judged from their questions and not their answers. I received wonderful question the other day.

How sqlcmd can be used along with currently running query script posted on your blog?

Please read following two posts before continuing this article as they cover background of this article.

SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor

SQL SERVER – Find Currently Running Query – T-SQL

If due to long running query or any resource hogging query SQL Server is not responding sqlcmd can be used to connect to server from another computer and kill the offending query. SQL Server provides a special diagnostic connection which allows administrators to access SQL Server when standard connection is no tpossible. Except very extreme situations dedicated administrator connection (DAC) is always successfull.

Let us see how we can use DAC using sqlcmd.

Connect SQL Server using sqlcmd and -A option which will establish DAC.

C:\>sqlcmd -S localhost -U sa -P dev -d master -A

For our test purpose run following query which overload server and probably make it unresponsive. If your configure is not strong I strongly suggest not to run following query.

Following query is just for test purpose and not part of solution.

USE AdventureWorks
GO
SELECT *
FROM Person.Address
CROSS JOIN Person.Address a1
CROSS JOIN Person.Address a2
CROSS JOIN Person.Address a3
CROSS JOIN Person.Address a4
CROSS JOIN Person.Address a5
CROSS JOIN Person.Address a6
CROSS JOIN Person.Address a7
CROSS JOIN Person.Address a8
CROSS JOIN Person.Address a9
GO

Once successfully connected it will provide prompt 1> enter following T-SQL query which will give SessionID of currently running query along with its elapsed time.

SELECT
req.session_id,
req.status,
req.total_elapsed_time
FROM sys.dm_exec_requests req
WHERE status = 'running'
AND req.total_elapsed_time > 1

Our previously running query gave session id 52 in on my server. The session id may be different for each SQL Server. Once the session id is figured out it can be killed using KILL [SessionID] command. Always make sure to type command GO after each complete query.

KILL 52

Once above query has run it will kill our example long running query and give following error.

Msg 233, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)

sqlcmd is really useful utility of SQL Server and it comes in handy when server is not responsive. I strongly suggest to bookmark this article as it can come to rescue when nothing works and SQL Server is unresponsive.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL Server is great product and it has many feature which are very unique to SQL Server. Regarding feature of SQL Server where multiple variable can be declared in one statement, it is absolutely possible to do.

Method 1: Multiple statements for declaring multiple variables

DECLARE @Var1 INT
DECLARE @Var2 INT
SET @Var1 = 1
SET @Var2 = 2
SELECT @Var1 'Var1', @Var2 'Var2'
GO

Method 2: Single statements for declaring multiple variables

DECLARE @Var1 INT, @Var2 INT
SET @Var1 = 1
SET @Var2 = 2
SELECT @Var1 'Var1', @Var2 'Var2'
GO

From above example it is clear that multiple variables can be declared in one statement. In SQL Server 2008 when variables are declared they can be assigned values as well.

Reference : Pinal Dave (http://www.SQLAuthority.com)