Enable User Instances in SQL Server

in VS when using wizard to create database in SQL server Express edition sometimes it will show

Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.

solution :
Open the SQL Server Management Studio Express. This is the downloadable program in the same site where you downloaded the SQL Server 2005 express used to manage SQL Server 2005 Express.
In the query editor type this text: exec sp_configure 'user instances enabled', 1.
Then type: Reconfigure.
Then restart the SQL Server database.
<pre name="code" class="Sql">

Optimizing SQL Server Clustered Indexes

When deciding on whether to create a clustered or non-clustered index, it is often helpful to know what the estimated size of the clustered index will be. Sometimes, the size of a clustered index on a particular column or columns may be very large, leading to database size bloat and increased disk I/O. 

Clustered index values often repeat many times in a table's non-clustered storage structures, and a large clustered index value can unnecessarily increase the physical size of a non-clustered index. This increases disk I/O and reduces performance when non-clustered indexes are accessed.

Because of this, ideally a clustered index should be based on a single column (not multiple columns) that is as narrow as possible. This not only reduces the clustered index's physical size, it also reduces the physical size of non-clustered indexes and boosts SQL Servers overall performance.

When you create a clustered index, try to create it as a UNIQUE clustered index, not a non-unique clustered index. The reason for this is that while SQL Server will allow you to create a non-unique clustered index, under the surface, SQL Server will make it unique for you by adding a 4-byte "uniqueifer" to the index key to guarantee uniqueness. This only serves to increase the size of the key, which increases disk I/O, which reduces performance. If you specify that your clustered index is UNIQUE when it is created, you will prevent this unnecessary overhead.

Happy query....

A little missunderstood in GROUP BY ln SQL SERVER

for example i tried to view how much news posted grouping by week in year 2009

     SELECT 
        DATEPART(WK,DatePosted) AS WeekNumber, 
        COUNT(NewsID) AS NewsPosts
     FROM News
     WHERE DATEPART(YYYY, dbo.News.DatePosted) = 2009
     ORDER BY WeekNumber

you will get the warning message

error , Line 5
Column 'News.DatePosted' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

what's wrong? yup you need to add the group by
so we are going to add the group by

    SELECT
        DATEPART(WK,DatePosted) AS WeekNumber,
        COUNT(NewsID) AS NewsPosts
    FROM News
    WHERE DATEPART(YYYY, dbo.News.DatePosted) = 2009
    GROUP BY WeekNumber
    ORDER BY WeekNumber

still error? try to use the column instead, it's because sql server group by only regonise the column in group by not alias (like in My SQL)

Get list of Ms Access Table

Sub ListAccessTables2(strDBPath)
   Dim cnnDB As ADODB.Connection
   Dim rstList As ADODB.Recordset

   Set cnnDB = New ADODB.Connection

   ' Open the connection.
   With cnnDB
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With

   ' Open the tables schema rowset.
   Set rstList = cnnDB.OpenSchema(adSchemaTables)

   ' Loop through the results and print the
   ' names and types in the Immediate pane.
   With rstList
      Do While Not .EOF
         If .Fields("TABLE_TYPE") <> "VIEW" Then
            Debug.Print .Fields("TABLE_NAME") & vbTab & _
               .Fields("TABLE_TYPE")
         End If
         .MoveNext
      Loop
   End With
   cnnDB.Close
   Set cnnDB = Nothing
End Sub

I must...

"I must learn to love the fool in me--the one who feels too much, talks too much, takes too many chances, wins sometimes and loses often, lacks self-control, loves and hates, hurts and gets hurt, promises and breaks promises, laughs and cries.

It alone protects me against that utterly self-controlled, masterful tyrant whom I also harbor and who would rob me of my human aliveness, humility, and dignity but for my Fool."

-Theodore I. Rubin-

JQuery Free E book

Better Interaction Design and Web Development with Simple JavaScript Techniques

• An introduction to jQuery that requires minimal programming experience
  
• Detailed solutions to specific client-side problems
  
• For web designers to create interactive elements for their designs
  
• For developers to create the best user interface for their web applications
  
• Packed with great examples, code, and clear explanations
  
• Revised and updated version of the first book to help you learn jQuery
  

Dowload (pdf)

Live Like You're Dying

One of these days you’ll be
under the covers you’ll be
under the table and you’ll realize
all of your days are numbered;
all of them one to one hundred.
All of them millions.
All of them trillions.
So what are you gonna do with them all?
You can not trade them in for mall.
no no

Cuma Bencong yang Nangis

Air mata yang keluar dapat dihapus,
sementara air mata yang tersembunyi
menggoreskan luka yang tidak akan pernah hilang

Apa yang membedakan boneka Barbie dengan pistol air? Sedari kecil, saya akan menjawab, boneka itu mainan cewek dan pistol air mainan cowok. Walaupun saat itu saya sudah bermain dengan boneka Star Wars ataupun He-Man The Master of The Universe, ah beda, ini kan bukan boneka, ini Action Figures.

Yang jelas sosialisasi pemisahan macam ini biasanya sudah diajarkan sejak kecil. Baik dari orang tua maupun masyarakat sekitar, termasuk teman-terman. Salah satu bentuknya adalah tadi, boneka vs pistol air, main masak-masakan vs main benteng-bentengan, kencing duduk vs kencing berdiri, dst.

Angka Acak di VB.NET

Random class udah ada di  .NET Framework class library
di constructornya, ada dua override method. ada yang minta input ada yang gak..

Random class punya tiga public method yaitu : Next, NextBytes, and NextDouble.

Next method mengembalikan angka acak biasa ,
NextBytes mengembalikan array yang berisi angka acak,
NextDouble mengembalikan angka acak untuk nilai desimal

contoh mengembalikan angka acak biasa:

Dim num As Integer = random.Next()

contoh mengembalikan angka acak < 1000.

Dim num As Integer = random.Next(1000)

angka acak diantara:

Private Function RandomNumber(min As Integer, max As Integer) As Integer
   Dim random As New Random()
   Return random.Next(min, max)
End Function 

buat string acak :

Private Function RandomString(size As Integer, lowerCase As Boolean) As String
   Dim builder As New StringBuilder()
   Dim random As New Random()
   Dim ch As Char
   Dim i As Integer
   For i = 0 To size - 1
      ch = Convert.ToChar(Convert.ToInt32((26 * random.NextDouble() + 65)))
      builder.Append(ch)
   Next
   i If lowerCase Then
      Return builder.ToString().ToLower()
   End If
   Return builder.ToString()
End Function 

kombinasi fungsi angka dan string acak diatas (example:password generator)

Public Function GetPassword() As String
   Dim builder As New StringBuilder()
   builder.Append(RandomString(4, True))
   builder.Append(RandomInt(1000, 9999))
   builder.Append(RandomString(2, False))
   Return builder.ToString()
End Function 'GetPassword 

>

If I don't let myself by happy now then when?

The first star I see may not be a star.
We can't do a thing but wait.
So let's wait for one more.
The time such clumsy time in deciding if it's time.
I'm careful but not sure how it goes.
You can loose yourself in your courage.
The mindless comfort grows when I'm alone with my 'great' plans.
This is what she says gets her through it:
"If I don't let myself by happy now then when?"
If not now when?
When the time we have now ends.
When the big hand goes round again.
Can you still feel the butterflies?
Can you still hear the last goodnight?
Close my eyes and believe wherever you are, an angel for me.

kejujuran

Terkadang kejujuran membuka dan melapangkan jalan....

beberapa hari ini

beberapa hari ini, kalo denger lagu terutama lagu dengan beatnya kenceng,
gayanya pengen mirip2 koil di klip kita masih bisa diselamatkan...
lagi seneng denger muse yang album the resistance, gw suka the resistancenya
huehehehe...

is your secret safe tonight?
And are we out of sight?
Or will our world come tumbling down?
Will they find our hiding place?
Is this our last embrace?
Or will the world start caving in?

Count Character Occurrences Function

CREATE FUNCTION [dbo].[ufn_CountChar] ( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN

DECLARE @vInputLength        INT
DECLARE @vIndex              INT
DECLARE @vCount              INT

SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = LEN(@pInput)

WHILE @vIndex <= @vInputLength
BEGIN
IF SUBSTRING(@pInput, @vIndex, 1) = @pSearchChar
SET @vCount = @vCount + 1

SET @vIndex = @vIndex + 1
END

RETURN @vCount

END
GO

Description

As can be seen from the user-defined function, it loops through each character in the input string (WHILE @vIndex <= @vInputLength) and compares the string characer against the input search characer (IF SUBSTRING(@pInput, @vIndex, 1) = @pSearchChar). If they are the same, the counter is incremented by 1 (SET @vCount = @vCount + 1).

Second Variant

Here's another way of determining the number of times a certain character occur in a given string without the use of a loop.

CREATE FUNCTION [dbo].[ufn_CountChar] 
( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN

RETURN (LEN(@pInput) - LEN(REPLACE(@pInput, @pSearchChar, '')))

END
GO

Description

This method is more efficient than the previous version primarily because it doesn't use a loop to determine the number of times the search character occurs in the given string. What it does is replace from the input string the search character with an empty character. It then subtracts the length of the resulting string from the length of the original string to determine the number of times the search character occurs in the input string.

To further illustrate the method performed by the user-defined function, let's say you have a string value of "The quick brown fox jumps over the lazy dog." and you want to determine the number of times the letter "o" occur in the string. The first step it does is to replace the search character, in this case the letter "o", with an empty character/string. So the resulting string after the REPLACE will be "The quick brwn fx jumps ver the lazy dg." Now we subtract the length of this resulting string, which is 40, from the length of the original input string, which is 44, and that gives us 4, which is the number of "o"'s in the given string value.

Third Variant

The previous version of the Count Character Occurrence user-defined function is not case-sensitive. If we want to count the number of "t"'s in lower-case from the same string value above, it will give us a value of 2 instead of just a return value of 1 because it will count the first "t" even if it is in upper-case. To make the user-defined function case-sensitive, it has to be modified as follows:

CREATE FUNCTION [dbo].[ufn_CountChar] 
( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN

RETURN (LEN(@pInput) - LEN(REPLACE(@pInputCOLLATE SQL_Latin1_General_Cp1_CS_AS, 
@pSearchChar COLLATE SQL_Latin1_General_Cp1_CS_AS, '')))

END
GO

Description

This version of the Count Character Occurrence user-defined function is the same as the previous version but with a small modification to make it case-sensitive. The only difference is the adding of the "COLLATE SQL_Latin1_General_Cp1_CS_AS" clause to both the input string and the search character. By changing the collation of both the input string and the search character to SQL_Latin1_General_Cp1_CS_AS, which is case-sensitive and accent-sensitive (CS_AS), the REPLACE function will only replace from the input string the search character that matches it exactly, including the case.

source:http://www.sql-server-helper.com/functions/count-character.aspx

view two or more column into one column - merubah dua atau lebih kolom menjadi satu kolom

merubah dua atau lebih kolom menjadi satu kolom, kalo pake MySQL kita dapat memakai fungsi coalesce...
untuk SQL server ini caranya, .
script SQLnya kayak gini

pertama kita buat tabel1

Code:

create table table1 (kolom1 int, kolom2 varchar(50)) 
Go 
  

trus kita isi datanya

Code:

insert table1 
select 1,'satu'  union all 
select 1,'dua'   union all 
select 1,'tiga'  union all 
select 2,'empat' union all 
select 2,'lima'  union all 
select 3,'enam'  union all 
select 3,'tujuh' union all 
select 3,'delapan' 
go

isi tabel1 jadi seperti ini

Kolom1	Kolom2
1	satu
1	dua
1	tiga
2	empat
2	lima
3	enam
3	tujuh
3	delapam

nah kita buat fungsi buat groupingnya

Code:

create function dbo.Group_Concat(@kolom1 int) 
returns varchar(5000)  
--outputnya yang akan jadi kolom2 
as begin    
declare @out varchar(5000) 
--mengabungkan isi kolom2 berdasarkan kriteria kolom1    
select   @out = coalesce(@out + ',' + kolom2, kolom2)   
from   table1    
where   kolom1 = @kolom1    
return @out 
end 

fungsi diatas akan mengrouping nilai kolom2 berdasarkan input yang dimasukin(kolom1)
kalo dipanggil fungsinya... misalnya

Call Group_Concat(1)

maka returnnya = "satu,dua,tiga"
nah sekarang kita buat query untuk nampilin bukan hanya satu input tapi semua nilai dari kolom1
yang itu dengan mengisi parameter untuk fungsi Group_concat dari sub query

querynya :

Code:

select kolom1, dbo.Group_Concat(kolom1) kolom2 
from   
(select   kolom1    from   table1    group by kolom1 ) tempTable 

dan outputnya akan menjadi

Kolom1	Kolom2
1	satu, dua, tiga
2	empat, lima
3	enam, tujuh, delapan

Gw nyobainnya pake Ms SQL server 2005.... seharusnya bekerja untuk semua versi SQl server

Pale

The world seems not the same

Though I know nothing has changed
It's all my state of mind
I can't leave it all behind
I have to stand up to be stronger
I have to try
To break free
From the thoughts in my mind
Use the time that I have
I can say goodbye
Have to make it right
Have to fight
'Cause I know in the end it's worthwhile
That the pain that I feel slowly fades away
It will be all right

I know
I should realize
Time is precious
It is worthwhile
Despite how I feel inside
Have to trust it'll be alright
Have to stand up to be stronger

Oh, this night is too long
Have no strength to go on
No more pain I'm floating away

Through the mist I see the face
Of an angel, calls my name
I remember you're the reason I have to stay

I'm looking for a book...

I'm looking for a book... something that can help me deal with what might be an awkward situation. Something funny might be nice, but not necessarily big, 'ha, ha, ha,' 'laugh, laugh, laugh' funny, and certainly not make-fun-of-other-people funny but rather something human-funny.
And, uh, if it could uh, sneak up on you, surprise you, and at the same time make you think that what you thought wasn't only right, in a wrong kind of way, but when you're wrong, there's a certain rightness in your wrongness... Well, what I mean is, more importantly, I'm looking to be swept up! And at the same time, not....

Forgoten

already forgotten
time to moving forward

SOMNOLENCE

Gilaaa.... ngantuk pisaaaannnn...

Debugging ASP classic with Visual Studio 2005

1. Classic ASP debugging only works with IIS. It does not work with the VS Development Web Server
2. In VS 2005 you have to attach to the ASP worker process (dllhost.exe in IIS 5).

Here is how to make ASP debugging work:

1. Enable ASP debugging on the server. (I also added DEBUG verb to the asp extension, but I am not sure if it is required).
2. Open classic ASP in VS 2005.
3. Set breakpoint.
4. View page in browser or run without debugging.
5. Debug | Attach to Process
6. Select show process from all user
6. Locate IIS ASP worker process (dllhost.exe on IIS5) which exposes x86 and Script and attach as Script.  usually the user is you computer name ex: (MYPC/IWAPT_MYPC)

I tried on Windows XP SP3 prof running IIS 5 and it worked for me.

gempa...

temannya orang ganteng, kita sebut saja si A: pilih site yg
temannya orang ganteng, kita sebut saja si B: masalah size itu tenang aj
A : bentar
A : gempa ...
A : kabur dulu
B : wkk

tadi gempa... semoga tidak apa-apa...