Man Made Code

How to limit the data returned by each columns in a SELECT statement?

2009-11-13T11:14:59.72-07:00

This article is specific to MS SQL Server. There can be various need based scenarios where the data returned by the SELECT statement should be limited to certain length for the columns of data type VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), TEXT, NTEXT, and IMAGE data types. This can be achieved using SUBSTRING or LEFT functions for specific columns in SELECT statement. But if you have a SELECT statement having many columns of the data types specified before and you want to apply data returned length limitation for each column, the better option would be to use TEXTSIZE command.

TEXTSIZE command in SQL Server can be used to limit the length of data returned for each column in a SELECT statement. This command is applicable to the columns of following data types:

VARCHAR(MAX)
NVARCHAR(MAX)
VARBINARY(MAX)
TEXT
NTEXT
IMAGE

The syntax to use the command is as given below:
SET TEXTSIZE < Specify the number of bytes here >

Example:
The below statement will set the data returned length to 200 characters and run the SELECT command with the data returned length to 200 characters for each column
SET TEXTSIZE 200
SELECT RecID, TxtCol01, TxtCol02, TxtCol03 from MyTableName

To fetch the default size of TEXTSIZE, use the below command
SELECT @@TEXTSIZE

To set the TEXTSIZE value to its default value of 2147483647 bytes, use the below command
SET TEXTSIZE 2147483647

How to swap data between two columns of a table in SQL Server?

2009-08-16T10:45:17.977-07:00

When we talk about swapping of data between two columns of a table in SQL Server, most of us think of writing SQL scripts in Stored Procedure to achieve it. Well, is it really required? Can a one line statement be written to swap data between two columns in a table? The answer is yes. In this article I cover a simple technique to swap data between two columns of a table.

Consider a table by name "MyTable" having 3 fields RecID as Integer, ColValue01 as VarChar of 50 and ColValue02 as VarChar of 50; as written in the below SQL statement
CREATE TABLE [MyTable] (RecID INT, ColValue01 VARCHAR(50), ColValue02 VARCHAR(50))

Now due to some program error, the data which was suppose to go to ColValue02 has gone to ColValue01 and Vise-a-Versa. We need to swap the data between the two columns, run the below script to achieve the same.

#Option 01: The process is simple, first declare a temp variable, move column 2 value to temp variable, then move column 1 value to column 2 and finally move temp variable value to column 2.

DECLARE @MyTemp AS VARCHAR(50)
UPDATE [MyTable] SET @MyTemp = ColValue02, ColValue02 = ColValue01, ColValue01 = @MyTemp))

#Option 02: The same result can be achieved without using a temp variable by simply cross assigning the column names as shown the scrip below.
UPDATE [MyTable] SET ColValue01 = ColValue02, ColValue02 = ColValue01))

Note: The column data can be exchanged between two or more columns of same data type only.

Favicon – The little icon on the address bar

2009-07-14T05:52:24.881-07:00

A Favicon is the little image displayed in your favorites and in the left corner of the Address Bar of Internet Explorer when you bookmark a page. The Favicon was first popularized by its appearance in the "Address Bar" and "Favorites Folder" using Internet Explorer. Currently, Internet Explorer 5.x (IE 5.x), Linux's Konqueror and Mozilla/Netscape 6.x (NS 6.x) Web browsers support the Favicon function.

A Favicon is a 16x16 pixel, 16 color icon graphics image with an .ico extension. It can be made in most graphic programs. To make it work you create the 16 x 16 pixel graphic, save it as Favicon.ico and upload it to the root directory of your web site (Upload using Binary Transfer Mode). Then add the following html code in between the heading tags of the web pages where you want the Favicon to appear.

………………..
………………..
………………..
………………..

There are some common problems with Favicon. They are not permanent and are stored in your temporary Internet files folder. If your Temporary Internet Files folder is cleared, your Favicons will be lost. In some cases, although you have changed the Favicon it is not reflected in the browser, this could be because of the cache problem. The browser may not be refreshing the Favicon. Just click on the browser default icon (In IE it is the blue color "e" symbol in the address bar) or your old Favicon and drag it to the body (Web page document area). This will refresh the Favicon of the browser and show the new Favicon of your web site.

Smarter the better – SQL Server tips – part 03

2009-06-13T10:42:33.747-07:00

This is in continuation to my previous post on SQL Server Tips.

1. SQL function RAND() is used to generate random numbers. It can have an input parameter as SEED

Syntax: RAND([SEED])

Use the below query to get new random number always (The chance of duplicate random number is possible if the query is executed more than 1 time with-in the same milli-second)

SELECT RAND(

(

DATEPART(MM, GETDATE()) * 100000 ) + (DATEPART(SS, GETDATE()) * 1000 ) + DATEPART(MS, GETDATE())

)

2. NULLIF(EXP1, EXP2) is used to compare two expressions are equal and return a NULL value if so. This can be used as a handy tool to compare two column data having same values

The below SQL statement will return the value True

Select ISNULL( NULLIF(‘ABC’, ‘ABD’), ‘True’ )

Suppose there are two columns FirstName and LastName in a table TblNames and you want to cross check and compare the two columns having same values, use the below query against each record in the table

Select RecID, ISNULL( NULLIF(FirstName, LastNam), ‘True’ ) from TblNames

3. Changing Object Owners: If you want to change the Object Owner for an the objects in the database, you can use this stored procedure

sp_changeobjectowner @objname = 'objectName' , @newowner = 'ownerName'

Smarter the Better – SQL Server Tips – Part 02

2009-05-16T11:52:01.019-07:00

This is in continuation to my previous post.

Use table variables instead of temporary tables.
Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available in SQL Server 2000 only.

Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.

I will add more as I find more...

Applying Windows Integrated Authentication in IIS 6 gives error HTTP 401.1 – Unauthorized: Logon Failed

2009-04-11T01:18:38.86-07:00

Recently I did hosting of a website on IIS 6 on Windows XP Professional SP2. The website is applied with Basic Authentication and Windows Integrated Authentication. Strangely the Integrated Authentication does not work, but the same website on Windows 2003 Server SP2 works fine. When attempted to browse the website, it does ask for username and password; but does not login and shows the error HTTP 401.1 – Unauthorized: Logon Failed.

I did some research on the Internet and found help at Microsoft Support (ref. link below). This issue is with Windows XP SP2 and Windows 2003 Server SP1, which include a loopback check security feature that is designed to help prevent reflection attacks on the computer. When you use the fully qualified domain name (FQDN) or a custom host header to browse a local website that is hosted on a computer using IIS 6 shows the error “HTTP 401.1 – Unauthorized: Logon Failed”. This happens when the website uses Integrated Authentication and has the name which is mapped to the local loopback address.

The workaround for the issues needs changes in the registry, which should be done correctly else it would lead to serious problems. It’s advisable to keep the backup of the registry before making the changes.

Method 1: Specify Host Names

Open Registry Editor
Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0
Right-click MSV1_0, point to New, and then click Multi-String Value
Type BackConnectionHostNames, and then press ENTER
Right-click BackConnectionHostNames, and then click Modify
In the Value data box, type the host name or the host names for the sites that are on the local computer, and then click OK
Quit Registry Editor, and then restart the IIS Admin service

Method 2: Disable the Loopback Check

Open Registry Editor
Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
Right-click Lsa, point to New, and then click DWORD Value
Type DisableLoopbackCheck, and then press ENTER
Right-click DisableLoopbackCheck, and then click Modify
In the Value data box, type 1, and then click OK
Quit Registry Editor, and then restart your computer

Ref. Link: http://support.microsoft.com/default.aspx?scid=kb;EN-US;896861

Smarter the Better – SQL Server Tips – Part 01

2009-03-08T07:04:17.866-07:00

There are many occasions where a SQL Server Developer uses scripts which take longer to execute. I would rather say a wrong choice or approach in writing scripts caused the execution of the script longer. There are smarter ways to achieve results. In this post I will try to consolidate few tips on smarter SQL Server programming.

Do you want to delete all rows from a table?
Use Truncate instead of Delete statement. Truncate is faster and does a reseed of identity column.

Use Owner Name prefix while writing Select statement:
Select thColumnName from dbo.theTableName
If the Owner Name is specified, the SQL Query Optimizer does not have to decide whether to retrieve from dbo.theTableName or some other owner’s table and avoids recompilation.

Do not use “Select Count(*)…”
The better way to fetch the count of rows:
Select rows from sysindexes where id = Object_ID(‘theTableName’) and IndID < 2

Do not use “sp_” prefix in your Stored Procedures:
The prefix “sp_” for Stored Procedures is a reserved prefix in MS SQL Server. When a stored procedure is given a call, the SQL Server first looks for the procedure in System Procedure List and then in User Defined Procedure List; hence delaying the execution of the procedure.

I will try to add more tips like these in future.

Finding Stored Procedure Create and Modified Date in SQL Server

2009-03-02T06:05:32.567-07:00

We all have created Stored Procedures in SQL Server. We keep doing regular updates on the applications using SQL Server database. The real problem comes when the application need to be made live and the database stored procedure is required to be synced with the live server. Unless there is no track of change monitored, it would be difficult while deploying the updates and upgrades in SQL SPs on to the live server. Now the question is more clear... Can I find the last modified date for a stored procedure in SQL Server?

Well, if you are using SQL Server 2000. You can get only the last modified date. The syntax for the same is

SELECT [Name], crDate
FROM sysobjects
WHERE [type] = 'p' and [Name] = ''

In case of SQL Server 2005, it is possible to find the last modified date for a stored procedure. The syntax for the same is

SELECT [Name], Create_Date, Modify_Date
FROM sys.objects
WHERE [type] = 'P' and [Name] = ''

What is the max length of a file name or folder name in Windows OS?

2009-02-20T08:28:29.343-07:00

Most of you have been using Windows OS. Have you ever tried finding, what is the length of a file name or a folder name could be? Except those who have specifically worked on File system Object (FSO) would have not bothered to know as how a file and folder name is defined in Windows OS.

Well the answer to this question is that it’s not the length of the file name or folder name; but the number of characters in the full path of the file or folder defines the max size of a file name or folder name in Windows OS. The max length of a file or folder path including itself is 255 characters (in Vista this is 260 characters).

For example, a folder name in the root of a drive (c:\ or d:\ or etc…) would be 244 characters long and a file name in the root of a drive would be 251 characters. If you have a folder name of length 244 characters long in the root drive of your hard disk, you will not be able to save a file or create a sub-folder under it. You would receive an error message “Destination path too long”.

I will end this article with few questions, which I will try to cover in my next article. Most of us have developed Document Repository System or written programs for uploading and storing files on the disk. Have you really taken care to check the file name or folder name length before uploading it to the server? How do we store files with long names? Is there any alternative apart from physical file storage on the disk?