How to Change the ‘sa’ password in SQL Server 2005,2008

0

14 years

by Ray Tawil in MS Sql Server

Method 1:

Login into SQL Server using Windows Authentication.

In Object Explorer, open Security folder, open Logins folder. Right Click on SA account and go to Properties.

Change SA password, and confirm it. Click OK.

Make sure to restart the SQL Server and all its services and test new password by log into system using SA login and new password.

Method 2:

1.Open the SQL Server express management studio
2.Connect to SQL Server using windows authentication

3.Right click the server name and choose properties

4.Go to security tab. Change server authentication to “SQL Server and Windows Authentication mode”

5.Click OK and restart SQL Server
6.Go to SQL Server studio management express
7.Expand the server and choose security and expand logins

8.Right click on SA, from properties modify the password and confirm password

Method 3: (From Command prompt)

1. Start a command prompt by typing Start – Run – cmd

2. Enter the following commands, pressing Enter after each line

OSQL -S yourservername -E
1> EXEC sp_password NULL, ‘yourpassword’, ’sa’
2> GO

Where yourservername is the name of your server and yourpassword is the new sa account password. Type exit twice to return to the server desktop.

Method 4: (Query) my personal favorite

Make a new query & write in it, then run.

Source code

USE [master]
 GO
 ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master],
 DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
 GO
 USE [master]
 GO
 ALTER LOGIN [sa] WITH PASSWORD=N’<insert_new_password_here>’ MUST_CHANGE
 GO

DateTime.ToString() Patterns

0

14 years

by Ray Tawil in Visual Basic.Net

All the patterns:

0 MM/dd/yyyy 08/22/2006
1 dddd, dd MMMM yyyy Tuesday, 22 August 2006
2 dddd, dd MMMM yyyy HH:mm Tuesday, 22 August 2006 06:30
3 dddd, dd MMMM yyyy hh:mm tt Tuesday, 22 August 2006 06:30 AM
4 dddd, dd MMMM yyyy H:mm Tuesday, 22 August 2006 6:30
5 dddd, dd MMMM yyyy h:mm tt Tuesday, 22 August 2006 6:30 AM
6 dddd, dd MMMM yyyy HH:mm:ss Tuesday, 22 August 2006 06:30:07
7 MM/dd/yyyy HH:mm 08/22/2006 06:30
8 MM/dd/yyyy hh:mm tt 08/22/2006 06:30 AM
9 MM/dd/yyyy H:mm 08/22/2006 6:30
10 MM/dd/yyyy h:mm tt 08/22/2006 6:30 AM
10 MM/dd/yyyy h:mm tt 08/22/2006 6:30 AM
10 MM/dd/yyyy h:mm tt 08/22/2006 6:30 AM
11 MM/dd/yyyy HH:mm:ss 08/22/2006 06:30:07
12 MMMM dd August 22
13 MMMM dd August 22
14 yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss.fffffffK 2006-08-22T06:30:07.7199222-04:00
15 yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss.fffffffK 2006-08-22T06:30:07.7199222-04:00
16 ddd, dd MMM yyyy HH’:’mm’:’ss ‘GMT’ Tue, 22 Aug 2006 06:30:07 GMT
17 ddd, dd MMM yyyy HH’:’mm’:’ss ‘GMT’ Tue, 22 Aug 2006 06:30:07 GMT
18 yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss 2006-08-22T06:30:07
19 HH:mm 06:30
20 hh:mm tt 06:30 AM
21 H:mm 6:30
22 h:mm tt 6:30 AM
23 HH:mm:ss 06:30:07
24 yyyy’-‘MM’-‘dd HH’:’mm’:’ss’Z’ 2006-08-22 06:30:07Z
25 dddd, dd MMMM yyyy HH:mm:ss Tuesday, 22 August 2006 06:30:07
26 yyyy MMMM 2006 August
27 yyyy MMMM 2006 August

The patterns for DateTime.ToString ( ‘d’ ) :

0 MM/dd/yyyy 08/22/2006
The patterns for DateTime.ToString ( ‘D’ ) :
0 dddd, dd MMMM yyyy Tuesday, 22 August 2006

The patterns for DateTime.ToString ( ‘f’ ) :

0 dddd, dd MMMM yyyy HH:mm Tuesday, 22 August 2006 06:30
1 dddd, dd MMMM yyyy hh:mm tt Tuesday, 22 August 2006 06:30 AM
2 dddd, dd MMMM yyyy H:mm Tuesday, 22 August 2006 6:30
3 dddd, dd MMMM yyyy h:mm tt Tuesday, 22 August 2006 6:30 AM
The patterns for DateTime.ToString ( ‘F’ ) :
0 dddd, dd MMMM yyyy HH:mm:ss Tuesday, 22 August 2006 06:30:07
The patterns for DateTime.ToString ( ‘g’ ) :
0 MM/dd/yyyy HH:mm 08/22/2006 06:30
1 MM/dd/yyyy hh:mm tt 08/22/2006 06:30 AM
2 MM/dd/yyyy H:mm 08/22/2006 6:30
3 MM/dd/yyyy h:mm tt 08/22/2006 6:30 AM
The patterns for DateTime.ToString ( ‘G’ ) :
0 MM/dd/yyyy HH:mm:ss 08/22/2006 06:30:07
The patterns for DateTime.ToString ( ‘m’ ) :
0 MMMM dd August 22
The patterns for DateTime.ToString ( ‘r’ ) :
0 ddd, dd MMM yyyy HH’:’mm’:’ss ‘GMT’ Tue, 22 Aug 2006 06:30:07 GMT
The patterns for DateTime.ToString ( ‘s’ ) :
0 yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss 2006-08-22T06:30:07

The patterns for DateTime.ToString ( ‘u’ ) :
0 yyyy’-‘MM’-‘dd HH’:’mm’:’ss’Z’ 2006-08-22 06:30:07Z
The patterns for DateTime.ToString ( ‘U’ ) :
0 dddd, dd MMMM yyyy HH:mm:ss Tuesday, 22 August 2006 06:30:07
The patterns for DateTime.ToString ( ‘y’ ) :
0 yyyy MMMM 2006 August
/ Represents the date separator defined in the current DateTimeFormatInfo..::.DateSeparator property. This separator is used to differentiate years, months, and days.
” Represents a quoted string (quotation mark). Displays the literal value of any string between two quotation marks (“). Your application should precede each quotation mark with an escape character (\).
‘ Represents a quoted string (apostrophe). Displays the literal value of any string between two apostrophe (‘) characters.
%c Represents the result associated with a c custom format specifier, when the custom date and time format string consists solely of that custom format specifier. That is, to use the d, f, F, h, m, s, t, y, z, H, or M custom format specifier by itself, the application should specify %d, %f, %F, %h, %m, %s, %t, %y, %z, %H, or %M. For more information about using a single format specifier, see Using Single Custom Format Specifiers.

Building a custom DateTime.ToString Patterns

The following details the meaning of each pattern character. Not the K and z character.
d Represents the day of the month as a number from 1 through 31. A single-digit day is formatted without a leading zero
dd Represents the day of the month as a number from 01 through 31. A single-digit day is formatted with a leading zero

ddd Represents the abbreviated name of the day of the week (Mon, Tues, Wed etc)
dddd Represents the full name of the day of the week (Monday, Tuesday etc)
h 12-hour clock hour (e.g. 7)
hh 12-hour clock, with a leading 0 (e.g. 07)
H 24-hour clock hour (e.g. 19)
HH 24-hour clock hour, with a leading 0 (e.g. 19)
m Minutes
mm Minutes with a leading zero
M Month number
MM Month number with leading zero
MMM Abbreviated Month Name (e.g. Dec)
MMMM Full month name (e.g. December)
s Seconds
ss Seconds with leading zero
t Abbreviated AM / PM (e.g. A or P)
tt AM / PM (e.g. AM or PM
y Year, no leading zero (e.g. 2001 would be 1)
yy Year, leadin zero (e.g. 2001 would be 01)
yyy Year, (e.g. 2001 would be 2001)
yyyy Year, (e.g. 2001 would be 2001)
K Represents the time zone information of a date and time value (e.g. +05:00)
z With DateTime values, represents the signed offset of the local operating system’s time zone from Coordinated Universal Time (UTC), measured in hours. (e.g. +6)
zz As z but with leadin zero (e.g. +06)
zzz With DateTime values, represents the signed offset of the local operating system’s time zone from UTC, measured in hours and minutes. (e.g. +06:00)
f Represents the most significant digit of the seconds fraction; that is, it represents the tenths of a second in a date and time value.
ff Represents the two most significant digits of the seconds fraction; that is, it represents the hundredths of a second in a date and time value.
fff Represents the three most significant digits of the seconds fraction; that is, it represents the milliseconds in a date and time value.
ffff Represents the four most significant digits of the seconds fraction; that is, it represents the ten thousandths of a second in a date and time value. While it is possible to display the ten thousandths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock’s resolution is approximately 10-15 milliseconds.
fffff Represents the five most significant digits of the seconds fraction; that is, it represents the hundred thousandths of a second in a date and time value. While it is possible to display the hundred thousandths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock’s resolution is approximately 10-15 milliseconds.
ffffff Represents the six most significant digits of the seconds fraction; that is, it represents the millionths of a second in a date and time value. While it is possible to display the millionths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock’s resolution is approximately 10-15 milliseconds.
fffffff Represents the seven most significant digits of the seconds fraction; that is, it represents the ten millionths of a second in a date and time value. While it is possible to display the ten millionths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock’s resolution is approximately 10-15 milliseconds.
F Represents the most significant digit of the seconds fraction; that is, it represents the tenths of a second in a date and time value. Nothing is displayed if the digit is zero.
: Represents the time separator defined in the current DateTimeFormatInfo..::.TimeSeparator property. This separator is used to differentiate hours, minutes, and seconds.

How to Enable Autologon for Windows Server 2008 Member Servers and Windows 7 Member Workstations

0

14 years

by Ray Tawil in MS Sql Server

autologon

Once you join a server to a domain, Windows will automatically delete the AutoAdminLogon value from the HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon registry key. This causes the userpasswords2 control to hide the “Users must enter a user name and password to use this computer” checkbox shown above.

Here’s how to get the missing checkbox back and configure Autologon:

Open a CMD prompt and enter the following (all on one line):

reg add “HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon” /v AutoAdminLogon /t REG_SZ /d “1” /f

Click Start, Run and enter control userpasswords2
Clear the checkbox for Users must enter a user name and password to use this computer and click OK
Enter the user name and password that will be used for Autologon and click OK

When the computer starts up the account you specified will be logged in automatically. Note that the password is encrypted on the computer.

This tip works for Windows 7, Windows Server 2008, and Windows Server 2008 R2.

autologon

Make a Textbox that accepts only numbers

3

14 years

by Ray Tawil in Visual Basic.Net

Source code

Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
Const pstr As String = "0123456789"
If pstr.IndexOf(e.KeyChar) = -1 Then
e.Handled = True
End If
End Sub

If you want to add “.” for exmaple to the combination you can change first line to

Source code

Const pstr As String = "0123456789"

Textbox that accepts only numbers

Prevent Adding Items to combobox

0

14 years

by Ray Tawil in Visual Basic.Net

Source code

Private Sub ComboBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
 e.KeyChar = ChrW(0)
End Sub

combobox Prevent Adding Items to combobox

Clearing all textboxes on a Form

0

14 years

by Ray Tawil in Visual Basic.Net

Do you have a form that has a lot of textboxes?

do you want to clear all those textboxes for example when there is a new?

this is the old scenario:

Source code

TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""

etc…………
this is my idea:

Source code

Sub ClearForm(ByVal frm_name As Form)
Dim Contrl As Control
For Each Contrl In frm_name.Controls
If (TypeOf Contrl Is TextBox) Then Contrl.Text = ""
Next Contrl
End Sub

this is my friend samir ibrahim‘s idea, compatible with “.net framework 3.5 +”

Source code

For Each _Ctrl As Control In Me.Controls.OfType(Of TextBox)()
_Ctrl.Text = ""
Next

The second idea is more optimized with same result.

Of course my idea is cooler because i made it

you just need to pass the form name to the function & it will clear the form for you, this will save your fingers extensive redundant typing

ClearForm clearing textbox textbox

Single Field to Multiple Rows

1

15 years

by Ray Tawil in Visual Basic.Net

You want your field from all in one field

(No column name)
———————–
Mike,Joe,Jerry,Ben

to become separate rows like below?

NAME
——–
Mike
Joe
Jerry
Ben

then use script below..

declare @toRowXML xml
SET @toRowXML = '<b><x>'+replace('Mike,Joe,Jerry,Ben',',','</x><x>')+'</x></b>'
SELECT @toRowXML
SELECT rtrim(ltrim(toRow.value('.','nvarchar(100)'))) as NAME
 from @toRowXML.nodes('/b/x') as t(toRow)

Random your data using Order by NEWID()

0

15 years

by Ray Tawil in MS Sql Server

This is not a new technology but I just would like to share or refresh your memory.

ORDER BY NEWID()

is used to randomly sorting query result.

it’s good if you would like to pick randomly few selected data for example : the lucky winner, the lucky customer, etc

if you want to random all of them then the query would look like this

SELECT Id FROM tableName
ORDER BY NEWID()

but imagine if you would only take one data from 500K rows you have. It would take quite some time. them don’t retrieve all of them

SELECT TOP N Id FROM tableName
ORDER BY NEWID()

How to Insert from other table that already exists

0

15 years

by Ray Tawil in MS Sql Server

The basic Insert statement is.

CREATE TABLE Customer (
ID int identity(1,1),
CustomerName varchar(30),
Address varchar(100),
Phone varchar (100)
)
INSERT INTO Customer (CustomerName, Address, Phone)

How about you want to put or just copy some of the data from other table to this Customer Table?

INSERT INTO Customer (CustomerName, Address, Phone)
SELECT CustomerName, Address, Phone From OldCustomer

In above query you’ll insert your customer table with data from OldCustomer table with ID less than 50 (0-49).

How about if you just want to create a replication of a table with data type?
On

SELECT * INTO newTable FROM OldTable

You don’t need to create the table first. Cause on select into statement the create table is already done then the insertion.

SELECT * INTO Customer FROM OldCustomer

How about if you just want to create a replication of some column in a table and columns’ data type?

SELECT CustomerName, Phone INTO Customer FROM OldCustomer

And if there are needs to use join or where clause just use it as you need it. 

example :

SELECT A.CustomerName, A.Phone, B.City INTO Customer
FROM OldCustomer A JOIN City B On A.CityID= B.CityID
WHERE City LIKE ':%'

INSERT INTO Customer (CustomerName, Address, Phone)
SELECT CustomerName, Address, Phone From OldCustomer A
JOIN City B On A.CityID= B.CityID
Where A.ID > 50
AND City LIKE 'L%'

Where ID > 50

values ('Jane', 'anywhere street', '9097655')

Coalesce another IsNull method

0

15 years

by Ray Tawil in Visual Basic.Net

COALESCE ( expression [ ,...n ] )

Coalesce is Equivalent with

CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2 … ELSE expressionN END
END

My little experiment with coalesce

DECLARE @i VARCHAR(20)
DECLARE @exp2
SET @exp2 = NULL

SET @i = null
SELECT COALESCE (@i + ‘ more;’, ‘It”s Null’) –result [It’s Null]
SELECT COALESCE (@i , ‘ more;’, ‘Stranger’) –result [ more;]
SELECT COALESCE (@i , @exp2, ‘Stranger’) –result [Stranger]

SET @i = ‘Something’
SELECT COALESCE (@i + ‘ more;’, ‘It”s Null’) –result [Something more;]
SELECT COALESCE (@i , ‘ more;’, ‘Stranger’) –result [Something]
SELECT COALESCE (@i , @exp2 , ‘Stranger’) –result [Something]

As you see if I use case and combine it with null function it’ll need more than one line. Coalesce is very useful and helpful in above case.

Method 1:

Method 2:

Method 3: (From Command prompt)

Method 4: (Query) my personal favorite

Blog Posts