VB code to add SQL Server
advanced entries/server aliases<BACK>This program is
written and tested in VB 6.0 against SQL Server
7.0. It uses registry API to programmatically add
SQL Server advanced entries. Actually you should
be using a tool called 'Client network utility'
that comes with SQL Server 7.0. My code comes in
handy when you want to dynamically add the
advanced entries from your application.

VB code to generate textual
graphics<BACK>This program is
written and tested in VB 6.0. It is very useful
in designing captions/headings for web pages,
with shadow effect. By mixing and matching the
colors between different layers of text and
playing around with the shadow effect, you will
be able to come up with cool graphics.

TagIT - A VB tool to HTMLize data<BACK>This program is
written and tested in VB 6.0. Often your ASP
pages pull and display textual data from a
backend database. While displaying this data, you
may want to add special formatting to specific
words or sentences. A common practice which
addresses this requirement is -- to store the
data in the database along with embedded HTML
tags. This tool comes in handy when you want to
embed the HTML tags in your textual data, before
posting the data into the database. Use this tool
on your data and save the output into your
database.

T-SQL code to find out
the nth highest number in a column<BACK>Ever wondered how
to find out the second highest salary from the
employees table? Or how to find out the third
oldest employee in the company? Here is a stored
procedure which accepts the table name, column
name, and nth number and displays the nth highest
number from the given column.

T-SQL
code to get the last day of the month!<BACK>The logic behind
this 'last day of the month' calculations is that
you get the month part of a given date, add one
to it, there by getting to the next month. Then
subtract 1 day from the first of that month. This
whole Thing can be written in one line and here
it is:

Procedure to script your data (to
generate INSERT statements from the existing
data)<BACK>This procedure
generates INSERT statements using existing data
from the given tables and views. Later, you can use these
INSERT statements to generate the data. It's very
useful when you have to ship or package a
database application. This procedure also comes in handy when you have to
send sample data to your vendor or technical support provider for troubleshooting purposes.

To
ommit the column list in the INSERT
statement: (Column list is included by
default)NOTE: If you have too
many columns, you are advised to ommit
column list, as shown below, to avoid
erroneous resultsEXEC sp_generate_inserts
'titles', @Include_Column_List = 0

To
generate INSERT statements for 'titles'
table for only those titles which contain
the word 'Computer' in them:EXEC sp_generate_inserts
'titles', @From = "from titles where
title like '%Computer%'"

Example
5:

To
specify that you want to include
TIMESTAMP column's data as well in the
INSERT statement:NOTE: By default
TIMESTAMP column's data is not scriptedEXEC sp_generate_inserts
'titles', @Include_Timestamp = 1

If you
are not the owner of the table, use
@owner parameter to specify the owner
name:NOTE: To use this option,
you must have SELECT permissions on that
tableEXEC sp_generate_inserts
Nickstable, @owner = 'Nick'

Example
8:

To
generate INSERT statements for the rest
of the columns excluding images:NOTE: When using this
otion, DO NOT set @include_column_list
parameter to 0EXEC sp_generate_inserts
imgtable, @ommit_images = 1

This trick uses a variable in the SELECT
statement and appends all the values of a given
column to that variable. I actually picked up
this trick from SWYNK mailing list, but I don't
remember the author's name anymore. If you know
the author or if you are the author of this
mailing list posting, please let me know and I
will be more than happy to mention your
name here!

This example uses the RAND function of SQL Server
to pick a random row from the given table. For
this code to work properly, you should have a
numeric (int) unique key. It's okay to not to
have a unique key but an numeric column is
essential.

This procedure generates random passwords using
RAND() function. It can be configured to generate
a simple or a complex password. You can also
customize the length of the password generated.
Complex passwords will include upper and lower
case letters, numbers and special characters. See
the code to realize how useful the RAND()
function is! When you choose to generate a simple
password (default behavior), SPECIAL CARE is
taken to generate meaningful/easy to remember
passwords.

This is a COM component (DLL) written in VB which
connects to SQL Server and generates the CREATE
TABLE script for a given table from given
database. It uses SQL DMO object model to connect
to SQL Server. You can use this component from VB
or VC application as well as from stored
procedures and T-SQL. The Zip file contains the
VB code for the COM component, a VB project that
shows the usage and a stored procedure
sp_scripter which instantiates this component to
generate CREATE TABLE scripts. This procedure
uses OLE Automation procedures such as
sp_OACreate, sp_OADestroy, sp_OAMethod to
instantiate the COM object. Unzip the Zip file
into a folder and then register the Script.DLL
using regsrv32.exe.

This procedure reads the SQL Server error log
using sp_readerrorlog and imports the error log's
contents into a specified table. It accepts three
parameters:

@log_name is the name of the table into which the
error log will be imported
@log_number is the error log number, 0 is default
and refers to current error log
@overwrite -- specify 1 for this parameter, so
that the target table could be overwritten, if it
already exists

T-SQL has no built-in function to convert a
string to proper case (In a proper case string,
the first letter of each word would be in upper
case. E.g: Bill Gates). So, I ended up writing my
own PROPERCASE function.

Create this procedure in the
master database and call it from the required
databases. When you want to see the locks in a
particular database, make sure, you are executing
this procedure from being in that database.

Here are the enhancements:

The program name that is
holding the lock is displayed

The object name is
displayed on which the locks are held

Database name in which
the locks are held is displayed

Using the @dbname
parameter you can restrict the displayed
lock information to a specific database

Using the @spid parameter
you can restrict the displayed lock
information to a specific spid

Examples
are available in the comments section of the
stored procedure text.