Showing posts with label databasE. Show all posts
Showing posts with label databasE. Show all posts

December 31, 2010

Microsoft SQL Server, Error: 916

(Microsoft SQL Server, Error: 916)

http://blog.cpugeni.com/archive/2008/09/22/microsoft-sql-server-management-studio-2008-error-916.aspx

I came across a problem when using Microsoft SQL Server Management Studio 2008 to connect and manage a database which is housed on my shared host’s SQL Server 2005, the problem was that I couldn’t actually access my database, as I clicked the “Database” node within “Object Explorer” I received the message:
The server principal “username” is not able to access the database “databasename” under the current security context. (Microsoft SQL Server, Error: 916)
image
Anyway, after an hour, I decided that I would try and access my database within Microsoft Visual Studio 2008, using the “Server Explorer”. I set myself up with a connection to the database server, and hooray, I could connect and access everything. That left me wondering why it wasn’t working in the Management Studio, so I spent some more time trawling the net for an answer (since I’m hardly a SQL Server guru!). I eventually found something to help me out…
When I clicked on the “Databases” node within SQL Server Management Studio, the software would (in the background) execute an T-SQL query to retrieve a list of databases along with additional information about those databases, one of those pieces of information is collation, which I didn’t have permission to do for every database, only my own.
Fix:
  1. In Object Explorer, click Databases
  2. Display “Object Explorer Details” (F7) or “View –> Object Explorer Details”
  3. Right click the column headers and deselect “collation”
  4. Refresh Databases, and hopefully bingo!

March 19, 2010

SQL Server Date Format

DATE FORMATS
Format # Query (current date: 12/30/2006) Sample
1 select convert(varchar, getdate(), 1) 12/30/06
2 select convert(varchar, getdate(), 2) 06.12.30
3 select convert(varchar, getdate(), 3) 30/12/06
4 select convert(varchar, getdate(), 4) 30.12.06
5 select convert(varchar, getdate(), 5) 30-12-06
6 select convert(varchar, getdate(), 6) 30 Dec 06
7 select convert(varchar, getdate(), 7) Dec 30, 06
10 select convert(varchar, getdate(), 10) 12-30-06
11 select convert(varchar, getdate(), 11) 06/12/30
101 select convert(varchar, getdate(), 101) 12/30/2006
102 select convert(varchar, getdate(), 102) 2006.12.30
103 select convert(varchar, getdate(), 103) 30/12/2006
104 select convert(varchar, getdate(), 104) 30.12.2006
105 select convert(varchar, getdate(), 105) 30-12-2006
106 select convert(varchar, getdate(), 106) 30 Dec 2006
107 select convert(varchar, getdate(), 107) Dec 30, 2006
110 select convert(varchar, getdate(), 110) 12-30-2006
111 select convert(varchar, getdate(), 111) 2006/12/30



TIME FORMATS
8 or 108 select convert(varchar, getdate(), 8) 00:38:54
9 or 109 select convert(varchar, getdate(), 9) Dec 30 2006 12:38:54:840AM
14 or 114 select convert(varchar, getdate(), 14) 00:38:54:840

http://www.mssqltips.com/tip.asp?tip=1145

January 28, 2010

Get Auto ID from SQL Server after insert something

use this,
select scope_identity() 
after you execute the insert query ..

January 18, 2010

configure SQL Server 2005 to allow remote connections

To configure SQL Server 2005 to allow remote connections, complete all the following steps:
  • Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
  • Turn on the SQL Server Browser service.
  • Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.
http://support.microsoft.com/kb/914277

    November 05, 2009

    SQL Server PL SQL to manipulate text

    source : http://www.mssqltips.com
    The following is a list of SQL Server 2000 T-SQL commands that exist to allow you to manipulate text data either from stored procedures, triggers, functions or embedded SQL code.
    Command Description
    CHARINDEX( findTextData, textData, [startingPosition] ) Returns the starting position of the specified expression in a character string. The starting position is optional.
    LEFT( character_expression , integer_expression )
    Returns the left part of a character string with the specified number of characters.
    LEN( textData ) Returns integer value of the length of the string, excluding trailing blanks
    LOWER ( character_expression ) Returns a character expression after converting uppercase character data to lowercase
    LTRIM( textData) Removes leading blanks
    PATINDEX( findTextData, textData ) Returns integer value of the starting position of text found in the string
    REPLACE( textData, findTextData, replaceWithTextData ) Replaces occurrences of text found in the string with a new value
    REPLICATE( character_expression , integer_expression ) Repeats a character expression for a specified number of times.
    REVERSE( character_expression ) Returns the reverse of a character expression.
    RTRIM( textData) Removes trailing blanks
    SPACE( numberOfSpaces ) Repeats space value specified number of times
    STUFF( textData, start , length , insertTextData ) Deletes a specified length of characters and inserts another set of characters at a specified starting point
    SUBSTRING( textData, startPosition, length ) Returns portion of the string
    UPPER( character_expression ) Returns a character expression with lowercase character data converted to uppercase.
     
    Examples
    The examples below are just simple SELECT statements using hard coded values.  You can use these functions when querying your tables, so you can manipulate the string values as you query your data and return the modified result.
    Query Value
    SELECT CHARINDEX('SQL', 'Microsoft SQL Server - SQL Server') 11 (SQL is found in the 11 position)
    SELECT CHARINDEX('SQL', 'Microsoft SQL Server - SQL Server', 20) 24 (SQL is found in the 24 position, since we started looking in position 20)
    SELECT LEFT('Microsoft SQL Server - SQL Server' , 20 ) Microsoft SQL Server (left 20 characters of the string)
    SELECT LEN('Microsoft SQL Server - SQL Server') 33 (total length of the string)
    SELECT LOWER('Microsoft SQL Server - SQL Server') microsoft sql server - sql server (string in lower case)
    SELECT LTRIM( ' Microsoft SQL Server - SQL Server ') Microsoft SQL Server - SQL Server (trimmed string removing leading spaces)
    SELECT PATINDEX( '%SQL%', 'Microsoft SQL Server - SQL Server' ) 11 (SQL is found in the 11 position)
    SELECT REPLACE( 'Microsoft SQL Server - SQL Server', 'Server', 'Server 2005' ) Microsoft SQL Server 2005 - SQL Server 2005 (string after we replace 'Server' with 'Server 2005')
    SELECT REPLICATE( 'x' , 10 ) xxxxxxxxxx (x replicated 10 times)
    SELECT REVERSE( 'Microsoft SQL Server' ) revreS LQS tfosorciM (string in reverse)
    SELECT RTRIM( ' Microsoft SQL Server - SQL Server ')  Microsoft SQL Server - SQL Server (string after removing trailing spaces)
    SELECT 'Microsoft' + SPACE(10) + 'SQL Server' Microsoft          SQL Server (string after inserting 10 spaces)
    SELECT STUFF( 'Microsoft SQL Server', 11 , 3 , '2005' ) Microsoft 2005 Server (string after replacing positions 11, 12, 13 with '2005')
    SELECT SUBSTRING( 'Microsoft SQL Server', 1, 9 ) Microsoft (substring of statement starting at position 1 for 9 characters)
    SELECT UPPER('Microsoft SQL Server - SQL Server') MICROSOFT SQL SERVER - SQL SERVER (string in upper case)
    In addition to using the commands by themselves you can use multiple commands at the same time to provide more meaningful results.
    Query Value
    SELECT LEFT('Microsoft SQL Server', CHARINDEX(' ', 'Microsoft SQL Server - SQL Server',13) - 1) Microsoft SQL (find portion of string where a space is found but starting at position 13)
    SELECT LEFT('Microsoft SQL Server', CHARINDEX(' ', 'Microsoft SQL Server - SQL Server') - 1) Microsoft (find portion of string where a space is found)
    SELECT LTRIM(RTRIM(' Microsoft SQL Server - SQL Server ')) Microsoft SQL Server - SQL Server (trim leading and trailing spaces)

    Next Steps
    • Get familiar with these commands and how they can be used to manipulate text data
    • Look for ways of using multiple commands together to reach the desired results

    November 03, 2009

    Mengatasi Masalah Connection Pool

    pesan error : 

    Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.


    Coba lihat di database IDE Management .. 
    klik kanan nama database kemudian cari menu detach ...
    dan cari koneksi2 yang mencurigakan dan layak di'bunuh' :)


    Good Luck

    September 16, 2009

    Error: 9002 SQL Server

    Error: 9002, Severity: 17, State: 2
    The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

    Nah kalo udah nemu error database (SQL Server khususnya) kayak di atas ..
    Causes :
    How to maintenance or even solve it :
    • check db integrity
    • shrink db
    • re-organize index
    • update statistics
    • backup db
    • maintenance cleanup
    and for additional run the dbcc shrinkdatabase("dbname", TRUNCATEONLY)

    April 13, 2008

    setting jdbc ibm db-2 using netbeans6

    kali ini Q gunain IDE Netbeans 6.0 untuk pengerjaan Servlet EJB aplikasiQ ..
    tapi nemu masalah (selalu xp) ,, server databaseQ ada pada kompi tersendiri pisah ma servletx (tapi untungnya masih terkoneksi jaringan .. wekekeke y jelaslah .. ngaco pol ..)
    Akhirnya Q harus bikin data source yg ngarah ke data connection database DB2 yg ada di tempat lain tadi.
    Otomatis Q musti punya driver nya dulu yang bisa digunakan oleh 'Web Server'x (di sini Q pake paket bundlex GlassFish V2, bisa juga pake Tomcat) ..
    driver bisa kamu dapetin di http://www.ibm.com/
    ada 2 file yg dibutuhin untuk driver ini ..
    1. db2jcc.jar
    2. db2jcc_license_cu.jar
    SETTING WEBSERVER
    • Next, misal kamu butuh tambahin atau ubah 'Web Serverx' masuk aja ke tag 'Services' kemudian klik kanan-> Add Server...




















    • Pilih server yang akan ditambahkan, misal glassfish V2 dan klik Next ...
















    • Kemudian pilih lokasi hasil install dari web server yg ada .. dan pilih setting untuk domain lokal, remote atau personal

















    • Masukkan autentikasi username dan password admin dari web server .. terakhir klik finish ...


















    SETTING JDBC

    Setelah web server tersetting, maka berikutnya Qt setting daripada jdbc untuk koneksi database db2 (kok inget film apa gitu y, ngomong daripada daripada ... :p)
    Tambahkan database Connection untuk DB2 ini dengan klik kanan pada 'Databases' -> 'New Connection' (tetep di tag 'Services')























    • Untuk menambahkan driver data connection baru langsung aja masuk ke New Driver ...

















    • Muncul form untuk setting classpath dari driver yang sudah di-download tadi ...



















    • tambahkan kedua file db2jcc.jar dan db2 db2jcc_license_cu.jar, secara otomatis netbenas bisa deteksi driver class .. tinggal namain aja di tag 'Name' kali ini a kasi nama DB2 JDBC(1)


















    • Bila settingnya udah bener maka muncul koneksi jdbc seperti di samping ini (icon yang patah brarti tidak terhubung)


    March 27, 2008

    how to create auto id with IBM-DB2 database

    Assume, that i have a table (in example "QUESTION") and i have a column with integer data type .
    Then i want to have auto-id in that column (in example "QST_ID") .

    That's two ways , there are via sql-operation and via-wizard based on IBM-DB2 Interface ..
    1. via SQL

    CREATE TABLE "QUESTION" ( "QST_ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
    (START WITH 1, INCREMENT BY 1, NO CACHE )
    , "QST_DESCRIPTION" VARCHAR (150) NOT NULL );

    2. via Wizard

    • First, right-click on 'Tables' tab to create a table











    • Then, fill the name of your table (i named it with "USER")











    • Specified the column that will be added to the table











    • then we make a column with auto-id, please check carefully in the red rectangular ... we click the Identity radiobox, then specified Intitial value, and Increment value ...













    • If done, click finish to end











    So, we have a table "QUESTION" with two columns, QST_ID and QST_DESCRIPTION .
    with the auto-id generated in QST_ID column

    March 17, 2008

    Database Concept

    Basis data atau database dalam pengertian umum dapat diartikan sebagai,
    • Himpunan kelompok data (arsip) yang saling berhubungan yang diorganisasi sedemikian rupa agar kelak dapat dimanfaatkan kembali dengan cepat dan mudah
    • Kumpulan data yang salng berhubungan yang disimpan secara bersama sedemikian rupa dan tanpa pengulangan (redundancy) yang tidak perlu, untuk memenuhi berbagai kebutuhan
    • Kumpulan file/tabel/arsip yang saling berhubungan yang disimpan dalam media penyimpanan tertentu
    Tujuan penggunaan database antara lain adalah untuk :

    1. Kecepatan dan Kemudahan (Speed)

    Yakni agar pengguna basis data dapat:
    - menyimpan data
    - melakukan perubahan/manipulasi terhadap data
    - menampilkan kembali data
    dengan lebih cepat dan mudah dibandingkan dengan cara biasa (baik manual maupun elektronis).

    2. Efisiensi Ruang Penyimpanan (Space)
    Agar kita mampu melakukan penekanan jumlah redundansi (pengulangan) data, baik dengan menerapkan sejumlah pengkodean atau dengan membuat relasi-relasi antara kelompok data yang saling berhubungan.

    3. Keakuratan (Accuracy)
    Agar data sesuai dengan aturan dan batasan tertentu dengan cara memanfaatkan pengkodean atau pembentukan relasi antar data bersama dengan penerapan aturan/batasan (constraint) tipe data, domain data, keunikan data dsb.

    4. Ketersediaan (Availability)
    Agar data bisa diakses oleh setiap pengguna yang membutuhkan, dengan penerapan teknologi jaringan serta melakukan pemindahan/penghapusan data yang sudah tidak digunakan/kadaluwarsa untuk menghemat ruang penyimpanan.

    5. Kelengkapan (Completeness)
    Agar data yang dikelola senantiasa lengkap baik relatif terhadap kebutuhan pemakai maupun terhadap waktu, dengan melakukan penambahan baris-baris data ataupun melakukan perubahan struktur pada basis data; yakni dengan menambahkan field pada tabel atau menambah tabel baru.

    6. Keamanan (Security)
    Agar data yang bersifat rahasia atau proses yang vital tidak jatuh ke orang/pengguna yang tidak berhak, yakni dengan penggunaan account (username dan password) serta menerapkan pembedaan hak akses setiap pengguna terhadap data yang bisa dibaca atau proses yang bisa dilakukan.

    7. Kebersamaan (Sharability)
    Agar data yang dikelola oleh sistem mendukung lingkungan multiuser (banyak pemakai), dengan menjaga/menghindari munculnya problem baru seperti inkonsistensi data (karena terjadi perubahan data yang dilakukan oleh beberapa user dalam waktu yang bersamaan) atau kondisi deadlock (karena ada banyak pemakai yang saling menunggu untuk menggunakan data).

    Objek-objek yang sering ditemui dalam basis data ;

    1. Entitas/Tabel
    Merupakan objek yang mewakili hal yang dimaksud dan akan diatur secara konsep maupun fisik,
    misal Entitas Pegawai, Entitas Pelanggan, Entitas Penjualan, Entitas Pembelian dll.

    2. Relasi
    Relasi menyatakan hubungan antar entitas, termasuk terhadap entitas itu sendiri (rekursif). Relasi dapat berupa relasi one to one , one to many , many to one, dan many to many *dibahas berikutnya ..

    3. Attribute
    Menyatakan atribut-atribut yang dimiliki oleh sebuah entitas sehingga dapat dibedakan dengan entitas lainnya, misalnya Untuk Entitas Pegawai ada atribut NIP, atribut Nama, atribut Alamat, atribut Nomor Telepon dsb.

    4. Others
    Berikutnya ada objek2 lainnya seperti, primary key, foreign key, view, stored procedure, trigger, indeks dsb. *dibahas berikutnya ..

    March 14, 2008

    SQL Error: ORA-xxxxx


    do you have any idea with this error with your oracle database ??

    * Every code like 'SQL Error: ORA-00942' and such have the meaning itself ..
    ** for example ORA-00001: --> that's mean unique constraint (string.string) violated
    ** it is caused by An UPDATE or INSERT statement attempted to insert a duplicate key.
    ** We can solve this problem with : Either remove the unique restriction or do not insert the key.

    Hmm that's useful I think ,,
    every code-error in Oracle had been captured at http://sdp-1.ora-code.com/


    let's solve it by yourself ..