SQL Random Line

Function SQLRandomLines(proc, params, fname, count)
Dim i, j, k, rid, rs, result, records, reccount, normalized

On Error Resume Next

PushError

SQLRandomLines = Null

Set rs = iOpen(proc, params)
If CheckPopError Then
Exit Function
End If

If rs.EOF Then
rs.Close
Set rs = Nothing
Exit Function
End If

records = rs.GetRows(adGetRowsRest)
If CheckPopError Then
Exit Function
End If

rid = -1
For i = 0 To rs.Fields.Count - 1
If (rs.Fields(i).Name = fname) Then
rid = i
End If
Next
If (rid < 0) Or (rid > rs.Fields.Count - 1) Then
rid = -1
End If
rs.Close
Set rs = Nothing

If (rid = -1) Then
Exit Function
End If
reccount = UBound(records, 2) - LBound(records, 2) + 1

If (reccount >= count) Then
normalized = RandomArray(count, LBound(records, 2), UBound(records, 2))
For i = LBound(normalized) To UBound(normalized)
normalized(i) = records(rid, normalized(i))
Next
Else
normalized = RandomArray(reccount, LBound(records, 2), UBound(records, 2))
For i = LBound(normalized) To UBound(normalized)
normalized(i) = records(rid, normalized(i))
Next
End If

ReDim result(UBound(records, 1), UBound(normalized))
For k = LBound(records, 2) To UBound(records, 2)
For i = LBound(normalized) To UBound(normalized)
If (CStr(records(rid, k)) = CStr(normalized(i))) Then
For j = LBound(records, 1) To UBound(records, 1)
If (TypeName(records(j, k)) = "String") Then
result(j, i) = VarTrimStr(records(j, k))
Else
result(j, i) = records(j, k)
End If
Next
End If
Next
Next
SQLRandomLines = result
End Function

SQL Random ID

Function SQLRandomIDs(proc, params, fname, count)
Dim i, rs, records, reccount, normalized

On Error Resume Next

PushError

SQLRandomIDs = Null

Set rs = iOpen(proc, params)
If CheckPopError Then
Exit Function
End If

If rs.EOF Then
rs.Close
Set rs = Nothing
Exit Function
End If

records = rs.GetRows(adGetRowsRest, , fname)
If CheckPopError Then
Exit Function
End If
rs.Close
Set rs = Nothing

reccount = UBound(records, 2) - LBound(records, 2) + 1
If IsNull(count) Or IsEmpty(count) Then
count = reccount
ElseIf (count < 0) Then
count = reccount
End If

If (reccount >= count) Then
normalized = RandomArray(count, LBound(records, 2), UBound(records, 2))
For i = LBound(normalized) To UBound(normalized)
normalized(i) = records(0, normalized(i))
Next
Else
normalized = RandomArray(reccount, LBound(records, 2), UBound(records, 2))
For i = LBound(normalized) To UBound(normalized)
normalized(i) = records(0, normalized(i))
Next
End If
SQLRandomIDs = normalized
End Function

Remove Duplicate Records

---

Remove duplicate database records

Database:	.mdb
Table Name:
Field Name:
Primary Key:

---

Import the SQL Server error log into a table

CREATE PROC sp_import_errorlog
(
    @log_name sysname,
    @log_number Int = 0,
    @overwrite bit = 0
)
As

Purpose:    To import the SQL Server Error Log into a table, so that it can be queried

Tested On:  SQL Server 2000

Limitation:     With Error messages spanning more than one line only the first line Is included In the table

Example 1:  To import the current Error Log To table myerrorlog
        EXEC sp_import_errorlog 'myerrorlog'

Example 2:  To import the current Error Log To table myerrorlog, And overwrite the table
        'myerrorlog' if it already exists
        EXEC sp_import_errorlog 'myerrorlog', @overwrite = 1

Example 3:  To import the previous Error Log To table myerrorlog
        EXEC sp_import_errorlog 'myerrorlog', 1

Example 4:  To import the Second previous Error Log To table myerrorlog
        EXEC sp_import_errorlog 'myerrorlog', 2


BEGIN
    Set NOCOUNT On
  
    Declare @sql varchar(500) --Holds To SQL needed To create columns from Error Log

    If (Select OBJECT_ID(@log_name,'U')) IS NOT NULL
        BEGIN
            If @overwrite = 0
                BEGIN
                    RAISERROR('Table already exists. Specify another name or pass 1 to @overwrite parameter',18,1)
                    RETURN -1
                End
            Else
                BEGIN
                    EXEC('DROP TABLE ' + @log_name)
                End
        End

  
    --Temp table To hold the output of sp_readerrorlog
    CREATE TABLE #errlog
    (
        err varchar(1000),
        controw tinyint
    )

    --Populating the temp table using sp_readerrorlog
    INSERT #errlog
    EXEC sp_readerrorlog @log_number

    --This will remove the header from the errolog
    Set ROWCOUNT 4
    DELETE #errlog
    Set ROWCOUNT 0

  
    Set @sql =  'SELECT
                CONVERT(DATETIME,Left(err,23)) [Date],
                SUBSTRING(err,24,10) [spid],
                Right(err,Len(err) - 33) [Message],
                controw
            INTO ' + QUOTENAME(@log_name) +
            ' FROM #errlog ' +
            'WHERE controw = 0'
  
    --Creates the table With the columns Date, spid, message And controw
    EXEC (@sql)
  
    --Dropping the temporary table
    DROP TABLE #errlog
  
    Set NOCOUNT OFF
Print 'Error log successfully imported to table: ' + @log_name
End

Call an Oracle Stored Procedure

Assume you have a procedure like this one below, And that it has been already created On the
Oracle database. This procedure doesn't return anything, but that doesn't change anything!
STEP #1:
/******STORED PROCEDURE On ORACLE DATABASE************/
create Or Replace procedure test_me
Is
w_count integer;
begin
insert into TEST values ('Surya was here');
--commit it
commit;
end;
/*****End OF STORED PROCEDURE****/


STEP # 2:
+++++++++
I assume you have tested it from sql*plus by running the
following statements:

/************TEST THE STORED PROCEDURE FROM SQL*PLUS******/
SQL> execute test_me

PL/SQL procedure successfully completed.

SQL>
/***************End OF TESTING THE STORED PROC************/

STEP# 3:
++++++++
/*****CALLING A STORED PROCEDURE FROM ASP******************/

1. USING THE CONNECTION OBJECT

You can execute stored procedures which perform Oracle Server side tasks And return you a recordset. You can only use this method If
your stored procedure doesn't return any OUTPUT values.

Note that -1 means no count of total number of records Is
required. If you want To Get the count, substitute count
With some Integer variable

Note that 4 means it Is a stored procedure. By using the
actual number -1 And 4, you don't need the server side
include ADOVBS.INC ;-)

The above would Do the job On the database And return
back To you without returning any recordsets.

Alternatively, you could:



W_count Is the number of records affected. If your stored
procedure were To return a query result, it Is returned
within your recordset (rs). This method Is useful With Stored procs
which return results of an SQL query


2. USING THE COMMAND OBJECT



STEP# 4
+++++++++
/************PASSING Input/OUTPUT PARAMETERS**************************/