Blogs - Gyan Guru

Smarter the Better – SQL Server Tips – Part 01

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… Continue

Added by Akash on March 8, 2009 at 7:30pm — No Comments

Understanding and Using PIVOT clause in SQL Server 2005

SQL Server 2005 has lots of new features, of which one is PIVOT clause. This is similar to Pivot tables in MS Excel and Matrix Control in Reporting Service. Here is a simple example demonstrating how to use PIVOT clause. I am right now taking a simple example. There are scenarios where there could be a need to write a big stored procedure to build a Pivot table in lower versions of SQL Server.

Do run the below query in Query Analyzer t… Continue

Added by Akash on February 7, 2009 at 9:24pm — No Comments

Using Advanced Stream Redirector (ASX) to stream audio/video through HTTP Protocol

Advanced Stream Redirector (ASX) files are based on the Extensible Markup Language (XML) syntax, and are made up of various elements with their associated tags and attributes. This is designed to store a list of Windows Media files (.WMA, .ASF, .WMV) to play during a multimedia presentation. The ASX files can be effectively used to play songs over the Internet/Intranet through HTTP protocol.

I will not write here on how one shoul

… Continue

Added by Akash on February 1, 2009 at 6:00am — No Comments

How to open .MSG Outlook file in Internet Explorer from a web application?

Considering an example web application where the application allows uploading files with extension DOC, RTF, XLS, PPT, GIF, JPEG, PDF and MSG. The view page has the list of attachments and allows viewing files by invoking relevant applications. But files with .MSG do not open or do not invoke Outlook to view it.

The solution for this is in fact very simple. Do follow the following steps:

Open IIS and navigate to the web site

… Continue

Added by Akash on January 18, 2009 at 11:30am — No Comments

How to check a web form can have HTML/Script Injection?

Injection of HTML code and/or client side scripts like JavaScript, VBScript, and Jscript by malicious web users into the web pages viewed by other users is a computer security vulnerability typically found in web applications. This is popularly known as Cross Site Scripting (XSS) Attack. Injection of the code can be done either from a web form or through a query string input. To check whether your web application is open to XSS, insert the following code given below in the Input T… Continue

Added by Akash on January 10, 2009 at 10:30am — No Comments

Gardening W3WP Worker Processes in IIS Web Garden

When a web application or web site is created in IIS 6.0, by default the application pool chosen is “Default Application Pool”. The administrator can create a new application pool. To know more on how to create an application pool, click here. By default there is one worker process (W3WP.EXE) associated to each application pool. A single Worker Process forms an application pool to which Web Applications and W… Continue

Added by Akash on January 4, 2009 at 7:49pm — No Comments

Another way to export SQL Server table data to a Text file

No doubt that a DTS or SSIS package can be used to export SQL Server table data to a text file. The below article is to demonstrate another way to export SQL Server table data to a text file (This is with reference to SQL Server 2005 and above). The xp_cmdshell extended stored procedure runs operating system commands from within the database engine. This is disabled by default in SQL Server 2005. This can be enabled in two ways: 1. Use sp_config to update advance options EXEC… Continue

Added by Akash on January 2, 2009 at 2:00pm — No Comments

Why we are not able to Index content in a PDF file using Microsoft’s Indexing Service?

Problem: We have created a Indexing Catalog in Microsoft’s Indexing Service and have included the folders to be indexed. We did a merge and the restarted the Indexing Service to enable the catalog. A server side script written in ASP and/or ASP.Net is written to perform search and display the results. We are able to see results with file extensions HTML, HTM, ASP, ASPX, DOC, XLS, and PPT. There are many PDF files in the website and we are not able to see any results from PDF files… Continue

Added by Akash on January 1, 2009 at 12:24pm — No Comments

Not able to change Server Log-on Mode in SQL Server

Problem: The SQL Server was installed with “Windows Only” log-on mode. We have logged-in as admin and try to change the log-on mode using server registration property to Mixed Mode (SQL Server and Windows). When we apply the setting and revisit the registration property, the change is not applied. How do we change the logon mode to Mixed mode? Solution: Open Registry Editor and navigate to HKEY_LOCAL_MACHINE >> SOFTWARE >> MICROSOFT >… Continue

Added by Akash on December 30, 2008 at 6:07pm — No Comments

How to remove yellow background from web form fields?

Some of us may be wondering as why few fields in the web form have a yellow background. Amazingly your colleague’s computer shows all web form fields normal (the way it should look). Well it’s clear that the web form HTML and CSS is proper, the question left is what is causing it to happen? What is causing it to happen is the frequently used, very handy GOOGLE TOOLBAR. In the installed Google Toolbar, select the “Settings” drop down menu (on the extreme right side) and click on “… Continue

Added by Akash on December 21, 2008 at 2:16pm — No Comments

How to clear Internet Explorer Browser History from Command Line?

Is it possible to clear Internet Explorer Browser History from Command Line? The answer is yes, very much. You can opt to write a batch file with the set of commands discussed below. To check and use these commands, open the command prompt and copy/paste the command; and press enter to execute it. Please note that these commands are applicable only for IE on Windows XP or Vista. Temporary Internet Files RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8… Continue

Added by Akash on December 8, 2008 at 10:57am — 3 Comments

Converting Row Data as Comma Separated String

One of my officemate Ranjan did some research on internet and found a simple and quick solution to have row data as comma separated string. The requirement of the task is as follows Example Requirement Case: There is a table by name tbl_CityState with three columns, RecId which is an Identity Column, StateName which is VarChar(50) holding the name of the state or province, and finally the CityName which is VarChar(50) holding the name of the cities with-in a State o… Continue

Added by Akash on December 4, 2008 at 7:00pm — No Comments

How to select random rows from and SQL Table?

IN SQL Server, it is easy to select one random record or a sequence of random records from a database table. For example if you have requirement to pick a single quiz winner randomly from the database table or you are making an online test application and need to pick X number of questions randomly from the database table; you can certainly use the below SQL queries… To select a single random record from a table run the below Select TOP 1 [IdentityColumnName] from [Table… Continue

Added by Akash on November 17, 2008 at 6:00am — No Comments

Row Size limitation in SQL 2000 and 2005

IN SQL Server 2000 and 2005, while creating a table it is possible to create as many number of columns with VARCHAR data type of size 8000. If you are using Query analyzer, you may notice a warning message reading… “Table has been created but its maximum row size (NumberofBytes) exceeds the maximum number of bytes per row (8060).” Not only this, when you try to insert or update a record in a table having columns with VARCHAR data type and the data size to be inserted in the columns is mo… Continue

Added by Akash on November 16, 2008 at 5:30am — No Comments

How to eliminate duplicate records from a database table?

Description of the problem: A database table by name MyTable has 3 data fields MyID, MyName, MyAddress. • MyID is Identity and primary key (PK) with Int data type • MyName is a VarChar(200) data type • MyAddress is TEXT data type Accidently, the PK and Identity is removed and there are duplicate records in the table. Now we need to introduce the PK back on MyID by cleaning the database table. There are 10,000,000 records in the table and we really do not know which record has got… Continue

Added by Akash on November 15, 2008 at 4:54pm — No Comments

CDOSYS Weird Problem – The email body contains ! symbol in-between the content

Problem: The code is simple and straight. The code builds HTML email and uses CDOSYS object to send the email to recipients. Logic is simple, build an HTML mailer string in a VBScript variable myMailBody. Assign the variable myMailBody to the HTML Body of the email using CDOSYS object and send the mailer to recipients. But the problem starts here… When the recipient views the mailer, S/He can see an ! symbol in-between the mailer content. Solution: A… Continue

Added by Akash on November 12, 2008 at 1:30pm — No Comments

SQL Server Permissions - Fixing Orphaned Users

Problem: The database backup is taken from the development environment and the BAK file is restored on the production server. On restoring the database from the BAK file, the database users with DB Owner rights are also restored along with the database. But checking the security >> user list in SQL server does not display these users. The SQL server does not allow deleting the users from the database manually as they own objects in the DB. SQL Server does not allow creating… Continue

Added by Akash on November 12, 2008 at 4:00am — No Comments

Internet Explorer - Image files is saved as .BMP for GIF/JPG formats

Problem: The website uses all GIF and JPG format images; but in IE when the same image file saved, saves the image file in .BMP format. IE File Save As saves for all image files in .BMP format Solution: There are three reasons for the problem. o There may be damaged ActiveX or Java Objects in the Download programs section of Internet Explorer. To resolve, remove damaged objects from Temporary Internet Files and Folders. o T… Continue

Added by Akash on November 11, 2008 at 3:10pm — No Comments

File Upload shoots error "Invalid Procedure Call Or Argument"

Problem: The File upload code works fine in the local environment, but shoots error on the production server as shown below. < % ……… vDataBounds = MidB(biData, nPosBegin, nPosEnd-nPosBegin) …………% > Invalid Procedure Call Or Argument Solution: MID is intended for use with languages that use the single-byte character set (SBCS), whereas MIDB is intended for use with languages that use the double-byte character set (DBCS).… Continue

Added by Akash on November 11, 2008 at 2:30pm — No Comments

IIS 6 - Enabling Web Service Extensions

To help minimize the attack surface of the server, IIS 6.0 is not installed on Windows Server 2003 by default. When you first install IIS 6.0, it is locked down — which means that only request handling for static Web pages is enabled, and only the World Wide Web Publishing Service (WWW service) is installed. None of the features that sit on top of IIS are turned on, including ASP, ASP.NET, CGI scripting, FrontPage® 2002 Server Extensions from Microsoft, and WebDAV publishing. If yo… Continue