My code library
Last
updated: November 24th '05 | Best viewed with: All popular
browsers | Best viewed at: 1024x768 | Links to external
sites will open in a new window
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.
Table
names and column names with spaces are
handled
All
datatypes are handled except images,
large text and binary columns with more
than 4 bytes
NULLs are
gracefully handled
Timestamp
columns are handled
Identity
columns are handled
Very
flexible and configurable
Non-dbo
owned tables are handled
Computed columns are handled
You can
filter the rows for which you want to
generate INSERTs
Usage:
Example
1:
To
generate INSERT statements for table
'titles':
EXEC sp_generate_inserts
'titles'
Example
2:
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 results
EXEC sp_generate_inserts
'titles', @Include_Column_List = 0
Example
3:
To
generate INSERT statements for
'titlesCopy' table from 'titles' table:
EXEC sp_generate_inserts
'titles', 'titlesCopy'
Example
4:
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 scripted
EXEC sp_generate_inserts
'titles', @Include_Timestamp = 1
Example
6:
To
print the debug information:
EXEC sp_generate_inserts
'titles', @debug_mode = 1
Example
7:
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
table
EXEC 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 0
EXEC sp_generate_inserts
imgtable, @ommit_images = 1
Example
9:
To
generate INSERT statements for the rest
of the columns excluding IDENTITY column:
EXEC sp_generate_inserts
mytable, @ommit_identity = 1
Example
10:
To
generate INSERT statements for the top 10
rows in the table:
EXEC sp_generate_inserts
mytable, @top = 10
Example
11:
To
generate INSERT statements only with the
columns you want:
EXEC sp_generate_inserts
titles, @cols_to_include =
"'title','title_id','au_id'"
Example
12:
To
generate INSERT statements by ommitting
some columns:
EXEC sp_generate_inserts
titles, @cols_to_exclude =
"'title','title_id','au_id'"
Example 13:
To avoid checking the foreign key constraints while loading data with INSERT statements:
NOTE: The @disable_constraints option
will disable foreign key constraints, by assuming that
the source data is valid and referentially sound
EXEC sp_generate_inserts titles, @disable_constraints = 1
Example 14:
To avoid scripting data from computed columns:
EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1
NOTE: Please
see the code and read the comments to understand
more about how this procedure works!
To generate INSERT statements for all the tables
in your database, execute the following query in
that database, which will output the commands,
that you need to execute for the same:
SELECT 'EXEC sp_generate_inserts ' +
'[' + name + ']' +
',@owner = ' +
'[' + RTRIM(USER_NAME(uid)) + '],' +
'@ommit_images = 1, @disable_constraints = 1'
FROM sysobjects
WHERE type = 'U' AND
OBJECTPROPERTY(id,'ismsshipped') = 0
If the output seems truncated or
incomplete in Query Analyzer, click
here for a solution!
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.
Here is an example:
regsvr32 "E:\code\Script.dll"
To unregister the DLL:
regsvr32 "E:\code\Script.dll" /u
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
Read the comments section inside the procedure
for examples.
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.
Example1: To convert the string 'william h gates'
to proper case:
SELECT dbo.PROPERCASE('william h
gates')
Example2: To convert the Notes field of titles
table in pubs database to proper case:
SELECT dbo.PROPERCASE(notes) FROM
pubs..titles
Click here to
download the equivalent stored procedure if you
are running SQL Server 7.0
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.