Social Nookmarks - Images

Technorati
YahooMyWeb
del.icio.us
Digg
Furl
Reddit
Spurl
Blinklist

Convert HTML to DOC. FREE!

Ever wanted to convert the HTML content generated by the ASP.NET to WORD / DOC?
Ever wanted to get rid of VIEWSTATE, FORM and other INPUT fields in ASP.NET?
Ever wanted to get rid of Top of Form and Bottom of Form labels inside of the generated word document?
Ever wanted to get rid of INPUT fields inside of the generated word document?
The solution is simple and apsolutely FREE! Enjoy!

C# Code:

private void ConvertToWord()
{
// Get Rid of VIEWSTATE and FORM tags
TextWriter textWriter = new StringWriter();
Server.Execute("test.aspx", textWriter);
Response.Output.Write(textWriter.ToString().Replace("form_tag_open", "your_tag_open").Replace("form_tag_close", "your_tag_close").Replace("input_tag_open", "your_tag_open").Replace("input_tag_close", "your_tag_close"));

// Convert to WORD
Response.ContentType = "application/msword";
Response.AddHeader("content-disposition", "attachment; filename=My_WORD_Document.doc");
}





Top 10 Mistakes in Web Design

Summary:
The ten most egregious offenses against users. Web design disasters and HTML horrors are legion, though many usability atrocities are less common than they used to be.

Since my first attempt in 1996, I have compiled many top-10 lists of the biggest mistakes in Web design. See links to all these lists at the bottom of this article. This article presents the highlights: the very worst mistakes of Web design. (Updated 2007.) Cartoon - Man searching for 'Honalulu' and getting no results. - Woman: 'Oh, forget it. Let's just go visit my mother in Fargo.'

1. Bad Search
Overly literal search engines reduce usability in that they're unable to handle typos, plurals, hyphens, and other variants of the query terms. Such search engines are particularly difficult for elderly users, but they hurt everybody.

A related problem is when search engines prioritize results purely on the basis of how many query terms they contain, rather than on each document's importance. Much better if your search engine calls out "best bets" at the top of the list -- especially for important queries, such as the names of your products.

Search is the user's lifeline when navigation fails. Even though advanced search can sometimes help, simple search usually works best, and search should be presented as a simple box, since that's what users are looking for.

2. PDF Files for Online Reading
Users hate coming across a PDF file while browsing, because it breaks their flow. Even simple things like printing or saving documents are difficult because standard browser commands don't work. Layouts are often optimized for a sheet of paper, which rarely matches the size of the user's browser window. Bye-bye smooth scrolling. Hello tiny fonts.

Worst of all, PDF is an undifferentiated blob of content that's hard to navigate.

PDF is great for printing and for distributing manuals and other big documents that need to be printed. Reserve it for this purpose and convert any information that needs to be browsed or read on the screen into real web pages.

3. Not Changing the Color of Visited Links
A good grasp of past navigation helps you understand your current location, since it's the culmination of your journey. Knowing your past and present locations in turn makes it easier to decide where to go next. Links are a key factor in this navigation process. Users can exclude links that proved fruitless in their earlier visits. Conversely, they might revisit links they found helpful in the past.

Most important, knowing which pages they've already visited frees users from unintentionally revisiting the same pages over and over again.

These benefits only accrue under one important assumption: that users can tell the difference between visited and unvisited links because the site shows them in different colors. When visited links don't change color, users exhibit more navigational disorientation in usability testing and unintentionally revisit the same pages repeatedly.

4. Non-Scannable Text
A wall of text is deadly for an interactive experience. Intimidating. Boring. Painful to read.

Write for online, not print. To draw users into the text and support scannability, use well-documented tricks:

* subheads
* bulleted lists
* highlighted keywords
* short paragraphs
* the inverted pyramid
* a simple writing style, and
* de-fluffed language devoid of marketese.

5. Fixed Font Size
CSS style sheets unfortunately give websites the power to disable a Web browser's "change font size" button and specify a fixed font size. About 95% of the time, this fixed size is tiny, reducing readability significantly for most people over the age of 40.

Respect the user's preferences and let them resize text as needed. Also, specify font sizes in relative terms -- not as an absolute number of pixels.

6. Page Titles With Low Search Engine Visibility
Search is the most important way users discover websites. Search is also one of the most important ways users find their way around individual websites. The humble page title is your main tool to attract new visitors from search listings and to help your existing users to locate the specific pages that they need.

The page title is contained within the HTML <title> tag and is almost always used as the clickable headline for listings on search engine result pages (SERP). Search engines typically show the first 66 characters or so of the title, so it's truly microcontent.

Page titles are also used as the default entry in the Favorites when users bookmark a site. For your homepage, begin the with the company name, followed by a brief description of the site. Don't start with words like "The" or "Welcome to" unless you want to be alphabetized under "T" or "W."

For other pages than the homepage, start the title with a few of the most salient information-carrying words that describe the specifics of what users will find on that page. Since the page title is used as the window title in the browser, it's also used as the label for that window in the taskbar under Windows, meaning that advanced users will move between multiple windows under the guidance of the first one or two words of each page title. If all your page titles start with the same words, you have severely reduced usability for your multi-windowing users.

Taglines on homepages are a related subject: they also need to be short and quickly communicate the purpose of the site.

7. Anything That Looks Like an Advertisement
Selective attention is very powerful, and Web users have learned to stop paying attention to any ads that get in the way of their goal-driven navigation. (The main exception being text-only search-engine ads.)

Unfortunately, users also ignore legitimate design elements that look like prevalent forms of advertising. After all, when you ignore something, you don't study it in detail to find out what it is.

Therefore, it is best to avoid any designs that look like advertisements. The exact implications of this guideline will vary with new forms of ads; currently follow these rules:

* banner blindness means that users never fixate their eyes on anything that looks like a banner ad due to shape or position on the page
* animation avoidance makes users ignore areas with blinking or flashing text or other aggressive animations
* pop-up purges mean that users close pop-up windoids before they have even fully rendered; sometimes with great viciousness (a sort of getting-back-at-GeoCities triumph).

8. Violating Design Conventions
Consistency is one of the most powerful usability principles: when things always behave the same, users don't have to worry about what will happen. Instead, they know what will happen based on earlier experience. Every time you release an apple over Sir Isaac Newton, it will drop on his head. That's good.

The more users' expectations prove right, the more they will feel in control of the system and the more they will like it. And the more the system breaks users' expectations, the more they will feel insecure. Oops, maybe if I let go of this apple, it will turn into a tomato and jump a mile into the sky.

Jakob's Law of the Web User Experience states that "users spend most of their time on other websites."

This means that they form their expectations for your site based on what's commonly done on most other sites. If you deviate, your site will be harder to use and users will leave.

9. Opening New Browser Windows
Opening up new browser windows is like a vacuum cleaner sales person who starts a visit by emptying an ash tray on the customer's carpet. Don't pollute my screen with any more windows, thanks (particularly since current operating systems have miserable window management).

Designers open new browser windows on the theory that it keeps users on their site. But even disregarding the user-hostile message implied in taking over the user's machine, the strategy is self-defeating since it disables the Back button which is the normal way users return to previous sites. Users often don't notice that a new window has opened, especially if they are using a small monitor where the windows are maximized to fill up the screen. So a user who tries to return to the origin will be confused by a grayed out Back button.

Links that don't behave as expected undermine users' understanding of their own system. A link should be a simple hypertext reference that replaces the current page with new content. Users hate unwarranted pop-up windows. When they want the destination to appear in a new page, they can use their browser's "open in new window" command -- assuming, of course, that the link is not a piece of code that interferes with the browser’s standard behavior. Cartoon - woman (at car dealership): 'How much is it with automatic transmission?' - sleazy salesman: 'I'll give you a hint - it's an EVEN number...'

10. Not Answering Users' Questions
Users are highly goal-driven on the Web. They visit sites because there's something they want to accomplish -- maybe even buy your product. The ultimate failure of a website is to fail to provide the information users are looking for.

Sometimes the answer is simply not there and you lose the sale because users have to assume that your product or service doesn't meet their needs if you don't tell them the specifics. Other times the specifics are buried under a thick layer of marketese and bland slogans. Since users don't have time to read everything, such hidden info might almost as well not be there.

The worst example of not answering users' questions is to avoid listing the price of products and services. No B2C ecommerce site would make this mistake, but it's rife in B2B, where most "enterprise solutions" are presented so that you can't tell whether they are suited for 100 people or 100,000 people. Price is the most specific piece of info customers use to understand the nature of an offering, and not providing it makes people feel lost and reduces their understanding of a product line. We have miles of videotape of users asking "Where's the price?" while tearing their hair out.

Even B2C sites often make the associated mistake of forgetting prices in product lists, such as category pages or search results. Knowing the price is key in both situations; it lets users differentiate among products and click through to the most relevant ones.

By Jakob Nielsen

SQL Tips - String User-Defined Functions

Introduction

String UDFs

StrIns

StrDel

StrSeparate

StrCHARINDEX

StrREPLACE

StrREVERSE

---

Introduction

I would like to write the series of articles about useful User-Defined Functions grouped by the following categories:

Date and Time User-Defined Functions

Mathematical User-Defined Functions

Metadata User-Defined Functions

Security User-Defined Functions

String User-Defined Functions

System User-Defined Functions

Text and Image User-Defined Functions

In this article, I wrote some useful String User-Defined Functions.

String UDFs

These scalar User-Defined Functions perform an operation on a string input value and return a string or numeric value.

StrIns

Inserts set of characters into another set of characters at a specified starting point.

Syntax

StrIns ( character_expression, start, character_expression )

Arguments

character_expression - an expression of character data. character_expression can be a constant, variable, or column of character data.

start - an integer value that specifies the location to begin insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned.

Return Types

nvarchar

The function's text:

CREATE FUNCTION StrIns ( @str_1 nvarchar(4000), @start int, @str_2 nvarchar(4000) ) RETURNS nvarchar(4000) AS BEGIN RETURN (STUFF (@str_1, @start, 0, @str_2)) END GO

Examples

This example returns a character string created by inserting the second string starting at position 2 (at b) into the first string.

SELECT dbo.StrIns('abcdef', 2, 'ijklmn')

Here is the result set:

------------
aijklmnbcdef

(1 row(s) affected)

StrDel

Deletes a specified length of characters at a specified starting point.

Syntax

StrDel ( character_expression, start, length )

Arguments

character_expression - an expression of character data. character_expression can be a constant, variable, or column of character data.

start - an integer value that specifies the location to begin deletion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned.

length - an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression.

Return Types

nvarchar

The function's text:

CREATE FUNCTION StrDel ( @str_1 nvarchar(4000), @start int, @length int ) RETURNS nvarchar(4000) AS BEGIN RETURN (STUFF (@str_1 , @start, @length, '')) END GO

Examples

This example returns a character string created by deleting three characters from the first string (abcdef) starting at position 2 (at b).

SELECT dbo.StrDel('abcdef', 2, 3)

Here is the result set:

---
aef

(1 row(s) affected)

StrSeparate

Inserts a specified character into the given string after every n-th character (from the end of the string).

Syntax

StrSeparate ( character_expression, term, number )

Arguments

character_expression - an expression of character data. character_expression can be a constant, variable, or column of character data.

term - a character.

number - an integer.

Return Types

nvarchar

The function's text:

CREATE FUNCTION StrSeparate ( @str nvarchar(4000), @term char(1), @number int ) RETURNS nvarchar(4000) AS BEGIN DECLARE @i int, @j int, @stepcount int IF (len(@str) <= @number) RETURN @str SELECT @str =REVERSE(@str), @i = 1, @j = @number + 1, @stepcount = len(@str) / @number WHILE @i <= @stepcount BEGIN SET @str = ISNULL(STUFF(@str, @j, 0, @term), @str) SET @j = @j + @number + 1 SET @i = @i + 1 END SET @str = REVERSE(@str) RETURN @str END GO

Examples

This example returns a character string created by inserting the space character after every three characters of the specified string (from the end of the string).

SELECT dbo.StrSeparate('12345678', ' ', 3)

Here is the result set:

----------
12 345 678

(1 row(s) affected)

StrCHARINDEX

Returns the starting position of the n-th entering of the specified expression in a character string.

Syntax

CHARINDEX ( expression1, expression2, start_location, number)

Arguments

expression1 - an expression containing the sequence of characters to be found. expression1 is an expression of the short character data type category.

expression2 - an expression, usually a column searched for the specified sequence. expression2 is of the character string data type category.

start_location - the character position to start searching for expression1 in expression2. If start_location is a negative number, or is zero, the search starts at the beginning of expression2.

number - an integer.

Return Types

int

The function's text:

CREATE FUNCTION StrCHARINDEX ( @expression1 nvarchar(4000), @expression2 nvarchar(4000), @start_location int = 0, @number int ) RETURNS int AS BEGIN DECLARE @i int, @position int SET @i = 1 WHILE (@i <= @number) AND (CHARINDEX(@expression1, @expression2, @start_location) <> 0) BEGIN SET @position = CHARINDEX(@expression1, @expression2, @start_location) SET @expression2 = STUFF(@expression2, CHARINDEX(@expression1, @expression2, @start_location), len(@expression1), space(len(@expression1))) SET @i = @i + 1 END RETURN @position END GO

Examples

SELECT dbo.StrCHARINDEX('12', '2312451267124', 0, 2)

Here is the result set:

-----------
7

(1 row(s) affected)

StrREPLACE

Replaces all occurrences of the second given string expression in the first string expression with a third expression starting from the start_location position.

Syntax

REPLACE('string_expression1','string_expression2','string_expression3',@start_location)

Arguments

'string_expression1' - the string expression to be searched.

'string_expression2' - the string expression to try to find.

'string_expression3' - the replacement string expression.

start_location - the character position to start replacing.

Return Types

nvarchar

The function's text:

CREATE FUNCTION StrREPLACE ( @string_expression1 nvarchar(4000), @string_expression2 nvarchar(4000), @string_expression3 nvarchar(4000), @start_location int ) RETURNS nvarchar(4000) AS BEGIN IF (@start_location <= 0) OR (@start_location > len(@string_expression1)) RETURN (REPLACE (@string_expression1, @string_expression2, @string_expression3)) RETURN (STUFF (@string_expression1, @start_location, len(@string_expression1) - @start_location + 1, REPLACE(SUBSTRING (@string_expression1, @start_location, len(@string_expression1) - @start_location + 1), @string_expression2, @string_expression3))) END GO

Examples

SELECT dbo.StrREPLACE('12345678912345', '23', '**', 4)

Here is the result set:

-------------------
1234567891**45

(1 row(s) affected)

StrREVERSE

Returns the reverse of a character expression starting at the specified position.

Syntax

REVERSE ( character_expression, start_location )

Arguments

character_expression - an expression of character data. character_expression can be a constant, variable, or column of character data.

start_location - the character position to start reversing.

Return Types

nvarchar

The function's text:

CREATE FUNCTION StrREVERSE ( @character_expression nvarchar(4000), @start_location int ) RETURNS nvarchar(4000) AS BEGIN IF (@start_location <= 0) OR (@start_location > len(@character_expression)) RETURN (REVERSE(@character_expression)) RETURN (STUFF (@character_expression, @start_location, len(@character_expression) - @start_location + 1, REVERSE(SUBSTRING (@character_expression, @start_location, len(@character_expression) - @start_location + 1)))) END GO

Examples

SELECT dbo.StrREVERSE('123456789', 3)

Here is the result set:

-------------------
129876543

(1 row(s) affected)

SQL Tips - Mathematical User-Defined Functions

Introduction

Mathematical UDFs

Factorial

PercentFrom

PercentValue

Degree

Allocation

Combination

---

Introduction

I would like to write the series of articles about useful User-Defined Functions grouped by the following categories:

Date and Time User-Defined Functions

Mathematical User-Defined Functions

Metadata User-Defined Functions

Security User-Defined Functions

String User-Defined Functions

System User-Defined Functions

Text and Image User-Defined Functions

In this article, I wrote some useful Mathematical User-Defined Functions.

Mathematical UDFs

These scalar User-Defined Functions perform a calculation, usually based on input values provided as arguments, and return a numeric value.

Factorial

Returns the factorial value of the given number.

Syntax

Factorial ( number )

Arguments

number - integer value.

Return Types

bigint

The function's text:

CREATE FUNCTION Factorial ( @number int ) RETURNS bigint AS BEGIN DECLARE @i int, @factorial bigint IF @number > 20 RETURN 0 SET @i = 1 SET @factorial = 1 WHILE @i <= @number BEGIN SET @factorial = @factorial * @i SET @i = @i + 1 END RETURN @factorial END GO

Examples

This example returns the factorial value of the number 5:

SELECT dbo.Factorial(5)

Here is the result set:

--------------------
120

(1 row(s) affected)

PercentFrom

Returns the percent of the expression1 in the expression2.

Syntax

PercentFrom ( expression1, expression2 )

Arguments

expression1 - is an expression of the exact numeric or approximate numeric data type category.
expression2 - is an expression of the exact numeric or approximate numeric data type category.

Return Types

float

The function's text:

CREATE FUNCTION PercentFrom ( @expression1 SQL_VARIANT, @expression2 SQL_VARIANT ) RETURNS float AS BEGIN RETURN (CAST(@expression1 AS FLOAT)/CAST(@expression2 AS FLOAT)*100) END GO

Examples

This example returns the percent of the 137 in the 273:

SELECT dbo.PercentFrom(137, 273)

Here is the result set:

-------------------------------------
50.183150183150182

(1 row(s) affected)

PercentValue

Returns the percent's value from the given expression for the given percent.

Syntax

PercentValue ( expression, percent )

Arguments

expression - is an expression of the exact numeric or approximate numeric data type category.
percent - integer value.

Return Types

float

The function's text:

CREATE FUNCTION PercentValue ( @expression SQL_VARIANT, @percent int ) RETURNS float AS BEGIN RETURN ( CAST(@expression AS FLOAT) / 100 * @percent ) END GO

Examples

Returns the percent's value from the number 137 for the percent 11:

SELECT dbo.PercentValue (137, 11)

Here is the result set:

--------------------------
15.07

(1 row(s) affected)

Degree

Returns the degree for the given number and degree value.

Syntax

Degree ( number, degree )

Arguments

number - is an expression of the exact numeric or approximate numeric data type category.
degree - integer value.

Return Types

float

The function's text:

CREATE FUNCTION Degree ( @number SQL_VARIANT, @degree int ) RETURNS float AS BEGIN DECLARE @i int, @res float SET @i = 1 SET @res = 1 WHILE @i <= @degree BEGIN SET @res = CAST(@number AS FLOAT) * @res SET @i = @i + 1 END RETURN @res END GO

Examples

Returns the degree 4 for the number 3:

SELECT dbo.Degree(3, 4)

Here is the result set:

-------------------------------
81.0

(1 row(s) affected)

Allocation

Returns the allocation from the m by n.

Syntax

Allocation ( m, n )

Arguments

m - integer value.
n - integer value.

Return Types

int

The function's text:

CREATE FUNCTION Allocation ( @m int, @n int ) RETURNS int AS BEGIN RETURN (dbo.Factorial(@m)/dbo.Factorial(@m-@n)) END GO

Examples

Returns the allocation from the 5 by 3:

SELECT dbo.Allocation(5,3)

Here is the result set:

-----------
60

(1 row(s) affected)

Combination

Returns the combination from the m by n.

Syntax

Combination ( m, n )

Arguments

m - integer value.
n - integer value.

Return Types

int

The function's text:

CREATE FUNCTION Combination ( @m int, @n int ) RETURNS int AS BEGIN RETURN (dbo.Factorial(@m)/(dbo.Factorial(@m-@n)*dbo.Factorial(@n))) END GO

Examples

Returns the combination from the 5 by 3:

SELECT dbo.Combination(5,3)

Here is the result set:

-----------
10

(1 row(s) affected)

SQL Tips - Meta Data User-Defined Functions

Introduction

Meta Data UDFs

COL_LENGTH2

COL_ID

INDEX_ID

INDEX_COL2

ROW_COUNT

---

Introduction

I would like to write the series of articles about useful User-Defined Functions grouped by the following categories:

Date and Time User-Defined Functions

Mathematical User-Defined Functions

Metadata User-Defined Functions

Security User-Defined Functions

String User-Defined Functions

System User-Defined Functions

Text and Image User-Defined Functions

In this article, I wrote some useful Meta Data User-Defined Functions.

Meta Data UDFs

These scalar User-Defined Functions return information about the database and database objects.

COL_LENGTH2

Returns the defined length (in bytes) of a column for a given table and for a given database.

Syntax

COL_LENGTH2 ( 'database' , 'table' , 'column' )

Arguments

'database' - the name of the database. database is an expression of type nvarchar.
'table' - the name of the table for which to determine column length information. table is an expression of type nvarchar.
'column' - the name of the column for which to determine length. column is an expression of type nvarchar.

Return Types

int

The function's text:

CREATE FUNCTION COL_LENGTH2 ( @database sysname, @table sysname, @column sysname ) RETURNS int AS BEGIN RETURN (COL_LENGTH(@database + '..' + @table, @column)) END GO

Examples

This example returns the defined length (in bytes) of the au_id column of the authors table in the pubs database:

SELECT dbo.COL_LENGTH2('pubs', 'authors', 'au_id')

Here is the result set:

-----------
11

(1 row(s) affected)

COL_ID

Returns the ID of a database column given the corresponding table name and column name.

Syntax

COL_ID ( 'table' , 'column' )

Arguments

'table' - the name of the table. table is an expression of type nvarchar.
'column' - the name of the column. column is an expression of type nvarchar.

Return Types

int

The function's text:

CREATE FUNCTION COL_ID ( @table sysname, @column sysname ) RETURNS int AS BEGIN DECLARE @col_id int SELECT @col_id = colid FROM syscolumns WHERE id = OBJECT_ID(@table) AND name = @column RETURN @col_id END GO

Examples

This example returns the ID of the au_fname column of the authors table in the pubs database:

USE pubs GO SELECT dbo.COL_ID('authors', 'au_fname') GO

Here is the result set:

-----------
3

(1 row(s) affected)

INDEX_ID

Returns the ID of an index given the corresponding table name and index name.

Syntax

INDEX_ID ( 'table' , 'index_name' )

Arguments

'table' - the name of the table. table is an expression of type nvarchar.
'index_name' - the name of the index. index_name is an expression of type nvarchar.

Return Types

int

The function's text:

CREATE FUNCTION INDEX_ID ( @table sysname, @index_name sysname ) RETURNS int AS BEGIN DECLARE @indid int SELECT @indid = indid FROM sysindexes WHERE id = OBJECT_ID(@table) AND name = @index_name RETURN @indid END GO

Examples

This example returns the ID of the aunmind index of the authors table in the pubs database:

USE pubs GO SELECT dbo.INDEX_ID('authors', 'aunmind') GO

Here is the result set:

------
2

(1 row(s) affected)

INDEX_COL2

Returns the indexed column name for a given table and for a given database.

Syntax

INDEX_COL2 ( 'database' , 'table' , index_id , key_id )

Arguments

'database' - the name of the database. database is an expression of type nvarchar.
'table' - the name of the table.
index_id - the ID of the index.
key_id - the ID of the key.

Return Types

nvarchar (256)

The function's text:

CREATE FUNCTION INDEX_COL2 ( @database sysname, @table sysname, @index_id int, @key_id int ) RETURNS int AS BEGIN RETURN (INDEX_COL(@database + '..' + @table, @index_id, @key_id)) END GO

Examples

This example returns the indexed column name of the authors table in the pubs database (for index_id = 2 and key_id = 1):

SELECT dbo.INDEX_COL2('pubs', 'authors', 2, 1)

Here is the result set:

-----------------------
au_lname

(1 row(s) affected)

ROW_COUNT

Returns the total row count for a given table.

Syntax

ROW_COUNT ( 'table' )

Arguments

'table' - the name of the table for which to determine the total row count. table is an expression of type nvarchar.

Return Types

int

The function's text:

CREATE FUNCTION ROW_COUNT ( @table sysname ) RETURNS int AS BEGIN DECLARE @row_count int SELECT @row_count = rows FROM sysindexes WHERE id = OBJECT_ID(@table) AND indid < 2 RETURN @row_count END GO

Examples

This example returns the total row count of the authors table in the pubs database:

USE pubs GO SELECT dbo.ROW_COUNT('authors') GO

Here is the result set:

-----------
23

(1 row(s) affected)

See this link for more information:
Alternative way to get the table's row count

SQL Tips - Date and Time User-Defined Functions

Introduction

Date and Time UDFs

DatePart

TimePart

GetWorkingDays

AddWorkingDays

FirstMonthDay

LastMonthDay

---

Introduction

I would like to write the series of articles about useful User-Defined Functions grouped by the following categories:

Date and Time User-Defined Functions

Mathematical User-Defined Functions

Metadata User-Defined Functions

Security User-Defined Functions

String User-Defined Functions

System User-Defined Functions

Text and Image User-Defined Functions

In this article, I wrote some useful Date and Time User-Defined Functions.

Date and Time UDFs

These scalar User-Defined Functions perform an operation on a date and time input value and return a string, numeric, or date and time value.

DatePart

Returns the date part of the datetime value.

Syntax

DatePart ( datepart )

Arguments

datepart - datetime value.

Return Types

varchar

The function's text:

CREATE FUNCTION dbo.DatePart ( @fDate datetime ) RETURNS varchar(10) AS BEGIN RETURN ( CONVERT(varchar(10),@fDate,101) ) END GO

Example

This example returns a character string contained the date part of the datetime value:

SELECT dbo.DatePart('11/11/2000 11:15AM')

Here is the result set:

----------
11/11/2000

(1 row(s) affected)

TimePart

Returns the time part of the datetime value.

Syntax

TimePart ( datepart )

Arguments

datepart - datetime value.

Return Types

varchar

The function's text:

CREATE FUNCTION dbo.TimePart ( @fDate datetime ) RETURNS varchar(10) AS BEGIN RETURN ( CONVERT(varchar(7),right(@fDate,7),101) ) END GO

Example

This example returns a character string contained the time part of the datetime value:

SELECT dbo.TimePart('11/11/2000 11:15AM')

Here is the result set:

----------
11:15AM

(1 row(s) affected)

GetWorkingDays

Returns the number of working days between two dates (not including these dates).

Syntax

GetWorkingDays ( StartDate, EndDate )

Arguments

StartDate - the datetime value (start date).
EndDate - the datetime value (end date).

Return Types

int

The function's text:

CREATE FUNCTION dbo.GetWorkingDays ( @StartDate datetime, @EndDate datetime ) RETURNS INT AS BEGIN DECLARE @WorkDays int, @FirstPart int DECLARE @FirstNum int, @TotalDays int DECLARE @LastNum int, @LastPart int IF (DATEDIFF(day, @StartDate, @EndDate) < 2) BEGIN RETURN ( 0 ) END SELECT @TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1, @FirstPart = CASE DATENAME(weekday, @StartDate) WHEN 'Sunday' THEN 6 WHEN 'Monday' THEN 5 WHEN 'Tuesday' THEN 4 WHEN 'Wednesday' THEN 3 WHEN 'Thursday' THEN 2 WHEN 'Friday' THEN 1 WHEN 'Saturday' THEN 0 END, @FirstNum = CASE DATENAME(weekday, @StartDate) WHEN 'Sunday' THEN 5 WHEN 'Monday' THEN 4 WHEN 'Tuesday' THEN 3 WHEN 'Wednesday' THEN 2 WHEN 'Thursday' THEN 1 WHEN 'Friday' THEN 0 WHEN 'Saturday' THEN 0 END IF (@TotalDays < @FirstPart) BEGIN SELECT @WorkDays = @TotalDays END ELSE BEGIN SELECT @WorkDays = (@TotalDays - @FirstPart) / 7 SELECT @LastPart = (@TotalDays - @FirstPart) % 7 SELECT @LastNum = CASE WHEN (@LastPart <> 0) THEN @LastPart - 1 ELSE 0 END SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum END RETURN ( @WorkDays ) END GO

Example

Returns the number of working days between '11/13/2000' and '12/27/2000':

SELECT dbo.GetWorkingDays ('11/13/2000', '12/27/2000')

Here is the result set:

-----------
31

(1 row(s) affected)

AddWorkingDays

Works like DATEADD, but adds the working days.

Syntax

AddWorkingDays ( StartDate, WorkDays )

Arguments

StartDate - the datetime value (start date).
WorkDays - the integer value (number of working days).

Return Types

datetime

The function's text:

CREATE FUNCTION dbo.ADDWorkingDays ( @StartDate datetime, @WorkDays int ) RETURNS datetime AS BEGIN DECLARE @TotalDays int, @FirstPart int DECLARE @EndDate datetime DECLARE @LastNum int, @LastPart int IF @WorkDays < 0 BEGIN SELECT @FirstPart = CASE DATENAME(weekday, @StartDate) WHEN 'Sunday' THEN 0 WHEN 'Monday' THEN 1 WHEN 'Tuesday' THEN 2 WHEN 'Wednesday' THEN 3 WHEN 'Thursday' THEN 4 WHEN 'Friday' THEN 5 WHEN 'Saturday' THEN 6 END IF ABS(@WorkDays) < @FirstPart SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate) ELSE BEGIN SELECT @TotalDays = (ABS(@WorkDays) - @FirstPart) / 5 SELECT @LastPart = (ABS(@WorkDays) - @FirstPart) % 7 SELECT @LastNum = CASE WHEN (@LastPart <> 0) THEN @LastPart - 1 ELSE 0 END SELECT @TotalDays = - 2 * (@TotalDays + 1) + @WorkDays SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate) END END ELSE BEGIN SELECT @FirstPart = CASE DATENAME(weekday, @StartDate) WHEN 'Sunday' THEN 6 WHEN 'Monday' THEN 5 WHEN 'Tuesday' THEN 4 WHEN 'Wednesday' THEN 3 WHEN 'Thursday' THEN 2 WHEN 'Friday' THEN 1 WHEN 'Saturday' THEN 0 END IF @WorkDays < @FirstPart SELECT @EndDate = DATEADD(dd, @WorkDays, @StartDate) ELSE BEGIN SELECT @TotalDays = (@WorkDays - @FirstPart) / 5 SELECT @LastPart = (@WorkDays - @FirstPart) % 7 SELECT @LastNum = CASE WHEN (@LastPart <> 0) THEN @LastPart - 1 ELSE 0 END SELECT @TotalDays = 2 * (@TotalDays + 1) + @WorkDays SELECT @EndDate = DATEADD(dd, @TotalDays, @StartDate) END END RETURN ( @EndDate ) END GO

Example

Adds 9 working days to '11/13/2002' and returns the result date:

SELECT dbo.AddWorkingDays ('11/13/2002', 9)

Here is the result set:

-------------------------------------
2002-11-26 00:00:00.000

(1 row(s) affected)

FirstMonthDay

Returns the first day of the month for the given date.

Syntax

FirstMonthDay ( date )

Arguments

date - datetime value.

Return Types

datetime

The function's text:

CREATE FUNCTION dbo.FirstMonthDay ( @Date datetime ) RETURNS datetime AS BEGIN RETURN (CAST(STR(MONTH(@Date)) + '/' + STR(01) + '/' + STR(YEAR(@Date)) AS DateTime)) END GO

Example

Returns the first day for the '06/15/99' date:

SELECT dbo.FirstMonthDay('06/15/99')

Here is the result set (from my machine):

-------------------------------------
1999-06-01 00:00:00.000

(1 row(s) affected)

LastMonthDay

Returns the last day of the month for the given date.

Syntax

LastMonthDay ( date )

Arguments

date - datetime value.

Return Types

datetime

The function's text:

CREATE FUNCTION dbo.LastMonthDay ( @Date datetime ) RETURNS datetime AS BEGIN RETURN (CASE WHEN MONTH(@Date)= 12 THEN DATEADD(day,-1,CAST('01/01/'+STR(YEAR(@Date)+1) AS DateTime)) ELSE DATEADD(day,-1,CAST(STR(MONTH(@Date)+1)+'/01/'+STR(YEAR(@Date)) AS DateTime)) END) END GO

Example

Returns the last day for the '06/15/99' date:

SELECT dbo.LastMonthDay('06/15/99')

Here is the result set (from my machine):

-------------------------------------
1999-06-30 00:00:00.000

(1 row(s) affected)

SQL Tips - Some useful UDF for SQL Server 2000

Introduction

UDF examples

Database creation date

Date the object was created

Get date part of datetime value

Get time part of datetime value

Get the number of working days between two dates

---

Introduction

SQL Server 2000 supports User-Defined Functions (UDFs) - one or more Transact-SQL statements that can be used to encapsulate code for reuse. User-defined functions cannot make a permanent changes to the data or modify database tables. UDF can change only local objects for this UDF, such as local cursors or variables.

There are three types of UDF in SQL Server 2000:

Scalar functions

Inline table-valued functions

Multistatement table-valued functions

Scalar functions return a single data value (not a table) with RETURNS clause. Scalar functions can use all scalar data types, with exception of timestamp and user-defined data types.

Inline table-valued functions return the result set of a single SELECT statement.

Multistatement table-valued functions return a table, which was built with many TRANSACT-SQL statements.

User-defined functions can be invoked from a query like built-in functions such as OBJECT_ID, LEN, DATEDIFF, or can be executed through an EXECUTE statement like stored procedures.

UDF examples

Here you can find some useful user-defined functions.

Database creation date

This UDF will return the creation date for a given database (you should specify database name as parameter for this UDF):

CREATE FUNCTION dbo.DBCreationDate ( @dbname sysname ) RETURNS datetime AS BEGIN DECLARE @crdate datetime SELECT @crdate = crdate FROM master.dbo.sysdatabases WHERE name = @dbname RETURN ( @crdate ) END GO

This is the example for use:

SELECT dbo.DBCreationDate('pubs')

Date the object was created

This UDF will return the creation date for a given object in the current database:

CREATE FUNCTION dbo.ObjCreationDate ( @objname sysname) RETURNS datetime AS BEGIN DECLARE @crdate datetime SELECT @crdate = crdate FROM sysobjects WHERE name = @objname RETURN ( @crdate ) END GO

This is the example for use:

SELECT dbo.ObjCreationDate('authors')

Get date part of datetime value

This UDF will return the date part of datetime value:

CREATE FUNCTION dbo.DatePart ( @fDate datetime ) RETURNS varchar(10) AS BEGIN RETURN ( CONVERT(varchar(10),@fDate,101) ) END GO

This is the example for use:

SELECT dbo.DatePart('11/11/2000 11:15AM')

Get time part of datetime value

This UDF will return the time part of datetime value:

CREATE FUNCTION dbo.TimePart ( @fDate datetime ) RETURNS varchar(10) AS BEGIN RETURN ( CONVERT(varchar(7),right(@fDate,7),101) ) END GO

This is the example for use:

SELECT dbo.TimePart('11/11/2000 11:15AM')

Get the number of working days between two dates

This UDF will return the number of working days between two dates (not including these dates):

CREATE FUNCTION dbo.GetWorkingDays ( @StartDate datetime, @EndDate datetime ) RETURNS INT AS BEGIN DECLARE @WorkDays int, @FirstPart int DECLARE @FirstNum int, @TotalDays int DECLARE @LastNum int, @LastPart int IF (DATEDIFF(day, @StartDate, @EndDate) < 2) BEGIN RETURN ( 0 ) END SELECT @TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1, @FirstPart = CASE DATENAME(weekday, @StartDate) WHEN 'Sunday' THEN 6 WHEN 'Monday' THEN 5 WHEN 'Tuesday' THEN 4 WHEN 'Wednesday' THEN 3 WHEN 'Thursday' THEN 2 WHEN 'Friday' THEN 1 WHEN 'Saturday' THEN 0 END, @FirstNum = CASE DATENAME(weekday, @StartDate) WHEN 'Sunday' THEN 5 WHEN 'Monday' THEN 4 WHEN 'Tuesday' THEN 3 WHEN 'Wednesday' THEN 2 WHEN 'Thursday' THEN 1 WHEN 'Friday' THEN 0 WHEN 'Saturday' THEN 0 END IF (@TotalDays < @FirstPart) BEGIN SELECT @WorkDays = @TotalDays END ELSE BEGIN SELECT @WorkDays = (@TotalDays - @FirstPart) / 7 SELECT @LastPart = (@TotalDays - @FirstPart) % 7 SELECT @LastNum = CASE WHEN (@LastPart <> 0) THEN @LastPart - 1 ELSE 0 END SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum END RETURN ( @WorkDays ) END GO

This is the example for use:

SELECT dbo.GetWorkingDays ('11/13/2000', '12/27/2000')

SQL Tips - Optimizing SQL Server Performance by using File and Filegroups

General concepts

There are no devices or segments in SQL Server 7.0 and SQL Server 2000, as there were in SQL Server 6.5. Now databases reside on operating-system files.
There are three types of such files:

primary

secondary

log

Each database consists of at least two files: one is a primary data file (by default, with the .mdf extension) the other is log file (by default, with the .ldf extension). There are also optional secondary data files (by default, with the .ndf extension).

A database can have only one primary data file, zero or more secondary data files, and one or more log files. Each database file can be used by only one database. So there is no such situation (as in SQL Server 6.5 was), when you can store both databases and their logs on the same device (on the same file with the .dat extension).

The data files (.mdf and .ndf) are combined into filegroups. A filegroup is just a collection of one or more database files. Each database file can be a member of only one filegroup. Log files, on the other hand, are not members of filegroups, but are managed separately.

There are three types of filegroups:

primary

user-defined

default

Each database has only one primary filegroup, only one default filegroup, and zero or more user-defined filegroups. If you don't specify user-defined filegroups, your database will contain only a primary filegroup, which will be also the default filegroup. The primary filegroup contains the primary data file with all system objects in it (system tables, system stored procedures, extended stored procedures and so on). You cannot remove system objects from the primary filegroup, but you can create user objects in the user-defined filegroups for allocation, performance, and administration purposes.

To create user-defined filegroup, you should use CREATE DATABASE or ALTER DATABASE statement with the FILEGROUP keyword. The default filegroup is the filegroup where all the new user objects will be created. You can change the default filegroup by using ALTER DATABASE statement with the DEFAULT keyword.

SQL Server database files can be configured to grow and shrink automatically to reduce the need for active database management and eliminate many of the problems that can occur when logs or databases run out of space. The Autogrow feature is set on by default for all versions of SQL Server 7.0 and 2000, but the Autoshrink feature is set on by default only for the Desktop Edition.

When you create a database, you must set an initial size for both the data and log files. If you want to set database files to grow automatically, you should also specify the Autogrow increment in megabytes, kilobytes, gigabytes, terabytes, or percent, the default is MB. You can also specify a maximum file size to prevent disk drives from running out of space.

Optimization tips

To optimize the performance of your SQL Server, consider the following:

• Set a reasonable size of your database.
  First of all, before database creation, you should estimate how big your database will be.
  To estimate the reasonable database size, you should estimate the size of each table individually, and then add the values obtained. See this link for more information:
  Estimating the Size of a Table



• Set a reasonable size for the transaction log.
  The general rule of thumb for setting the transaction log size is to set it to 20-25 percent of the database size. The smaller the size of your database, the greater the size of the transaction log should be, and vice versa. For example, if the estimation database size is equal to 10Mb, you can set the size of the transaction log to 4-5Mb, but if the estimated database size is over 500Mb, the 50Mb may be enough for the size of the transaction log.



• Leave the Autogrow feature on for the data files and for the log files.
  Leave this feature to let SQL Server to automatically increase allocated resources when necessary without DBA intervention. The Autogrow feature is necessary when there is no DBA in your firm or your DBA doesn't have a lot of experience.



• Set a reasonable size of the autogrow increment.
  Setting a database to automatically grow results in some performance degradation, therefore you should set a reasonable size for the Autogrow increment to avoid automatically growing too often. Try to set the initial size of the database, and the size of the Autogrow increment, so that automatic growth will occur once per week or less.



• Don't set the autoshrink feature.
  Autoshrinking results in some performance degradation, therefore you should shrink the database manually or create a scheduled task to shrink the database periodically during off-peak times, rather than set Autoshrink feature to on.



• Set the maximum size of the data and log files.
  Specify the maximum size to which the files can grow to prevent disk drives from running out of space.



• Create a user-defined filegroup and make it the default filegroup.
  It's a good decision in most cases to store and manage system and user objects separately from one another, so the user objects will not compete with system objects for space in the primary filegroup. Usually, a user- defined filegroup is not created for small databases, for example, if the database is less than 100Mb.



• Create a user-defined filegroup and create some tables in it to run maintenance tasks (backups, DBCC, update statistics, and so on) against these tables.
  LOAD TABLE and DUMP TABLE are no longer supported in SQL Server 7.0 (and higher), but you can place a table in its own filegroup and can backup and restore only this table. So you can group user objects with similar maintenance requirements into the same filegroup.



• If you have several physical disk arrays, try to create as many files as there are physical disk arrays so that you have one file per disk array.
  This will improve performance, because when a table is accessed sequentially, a separate thread is created for each file on each disk array in order to read the table's data in parallel.



• Don't create many data and log files on the same physical disk array.
  Leaving the autogrow feature on for the data and for the log files can cause fragmentation of those files if there are many files on the same physical disk array. In most cases, it's enough to have 1-2 database files on the same physical disk.



• For heavily accessed tables, place these tables in one filegroup and place the table's indexes in a different filegroup on different physical disk arrays.
  This will improve performance, because separate threads will be created to access the tables and indexes.



• For heavily accessed tables with text/image columns, place this table in one filegroup and place text/image columns in a different filegroup on different physical disks.
  You can use CREATE TABLE statement with TEXTIMAGE_ON keyword to place text/image columns in a different filegroup. See the SQL Server BOL for details.



• Place the log files on other physical disk arrays than those with the data files.
  Because logging is more write-intensive, it's important that the disk arrays containing the SQL Server log files have sufficient disk I/O performance.



• If one of your join queries is used much more often than others, place the tables used in this query in different filegroups on different physical disk arrays.



• If you have read-only tables, place these tables in different filegroups on different physical disk arrays and use the ALTER DATABASE statement to make just this filegroup READ ONLY.
  This not only increases read performance, it prevents any data changes and allows you to control permissions to this data.



• Use the Windows NT Performance Monitor to determine the appropriate number for the data and log files on your server by checking the Disk Queue Length counter.
  Consider reducing the number of files and filegroups you have for your databases if the Disk Queue length on your server averages above 3, and continue monitoring once you have made your changes to ensure that your disk I/O is optimum over the long term.

Literature

1. SQL Server Books Online.

2. Microsoft SQL Server 7.0 Performance Tuning Guide

3. Microsoft SQL Server 7.0 Storage Engine Capacity Planning Tips

SQL Tips - Index Optimization Tips

General concepts

In this article, I want to show how you can improve the speed of your queries by choosing the proper indexes, what kinds of indexes MS SQL supports and what is the advantage and disadvantage of using indexes in particular situation.

There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

When you create clustered or nonclustered index you can set the fill factor option to specify how full SQL Server should make each index page. When there is no free space to insert new row on the index page, SQL Server will create new index page and transfer some rows from the previous page to the new one. This operation is called page splits. You can reduce the number of page splits by setting the appropriate fill factor option to reserve free space on each page of the index. The fill factor is a value from 1 through 100 that specifies the percentage of the index page to be left empty.

The default value for fill factor is 0. It is treated similarly to a fill factor value of 100, the difference in that SQL Server leaves some space within the upper level of the index tree for FILLFACTOR = 0. The fill factor percentage is used only at the time the index is created.

A covering index is an index, which includes all of the columns referenced in the query. So the index contains the data you are looking for and SQL Server does not have to look up the actual data in the table.

When you perform many data modification operations (INSERT, UPDATE or DELETE statements), then the table fragmentation occurs. If you want to determine the level of fragmentation, you can run the DBCC SHOWCONTIG statement.

Optimization tips

• Every index increases the time in takes to perform INSERTS, UPDATES and DELETES, so the number of indexes should not be very much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased.



• Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index.



• Try to create indexes on columns that have integer values rather than character values.



• If you create a composite (multi-column) index, the order of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.



• If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns.



• Create surrogate integer primary key (identity for example) if your table will not have many insert operations.



• Clustered indexes are more preferable than nonclustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY.



• If your application will be performing the same query over and over on the same table, consider creating a covering index on the table.



• You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.



• You can use the sp_MSforeachtable undocumented stored procedure to rebuild all indexes in your database. Try to schedule it to execute during CPU idle time and slow production periods.


sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

Literature

1. SQL Server Books Online.

2. Microsoft SQL Server 7.0 Performance Tuning Guide

3. SAP R/3 Performance Tuning Guide for Microsoft SQL Server 7.0

SQL Tips - View Transaction Log

It's not necessary to view the SQL Server transaction log, but if you are a DBA, it may be useful to resolve some problems. It may be useful for understanding the internal SQL Server architecture too.

SQL Server 6.5

Every database contains the syslogs system table in SQL Server 6.5. This table contains the transaction log, and is used by SQL Server for recovery and roll forward. You cannot modify syslogs system table manually.

This is from SQL Server Books Online (the syslogs table's columns):

Column Datatype Description xactid binary(6) Transaction ID op tinyint Update-operation number

So, to view the transaction log you can use the following select statement:

SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs

There are some op values:

op = 0 - BEGIN TRANSACTION op = 4 - Insert Row op = 5 - Delete Row op = 9 - Modify Row op = 30 - END TRANSACTION

SQL Server 7.0/2000

There is no syslogs system table in SQL Server 7.0/2000. The database log is now an operating system file. So, this SQL statement:

SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs

will return error:

Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'syslogs'.

You can use the following undocumented command in this case:

DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )

PARAMETERS:
Dbid or dbname - Enter either the dbid or the name of the database in question.

type - is the type of output:

0 - minimum information (operation, context, transaction id)

1 - more information (plus flags, tags, row length)

2 - very detailed information (plus object name, index name, page id, slot id)

3 - full information about each operation

4 - full information about each operation plus hexadecimal dump of the current transaction log's row.

by default type = 0

To view the transaction log for the master database, you can use the following command:

DBCC log (master)

SQL Tips - Copy text or image into or out of SQL Server

In this article, I want to show, how you can copy a single text or image value into or out of SQL Server by using the textcopy.exe utility. You can find this utility in the directory containing the standard SQL Server EXE files (C:\Mssql\Binn for SQL Server 6.5, by default and C:\Mssql7\Binn for SQL Server 7.0, by default).

Textcopy.exe utility is not described in SQL Server Books Online, but you can get its description by typing textcopy /? from the command prompt. This is the description:

Copies a single text or image value into or out of SQL Server. The value is a specified text or image 'column' of a single row (specified by the "where clause") of the specified 'table'. If the direction is IN (/I) then the data from the specified 'file' is copied into SQL Server, replacing the existing text or image value. If the direction is OUT (/O) then the text or image value is copied from SQL Server into the specified 'file', replacing any existing file. TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]] [/D [database]] [/T table] [/C column] [/W"where clause"] [/F file] [{/I | /O}] [/K chunksize] [/Z] [/?] /S sqlserver The SQL Server to connect to. If 'sqlserver' is not specified, the local SQL Server is used. /U login The login to connect with. If 'login' is not specified, a trusted connection will be used. /P password The password for 'login'. If 'password' is not specified, a NULL password will be used. /D database The database that contains the table with the text or image data. If 'database' is not specified, the default database of 'login' is used. /T table The table that contains the text or image value. /C column The text or image column of 'table'. /W "where clause" A complete where clause (including the WHERE keyword) that specifies a single row of 'table'. /F file The file name. /I Copy text or image value into SQL Server from 'file'. /O Copy text or image value out of SQL Server into 'file'. /K chunksize Size of the data transfer buffer in bytes. Minimum value is 1024 bytes, default value is 4096 bytes. /Z Display debug information while running. /? Display this usage information and exit. You will be prompted for any required options you did not specify.

You can use the following stored procedure to simplify the using of textcopy.exe utility:

CREATE PROCEDURE sp_textcopy ( @srvname varchar (30), @login varchar (30), @password varchar (30), @dbname varchar (30), @tbname varchar (30), @colname varchar (30), @filename varchar (30), @whereclause varchar (40), @direction char(1)) AS DECLARE @exec_str varchar (255) SELECT @exec_str = 'textcopy /S ' + @srvname + ' /U ' + @login + ' /P ' + @password + ' /D ' + @dbname + ' /T ' + @tbname + ' /C ' + @colname + ' /W "' + @whereclause + '" /F ' + @filename + ' /' + @direction EXEC master..xp_cmdshell @exec_str

This is the example to copy image into SQL Server database pubs, table pub_info, column name logo from picture.bmp file where pub_id='0736':

sp_textcopy @srvname = 'ServerName', @login = 'Login', @password = 'Password', @dbname = 'pubs', @tbname = 'pub_info', @colname = 'logo', @filename = 'c:\picture.bmp', @whereclause = " WHERE pub_id='0736' ", @direction = 'I'

By the way, you should insert something into text/image column before copy text/image into it. I mean, this value should not be null. You should write (for example):

INSERT INTO [dbo].[images] VALUES ('1', 0x0, null)

instead of:

INSERT INTO [dbo].[images] VALUES ('1', null, null)

Otherwise, you will get the following error message:

ERROR: Text or image pointer and timestamp retrieval failed.