Add Client Name in SQL Profiler Results

Often you may need to analyze results from the SQL Server profiler to know blocking CPU time, Reads etc. By default the profiler shows you a set of columns in the grid. One of the columns is the ClientProcessId. This column gives information about the Id of the client that accesses the server.

If you want to know the name of the client, you can add that column in the grid using the following steps

Start SQL Server profiler > Navigate to File > Templates > Edit template

A new window will open. Click on "Events Selection" Tab. At the bottom, check the option labeled "Show all columns"

In the window, check Hostname checkbox in the Audit Login event as shown above and click the Save button. Now Restart the Profiler. It will start showing the client name too in the results.

Feedback:

SQL Server File Location: Points to Consider

In SQL Server, file locations can be specified in some commands like during bulk insert, listing directory information, using OPENROWSET function, etc. When you specify file location, you should note the following points:

1. By default SQL Server searches for the file location in Server's directory. For example, in a Bulk insert command, if you specify D:\emp.txt, it is by default searched in the Server's
directory. If your server name is myServer, SQL Server will try to find out the file path in that server only.

2. SQL Server will not recognize client's file location until specified using the UNC path. For example say your Server name is myServer and the SQL Server client is installed in your local system named myClient that connects to Server. Say you have a file named emp.txt in your computer’s D drive and you want to specify that file location in the BULK INSERT command. In this case, the command should be

3. When UNC path is specified, the file should be given atleast a read access to the server, in which

the Query is executed. In the above example, D:\emp.txt file should be given atleast read only access on the server myServer. Otherwise an error "File not found error" will be thrown

The same applies to other functions as well which accept file locations - such as xp_cmdshell, OPENROWSET etc.

Feedback:

SQL Server: LPAD and RPAD functions equivalent

Oracle has two formatting functions LPAD and RPAD which formats the number by appending leading and trailing zeros respectively. SQL Server does not have direct equivalent functions.

However you can simulate these functions using other string functions available in SQL Server. Let us take a practical example. Suppose you want to export data to fixed format file and the requirement is that the number should be 10 digits long, in such a way that if the total number of digits is less than 10, the remaining digits should be filled with zeroes.

Consider the following example

declare @num int
set @num=872382
select
right(replicate('0',10)+cast(@num as varchar(15)),10) aS lpad_number,
left(cast(@num as varchar(15))+replicate('0',10),10) as rpad_number

The above code shows numbers in two formats. The first is left padded and second is right padded. The replicate function is used to replicate 0 for 10 times and the actual number is converted to string and then appended with zeroes.

The right function picks the last 10 digits from the result, so that it has 4 leading zeroes, as the original number shown in the code above has only 6 digits. We then reverse the same by converting a number to varchar and appending leading zeroes at the end. We then use the LEFT function which picks the number where last 4 digits are zeroes.

OUTPUT

Feedback:

SQL Server: Schedule Jobs every Alternate Week

SQL Server agent is used to schedule a job that has to run periodically. If you are a DBA, one of your admin tasks is to schedule jobs. Suppose you are in a situation to run a set of codes every alternate Saturday, you can do it using the following steps:

Open SQL Server Management Studio (SSMS). Under SQL Server agent, create a new job named test. Click on Schedules and button called New.

A new window will popup. Under frequency section, select weekly for "Occurs" option. Select 2 on "Recurs every" option. Under days option choose "Saturday". Click ok.

Now the job will run every alternate Saturday on the time specified.

Feedback:

SQL Server: Resolving DateTime Conversion Errors

Sometimes your queries may return errors when using DateTime values. Consider the following select statement

select cast('19/12/2000' as datetime)

This statement will fail with the following error message:

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The reason is that SQL Server either does not recognize the input date format or the date is invalid. It is important to know the Server's date setting before using any date formats in your queries.

There is a DBCC command which can be used to determine the current date format

DBCC USEROPTIONS

When you run the code, the third row gives you the current dateformat. It is mdy on my machine, so you should input dates in mm-dd-yyyy format. If it is dmy, you should input it in dd-mm-yyyy format. The input format in most cases, should be based on the server's date format.

Now when you run the following code by formatting dates in mdy format (in sync with your machine format), it works fine:

select cast('12/19/2000' as datetime)



Note: It is also better to use an unambiguous date format YYYYMMDD HH:MM:SS which will work for all date settings.

Feedback: