Membuat Alias di SQL Server 2008 R2

Manfaat dari penggunaan alias untuk koneksi database ke SQL Server * ketimbang langsung mengarahkannya ke Nama server atau IP address dimana SQL server berada * salah satunya adalah kita tidak perlu mengubah-ubah string koneksi di aplikasi yang kita buat jika ada perubahan di sisi server (Nama atau IP Address-nya).

Mungkin bagi rekan yang pernah memanfaatkan Crystal report di programnya (bawaan MV Studio 2005, saya tidak tau untuk versi di atasnya apakah masalahnya masih sama)  akan tau betapa bergunanya pemanfaatan alias ini. di MVS 2005 koneksi DB dari crystal report walaupun sudah di ubah di coding-nya untuk diarahkan ke mechine DB yg baru yang kita gunakan, tetap saja kita masih harus mengubah koneksi di Report Designnya ke mechine yang baru juga. Dengan memanfaatkan Alias ini perosalan tersebut bisa diatasi. * atau bagi yang tau caranya yang lebih smart mohon di sharing di sini, untuk masalah koneksi ke Crystal Report *.

Membuat Alias di SQL Server 2008 R2 sebenarnya sama saja dengan versi-versi SQL sebelumnya, hanya saja kalau di Versi 2005 ke atas untuk melakukan konfigurasi microsoft sudah membundle menjadi satu di SQL Server Configuration Manager. Secara default biasanya ada di sub menu START => All Programs => Microsoft SQL Server 2008 R2 => Configuration Tools => Sql Server Configuration Manager.

Gambar 1 : Jendela utama Sql Server Configuration Manager

Kemudian di jendela utama tersebut, Klik Kanan di Sub Menu Aliases ==> kemudian pilih New Alias..., akan tampil jendela di bawah ini

Gambar 2 : Jendela untuk memasukan Alias Name, Port Name, Protocol dan Server

Alias Name :

                       dalam contoh ini digunakan nama alias PTXXX>

Port No      : < Isikan Port No, sesuaikan dengan port TCP/IP yg anda gunakan,
                       cara mengetahui no port yang digunakan ikuti langkah-langkah
                       pada gambar 3 >
Protocl       : < Pilih TCP/IP atau protocol lain yang anda kehendaki,
                       jika anda memilih protocol TCP/IP secara otomatis
                       Field Port No harus anda isi>
Server        : < Tentukan dimana server terletak, pada kasus ini
                       server berada di mecihne local, pada kasus  mungkin berbeda >.

Gambar 3 : Langkah-langkah untuk mengetahui port TCP yang digunakan pada SQL Server 2008 R2, 1. Pada Sql Server Network Configuration , single klik Instance yang anda inginkan (Protocol For MSSQLSERVER, dalam kasus ini), 2. Double Click Protocl TCP/IP di jendela sebelah kiri, 3. Klik Tab IP Address di jendela TCP/IP Properties.

Setelah langkah diatas dilakukan sekarang tinggal mencoba konek ke SQL Serevr dengan memanfaatkan Alias yang baru saja kita buat, cukup dengan mengganti bagian : Data Source=(local) dari (local) ke Alias yang dibuat yaitu Data Source=PTXXX.  Thank's Semoga bermanfaat..

Spesial karakter Bracket "[" versus COALESCE

Misalkan kita mempunyai tabel data Barang di SQL SERVER 2008 R2, katakanlah nama tabel tersebut MST_ITEM, yang mempunyai kolom ITEM_CODE, ITEM_NAME, ITEM_SATUAN. berikut ini list hasil setelah kita lakukan query dengan menggunakan perintah SELECT * FROM MST_ITEM :

ITEM_CODE          ITEM_NAME          ITEM_SATUAN
==============================================
0101001              Pad Cat                       EA
0101002              Oil Filter [Cat]             EA
0101003              Element AS [CAT]      EA

perhatikan karakter "[" "]" yang ada pada kolom ITEM_NAME, inilah inti yang ingin kami sampaikan dari tulisan ini. pada kasus saya, saya mempunyai sebuah Stored Procedure(SP) dengan parameter Filter berdasarkan ITEM_NAME nya, jika user tidak memberikan nilai pada parameter ITEM_NAME maka yang ditampilkan adalah seluruh data (artinya tidak ada data yang difilter), akan tetapi jika ada nilai parameter yang diberikan maka data yang ditampilkan adalah berdasarkan nilai filter yang diberikan. Isi transact_sql di SP tersebut kira-kira seperti di bawah ini,

CREATE PROCEDURE GET_ALL_ITEM_WITH_FILTER
   @ITEM_NAME VARCHAR(100)=NULL
AS
BEGIN
   SELECT * FROM MST_ITEM WHERE ITEM_NAME LIKE '%'+ COALESCE(@ITEM_NAME,
   ITEM_NAME) +'%'
END

Saat SP dijalankan dengan nilai parameter ITEM_NAME Null (artinya tidak ada data yang difilter), data yang tampil hanya item Pad Cat saja,


ITEM_CODE          ITEM_NAME          ITEM_SATUAN
==============================================
0101001              Pad Cat                       EA

sedang ke-2 item lainnya tidak muncul. ketika saya analisa kembali SP saya, disana tidak ada yang mencurigakan, semuanya berjalan dengan normal. akhirnya saya amati data yang ada dan tuduhan pun saya alamatkan ke karakter "[" atau bracket siku yang ada pada Item Name, pada kasus saya kebetulan user mengeluhkan bahwa ada item data yang tidak muncul, dari hasil laporannya ternyata Item namenya tersebut mengandung spesial karakter barcket "[" "]" sementara data yang tampil seluruhnya tidak ada yang mengandung bracket siku pada item namenya. lalu kemudian saya coba untuk menghilangkan karakter "[" dan "]" dari salah satu Item Name yang tidak muncul. Hasilnya, ternyata data tersebut sekarang muncul.

Kesimpulan sementara saya * hal ini karena saya yakin tidak ada yang salah dengan query saya di SP * adalah Bracket siku bermasalah dengan COALESCE, saya tidak tahu apa ada orang lain yang mengalami masalah yang sama dengan saya, saya juga tidak tahu apakah ini bug dari function COALESCE di SQL Server atau karena saya salah didalam memanfaatkan fungsi COALESCE, sebab ketika tulisan ini dibuat saya belum sempat mencarinya di internet.

Setitik masalah yang terpecahkan ini *paling tidak bagi saya* semoga bermanfaat bagi yang membutuhkan, Terimakasih.

Menggabungkan Nilai Dua Buah Field atau Lebih Bertipe Varchar Pada SQL Server 2000

Kasus :

Ceritanya Si Budi punya keinginan buat ngegabungin 3 buah field bertipe Varchar dalam sebuah Query, katakanlah field tersebut adalah FirstName, MidleName dan LastName *Gile nih si Budi udah pinter bahasa inggris juga* dari sebuah tabel sebut saja tabel Employee. singkat kata kemudian si Budi membuat perintah T-SQL berikut ini pada Query Analyzer Kesukaannya *kyk kacang goreng aja pake ksukaan segala*,

SELECT FirstName + ' ' + MidleName +' '+ LastName AS EmployeeName FROM EMPLOYEE

Terus di tekannya F5, hasilnya tampak di bawah ini :

===========

Bambang Belum Pamungkas

Budi Emang Sudarsono

Atep  Saragih

Ujang  Panghadena

Uut Cantik Permatasari

kemudian dia amati. data satu, dua dan lima ok, sesuai dengan yg di harapkan, tapi data tiga dan empat kok kesannya ompong ya... waduh.. lah piye toh iki *walah ternyata si budi wong jowo toh..*

Setelah diselidiki ternyata untuk employee bernama Atep dan Ujang ga punya midle name alias midle namenya NULL *lah kamu juga bud.. bud.. wong jowo ae gawe database employee name pake dipisah2 segala*.

Solusi :

Akhirnya setelah makan siang yang dirasa tidak enak, karena si Budi kepikiran terus ma permasalahan T-SQL yang sedang dikerjakannya, sampe-sampe semuanya jadi hambar, makanan terasa tidak enak, yah jadilah dia hanya menghabiskan 2 piring Nasi, 2 potong bebek goreng plus 2 porsi lalapan masih ditambah 1 mangkuk soto ditutup dengan 2 gelas Jus Mangga kesukaannya.. * whue whue.. gila loh Bud, ga nafsunya aja kyk gitu apalgai kalo lagi nafsu makan *. yah setelah makan siang yang tidak enak tersebut budi akhirnya menemukan solusi yang dipikirnya cukup mujarab, segera saja dia duduk manis di depan komputer yg menjadi sahabat setianya dikala susah dan senag, komputer yang selalu menegrti perasaannya, inilah dia yang tidak pernah mengeluh walau terus-terusan diperintah selalu hadir dalam setiap keadaan dengan hiburan-hiburannya yg uptodate, pokoknya komputer ini cinta matinya si Budi dah....

singkat cerita * perasaan dari tadi singkat cerita terus, tapi ga kelar-kelar * dibukalah Si Query Analyzer, terus dengan penuh penghayatan Budi mulai menekan-nekan tonjolan-tonjolan mungil dari kekasihnya, kira-kira begini perintahnya,

SELECT Isnull(FirstName,'') + Isnull(' '+ MidName,'') + Isnull(' '+ LastName,'') As EmployeeName FROM EMPLOYEE

Terakhir di Tekan F5 dan jreng.. jreng.... iniliah dia hasilnya

========================

Bambang Belum Pamungkas

Budi Emang Sudarsono

Atep Saragih

Ujang Panghadena

Uut Cantik Permatasari

Si Budi jingkrak-jingkrak sambil mencium kekasihnya... ah.. ternyata gini aja toh solusinya.. sampe bikin gw ga enak makan siang..umpat si Budi * eh Bud.. Bud, ojo takabur lah kon, nek iso yo ngucapno Alhamdulillah.. ojo takabur, ojo misuh-misuh.. yo bud .. yooo yooo*

segera saja si Budi pergi ke musola, kemudian dia berwudlu terus sujud, shalat 2 rakaat buat mengucapkan syukur kepada Allah yg telah membimbingnya selama ini untuk dapat menyelesaikan setiap permasalahan yang dia hadapi.. itulah si Budi, dia memang lugu namun baik hati dan taat beribadah.. * .. Loh.. apa maksudnya..*

Menghitung Selisih Jam dan Tanggal

PREFACE
oprasi Jam dan Tanggal pada suatu aplikasi database seringkali ditemukan, misalnya menghitung selisih antara jam masuk kerja dengan jam keluar kerja sehingga diperoleh total jam kerja bagi setiap pegawai. Begitu pula ada kalanya kita dihadapkan pada permasalahan menghitung selisih dua buah tanggal.

Untungnya pada VB.Net, fungsi-fungsi untuk melakukan hal tersebut sudah ada, kita tinggal mengembangkannya. sebagaimana pada tulisan berikut ini yang akan sedikit membahas tentang fungsi-fungsi tersebut.

Untuk menghitung selisih dua buah jam atau tanggal kita bisa memanfaatkan fungsi .subtract dari Class TimeSpan dan Date.
Lebih jelasnya lihat dan pelajari kode program di bawah ini.
berikut ini ada tiga fungsi yang saya buat untuk melakukan pengitungan selisih 2 buah jam dan selisih 2 buah tanggal, serta sebuah fungsi untuk mendapatkan nama hari dari tanggal yang dientry-kan, sbb :

FUNGSI TimeDifCalc
Berfungsi untuk menghitung selisih 2 buah jam.

Private Function TimeDifCalc(ByVal myTime1 As String, ByVal myTime2 As String) As Double
Dim T1 As TimeSpan
Dim T2 As TimeSpan
Try
T1 = New TimeSpan(Val(Mid(myTime1, 1, 2)), Val(Mid(myTime1, 4, 2)), 0)
T2 = New TimeSpan(Val(Mid(myTime2, 1, 2)), Val(Mid(myTime2, 4, 2)), 0)

Return T2.Subtract(T1).TotalHours
Catch ex As Exception
MessageBox.Show(ex.Message, "Time Calc Diff Failure", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Function

FUNGSI DateDifCalc
Berfungsi untuk menghitung selisih dua buah tanggal.

Private Function DateDifCalc(ByVal TheDate1 As String, ByVal TheDate2 As String) As Integer
Try
Dim myDate1 As Date
Dim myDate2 As Date

myDate1 = TheDate1
myDate2 = TheDate2
Return myDate2.Subtract(myDate1).TotalDays
Catch ex As Exception
MessageBox.Show(ex.Message, "Date Calc Diff Failure", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Function

FUNGSI GetTheDayName
Berfungsi untuk mendapatkan nama hari dari suatu tanggal yang di entry-kan

Private Function GetTheDayName(ByVal TheDate As String) As String
Try
Dim x As Date
Dim y As Date
y = TheDate
x = New DateTime(y.Year, y.Month, y.Day)
Return x.DayOfWeek.ToString()
Catch ex As Exception
MessageBox.Show(ex.Message, "Get Day Name Failure", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Function

OK, Selesai deh, Thank's Semoga bermanfaat
Referensi : googling and MSDN

Tentang "Column Schema" pada suatu Table

PRA KATA

Suatu saat adakalnya kita ingin mendapatkan informasi detail tentang suatu table, mulai dari nama kolom, tipe datanya serta atrribute yang lainnya. banyak hal yang bisa dilakukan dengan mengetahui infromasi "Schema" suatu table, salah satunya mungkin dengannya kita bisa membuat sebuah progam aplikasi untuk menggenerate script SQL seperti Insert, Update maupun delete, atau untuk keperluan lainnya.

PEMBAHASAN

Tulisan ini diperkaya dan dimodifikasi dari sumber berikut ini (How To Retrieve Column Schema Using the DataReader GetSchemaTable Method and Visual Basic .NET) dengan gaya pembahasan yg punya blog ini tentunya.

Untuk mendapatkan informasi seputar Column schema dari suatu tabel ada beberapa cara yang bisa dilakukan, pada tulisan ini kita akan memanfaatkan fasilitas ExecuteReader(commandBehavior) dari OleDBCommand.

Inti dari cara mendapatkan column schema dari suatu tabel adalah pada pemberian parameter dari fungsi ExecuteReader yaitu CommandBehavior, CommandBehavior adalah suatu enumeration yang memberikan gambaran mengenai hasil query dan efeknya pada database. Anggota dari CommandBehavior adalah sbb : CloseConnection, Default, KeyInfo, SchemaOnly, SequentialAccess, SingleResult dan SingleRow.

kemudian dari sekian banyak member CommandBehavior itu, mana yg sesuai dengan keinginan kita, yaitu untuk mendapatkan column schema dari suatu tabel?? jawabnya adalah hanya dua yaitu KeyInfo, SchemaOnly. Perbedaan antara penggunaan parameter enum Keyinfo dan SchemaOnly adalah sebagai berikut, jika kita menggunakan param enum KeyInfo query yang dihasilkan adalah berisi informasi kolom dan Primary Key dari suatu tabel, sedang jika yang kita gunakan param enum SchemaOnly maka query yang dihasilkan hanya berisi Informasi Kolom saja. uppss kayaknya cukup neh seputar teorinya, lebih lanjutnya tentang ExecuteReader dan commandBehaviour bisa langsung merujuk ke MSDN-nya.

Berikutnya kita akan mencoba mengimplementasikan  sedikit teori diatas kedunia nyata(tentunya sesuia dengan tema kita diatas).

1. Buatlah sebuah project baru(Windows Application) dari VS.Net 2005 atau yang lainnya
2. Pada Form1 tambahkan komponen Berikut ini, dan atur letaknya sesui selera anda :)  :
1. Button, Name:Button1
2. GridView, Name:Grid
3.  Tambahkan sebuah modul, beri nama modGlobal atau suka2 anda, modul ini berisi procedure dan function yang digunakan pada program utama, terdiri dari function GetSQLDataType(DataTypeNum) yang berfungsi untuk mendapatkan nama type data provider(dalam hal ini SLQ Server), karena pada schema yang tersedia hanya kode nomor dari Type Data Providernya saja, kemudian SetGridColText dan SetGridColCheckBox yang berfungsi untuk mensetting Column text dan Ceheck Box pada grid.. <* lumayan neh bonus tulisan, gw nyarinya lumayan susah neh, gw sharing gratis tiss, mudah2an ada yang berminat.. *>. kopikan kode program berikut ini pada modul yang anda buat:

Public Function GetSQLDataType(ByVal TheDataTypeNum As Integer) As String
        Dim MyDTName As String = ""
        Select Case TheDataTypeNum
            Case 20 : MyDTName = "BigInt"
            Case 128 : MyDTName = "Binary"
            Case 11 : MyDTName = "Bit"
            Case 129 : MyDTName = "Char"
            Case 135 : MyDTName = "DateTime"
            Case 131 : MyDTName = "Decimal"
            Case 5 : MyDTName = "Float"
            Case 205 : MyDTName = "Image"
            Case 3 : MyDTName = "Int"
            Case 6 : MyDTName = "Money"
            Case 130 : MyDTName = "Nchar"
            Case 203 : MyDTName = "Ntext"
            Case 131 : MyDTName = "Numeric"
            Case 202 : MyDTName = "Nvarchar"
            Case 4 : MyDTName = "Real"
            Case 135 : MyDTName = "SmallDateTime"
            Case 2 : MyDTName = "SmallInt"
            Case 6 : MyDTName = "SmallMoney"
            Case 12 : MyDTName = "SQLVariant"
            Case 201 : MyDTName = "Text"
            Case 128 : MyDTName = "TimeStamp"
            Case 17 : MyDTName = "TinyInt"
            Case 72 : MyDTName = "UniqueIdentifier"
            Case 204 : MyDTName = "VarBinary"
            Case 200 : MyDTName = "Varchar"
        End Select
        Return MyDTName
    End Function 

Public Function SettGridColText(ByVal hdrText As String, ByVal hdrName As String, _

ByVal lbrCol As Integer , ByVal TheReadO As Boolean, ByVal AuMode As DataGridViewAutoSizeColumnMode, _

Optional ByVal VisibleCol As Boolean = True, Optional ByVal ContAllignment As DataGridViewContentAlignment = DataGridViewContentAlignment.BottomCenter) As DataGridViewTextBoxColumn

        Dim TheCol As New DataGridViewTextBoxColumn

        With TheCol

            .HeaderText = hdrText

            .Name = hdrName

            .Width = lbrCol

            .ReadOnly = TheReadO

            .AutoSizeMode = AuMode

            .Visible = VisibleCol

            .DefaultCellStyle.Alignment = ContAllignment 'Contain Alligment

        End With

        Return TheCol

      End Function

      Public Function SettGridColCehckBox(ByVal hdrText As String, ByVal hdrName As String, _

ByVal lbrCol As Integer , ByVal TheReadO As Boolean, _

ByVal AuMode As DataGridViewAutoSizeColumnMode, _

Optional ByVal VisibleCol As Boolean = True, _

Optional ByVal ContAllignment As DataGridViewContentAlignment _

= DataGridViewContentAlignment.BottomCenter) As DataGridViewCheckBoxColumn

        Dim TheCol As New DataGridViewCheckBoxColumn

        With TheCol

            .HeaderText = hdrText

            .Name = hdrName

            .Width = lbrCol

            .ReadOnly = TheReadO

            .AutoSizeMode = AuMode

            .Visible = VisibleCol

            .DefaultCellStyle.Alignment = ContAllignment 'Contain Alligment

        End With

        Return TheCol

End Function

4. Kemudian pada form1 tambahkan kode program berikut ini

Private Sub SettGridView()

        'No

        Dim ColNo As New DataGridViewTextBoxColumn

        ColNo = SettGridColText("No", "hdrNo", 25, True, DataGridViewAutoSizeColumnMode.None)

        'Tax Name

        Dim ColTaxName As New DataGridViewTextBoxColumn

        ColTaxName = SettGridColText("Name", "hdrName", 150, True,_

      DataGridViewAutoSizeColumnMode.None, True,_

      DataGridViewContentAlignment.MiddleLeft)

        'Prosentase

        Dim ColProvDataType As New DataGridViewTextBoxColumn

        ColProvDataType = SettGridColText("Prov DType", "hdrProvDataType", 80, True,_

DataGridViewAutoSizeColumnMode.None, True, _

DataGridViewContentAlignment.MiddleLeft)

        Dim ColSysDataType As New DataGridViewTextBoxColumn

        ColSysDataType = SettGridColText("Sys DType", "hdrSysDataType", 80, True,_

DataGridViewAutoSizeColumnMode.None, True,_

DataGridViewContentAlignment.MiddleLeft)

        Dim ColLength As New DataGridViewTextBoxColumn

        ColLength = SettGridColText("Length", "hdrLength", 50, True,_

DataGridViewAutoSizeColumnMode.None, True,_

DataGridViewContentAlignment.MiddleRight)

        Dim ColPKey As New DataGridViewCheckBoxColumn

        ColPKey = SettGridColCehckBox("PK", "hdrPkey", 40, False,_

 DataGridViewAutoSizeColumnMode.None, True,_

DataGridViewContentAlignment.MiddleCenter)

        Dim ColSelect As New DataGridViewCheckBoxColumn

        ColSelect = SettGridColCehckBox("Select", "hdrSelect", 40, False,_

 DataGridViewAutoSizeColumnMode.None, True,_

 DataGridViewContentAlignment.MiddleCenter)

        With grid

            .Columns.Clear()

            .AllowUserToAddRows = False

            .AllowUserToDeleteRows = False

            .Columns.Add(ColNo)

            .Columns.Add(ColTaxName)

            .Columns.Add(ColProvDataType)

            .Columns.Add(ColSysDataType)

            .Columns.Add(ColLength)

            .Columns.Add(ColPKey)

            .Columns.Add(ColSelect)

        End With

    End Sub

    Private Sub getSchema()

        Dim cn As New OleDbConnection()

        Dim cmd As New OleDbCommand()

        Dim schemaTable As DataTable

        Dim myReader As OleDbDataReader

        Dim myField As DataRow

        Dim myProperty As DataColumn

        'Open a connection to the SQL Server Northwind database.

        cn.ConnectionString = "Provider=SQLOLEDB;Data Source=SERVER_ANDA;User ID=USER_SA_ANDA;Password=PASSWORD_SA_AND;Initial Catalog=Northwind"

        cn.Open()

        'Retrieve records from the Employees table into a DataReader.

        cmd.Connection = cn

        cmd.CommandText = "SELECT * FROM Employee"

        myReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)

        'Retrieve column schema into a DataTable.

        schemaTable = myReader.GetSchemaTable()

        Dim iRow As Integer = 0

        'For each field in the table...

        For Each myField In schemaTable.Rows

            'For each property of the field...

            grid.Rows.Add()

            grid.Item("hdrName", iRow).Value = myField.Item(0).ToString()

            For Each myProperty In schemaTable.Columns

                If myProperty.ColumnName = "DataType" Then

                    grid.Item("hdrProvDataType", iRow).Value = _

 GetSQLDataType(Val(myField("ProviderType").ToString()))

                    grid.Item("hdrSysDataType", iRow).Value = myField("DataType").ToString()

                    grid.Item("hdrLength", iRow).Value = myField("ColumnSize").ToString()

                End If

            Next

            iRow = iRow + 1

        Next

        myReader.Close()

        cn.Close()

    End Sub

5. Terakhir pada button1 tambahkan kode berikut ini

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles _

                        Button1.Click

        SettGridView()

        getSchema()

    End Sub

6. Now Run programmnya, dan klik button 1 amati apa yg terjadi. Selamat berxperimen dan berimprovisasi, nanti kalo udah bisa kasih tau gw ya..
7. Program lengkap dapat didownload disini
   

FINALLY

Akhir kata semoga bermanfaat.

SUMBER

1.       MSDN Online

2.       http://support.microsoft.com/kb/310108