SQL 2000 Frequently Asked Questions

Finding Out the Version of Microsoft SQL Server
Using a SQL tool execute the following:

• USE master SELECT @@VERSION

Checking If A Local Temporary Table Already Exists
To test if a temporary table already exists, the "OBJECT_ID" system function can be used. For example the following code tests to see if the local temporary table '#AnyTable' already exists and if the table exists the word 'EXISTS' is printed.

IF OBJECT_ID('tempdb..#AnyTable') IS NOT NULL PRINT 'EXISTS'

Number of SQL Commands Awaiting Distribution

To count the number of replicated commands that are outstanding the MSrepl_commands table should be queried on the distribution database as follows:

select count(*)from distribution..MSrepl_commands

View All Outstanding Replicated Commands

To view (browse) the replicated commands that are outstanding the stored procedure sp_browsereplcmds should be executed. Please note that if you many transactions outstanding it may take a while for the result set is returned:

exec distribution..sp_browsereplcmds

View Top 1000 Outstanding Replicated Commands

To view (browse) the top 1000 outstanding replicated commands execute the following script:

declare @query nvarchar(4000),@dbname sysname
set @query='
select top 1000
xact_seqno,originator_id,publisher_database_id,article_id,type,convert(int,partial_command),command
from MSrepl_commands
order by originator_id, publisher_database_id, xact_seqno,article_id,command_id
'
set @dbname=db_name()
exec master..xp_printstatements @query, @dbname

Performing a Case Sensitive Comparison when SQL Server is Configured as Case-Insensitive

A case sensitive check can be performed by casting both columns or constants to the VARBINARY type; see examples below:

SELECT 'We Have A Match Case Senisitve Match' WHERE CAST('SENSITIVE'AS VARBINARY(999))=CAST('sensitive'AS VARBINARY(999))
SELECT 'We Have A Match Case Senisitve Match' WHERE CAST('SENSITIVE'AS VARBINARY(999))=CAST('SENSITIVE'AS VARBINARY(999))

Checking if a Table Column already Exists in a SQL Table

On odd occasions it is necessary to test a table to see if a column already exists. This is basically accomplished by checking the database system tables 'SysObjects' and 'SysColumns' for the relevant data. The example below shows such a check using a SQL Server 2000 User Defined Scalar Function. It also shows how to call the user defined function.

CREATE FUNCTION ColumnAlreadyExists(@TableName NVARCHAR(128),@ColumnName NVARCHAR(128))
RETURNS INTEGER--Returns 0 if column does not exist. Returns 1 if column exists.
AS
BEGIN
--See if the Table already contains the column.
IF EXISTS
(SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
WHERE ObjectProperty(O.ID,'IsUserTable')=1
AND O.Name=@TableName
AND C.Name=@ColumnName)
RETURN 1
--Table does not contain the column.
RETURN 0
END
GO

--Example of using SQL Server 2000 User Defined Scalar Function
--Drop then Add BT_FORMATTED column on the Temp_Hotspot table.
IF .dbo.ColumnAlreadyExists('Temp_Hotspot','BT_FORMATTED')=1
ALTER TABLE Temp_Hotspot DROP COLUMN BT_FORMATTED
ALTER TABLE Temp_Hotspot ADD BT_FORMATTED VARCHAR(500)

Function to Return a Sub String from a Delimited String

This SQL function is similar to the VB Split function and also similar to the Mumps (M) database $Piece function.

PIECE returns the specified substring from the specified Character Expression within the bounds of an input delimiter. The substring returned depends on the parameters input.

Parameters:
- @CharacterExpression = String expression containing substrings and delimiters. (i.e. 'DOWNLOAD#MSDE#ADMIN').
- @Delimiter = Single delimiter character (i.e. '#').
- @Position = Integer with a value of 1 or above. Represents the position of the substring to be returned.

Notes:
If @Position is zero, negative, or greater than the number of delimiters in @CharacterExpression, PIECE returns a null string.

I have written the PIECE SQL routine as a user-defined function, or to be more precise a User-Defined Scalar Function. An example of calling a user-defined scalar function is "SELECT .dbo.Piece('DOWNLOAD#MSDE#ADMIN','#',2)". Since user-defined functions were introduced in Microsoft SQL Server 2000 you shall require this version of SQL Server or higher in order to use this functionality. Should you have Microsoft SQL Server 7 or lower I would recommend that you convert the function to a stored procedure. The latter shouldn't be that difficult since the routine is no more than 10 lines long.

Examples of Use:
SELECT .dbo.PIECE('DOWNLOAD','#',0) returns NULL'.
SELECT .dbo.PIECE('DOWNLOAD','#',1) returns 'DOWNLOAD'.
SELECT .dbo.PIECE('DOWNLOAD#','#',1) returns 'DOWNLOAD'.

SELECT .dbo.PIECE('DOWNLOAD#MSDE#ADMIN','#',0) returns NULL'.
SELECT .dbo.PIECE('DOWNLOAD#MSDE#ADMIN','#',1) returns 'DOWNLOAD'.
SELECT .dbo.PIECE('DOWNLOAD#MSDE#ADMIN','#',2) returns 'MSDE'.
SELECT .dbo.PIECE('DOWNLOAD#MSDE#ADMIN','#',3) returns 'ADMIN'.
SELECT .dbo.PIECE('DOWNLOAD#MSDE#ADMIN','#',4) returns NULL.

SELECT .dbo.PIECE('DOWNLOAD#MSDE#ADMIN#','#',4) returns ''.

SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN','#',0) returns NULL.
SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN','#',1) returns ''.
SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN','#',2) returns ''.
SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN','#',3) returns 'DOWNLOAD'.
SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN','#',4) returns 'MSDE'.
SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN','#',5) returns 'ADMIN'.
SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN','#',6) returns NULL.

SELECT .dbo.PIECE('##DOWNLOAD#MSDE#ADMIN#','#',6) returns ''.

Anyway, here's the code:

CREATE FUNCTION Piece(@CharacterExpression VARCHAR(999),@Delimiter CHAR(1),@Position INTEGER)
RETURNS VARCHAR(999)--Returns the specified substring from the specified Character Expression within the bounds of a input delimiter.
AS
--Function to Return a Sub String from a Delimited string.
-- PIECE returns the specified substring from the specified Character Expression within the bounds of an input delimiter. The substring returned depends on the parameters input.
--
-- Parameters:
-- - @CharacterExpression = String expression containing substrings and delimiters. (i.e. 'DOWNLOAD#MSDE#ADMIN').
-- - @Delimiter = Single delimiter character (i.e. '#').
-- - @Position = Integer with a value of 1 or above. Represents the position of the substring to be returned.
--
-- Notes:
-- If @Position is zero, negative, or greater than the number of delimiters in @CharacterExpression, PIECE returns a null string.
-- Example of use: "SELECT .dbo.Piece('DOWNLOAD#MSDE#ADMIN','#',2)" would return 'ADMIN'.
BEGIN
--@Position should be 1 or more.
IF @Position<1 RETURN NULL
IF LEN(@Delimiter)<>1 RETURN NULL--Perhaps its better to add a raise error here !!!
--Work out start position of the substring.
DECLARE @Start INTEGER
SET @Start=1
WHILE @Position>1
BEGIN
SET @Start=ISNULL(CHARINDEX(@Delimiter,@CharacterExpression,@Start),0)
IF @Start=0 RETURN NULL
SET @Position=@Position-1
SET @Start=@Start+1
END
--Work out end position of the substring.
DECLARE @End INTEGER
SET @End=ISNULL(CHARINDEX(@Delimiter,@CharacterExpression,@Start),0)
IF @End=0 SET @End=LEN(@CharacterExpression)+1--If no end delimiter then that's OK. Were just have all remaining characters.
RETURN SUBSTRING(@CharacterExpression,@Start,@End-@Start)
END
GO

Forcing the Screen Buffer to be Displayed

In order to force the contents of the screen buffer to be outputted staight away please use the following SQL:

RAISERROR('',0,1) WITH NOWAIT