Saturday, November 27, 2010

วิธีใช้งาน visual Basic 2005 กับ ฐานข้อมูล SQL Server 2005


วิธีใช้งาน visual Basic 2005 กับ ฐานข้อมูล SQL Server 2005
เมื่อกี่ไปเห็นที่ Google Guru มีคนถามถึงเรื่องนี้งั้นผมขอ แชร์ด้วยคนนะครับ

สร้าง connection ก่อนครับ

Module conndb

Public SLDBconnection As String = ReadINI.INIRead(Path.GetFullPath(".") & "\dbini.ini", "APPCONFIG", "SLDBconnection", "")


End Module
-----------------------------------------------------------------------------
ส่วนใน DBini file นั้นผมเขียนไว้ประมาณนี้ครับ


[APPCONFIG]
SLDBconnection = Data Source=nb-montree;database=Database;uid=sa;pwd=sa



usr=
pwd=

----------------------------------------------------------------

ต่อไป Class สำหรับอ่าน ini ครับ

Public Class ReadINI
#Region "API Calls"
' standard API declarations for INI access
' changing only "As Long" to "As Int32" (As Integer would work also)
Private Declare Unicode Function WritePrivateProfileString Lib "kernel32" _
Alias "WritePrivateProfileStringW" (ByVal lpApplicationName As String, _
ByVal lpKeyName As String, ByVal lpString As String, _
ByVal lpFileName As String) As Int32
Private Declare Unicode Function GetPrivateProfileString Lib "kernel32" _
Alias "GetPrivateProfileStringW" (ByVal lpApplicationName As String, _
ByVal lpKeyName As String, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Int32, _
ByVal lpFileName As String) As Int32
#End Region

Public Shared Function INIRead(ByVal INIPath As String, _
ByVal SectionName As String, ByVal KeyName As String, _
ByVal DefaultValue As String) As String
' primary version of call gets single value given all parameters
Dim n As Int32
Dim sData As String
sData = Space$(1024) ' allocate some room
n = GetPrivateProfileString(SectionName, KeyName, DefaultValue, _
sData, sData.Length, INIPath)
If n > 0 Then ' return whatever it gave us
INIRead = sData.Substring(0, n)
Else
INIRead = ""
End If
End Function

End Class
---------------------------------------------------------------------
จากนั้นก็ลงมือสร้าง ฟอร์ม หน้าตาแบบนี้ แล้วก็ประยุคต์ source code ดูนะครับ






Public Class frmitem



Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click
Dim connection As New SqlConnection
Dim da As SqlDataAdapter
Dim ds As DataSet = New DataSet

With connection
If .State = ConnectionState.Open Then .Close()
.ConnectionString = SLDBconnection
.Open()
End With


Dim command As SqlCommand = New SqlCommand("STPxListItem", connection)
command.CommandText = "Exec STPxListItem '" & txtsitem.Text & "'"
command.Connection = connection 'Active Connection


da = New SqlDataAdapter(command)
da.Fill(ds, "litem")


If ds.Tables("litem").Rows.Count <> 0 Then

With grdview
.ReadOnly = True
.DataSource = ds.Tables("litem")
End With

Else

grdview.DataSource = Nothing
End If



End Sub
End Class

Friday, November 26, 2010

Percent Discount

วิธีคิด เปอร์เซ็นส่วนลดสินค้า

วิธี คำนวณ เปอร์เซ็นส่วนลดสินค้า เสื้อผ้า

วันนี้ผมมี วิธี คำนวณ ส่วนลดสินค้าว่าที่ร้านลดให้เรานั้นคิดเป็น กี่เปอร์เซ็นกันแน่
ตัวอย่างเช่น ร้านขาย เสื้อผ้าแฟชั่นสไตล์เกาหลี ร้านขาย แฟชั่นสไตล์เกาหลี ติดป้าย ราคาขาย เสื้อผ้าแฟชั่นสไตล์เกาหลี ไว้ 1300 ผมเข้าไปต่อราคาเหลือ 850 บาท และถ้าผมอยากรู้ว่า ร้านนั้นเขาลดราคาให้ผมกี่เปอร์เซ็นกันแน่นะ มาเริ่มกันเลยดีกว่า

ผมกำหนดให้ ราคาเก่า = X ,ราคาใหม่ = Y , Z = จำนวนเงินส่วนลด
z = x - y
z = 1300 - 850
z = 450 บาท

ฉะนั้นผมรู้แล้วว่าร้าน ขาย เสื้อ เสื้อแฟชั่นสไตเกาหลี ลดให้ผม 450 บาท แล้ว 450 บาทนั้นคิดเป็นกี่เปอร์เซ็นของ 1300 ล่ะครับที่นี้
PD = Percent Discount
PD = (z/x) x 100
แทนค่า

PD = (450/1300) x 100
PD = 34.61 %

ฉะนั้นเราก็รู้แล้วนะครับว่าเราได้ส่วนลดตั้ง 34.61 เปอร์เซ็นเลยที่เดียว
ผมคิดว่าสูตรง่ายพวกนี้เป็นสิ่งที่เราไม่ควรมองข้ามนะครับเลยเก็บมาฝากเพื่อน ๆ พี่ ๆ น้อง ๆ ครับ

Saturday, November 20, 2010

Link for Free web directory submit

Link for Free web directory submit, free web site submit,submit web page
Hi,Friend. You are looking for free Link Submit Web high Rank .
This link help you to finding the High Rank(PR) and Free Submittion web . you can click on the link
and then you find out the web for you submit, i think,You choose PR each the web should be that more than you web site or you choose PR > 2 and then when you stay on the submittion web you must to find out wording is Submit Link,Submit site ,something like that.
Okay Let go to Free web directory submit.

Friday, November 19, 2010

การ add on new domain name to hosting of godaddy.com

วิธีการ How to add on new domain name to hosting of godaddy.com

Hi ,Friend,I can help you to add secound new domain name to godaddy.com.
you can follow by below.

1. Login to www. godaddy.com
- Log in with user name and Password of your host
2. choose the host name for you need to add new domain name .
3.Click Launch Button


4. Look at the Menu Click on the

"Setting" and then " Domain Management"

5.Look at the right Top Screen clikc on "Add Domain"

6.fill in the your domain for example 9montree.com

7.Choose the Path or create new the folder for keep your website files.

8. Finish the Step for Add on domain name to hosting of www.godaddy.com


Thank you.


Monday, November 15, 2010

Solution for correct Limited or no Connectivity wireless on Laptop windows xp,vista

Solution for correct " Limited or no Connectivity wireless " on Notebook ,Laptop used windows xp,vista

Hi , friend , I have found the problem can't connect to the network and then i have check the Network Icon and then i took double click on that i have found the message is " Limited or no Connectivity wireless " .

Solution : Reinstall the Wireless and then please sure, you uninstall the old driver first and then
restart Machine and install Wireless Driver on Laptop againts.

Thank you.

ms sql server 2005 restore failed because the database is in use.

Ms sql server 2005,Express,2008 restore failed on ERP Syteline Database because the database is in use. Hi, Friend.If you looking for solution for restore database with out error same below

" system.data.sqlclient.sqlerror:exclusive access could not be obtained because the database is in use. "


from message above i sure , you have been restoring the database while that have the application is in use so i have script for find out the problem .

1. Log in to sql server -> create new query -> key in the script
use Database name
exec sp_who -- if you need to check who have been using the database.
From a picture . Sured, the database is in use.



2. Stop Service of Application to use the MS SQL server 2005 | Express | 2008





3. Run Restore against . I have sured , you can restore the database MS SQL 2005 ,Express , 2008





Friday, November 12, 2010

What is ERP ATP,CTP

What is ERP ATP,CTP

ATP : Available to Promise

-CTP : Capacity to Promise .

If i have a time i will come back to write to continue.



Tuesday, November 9, 2010

Free Solution : The Service did not respond to the start on Windows xp

error 1053 : The Service did not respond to the start or control request in a timely fashion

Problem : Because the system event log full .

Solution : Clear the Event system log and application log and then restart the service against.


How to create schedule backup sql server 2005 (Cont 2)

How to create | Creating | Build | schedule backup sql server 2005 | sql server 2008

6. you can Right Click or Double on the Task below.
if you Right click , You must to select the Edit menu for in put the Backup Detail .



7. Fill in and Key in the input deatil
Back up type : Full if you want to backup every thing you can select other backup type by Drop Down Lit.
You don't forget to choosed the Database name ,You want to backup .




8. Setup the path for keep the backup file and Last name of Backu file (*.bak)
Finish the input the backup database detail.




9. Go to Step ,Set up the Schedule Time for the Backup
Click on My High Light in the picture.



10. Key in the Job Schedule Detail.



11. Click Save for you confirm the Schedule Time.


12. When you finish the create the Schedule Backup database so you will see same below.



13. You can waiting untill the Schedule follow by you set up or Right Click -> Exec on the Job Plan and If that finish ,You will see the Message Box below .






14. Go to the Path for keep the database backup file .

You will see the backup database file Databasename_Backup_yyyymmddd.bak





Hope you enjoy on My Blog. See you again next Topic.


Back To the First Page

Monday, November 8, 2010

how to solve The system cannot find the file specified on windows xp

how to solveThe system cannot find the file specified on windows xp
Now , I have used the windows XP3 . I have found that when i try to start the one service .
so , I try to start that many time but the error have been same so that have root cause is
The Event Log is full so you must to delete or clear the System Log and Application Log and then restart the service against . I sured that worked.
below is method for clear that.

How to trust Database sql server 2005

How to trust Database sql server 2005,Express
Okay,I must to used the SQL script when you have to restore the database from the database backup from aother Machine so You must to run the script for change some thing to your machine .

1. Log in to the SQL Server .
2. Open the new Query and then copy below .

alter database [your database name]
set trustworthy ON;


Plase waiting for the Compleated message .

Thanks.

Google turns off GMail data feed to sites like Facebook


Google turns off GMail data feed to sites like Facebook


GMail contacts will no longer be automatically handed over to other websites and services, says Google.

The search firm will now only share user information if the site wanting access provides reciprocal data feeds to others.

The policy switch was primarily aimed at Facebook, said Google, complaining that the social network left users in a "data dead end".

Google said the policy switch would be implemented over the next few weeks.

Like many other web firms, Google lets others get at the data it holds on users of its many services via what is known as an Application Programming Interface (API).

Before the policy switch sites such as Facebook used Google's API to let their users automatically import GMail contacts so they could rapidly fill out their profile and find others that use the service.

Now Google will only give automatic access to GMail contacts to those sites and services that let others mine the data they hold.

In a statement shared with the Reuters newswire, Google singled out Facebook for criticism.

"We have decided to change our approach slightly to reflect the fact that users often aren't aware that once they have imported their contacts into sites like Facebook, they are effectively trapped," said Google.

Facebook has yet to comment on the row.

Gartner analyst Ray Valdes told Reuters that Google's decision is tied into its business ambitions.

"Google needs to evolve to become a big player in the social Web and it hasn't been able to do that," he said.

Analysts also suspect that Google's decision is related to the deal struck between Facebook and Microsoft that allows user data to power the Bing search engine.

Although Google has stopped the automatic siphoning of GMail data it is still possible for users to download their contacts and then can be shared

Sunday, November 7, 2010

Solution for solve the problem Restore Database failed for Sql server 2005 ,SQL Express วิธี แก้

การ วิธี แำก้ How to Restore Database SQL Server 2005,SQL Express ?
I have found the error
"Restore failed for Sql server 2005 ,2008,SQL Express
Additional information.
system.data.sqlclient.sqlerror :The backup set holds a backup of a database other than the existing.
" the picture below when i try to restore the database from my database backup ,I have forgot tell you to My database server is version SQL Server 2005 but you can apply that to used to SQL Server 2008,MS SQL Express .
Root cause of problem: The system warning that for inform you to unsure you are working about Restore database while you have the existing database so you can follow to my Step.

Message Error.








Restore failed for Sql server 2005 ,2008,SQL Express
Additional information.
system.data.sqlclient.sqlerror :The backup set holds a backup of a database other than the existing.


Solution : After the you found the message . you not close the the form because you can you to Options Tab by Click Left on that and then you must to Tick on the Restore Options : Tick "Overwrite the existing database" and then Click Ok .


Waiting untill Program show below.




End of Procedure.

Friday, November 5, 2010

Free Norton Removal Tool Download -ฟรี

Free Norton Removal Tool Download in the us ฟรี
Befor the Antivirus patch will release you can used the Removal Tool first and then you can you it free in case you don't have the Norton Antivirus software .

Note : One the Removal tool download you can clean that only one Virus and then you don't forget follow by the information because after you done scan the Virus may be you must to change some thing in the registry . Good Luck.

for scans and clears your PC of spyware and viruses
Click Link : http://us.norton.com/security_response/removaltools.jsp

Virus List :


09/13/10W32.Imsolk.B@mm Removal Tool
09/06/10Backdoor.Tidserv Removal Tool
10/29/09Trojan.Ramvicrype Removal Tool
04/16/09Symantec Trojan.Ransomlock Key Generator Tool
04/15/09Trojan.Initbar Removal Tool
03/24/09Trojan.Xrupter Removal Tool
02/20/09W32.Virut Removal Tool
02/01/09Trojan.Bankpatch Removal Tool
01/13/09W32.Downadup Removal Tool
07/22/08Trojan.Brisv.A!inf Removal Tool
01/11/07Backdoor.Haxdoor.S/Trojan.Schoeberl.E Removal Tool
01/04/07W32.Spybot.ANDM Removal Tool
11/29/06W32.Spybot.ACYR Removal Tool
10/19/06W32.Rajump Removal Tool
10/17/06W32.Pasobir Removal Tool
10/04/06Symantec Support Tool ActiveX Control Cleanup Tool
09/23/06Trojan.Linkoptimizer Removal Tool
09/14/06W32.Bacalid Removal Tool
03/23/06Trojan.Abwiz Removal Tool
03/23/06Trojan.Exponny Removal Tool
03/23/06Trojan.Sientok Removal Tool
03/23/06W32.Antinny Removal Tool
03/17/06W32.Davs Removal Tool
02/02/06W32.Kiman Removal Tool
01/17/06W32.Blackmal@mm Removal Tool
12/02/05W32.Secefa Removal Tool
11/10/05Backdoor.Ryknos Removal Tool
11/03/05Trojan.Lodear Removal Tool
10/20/05Symantec Mobile Threats Removal Tool
09/22/05W32.Pexmor@mm Removal Tool
08/29/05W32.Bobax@mm Removal Tool
08/17/05W32.Esbot Removal Tool
08/15/05W32.Zotob Removal Tool
07/19/05W32.Reatle@mm Removal Tool
05/16/05Trojan.Jasbom Removal Tool
04/29/05Trojan.Vundo.B Removal Tool
04/13/05W32.Mytob.AR@mm Removal Tool
03/18/05W32.Serflog Removal Tool
03/08/05W32.Kelvir Removal Tool
03/07/05W32.Serflog.A Removal Tool
02/28/05W32.Mytob@mm Removal Tool
02/03/05W32.Bropia Removal Tool
12/17/04W32.Envid@mm Removal Tool
11/22/04Trojan.Vundo Removal Tool
11/17/04W32.Bofra@mm Removal Tool
10/04/04Adware.JustFindIt Removal Tool
08/10/04Backdoor.Agent.B Removal Tool
08/04/04W32.Evaman.C Removal Tool
06/14/04W32.Erkez.B@mm Removal Tool
06/02/04W32.Korgo Removal Tool
05/20/04W32.Donk.Q Removal Tool
05/06/04Tool to reset shell\open\command registry keys
05/01/04W32.Sasser Removal Tool
04/21/04W32.Opasa@mm Removal Tool
04/20/04W32.Erkez@mm Removal Tool
04/07/04W32.Blackmal.B@mm Removal Tool
04/02/04W32.Gaobot.UJ Removal Tool
03/14/04W32.Beagle.MO@mm Removal Tool
02/18/04W32.Netsky@mm Removal Tool
01/30/04W32.HLLW.Anig Removal Tool
01/27/04W32.Mydoom@mm Removal Tool
01/19/04W32.Beagle@mm Removal Tool
01/13/04W32.Gaobot Removal Tool
10/29/03W32.Sober Removal Tool
10/03/03Trojan.Qhosts Removal Tool
09/19/03W32.Swen.A@mm Removal Tool
08/19/03W32.Dumaru Removal Tool
08/19/03W32.Sobig.F@mm Removal Tool
08/18/03W32.Welchia.Worm Removal Tool
08/11/03W32.Blaster.Worm Removal Tool
08/08/03Backdoor.Winshell.50 Removal Tool
08/01/03W32.Mimail Removal Tool
06/27/03W32.Mumu.B.Worm Removal Tool
06/25/03W32.Sobig.E@mm Removal Tool
06/16/03W32.ExploreZip.Worm Removal Tool
06/06/03W32.Femot.Worm Removal Tool
06/05/03W32.Bugbear.B@mm Removal Tool
06/04/03Bat.Mumu.A.Worm Removal Tool
06/01/03W32.Sobig.C Removal Tool
05/18/03W32.Sobig.B Removal Tool
05/12/03W32.HLLW.Fizzer Removal Tool
04/14/03W32.HLLW.Nebiwo Removal Tool
02/24/03W32.HLLW.Lovgate Removal Tool
01/25/03W32.SQLExp.Worm Removal Tool
01/14/03W32.Sobig.A@mm Removal Tool
01/09/03W32.Lirva Removal Tool
11/25/02W32.HLLW.Winevar/W32.Funlove.4099 Removal Tool
11/15/02W32.Brid.A@mm/W32.Funlove.4099 Removal Tool
10/01/02W32.Bugbear@mm Removal Tool
09/30/02W32.Opaserv.Worm Removal Tool
08/01/02W32.Magistr Removal Tool
07/16/02W32.Frethem Removal Tool
07/03/02W32.Yaha Removal Tool
05/10/02Backdoor.Autoupder Removal Tool
04/18/02W32.Klez Removal Tool
04/15/02W2k.Stream Removal Tool
04/15/02Wscript.Kakworm Removal Tool
04/01/02W32.Gibe@mm Removal Tool
03/28/02W32.Mylife Removal Tool
12/04/01W32.Goner.A@mm Removal Tool
11/28/01W32.Badtrans.B@mm Removal Tool
10/30/01W32.Nimda.E@mm Removal Tool
09/19/01W32.Nimda.A@mm Removal Tool
08/09/01CodeRed Removal Tool
07/31/01VBS.Potok@mm Removal Tool
07/20/01W32.Sircam.Worm@mm Removal Tool
07/16/01VBS.Haptime Removal Tool
03/09/01DOS FunLove.4099 Fix Tool
02/20/01W32 HybrisF Fix Tool
01/11/01W95.CIH Removal Tool
01/06/01W95.HybrisF Fix Tool
12/22/00Fix W32.Funlove.4099 Tool (Cleanflc.exe)
12/22/00PrettyPark.Worm Removal Tool
12/22/00VBS.LoveLetter Fix
12/22/00VBS.Stages.A Fix
12/21/00Happy99.Worm Removal Tool
12/21/00W32.Navidad Fix
12/20/00Kak.Worm.B Fix
12/20/00W32.HLLW.QAZ.A Fix
12/20/00W32.Kriz Removal Tool
12/19/00BuddyList Removal Tool
12/15/00W95.MTX Fix Tool

Free Download Norton Antivirus Update

Free Download Norton Antivirus Update | for Windows 2000/XP/Vista/windows 7

If you have problem same me when i need to download the AntiVirus File Update so it hard to find a link for that ,Therefore i have build the short cut for you click this link that bring you to download screen about that. Link below.

Note:
You can choose the file name is yyyymmdd-XXX-x86.exe if you need to download first and the used that to other client.

http://www.symantec.com/business/security_response/definitions/download/detail.jsp?gid=n95



Supports the following versions of Symantec antivirus software:

* Norton AntiVirus 2005 for Windows 2000/XP Home/XP Pro
* Norton AntiVirus 2006 for Windows 2000/XP Home/XP Pro
* Norton AntiVirus 2007 for Windows XP Home/XP Pro/Vista
* Norton 360 version 1.0 for Windows XP/Vista
* Symantec AntiVirus 3.0 CacheFlow Security Gateway
* Symantec AntiVirus 3.0 for Inktomi Traffic Edge
* Symantec AntiVirus 3.0 for NetApp Filer/NetCache
* Symantec AntiVirus 10.0 Corporate Edition Client
* Symantec AntiVirus 10.1 Corporate Edition Client
* Symantec AntiVirus 10.2 Corporate Edition Client
* Symantec AntiVirus for Bluecoat Security Gateway for Windows 2000 Server/2003 Server
* Symantec AntiVirus for Clearswift MIMESweeper for Windows 2000 Server/2003 Server
* Symantec AntiVirus for Microsoft ISA Server for Windows 2000 Server/2003 Server
* Symantec Mail Security for Domino v 5.x
* Symantec Mail Security for Domino v 7.x
* Symantec Mail Security for Domino (32-bit) v 8.0.x
* Symantec Mail Security for Microsoft Exchange
* Symantec Mail Security for SMTP v 5.x
* Symantec Web Security 3.0 for Windows
* Symantec AntiVirus Scan Engine for Windows

How to create the schedule backup sql server 2005 ,2008

How to set auto schedule backup sql server 2005 | sql server 2008

Hi,Friend .I'd like to present about how to create the schedule database backup for sql server 2005,2008 so you can follow my step.

1. Log in the Sql server by user and then user should be have to grant the Admin permission.



2. Clikc on the Management Folder and then you will see the folder name is " maintenance plans " and then Right Click and choosed the "New maintenance Plan"



3. Key in the Job's Name follow by you want that and then Click OK.



4. Left Click and Drage the Back up Database Task to the working area.



5. You have seen the Back up Database Task from your Drag just now .




Click Here to See The Next Page

Thursday, November 4, 2010

How to Maintenance SQL Server 2005

วิธี การ Maintenance SQL Server 2005 | Maintenance SQL Server 2008 | SQL 2005 Express | Sql management Studio

How to SQL Server Maintenance
SQL Server statistics that are out of date and tables and indexes that are significantly
fragmented adversely affect system performance. You can monitor their condition and take
steps to enhance their performance.



Statistical Information


SQL Server uses statistical information about the distribution of values in a column to
determine the optimal strategy for evaluating a query. Distribution statistics help the
system estimate how efficient an index would be in retrieving data associated with a key
value or range specified in the query.
As the data in a column changes, index and column statistics can become out-of-date,
affecting query performance. The statistics should be refreshed anytime significant
numbers of changes to keys occur in the index.



You can use the dbcc show_statistics statement to generate a report on the distribution
statistics for an index. The statements in this section use the following syntax:
dbcc show_statistics (table_name, index_name)
In SQL Query Analyzer, with the application database selected as the current database,
the following statements show the current statistics and the last time statistics were
updated for primary keys in major tables:
dbcc show_statistics (item, pk_item)
dbcc show_statistics (customer, pk_customer)
dbcc show_statistics (ledger, pk_ledger)
dbcc show_statistics (matltran, pk_matltran)
dbcc show_statistics (matltran_amt, pk_matltran_amt)
dbcc show_statistics (journal, pk_journal)
dbcc show_statistics (ledger_all, pk_ledger_all)
The results indicate the selectivity of an index (the lower the density returned, the higher
the selectivity) and provide the basis for determining whether an index is useful in
optimizing queries.
See SQL Server Help for dbcc show_statistics and other DBCC (Database Console
Commands) statements.


Update Statistics

Use the Transact-SQL statement UPDATE STATISTICS if
• A process suddenly takes much longer than usual to run
• There is a significant change in the key values in an index



• A large amount of data in an indexed column has been added, changed, or removed,
or the table has been truncated using the TRUNCATE TABLE statement and then
repopulated.
We recommend that you update statistics nightly or weekly.
This example updates the statistics for all indexes on the customer table.
UPDATE STATISTICS customer
To update statistics for all tables in the in the current database, you can run the SQL Server
stored procedure sp_updatestats, which uses UPDATE STATISTICS:
EXEC sp_updatestats
For more information, see SQL Server Help for UPDATE STATISTICS and
sp_updatestats



Fragmentation Information

Fragmentation occurs through data modifications (INSERT, UPDATE, and DELETE). For
queries that scan part or all of a table, this fragmentation can cause additional pages to be
read, adversely affecting performance.
You can use the Transact-SQL DBCC SHOWCONTIG statement to display fragmentation
information for the data and indexes of a specified table.
To determine whether a table is heavily fragmented, use the following syntax in SQL Query
Analyzer, with the application database selected as the current database:
DBCC SHOWCONTIG (table_name)
In the result set, the value of Logical Scan Fragmentation gives an indication of the table's
fragmentation level. The value should be close to zero, although a value from 0% through
10% may be acceptable.
To show in a grid an abbreviated result set for every index on every table, use:
DBCC SHOWCONTIG WITH TABLERESULTS, FAST
To show the full result set for every index on every table, use:
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
For more information, see SQL Server Help for DBCC SHOWCONTIG.


Defragment Indexes

We recommend that you rebuild your table indexes on a weekly basis if possible.
The Transact-SQL DBCC INDEXDEFRAG statement defragments indexes of a specified
table, improving index-scanning performance.
DBCC INDEXDEFRAG (database_name, table_name, index_name)
The script below uses DBCC INDEXDEFRAG and DBCC SHOWCONTIG to defragment
all indexes in a database fragmented above a declared threshold of 30 percent. The script
is from Microsoft’s Transact-SQL Reference, copyright © 2004 Microsoft Corporation, One
Microsoft Way, Redmond, Washington 98052-6399 U.S.A.; all rights reserved.
Note that you must specify a database before you run the script.
/*Perform a 'USE ' to select the database in which to run
the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)



DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables



INTO @tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO


วิธี Maintenance SQL Server 2005

Tuesday, November 2, 2010

How to write command line | Script backup database sql Server 2005

How to write command line | Script backup database sql Server 2005

Start from create the BAT File first. Open the Notepad Program
Copy the Script below to Notepad Program and then you must to change on Highlight .


@ECHO OFF

@echo.
@echo +++ Begining Backup DB +++
sqlcmd -Slocalhost -Usa -Psa -i"bk_app.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed

:success
rem cls
@echo.
@echo.
@echo.
@echo.
echo +++ Database Backup successful +++
pause
exit

:err_bkp_failed
@echo Database Backup Failed
pause
===================================================================

Next Step,

Write the SQL Script , I have writed the script to control the part and naming of file so you must to change for highlight.


declare @ty char(4)
declare @tm char(2)
declare @td char(2)
declare @tbkname char(50)

set @ty = '';
set @tm = '';
set @td = '';

set @ty = convert(char,year(getdate()))
set @tm = convert(char,month(getdate()))
set @td = convert(char,day(getdate()))
set @tbkname = 'D:\SLBACKUP\KLK_DBBACKUP\' + @ty + rtrim(@tm) + @td + '_app.bak'
--print @tbkname
BACKUP DATABASE [databasename] TO DISK = @tbkname WITH NOFORMAT, NOINIT, NAME = N'databasename-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

And you don't forget to save the Last name of Sql Script to *.SQL but you develop on SQL Server the defult is *.sql .

when you need to used that you can duble click on BAT file and you can check the result on Backup Path and the peple usefull to BAT file backup for SQL 2005 Express becauase SQL Express it don't have Schedule Backup.




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

Aging and Shelf Life

after long time not posting.                   Aging and Shelf Life  on ERP Conceptual or general concept. here is so many confuse if you ...