Creating and Testing a Database in SQL Express
Author: jason
Date: 2007-05-03 20:21:58
Category: Technical

It is a fairly simple task to create a new database, tables, fields, and users in SQL Express. In this lesson we will use a VBscript file to ensure we can read from the database and table we create.

New Database
To open SQL Server Management Studio Express go to Start - All Programs - Microsoft SQL Server 2005
On the "Connect to Server" screen click Connect
If you are unable to connect ensure the authentication is setup correctly and that the SQL service is running




Right-click on Databases and click "New Database"




Enter the name "just_testing" for the database click OK




New Table
Expand the new database
Expand the Tables section
Right-click and select "New Table"




In the center screen you can enter the column names and data types you need
In the first column enter "id" for a "Column Name", select "int" for the "Data Type", and uncheck "Allow Nulls"
Scroll down in the "Column Properties", expand "Identity Specification", and change the drop down in "(Is Identity)" to Yes
Click in the empty row beneath "id"
Enter "name" for the "Column Name", leave the other settings the same
On the far right side set table name to "first_table"
Click the Save button at the top
Click the small X to close the table




Create new table row
Expand the "just_testing" database
Highlight Tables
On the right side right-click "first_table" and select Open Table




Click in the box under the "name" column and enter any name
Hit the Tab key. The "id" field should auto increment
Click the small X to close the pane




New User
Expand Security and Logons
Right-click and select "New Login"
Enter the login name of "sqluser"
Select "SQL Server Authentication"
Enter a password of "sqlpassword"
Uncheck "Enforce Password Policy"
Change the default database to the newly created database "just_testing"




Click on User Mapping
Check the "Map" for the new database "just_testing"
At the bottom check "db_datareader"
Click OK




Testing the database
On the desktop make a new text file and name it "db.vbs"
Copy the below code into the new file and save it
Make sure the extension is ".vbs"
Code

Dim adoConn
Dim rsGetInfo

Set adoConn = CreateObject("ADODB.Connection")
adoConn.Open("Driver={SQL Server};Server=127.0.0.1;Database=just_testing;Uid=sqluser;Pwd=sqlpassword;")

' Create an ADO recordset object
Set rsGetInfo = Wscript.CreateObject("ADODB.Recordset")

' Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM first_table"

' Open the recordset with the SQL query
'rsGetInfo.Open strSQL, adoConn

Set rsGetInfo = adoConn.Execute (strSQL)

While Not rsGetInfo.EOF
Wscript.Echo rsGetInfo("id") & ", " & rsGetInfo("Name")
rsGetInfo.MoveNext
Wend

rsGetInfo.Close
adoConn.Close


Double-click the db.vbs file and you should be rewarded with the name you entered earlier




References
Microsoft SQL Express Homepage
SQL Express Overview
SQL Queries
SQL Express Downloads
SQL Express System Requirements



jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009