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

0

15 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

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

0

15 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

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')

More about insert statement

0

15 years

by Ray Tawil in MS Sql Server

I’ve wandered in some forums I’ve found another way to insert multiple data.

before

INSERT INTO TableName (Col1, Col2)
VALUES (1, 'John');
INSERT INTO TableName (Col1, Col2)
VALUES (2, 'Mike');
INSERT INTO TableName (Col1, Col2)
VALUES (3, 'Jane');

another way I found

INSERT INTO TableName (Col1, Col2)
SELECT 1 , 'John'
UNION ALL
SELECT 2 , 'Mike'
UNION ALL
SELECT 3 , 'Jane'

--only in SQL Server 2008
INSERT INTO TableName (Col1, Col2)
VALUES (1, 'John'),
(2, 'Mike'),
(3, 'Jane')

insert Mutiple inserts in 1 statement

Find Character occurrence in String

0

15 years

by Ray Tawil in MS Sql Server

I made this query to answer a challenge given from a programming forum. After the query is done I realized that I’ve made a mistake. And it was quite a mistake. The challenge ask you to list all the character that occur in sequence. What I made is a query to list the most sequential occurrence character..

I know this query is far from perfect. But just let me share it with you.

this is the data source

Data
------------------------------------
9992EDC6-D117-4DEE-B410-4E5FAE46AE97
0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1
4A73E7EB-7777-4A04-9258-F1E75097977C
5AAF477C-274D-400D-9067-035968F33B19
725DA718-30D0-44A9-B36A-89F27CDFEEDE
8083ED5A-D3B9-4694-BB04-F0B09C588888
22244444-43B9-4694-BB04-F0B098888888

expected result :

Data				        pattern	len	pattern2	len2
----------------------------------------------------------------------------
9992EDC6-D117-4DEE-B410-4E5FAE46AE97	999	3	NULL		NULL
0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1	NULL	NULL	NULL		NULL
4A73E7EB-7777-4A04-9258-F1E75097977C	7777	4	NULL		NULL
5AAF477C-274D-400D-9067-035968F33B19	AA	2	NULL		NULL
725DA718-30D0-44A9-B36A-89F27CDFEEDE	44	2	NULL		NULL
8083ED5A-D3B9-4694-BB04-F0B09C588888	88888	5	NULL		NULL
22244444-43B9-4694-BB04-F0B098888888	8888888	7	NULL		NULL

DATA					Char	Pos	Len
------------------------------------------------------------
9992EDC6-D117-4DEE-B410-4E5FAE46AE97	9	1	3
0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1	NULL	NULL	NULL
4A73E7EB-7777-4A04-9258-F1E75097977C	7	10	4
5AAF477C-274D-400D-9067-035968F33B19	A	2	2
725DA718-30D0-44A9-B36A-89F27CDFEEDE	4	15	2
8083ED5A-D3B9-4694-BB04-F0B09C588888	8	32	5
22244444-43B9-4694-BB04-F0B098888888	8	30	7

My query

Create table #t (Data VARCHAR(40), pattern varchar(50), [len] int,
pattern2 varchar(50), len2 int )

INSERT #t (Data) SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97'
INSERT #t (Data) SELECT '0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1'
INSERT #t (Data) SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C'
INSERT #t (Data) SELECT '5AAF477C-274D-400D-9067-035968F33B19'
INSERT #t (Data) SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE'
INSERT #t (Data) SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888'
INSERT #t (Data) SELECT '22244444-43B9-4694-BB04-F0B098888888'

select * from #t

declare @pattern varchar(20)
set @pattern = null
declare @maxlen int
declare @e int
declare @i varchar(2)
declare @j varchar(2)

set @maxlen = (select MAX(len(data)) from #t)
print @maxlen

declare @q nvarchar(4000)

set @i = 1
set @j = 2

while (@i < 36)
 begin
 print @i

 set @q = '
 update  #t
 set pattern = right(isnull(pattern,''''),isnull(len(pattern),1)-1) +
               SUBSTRING(data, '+@i+', 1) + SUBSTRING(Data,'+@j+',1)
 where SUBSTRING(data, '+@i+', 1) = SUBSTRING(Data,'+@j+',1)
 and RIGHT(ISNULL(pattern, SUBSTRING(Data,'+@i+',1)),1) = SUBSTRING(Data,'+@i+',1)
 and isnull(substring(data, '+@i+'-[len]+1, 1),SUBSTRING(data, '+@i+', 1))
     =  SUBSTRING(data, '+@i+', 1)

 update  #t
 set pattern2 = right(isnull(pattern2,''''),isnull(len(pattern2),1)-1) +
                SUBSTRING(data, '+@i+', 1) + SUBSTRING(Data,'+@j+',1)
 where SUBSTRING(data, '+@i+', 1) = SUBSTRING(Data,'+@j+',1)
 and RIGHT(ISNULL(pattern2, SUBSTRING(Data,'+@i+',1)),1)
                              = SUBSTRING(Data,'+@i+',1)
 and isnull(substring(data, '+@j+'-[len2]+1, 1),
      SUBSTRING(data, '+@i+', 1)) =  SUBSTRING(data, '+@i+', 1)
 and [len] != 0

 update #t
 set [len] = len(pattern),
 len2 = len(pattern2)

 update #t
 set [pattern] = [pattern2],
 [len]= len2,
 pattern2 = NULL
 where len2 > [len]
 and SUBSTRING(data, '+@j+', 1) <> SUBSTRING(data, '+@j+'+1, 1)

 update #t
 set [pattern2] = NULL,
 [len2]= NULL
 where SUBSTRING(data, '+@j+', 1) <> SUBSTRING(data, '+@j+'+1, 1)
 '

 print @q
 exec SP_executesql @q
 set @i +=1
 set @j +=1

 end

select * from #t
select DATA, LEFT(pattern,1) AS 'Char',  PATINDEX('%'+pattern+'%', DATA) AS Pos, [Len]
 from #t

Basically what I am trying to do is compare the char with the following char. And Keep the pattern and if I found another sequential occurrence the most frequent will be recorded.

Character Find Character Occurence in String occurrence

Create temporary table in dynamic query

0

15 years

by Ray Tawil in MS Sql Server

there are cases when you are forced to create temporary table in dynamic query.

below is sample of creating temporary then select it in dynamic query.

declare @tableName varchar(30)
set @tableName = 'TableB'
declare @query nvarchar(4000)
set @query = 'CREATE TABLE #'+@tableName+'
                    ( id int,
                      data varchar(30)
                    )
              insert into #'+@tableName+' values(1, ''Me'')
              SELECT * from #'+@tableName
 exec sp_executesql @query

dynamic query temporary table

How to Connect to SQL Server Database from Visual FoxPro 9

12

15 years

by Ray Tawil in MS Sql Server

In Microsoft public newsgroups, I’ve noticed a recent increase in the number of questions that deal with how to connect from Visual Foxpro to SQL Server, and the problems related to making this connection. So I’ve decided to write this article to cover such an important topic.

There are two functions that can be used to establish a connection with the a remote SQL Server from Visual FoxPro:

SQLConnect()
SQLStringConnect()

The SQLConnect() Function

There are two ways to use the SQLConnect() function to connect to a remote data source, such as SQL Server. The first requires that you supply the name of a data source as defined in the ODBC Data Source Administrator applet of the Control Panel.

The following example creates a connection to a remote server using the ODBCNorthwind DSN:

LOCAL hConn

hConn = SQLConnect(“ODBCNorthwind”, “sa”, “”)

The second way to use SQLConnect() is to supply the name of a Visual FoxPro connection that was created using the create connection command. The CREATE CONNECTION command stores the metadata that Visual FoxPro needs to connect to a remote data source.

The following example creates a Visual FoxPro connection named Northwind and then connects to the database described by the connection:

LOCAL hConn

CREATE DATABASE cstemp

CREATE CONNECTION Northwind ;

DATASOURCE “ODBCNorthwind” ;

USERID “sa” ;

PASSWORD “”

hConn = SQLConnect(“Northwind”)

SQLStringConnect() Function

The other function that can be used to establish a connection to a remote data source, such as SQL Server, is SQLStringConnect(). Unlike SQLConnect(), SQLStringConnect() requires a single parameter, a string of semicolon-delimited options that describes the remote data source and optional connections settings.

The valid options are determined by the requirements of the ODBC driver. Specific requirements for each ODBC driver can be found in that ODBC driver’s documentation.

The following table lists some commonly used connection string options for SQL Server:

Option	Description
DSN	References an ODBC DSN.
Driver	Specifies the name of the ODBC driver to use.
Server	Specifies the name of the SQL Server to connect to.
UID	Specifies the login ID or username.
PWD	Specifies the password for the given login ID or username.
Database	Specifies the initial database to connect to.
APP	Specifies the name of the application making the connection.
WSID	The name of the workstation making the connection.
Trusted_Connection	Specifies whether the login is being validated by the Windows NT Domain.

Not all of the options listed in the above table have to be used for each connection.

For instance, if you specify the Trusted_Connection option and connect to SQL Server using NT Authentication, there is no reason to use the UID and PWD options since SQL Server would invariably ignore them. The following code demonstrates some examples of using SQLStringConnect().

Note: You can use the name of your server instead of the string.

SQL Server 2000 code example:

LOCAL hConn

hConn = SQLStringConnect(“Driver=SQL Server;Server=<SQL2000>;”+ ;

UID=sa;PWD=;Database=Northwind”)

hConn = SQLStringConnect(“DSN=ODBCNorthwind;UID=sa;PWD=;Database=Northwind”)

hConn =
SQLStringConnect(“DSN=ODBCNorthwind;Database=Northwind;Trusted_Connection=Yes”)

Handling Connection Errors

Both the SQLConnect() and SQLStringConnect() functions return a connection handle. If
the connection is established successfully, the handle will be a positive integer. If Visual FoxPro failed to make the connection, the handle will contain a negative integer. A simple
call to the AERROR() function can be used to retrieve the error number and message. The following example traps for a failed connection and displays the error number and message using the Visual FoxPro MESSAGEBOX() function.

Visual FoxPro returns error 1526 for all errors against a remote data source. The fifth element of the array returned by AERROR() contains the remote data source-specific error.

#define MB_OKBUTTON 0

#define MB_STOPSIGNICON 16

LOCAL hConn

hConn = SQLConnect(“ODBCNorthwind”, “falseuser”, “”)

IF (hConn < 0)

LOCAL ARRAY laError[1]

AERROR(laError)

MESSAGEBOX( ;

laError[2], ;

MB_OKBUTTON + MB_STOPSIGNICON, ;

“Error ” + TRANSFORM(laError[5]))

ENDIF

Disconnecting From SQL Server

It is very important that a connection be released when it is no longer needed by the application because connections consume valuable resources on the server, and the number of connections may be limited by licensing constraints.

You break the connection to the remote data source using the SQLDisconnect() function. SQLDisconnect() takes one parameter, the connection handle created by a call to either SQLConnect() or SQLStringConnect(). SQLDisconnect() returns a 1 if the connection was correctly terminated and a negative value if an error occurred.

The following example establishes a connection to SQL Server, and then drops the connection:

LOCAL hConn,lnResult

*hConn = SQLStringConnect(“Driver=SQL Server;Server=<SQL2000>;”+ ;

UID=sa;PWD=;Database=Northwind”)

hConn = SQLConnect(“ODBCNorthwind”, “sa”, “”)

IF (hConn > 0)

MESSAGEBOX(“Connection has done”)

lnResult = SQLDisconnect(hConn)

IF lnResult < 0

MESSAGEBOX(“Disconnect failed”)

ENDIF && lnResult < 0

ENDIF && hConn > 0

If the parameter supplied to SQLDisconnect() is not a valid connection handle, Visual FoxPro will return a run-time error (#1466). Currently there is no way to determine whether a connection handle is valid without attempting to use it.

To disconnect all SQL pass through connections, you can pass a value of zero to SQLDisconnect().

source: Sayed Geneidy

Connect to SQL Server Database from Visual FoxPro 9 sql server visual foxpro 9