Tricks for Paging and Row Offset in Various Versions of SQL Server

Paging is one of the most needed tasks when developers are developing applications. SQL Server has introduced various features of SQL Server 2000 to the latest version of SQL Server 2012. Here is the blog post which I wrote which demonstrates how SQL Server Row Offset and Paging works in various versions of the SQL Server. Instead of giving the generic algorithm, I have used AdventureWorks database and build a script. This will give you better control over your data if you have installed the AdventureWorks database and you can play around with various parameters. The goal is to retrieve row number 51 to 60 from the table Sales.SalesOrderDetails of database AdventureWorks.

Project - SMS Gateway Sederhana Dengan ActiveXpert

Atas permintaan salah seorang teman baik saya untuk memberikan contoh tentang program SMS gateway, berikut contoh program SMS Gateway paling sederhana menggunakan ActiveXpert dan database SQL Server.

Silakan Download Disini

Semoga Bermanfaat

Runtime Error 430 - Class Does Not Support Automation or Does Not Support Expected Interface

Compile project VB6 menggunakan Windows 7 SP1 kadang menimbulkan error saat dijalankan di Windows XP. Hal ini disebabkan karena ADODB di Windows 7 SP1 tidak compatible dengan Windows XP dan akan muncul "Runtime Error 430 - Class Does Not Support Automation or Does Not Support Expected Interface".

Untuk dapat meng-compile project VB6 di Windows 7, berikut langkah-langkahnya :

• Buka Folder %ProgramFiles%\Common Files\System. Untuk Windows 64bit buka folder %ProgramFiles(x86)%\Common Files\System\
• Set permission folder "ado\" menjadi owner dan permission menjadi read/write.
• Buka folder "ado"
• Rename file "msado27.tlb" menjadi "msado27_new.tlb"
• Rename file "msado28.tlb" menjadi "msado28_new.tlb"
• Cari file "msado27.tlb" dari komputer Windows 7 (Bukan SP1)  atau anda bisa dapatkan disini
• Copy file "msado27.tlb" ke folder "ado\" di Windows 7 SP1
• Duplikat file "msado27.tlb" dan rename menjadi "msado28.tlb"
• Restart System

Ini sudah saya coba dan berhasil.
Semoga berguna...

Sumber

SQL Server Msg 15138 - The database principal owns a schema in the database, and schema cannot be dropped

The error message of SQL Server is self explanatory as there were schema associated with the user and we have to transfer those schema before removing the User.
In this post I will explain the workaround for this error:

Lets assume I am trying to drop a user named “TestUser” from DemoDB database.
Now, run the below query in the database from which we are trying to drop the user.

Use DemoDB ;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID(‘TestUser’)

Functions for calculating the Min, Max and StdDev

The following functions can be used to calculate the minimum, maximum and standard deviation of a list of arguments.

Option Explicit


'Purpose   :    Returns the Minimum value from a parameter Array
'Inputs    :    avValues() as Variant
'Outputs   :    The Min Value contained within the input (excluding empty values)

'Notes     :    Examples:
'               Min(1,2,empty,-1)               Returns -1
'               Min(Array(1,2,-1),-4,-9.9)      Returns -9.9
'               Min(1/Jan/99,2/Jan/99)          Returns 1/Jan/99
'Revisions :

Function Min(ParamArray avValues() As Variant) As Variant
    Dim vThisItem As Variant, vThisElement As Variant
    
    On Error Resume Next
    For Each vThisItem In avValues
        If IsArray(vThisItem) Then
            For Each vThisElement In vThisItem
                Min = Min(vThisElement, Min)
            Next
        Else
            If vThisItem < Min Then
                If Not IsEmpty(vThisItem) Then
                    Min = vThisItem
                End If
            ElseIf IsEmpty(Min) Then
                Min = vThisItem
            End If
        End If
    Next
    On Error GoTo 0
End Function

'Purpose   :    Returns the Maximum value from a parameter Array
'Inputs    :    avValues() as Variant
'Outputs   :    The Max Value contained within the input (excluding empty values)
'Notes     :    Examples:
'               Max(1,2,empty,-1)               Returns 2
'               Max(Array(1,2,-1),-4,-9.9)      Returns 2
'               Max(1/Jan/99,2/Jan/99)          Returns 2/Jan/99
'Revisions :

Function Max(ParamArray avValues() As Variant) As Variant
    Dim vThisItem As Variant, vThisElement As Variant
    
    On Error Resume Next
    For Each vThisItem In avValues
        If IsArray(vThisItem) Then
            For Each vThisElement In vThisItem
                Max = Max(vThisElement, Max)
            Next
        Else
            If vThisItem > Max Then
                If Not IsEmpty(vThisItem) Then
                    Max = vThisItem
                End If
            ElseIf IsEmpty(Max) Then
                Max = vThisItem
            End If
        End If
    Next
    On Error GoTo 0
End Function


'Purpose   :    Returns the Average of many things, they could be dates or numbers.
'Inputs    :    avValues                A 1D Array of Values to evaluate
'Outputs   :    The average value of the input parameters


Function Average(ParamArray avValues() As Variant) As Variant
    Dim vTotal As Variant, lThisItem As Variant, vThisElement As Variant, lItems As Long
    
    For Each lThisItem In avValues
        If IsArray(lThisItem) Then
            For Each vThisElement In lThisItem
                If Not IsEmpty(vThisElement) And IsNumeric(vThisElement) Then
                    vTotal = vTotal + vThisElement
                    lItems = lItems + 1
                End If
            Next
        ElseIf Not IsEmpty(lThisItem) And IsNumeric(lThisItem) Then
            vTotal = vTotal + lThisItem
            lItems = lItems + 1
        End If
    Next
    If lItems Then
        Average = vTotal / lItems
    End If
End Function

'Purpose   :    Calculate the Standard Devation of a population
'Inputs    :    avValues. A 1D Array of Values.
'               [avWeights]. A 1D Array of weights. If supplied the function
'               will calculated a weighted standard deviation.
'Outputs   :    The Standard Deviation or N/A if less than three values


Function StdDevP(avValues As Variant, Optional avWeights) As Variant
    Dim dThisWeight As Double, lThisItem As Long
    Dim dValue1 As Double, dValue2 As Double, dSumWeights As Double
    
    On Error GoTo ErrFailed
    If UBound(avValues) - LBound(avValues) >= 3 Then
        'Have more than three values
        dThisWeight = 1
        For lThisItem = LBound(avValues) To UBound(avValues)
            If IsArray(avWeights) Then
                dThisWeight = avWeights(lThisItem)
            End If
            dValue1 = dValue1 + (dThisWeight * avValues(lThisItem) * avValues(lThisItem))
            dSumWeights = dSumWeights + dThisWeight
            dValue2 = dValue2 + (dThisWeight * avValues(lThisItem))
        Next
        dValue1 = dValue1 / dSumWeights
        dValue2 = (dValue2 / dSumWeights) ^ 2
        'Abs prevents a run time if round errors occur
        'which make the number negative
        StdDevP = Abs(dValue1 - dValue2) ^ 0.5
    Else
        'Require three values
        StdDevP = "N/A"
    End If
    Exit Function
    
ErrFailed:
    Debug.Print "Error in StdDevP: " & Err.Description
    StdDevP = "N/A"
End Function

SUMBER