Stopping and Starting SQL Server Instance 08R2 Express

startstop

Simply within SSMS right click on the instance and hit STOP and
To start an instance if you are still in SSMS you can simply right click and hit start

Note if you stop a SQL Server instance from within SSMS AND then exit SSMS and try and go back in you will be denied as SSMS is unable to connect to a service that is not up and running.

In such a case to restart the instance you need to go to SQL server configuration manager and click on the instance and restart from there.
In Windows 8.1 you can get to config manager by using the search facility.

Windows 10 Alternative
Hit the search ring
type in services.msc
A new dialog should appea that will have Services(Local) with several columns – Name / Description / Status / StartupType
You want the Status to be Running
Use the mouse to highlight SQL Server (MSSQLServer) and then right click
Start

Your server should now be running

Allowing sa login and altering password SSMS SQL 08 R2 Express

password-security

Recently I installed SQL Server 08 R2 Express.

As part of the standard installation the sa login is disabled as default.

Clearly although this is a useful security feature it is a bit awkward if you are wanting to undertake certain tasks and as part of the installation this security feature is in no way obvious.

So what do you do to re-enable it?

Firstly log into SSMS under the machine windows login.

Go to the Security section of the server (not any of the databases) and expand the Logins branch. SA the system admin should be listed and if it is disabled it will have a small red down arrow next to it.

To re-enable highlight sa and right click

Select properties

Select Status

now alter the following
Permissions to connect to database engine
Set this to GRANT

Login
Set this to Enabled

Now highlight instance itself and right click and go to properties.
Within server properties highlight Security.
And change set server authentication to
SQL Server and Windows Authentication mode.

You should have sa login now enabled – don’t worry if sa still has a small red arrow next to it the SSMS client needs to be refreshed to see changes to set up.

If you want to change the password for the SSMS server I would recommend running the following.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = 'DifficultPass9£' ;
GO

Note you can enforce the requirement for a strong password within the same security section of the sa login if you require.

Attach DSN Less Connection to Link MS Access to SQL Server

link2

This code is generally available all over the tinternet nonetheless I list it here for my own personal use. I use an autoexec macro to trigger the code on open

With a Run Code action to trigger the AttachDSNLessTable…
So the code in the macro might look something like this
AttachDSNLessTable(“Table01Invoices”,”Table01Invoices”,”Server01″,”AccountingDatabase”,””,””)

Alternatively you could run it from the immediate window of the VBA module section.
Ctrl + G to get the immediate window up then create the function with the required parameters placing a question mark in front of the function eg
?AttachDSNLessTable(“Table01Invoices”,”Table01Invoices”,”Server01″,”AccountingDatabase”,””,””)

Pressing return will result in True result and when you go to the tables section Table01Invoices or your table should appear. Note if you have the tables section open of the database window then you will need to refresh.

Honestly works a treat and you can totally revolutionise processes if you are allowed to use the ease of front end design of something like MS Access with the scalability and power of SQL Server.

Needless to say vendors tend to be universally unwilling to give me details of their(/our!) backends.

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName:Name of the table
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
    
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
      
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
    
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function