simple python unit test sample,TDD rule

import unittest
class TestTank(unittest.TestCase):
        def testImage(self):
                tank="boo"
                assert tank != "boo", "tank image is None"
        def test1(self):
                tank ="moo"
                assert tank != "moo", "test1 tank image is None"


if __name__=="__main__":
        unittest.main()

more...

Generate XSD from XML with DataSet (VB.NET)

<Test()> _
        Public Sub generateXsd()
        Try

            Dim d As DataSet = New DataSet()
            d.ReadXml("C:\projects\LeadInformation.xml")
            d.WriteXmlSchema("C:\projects\LeadInformation.xsd")
        Catch ex As Exception
        End Try

    End Sub

more...

SQL server 2005-create indexes for temporary tables t-sql

 1 SELECT
 2 ad.[DealerId]
 3 ,ad.[APSDealerId]
 4 ,cu.CustomerID
 5 ,cu.[FirstName]
 6 ,cu.[LastName]
 7 ,cu.[DOB]
 8 ,cu.OwnerID
 9 ,cu.referencefield1
10 ,cu.referencefield2
11 into #tmain
12 FROM [Customers] cu
13 INNER JOIN [AdvanceCustomer] ac ON ac.[CustomerID] = cu.[CustomerID]
14 INNER JOIN [ApsUbillDealer] ad ON ad.[DealerId] = ac.[DealerID]
15 WHERE cu.[DateCreated] >  @XmlData.value('(/args/FromDate)[1]','varchar(10)')
16 AND cu.[DateCreated] <  @XmlData.value('(/args/ToDate)[1]','varchar(10)')
17 
18 CREATE UNIQUE CLUSTERED INDEX IX_1 on #tmain (CustomerID, OwnerID)
19 CREATE INDEX IX_2 on #temp_employee_v1 (APSDealerId)
20 
21 

more...

To Add gVIM to External Tools to Visual Studio or SQL Management Studio

Title:vim&-
Command:C:\vim\vim71\gvim.exe
Arguments: -p -o --remote-silent +$(CurLine) $(ItemPath)
now vim can be called by : <Alt-T> -
more...

Create SQL INSERT from Excel file (ruby script)

import excel file into SQL.
first row is column names.

require 'win32ole'
xl = WIN32OLE.connect('Excel.Application')
wb = xl.ActiveWorkbook
wb.Worksheets.each do |ws|
    data = ws.UsedRange.Value
    if data!=nil
            field_names = data.shift
            flds=field_names.join(',')
            data.each do |row|
                row.collect! { |f| f = "'" + f.to_s + "'" }
                puts ("INSERT INTO [#{ws.Name}] (#{flds}) VALUES \
                    ( #{row.join(',')} );")

            end
  end
end

As alternative there is app that can convert Excel into SQL witn CREATE TABLE and INSERT statements

Handling custom error messages from sql in VB.NET (System.SslClient , SQL Server 2005)

Imports System.Data.SqlClient
Imports CSharp.Core

Public Class DBProvider

    Public Sub LogSQLMessages(ByVal sender As Object, ByVal e As SqlInfoMessageEventArgs)
        Logger.prn("SQLMessage:" & e.ToString)
    End Sub

    Private Shared youBillConn As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString

    Public Function ExecuteScalar(ByVal spname As String, ByVal ParamArray sparams() As Object) As Object
        Dim sqlc As New SqlConnection(youBillConn)
        Try
            AddHandler sqlc.InfoMessage, AddressOf LogSQLMessages
            'sqlc.FireInfoMessageEventOnUserErrors = True
            sqlc.Open()
            Return SqlHelper.ExecuteScalar(sqlc, spname, sparams)

        Catch ex As SqlClient.SqlException

            Dim erix As Integer = ex.Message.IndexOf("MSIEX:[[")
            Dim eree As Integer = ex.Message.IndexOf("]]")

            If erix >= 0 And eree > 0 And eree > erix Then
                Assertions.Throw(ex.Message.Substring(erix + 8, (eree - erix) - 8))
            Else
                Logger.prn(ex)
                Throw ex
            End If

            Return Nothing

        Catch allex As Exception
            Logger.prn(allex)
            Throw allex

        Finally
            sqlc.Close()
        End Try

    End Function

End Class

more...

TRY..CATCH in T-SQL (sqlserver 2005)

SELECT * FROM sys.messages
ALTER  Procedure usp_sp1
@XMLData XML
AS
BEGIN
DECLARE
        ...declarations...
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRANSACTION usp_sp1_tran
        ...transactions...
COMMIT TRANSACTION usp_sp1_tran
END TRY
BEGIN CATCH
        ROLLBACK TRAN usp_sp1_tran

        DECLARE @ErrorMessage NVARCHAR(4000)
           ,@ErrorNumber INT
           ,@ErrorSeverity INT
           ,@ErrorState INT
           ,@ErrorLine INT
           ,@ErrorProcedure NVARCHAR(200) ;

        SELECT  @ErrorNumber = ERROR_NUMBER()
               ,@ErrorSeverity = ERROR_SEVERITY()
               ,@ErrorState = ERROR_STATE()
               ,@ErrorLine = ERROR_LINE()
               ,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ;


        SELECT  @ErrorMessage = 'Procedure:' + @ErrorProcedure + '; Line:'
                + CAST(@ErrorLine AS VARCHAR(10)) + '; ' + ERROR_MESSAGE() ;

            --EXEC usp_Email @ErrorMessage, 'mymail@host.com'                     
        RAISERROR ( @ErrorMessage, 16, 1 )
END CATCH
END

more...

Custom RAISERROR in SQL Server 2005 (T-SQL)

how to add custom message %s is place holder for argument:

 exec sp_addmessage @msgnum = 60555, @severity = 16,
@msgtext = 'MSIEX:[[%s]]',
@lang = 'us_english';
go

to see your message in messages table:

SELECT * FROM sys.messages

Assert procedure in TSQL :

ALTER  PROC      usl_AssertionsThrow
@msg VARCHAR(MAX)
AS
BEGIN
         RAISERROR ( 60555, 20, 1,@msg) WITH LOG
END

this setting terminate stored procedure after RAISERROR

SET XACT_ABORT ON

more...

Custom Error in MS-SQL server by RAISERROR (TSQL)

--Add message 
exec sp_addmessage @msgnum = 60000, @severity = 16,
@msgtext = 'The record has been modified already by other process',
@lang = 'us_english';
go
-- sample of usafe
if  @CurrCheckSum <> @xmlCheckSum
                RAISERROR (60000, 16, 1)
        ELSE

more...