Category: Configuration

MS Azure – Web App Service – Notes on creating a staging web front end on an existing Web App Service

At some point in developing sites for clients you will may want a satellite site feeding off the main database but with prototype features on it. Considering that certain levels of web app service come with free staging sites you may as well use these rather than either the free service which may be less well provisioned or a separate paid site that you will have to pay additional money for. The number of slots available will depend on the service you are on.

Beginners guide to setting up a staging slot

Once you have the site up and running you operate it in the same way as a normal service and so you can get the publishing details from the Get Publish Profile.

SQL Azure – Using Azure Automation and Runbooks to Schedule an Azure SQL Stored Procedure

Its vital to be able to run stored procedures on a schedule it can be extremely important for doing daily housekeeping.

This site was where I learned how to do this.
How to Schedule an Azure SQL Database’s Stored Procedure

Runbooks sit in Azure Automation and a Runbook consists of a script that performs a task.

Runbooks can be written in Python or Powershell. See the last part of this post for an important additional issue.

"Data Source=01server.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=01userid;Password=JK12jl423;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"

# Instantiate the connection to the SQL Database
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Data Source=servername.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=userid;Password=password;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"

$sqlConnection.Open()
Write-Output "Azure SQL database connection opened"

# Define the SQL command to run
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 120
$sqlCommand.Connection = $sqlConnection

Write-Output "Issuing command to run stored procedure"

# Execute the SQL command
$sqlCommand.CommandText= 'exec [dbo].[storedProcedure]'
$result = $sqlCommand.ExecuteNonQuery()
Write-Output "Stored procedure execution completed"

# Close the SQL connection
$sqlConnection.Close()

Write-Output "Run completed"

Honestly the hardest part is getting the code setting up the scheduling after that is relatively easy.

WARNING : Runbooks have dynamic IPs so although you can set grant access to an IP your runbook will only run for a certain period before it stops working unless you either have a Hybrid Runbook Worker setup OR you allow Azure services and resources to access this server (which isn’t recommended) The next post I will do is my initial notes on how to configure a Hybrid Runbook Worker.

Simple Bat File to open multiple Web Pages in One Browser Window with alternate Tabs

Simple but can be useful

Useful if you are wanting to open multiple tabs in a browser window at the same time

@echo off
start "Simple Search" "https://planning.westlothian.gov.uk/publicaccess/search.do?action=simple&searchType=Application"
start "Google Maps " "https://www.google.co.uk/maps/@55.8625775,-3.6759593,17z"
start "WLC Spade" ;
start "idoxEDRMS Login" "http://cc-dmsapp-01:8080/IDOXSoftware/secure/IG_Main?url="

Run Web Applications in Application Mode : Google Chrome

Turns out the Chrome browser has implemented a really nice display option specifically for websites that have aspects of an application which allows you to remove the clutter at the top of your browser window and makes a website appear in an application like window.

Go to the web page you are interesting in accessing via an application. In my case rounduptheusualsuspects.org

In the top right corner of the web browser next to the address bar you should see three vertical dots – select it to get the drop down menu and then select More Tools and then Create Shortcut.

You should now see the following dialog – ensure the Open as Window is ticked and then hit the Create Button. You can alter its name if you wish.

Now you get a nice icon on your desktop related to the site and what’s more when you open it up there is no address bar and within the taskbar you get the correct icon for the website see below example.

Compact Database automatically using this MS Access Function and VB Script

If like me you sometimes need to run multiple SQL statements in MS Access on a regular basis maybe at a particular time some of which are deletes, you will need to find a way to automate regular compacts. Here’s some code scavenged from the interweb that will allow you to do this.

Firstly create the following function in your target MS Access database.

Public Function CompactDatabase()
      Dim vStatusBar As Variant
      DoCmd.SetWarnings False
            
          If FileLen(CurrentDb.Name) > 2000000 Then
              Application.SetOption ("Auto Compact"), 1
              Application.SetOption "Show Status Bar", True
              vStatusBar = SysCmd(acSysCmdSetStatus, "The application will be compacted on close during compaction please do not interrupt")
          Else
              Application.SetOption ("Auto Compact"), 0
          End If
                
End Function

Next open up notepad paste in the following code save it as a txt file and then in explorer edit the suffix to vbs. Then simply double click on the file within explorer to run it. You should see the access database you have identified in the VB script open and then immediately close at which point it carries out the compact. The eagle eyed will have spotted that the above function doesn’t actually perform a compact. All it does is set the compact on close option to True within Access settings. The VB Script then instructs the database to close and Access compacts the database on exit.

set oAccess = createobject("Access.Application")

oAccess.OpenCurrentDatabase "C:Path\TargetDatabaseyouwantCompacted.mdb"
oAccess.visible = true
wscript.sleep 1000
oAccess.Run "CompactDatabase"
oAccess.closecurrentdatabase

set oAccess = nothing

A VB script such as this could be used to remotely run any MS Access function or functions – simply substitute the “CompactDatabase” parameter (or add further run commands) with the name(s) of the function(s) in the database you wish to trigger and then run the VB Script. Be warned the speed at which functions and commands within functions run may vary when called from a vbscript so you may have to use some experimentation to input things like pauses and waits if you start to get very imaginative with the functions run.

VBS – Pieces of code

Shutdown computer

Option Explicit
Dim oShell
Set oShell = Wscript.CreateObject("Wscript.Shell")
oShell.Run "SHUTDOWN -T 60 -S" 'wait 60 seconds before shutting down

Trigger speech

Option Explicit 
Dim speechobject
set speechobject=createobject("sapi.spvoice")
speechobject.speak "Your system is setup and ready for your day"

Delay for Seconds (10)

Option Explicit
Dim dteWait
dteWait = DateAdd("s", 10, Now())
Do Until (Now() > dteWait)
Loop

Sleep Function for Delay – Count in Milliseconds (5 mins below)

WScript.Sleep 5*60*1000

Using Task Scheduler and Visual Basic Scripts to automate pretty much anything Win 7

This is hardly ground breaking but windows Task Scheduler can be used to run VBS scripts on a variety of events to automate repetitive and boring tasks. Backups for example or refreshing caches on web browsers before users come in.

Hit Window button

Type “Task Scheduler”

Press return you should get the following screen.

Schedule1

Make sure Task Schedule Library is selected in the tree on the left then within Actions sub window on the right click Create

Schedule2

Give the Task a name in the area above marked with a red arrow and then go to the actions Tab where you can use the New… button to navigate to the vbs file you wish to run.

Schedule3

Next use the browse button OR if you know the path and file name simply type in the path to your visual basic script you would like to run. Here I have put in an imaginary backup script. Next go to the Trigger tab and again hit the New… tab

The Begin Task selection list gives a good indication of when you want to trigger whatever tasks.

Schedule4

If we continue with the theme and run a vbs script to do a backup at 11:48 everyday

Schedule5

Setting up a Blank SQL Server Spatially enabled Table using Microsoft SQL Server Management Studio 2008R2 Express and displaying it in QGIS 2.8.1

Programs used;

1- SQL Server 2008R2 Express
2- SQL Server Management Studio 2008R2 Express
3- QGIS

The example uses UK national grids coordinates to create a Triangle Polygon in a SQL Server Table

I’ve previously written that while we’ve had spatially enabled SQL Server for over 5 years I constantly come across line of business applications that although using SQL Server have not and do not intend to spatially enable the application. This is undoubtedly because of the difficulty in re-designing legacy systems actively in use and because the benefits although significant are not generally requested by all but the most knowledgable of colleagues.

While I understand this legacy system reasoning spatially enabled databases are the future so its just a matter of when and not if an application will require alteration. Understanding it in this context makes it really a requirement to start seriously planning for its inclusion.

Developerers creating new applications however should always consider spatially enabling relevant tables from the start even if it is not specked by the client/colleague. It being so much easier to spend a couple of minutes future proofing the schema of a new born database rather than hours trying to retrofit a live in production back end.

Firstly it’s important to understand what a geodatabase in SQL Server actually is.
Really it is a normal database which has one table that has a field that has a geometry or geography value type. In this example I will use desktop QGIS 2.8.1 to display the resulting geometry but any other digital mapping package that can link to SQL Server could be used. SQL Server also has a very rudimentary Mapping Display but you will need something better if you want to manipulate boundaries visually.

Many digital mapping products have plugins that will create Geodatabases and tables however I haven’t seen one for QGIS. I really wanted to be able to create spatial SQL tables on my own without recourse to paid tools directly in SQL Server Management Studio. So here’s my method of creating blank polygon table whose geometry is ready to be read and edited in QGIS or any other digital mapping system just using SQL Server Management Studio Express 08R2.

Steps
1. Create a new Table
2. Ensure the table has an identity Key that increments
3. Create a geometry column
4. Write a query that updates the geometry column

UPDATE T001Sites SET Coordinates=geometry::STGeomFromText(‘POLYGON((301804 675762,295789 663732,309581 664870,301804 675762))’,27700)

You will note that there are four coordinates here (each coordinate being a pair of numbers )
The first coordinate and last are the same this is required by SQL to ensure that the polygon is closed.

The 27700 number is the Spatial Reference System Identifier (SRID) – it is a unique value used to unambiguosly identify projecttion. 27700 is the unique identifier for the United Kingdom National Grid and represents the coordinates my example refer to. The spatial reference identification system is defined by the European Petroleum Survey Group (EPSG) standard which is a set of standards developmed for cartography surveying etc and owned by the Oil and Gas Producers Group list here; http://www.epsg-registry.org/

The above coordinates display a triangle in West Lothian near Edinburgh

5. Set up the connection to SQL Server Instance

Ensure the box marked “Only look in the geometry_columns metadata table” checkbox is unchecked. By default this is checked and if the geometry_columns table does not exist you will get an error message.

QGIS-SSMS-Connection

6. Display the table and edit as appropriate.

Select the table then hit the Add button

QGIS-SSMS-TableReadyforDisplay

And here is the SQL Server table in QGIS ready to be added to or edited.
QGISshowingSQLServerPolygon

Administering a Web Application some thoughts

In the last couple of years I have started administering a bought in Web Application from an outside vendor. I have found this really quite educational. I will not reveal the name of the product the vendor seems neither better nor worse than other vendors and many of the issues I am experiencing I see repeated with other admins of other web applications.

The software consists of various pieces of middleware linking SQL Server and the .Net and Javascript files. It uses a fairly standard Microsoft Stack to deliver a single page application. The big selling point was that that it was widely available through browsers pretty much anywhere on the web.

We have had it up and running in anger for a couple of years now and I have about 75 users all internal.

The vendor has created a new version and we now need to update. In discussion with the company they tell me that I should not be doing this myself and it is standard practice to get them in to upgrade the version. We already use it more as a stack technology to build our own applications I would want to continue this in the future. They have indicated that it will cost money to get them in to upgrade the software. This is triggering alarm bells because the existing configuration is very complicated, errors are quite regular and error messages tend to be pretty cryptic or non existent. I also like doing setup myself as it teaches me the ins and outs and speeds my learning up for systems which have non-trivial configuration.
A project board has been set up senior management are on board and well its grown legs arms and tails.

We have not widely publicised the application to the general public and they do not access the application at present.

The application itself requires significant amounts of configuration to set up correctly and parameters tend to be static and buried deep in very large HTML files. All this leads me to conclude that configuration in the new version is likely to be just as awkward. The company’s help desk is very good and there is extensive documentation but the sheer obscurity and quantity of configuration parameters makes the documentation the size of a phone directory, difficult and opaque.

Here’s some questions I should remind myself of when looking at New Web Applications in the future.
Are staff really so distributed that you require wide distribution?
Is it an internal or external distribution? – If not external do you really need a web enabled application.
Do you have internal expertise that is interested in developing applications?
With Web Applications or Local applications the backend database technology could well be the same. Internal local area development is likely to be far more flexible / cheap / quick and could motivate staff additionally it can be far more rich in its scope. It would be possible for instance to develop rich interface internally and a minimal interface externally using something like Lightswitch. This would take advantage of the strengths of both development environments.

There is a big push towards web applications I just think IT managers need to carefully consider their positions these applications tend not to be cheap and have a number of disadvantages that seem to have been pushed out of sight.

Start up VBS

OPTION EXPLICIT
 
Dim WshShell
Dim counter
Dim shell
Dim Outlook
Dim objWord
Dim objshell
 
'Flash keyboard to show code is being hit
 
Set WshShell = CreateObject( "WScript.Shell" )
counter = 0
While counter < 18
                WshShell.SendKeys "{NUMLOCK}"
                WScript.Sleep 200
                WshShell.SendKeys "{NUMLOCK}"
                WshShell.SendKeys "{CAPSLOCK}"
                WScript.Sleep 200
                WshShell.SendKeys "{CAPSLOCK}"
                WshShell.SendKeys "{SCROLLLOCK}"
                WScript.Sleep 200
                WshShell.SendKeys "{SCROLLLOCK}"
                WshShell.SendKeys "{CAPSLOCK}"
                WScript.Sleep 200
                WshShell.SendKeys "{CAPSLOCK}"
                WshShell.SendKeys "{CAPSLOCK}"
                counter = counter + 1
WEnd
 
'It is not possible to poll the state of the keyboard using VBS but strangely if you use vb through word therefore it is necessary to open word turn caps lock check on the status and if caps locks is on turn them off
 
Set objShell = CreateObject("WScript.Shell")
Set objWord = CreateObject( "Word.Application" )
 
if objWord.CapsLock <> 0 then
   ' turn capslock off
   objShell.SendKeys "{capslock}"
 
end if
 
if objWord.NumLock = 0 then
'turn numlock on
objShell.SendKeys "{numlock}"
 
end if
 
'This line required to delay Windows long enough to allow the computer to open word - if it trys to quite word before fully open you will get an error depending on your machine speed may be better to use the actual seconds
WScript.Sleep(100)
objWord.Quit
 
'Startup MS Outlook
 
CONST PATH_TO_OUTLOOK = """C:\Program Files (x86)\Microsoft Office\Office14\OUTLOOK.EXE"""
CONST SHOW_NORMAL = 3
CONST MINIMIZE = 1
 
SET shell = WScript.CreateObject("WScript.Shell")
 
' Open Outlook
shell.Run PATH_TO_OUTLOOK, 2
 
ON ERROR RESUME NEXT
 
' Grab a handle to the Outlook Application and minimize
SET outlook = WScript.CreateObject("Outlook.Application")
WScript.Sleep(100)
outlook.ActiveExplorer.WindowState = SHOW_NORMAL
 
' Loop on error to account for slow startup in which case the
' process and/or the main Outlook window is not available
WHILE Err.Number <> 0
  Err.Clear
  WScript.Sleep(100)
  SET outlook = NOTHING
  SET outlook = WScript.CreateObject("Outlook.Application")
WEND
 
ON ERROR GOTO 0
 
SET outlook = NOTHING
SET shell = NOTHING

The Handover – where the dragons lie

woman-see-dragons-everywhere-2

The handover – often someone figures out that something needs to be done – maybe they have realised an important omission or maybe there is a problem that only comes to light in a process when you start designing.

For whatever reason you can’t do the work yourself and you need to hand the work to someone else. This I consider to be a dangerous task the threat of information loss is immense. Here are some points I try to ensure to minimise this ever present risk.

Give it to an identifiable person – I always find it curious how top management consider it important that they know each other and feel comfortable in each others presence they carefully pick their colleagues and treat them with respect by ensuring accurate names are used and generally constantly talking to each other often informally and flexibly. However I have seen systems implemented by management which actively discourage these same forms of informal communication and actually discourage personal responsibility.

Give that person tasked responsibility for the whole task and allow them the flexibility to tackle it how they see fit. People adjust processes so that they are more productive and will use their personal skills in the workplace often when they were not hired on that basis. A regular example of this is probably individuals who can speak multiple languages. I have seen several situations where a customer from another nation arrives and someone in a team happens to speak their language in a flexible environment that person often gets involved in that task simply because good communication is the first step in tackling a problem. I was in the Premier Inn in London and I was pleased to note that the staff all had badges on them noting not just their names but the flags relating to languages they could speak. It was nice to see waiters with multiple flags on them. Gave you just a small bit of background on the person and encouraged customers of particular nationalities to seek out correct staff.

Give it to someone who is familiar with the domain (scope of work) complicated tasks need not just technical skill but domain specific skill. Most people know how a booking systems works as nearly everyone is familiar with booking things online. But would you expect a general programmer to be familiar with the process of registering births marriages and deaths or submitting tax returns for instance. If someone doesn’t have domain specific skills give them extra time.

Close physical proximity is a bonus – so called water cooler conversations are often about work even if it may stray into what might be considered gossip. For some even then gossip may inform you of particular tender subjects that allow you to better consider the respect of your colleagues and better conduct yourself in conversation to get the optimum of work between individuals. It also allows for constant review of process and answer of questions when they arise. I prefer in e-mails to ask single questions at a time. I find that if you store up questions in a massive e-mail the first two questions are asked and the others are just omitted. As far as I can tell this is a human trait. Its my experience that when I sit close to individuals I ask questions when they arise which when answered usually completely negate the need for further questions. This uses comparative advantage which is always a bonus.

Concentrate on the outcomes not the technology that they are using especially if there are no costs to something let them use what they want. Databases are databases – spreadsheets are spreadsheets – they prefer the dvorak keyboard to qwerty keyboard good for them. For some spreadsheets are better for them than databases others will always choose databases – Does it really matter. I have seen some really excellent spreadsheets for tasks that I would have preferred to use databases. You could argue one over the other for particular tasks and maybe even prove that one was better than the other on somekind of mathematical basis. But would SQL Server really be better for your granny than a spreadsheet. NO.

Ensure that the individual will have a continual link to the system post development – he should recognise that the stability and the continuation of the system is as important as simply pushing something out the door.

At least initially continually check up on the individual and ensure that things are progressing as intended. This requires time at the beginning but you should be able to wind it down after that.

Handovers are fraught with danger don’t make them be fraught with danger for you.

International Domain Names

I was helping to set up a domain for a friend the other day and I wondered whether they would like a domain in Cyrillics. Led to the question was that even possible.

I try to be aware and present but things pass me by sometimes. Particularly because I don’t really watch television. I have already posted about Cyrillics not being supported in some visual basic editors well turns out Cyrillics were not supported for URLs until 6th of May 2010.

It would be interesting to see the take up of Cyrillic URLs I note that the Cyrillic domain of Yandex url яндекс.рф re-directs to a latin version

One interesting url is the Russian President
президент.рф

Some quick research has revealed that DNS convention is old (no real surprise there) and only supports the 26 Latin characters A through Z numbers and the dash. When a non-latin based URL is placed in the address line the alphabet is re-encoded to a system called Punycode which is a way of representing a domain name with a non-DNS character set within the DNS character set. These domains are called International Domain Names (IDNs) If you wish to display IDNs properly within the address bar you need to go to Chrome settings and select the appropriate language relating to the character set of the IDN the url should appear correct otherwise you get a strange punycode translation that might be mistaken some weird non-base ten numerate system.

Doubtless Yandex did not consider this attractive as many peoples first reaction to that kind of URL would be to think that they had been re-directed to a dodgy website.

Access Connection Strings – Link

Some notes on using SQL Server connection strings both using the default ODBC client drivers and also the native SQL Server driver.

Juan Soto of Access Experts explains connection strings from MS Access to SQL Server

Additionally I am hearing that it is better to use the SQL Server Native Client ODBC driver than the default SQL Server driver as a recent security update on SQL Server has caused issue with some applications using the default SQL driver.

Juan Soto of Access Experts explains issues with November 2014 SQL Server update for MS Access applications

Dealing with Dates can still catch you out

The other day I was dealing with dates and I found a cracking little gotcha that might just be catching a few people out.

When is 07/11/14 not 07/11/14

When one is 17 November 2014 and one is 17 November 1914

I’ve been dealing with legal dates and I needed to set dates some 25 years in the future for payments of moneys. That’s fine just typing in dates I’ve been putting dates in which turn out to be a century in the past. This led me to investigate.

Within windows we get very used to typing in dates by using 6 digits – 01/01/14 or 09/05/15 – most users computers would recognise these dates as 1st of January 2014 and 9th September 2015. What many users may not consider , myself included , is that the interpretation of the year in this scenario is relative. That is relative to a century. We happen to be in the 21st century but if you type in 99 most computers will interpret the year as 1999 and not 2099. Fine but at what time does it change the century. Well turns out there is a configuration setting.

Here’s how you get to the configuration settings in Win 8.1

Control Panel
Clock, language and Region
Set the date and time…
Change date and time

Should get you to the following;

Win8ControlPanelDateTime

Then hit Change calendar settings;

Win8CustomizeDateFormat

The danger is of course that users will type in 07/11/30 thinking this will be 2030 when the two digit interpretation configuration will interpret it as 7 November 1930. How it interprets this comes down to the above setting. I believe at present this is the default setting.

Its one to watch out for and doubtless it may be catching some organisations out.

Graphics vs Text

khmerconSo today it is a bit slow for me so I answered a question from a lad in Phnom Phen in Stack Overflow. He was looking for support for Khmer in MS Access 2013, the official language of Cambodia. He has a database but unfortunately he can’t get records to sort alphabetically for Khmer text. I’m not sure I found a solution I made some suggestions. I’m always really interested in questions like that because a solution for him would be the kind of thing that would be useful to anyone able to write Khmer which is presumably millions. I had a look at the alphabet – wow – computer fonts seem to do a terrible job of displaying it reducing it to the point where at a similar scale to other fonts I need a magnifying glass to distinguish differing letters. I guess you just have to choose larger font sizes but it was tiny at the scales I was seeing examples. It made me wonder how much cultures with difficult or tricky languages are disadvantaged in day to day commerce. Obviously to me their alphabet is only pictures.

But then in reality that is only what all alphabets are.

I always think on this when people say we need more pictures and icons in the UI

Caspol.exe

Never heard of it?

I hadn’t either – it is however a small exe that stands for Code Access Security Policy Tool which enables users and administrators to modify security policy for the client machine to .NET framework.

This tool is automatically installed with .NET frameork.
The following alters privileges of websites to allow them access to the .net framework on the client machine.

The following is the full parameters associated with a .bat file that passes parameters to caspol.exe

C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\caspol -m -ag 1 -url http://servername/* FullTrust
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\caspol -m -ag 1 -url http://servername/* FullTrust
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\caspol -m -ag 1 -url http://servername/* FullTrust
C:\Windows\Microsoft.NET\Framework64\v2.0.50727\caspol -m -ag 1 -url http://servername/* FullTrust

40 Useful Facts to Know about a System

40

A list of points I should remember to ask when doing ongoing system development, total re-design or external purchase.

For alternative systems rank both existing and new and compare.
Collection of facts is as objective as I could make it – interpretations and consideration as ever is subjective.

I have my own self built project software that records most of these points. The users and user roles is particularly useful my home baked pm software is integrated into my contacts which means if individuals titles and contact details change then the details below are automatically updated.

1. Name of system
2. Version number
3. General Description of the system (written)
4. Date of Creation
5. Name of Creator along with contact details
6. Name of Administrator along with contact details (If different from Creator)
7. Name of the person responsible for backing up the system
8. Backup description
9.Names of existing users – should include administrators and creator
(Name – Organisation Position – e-mail – contact no – approx time used per day – Method of access – Role in system – Description of tasks carried out – Machine tag numbers if required)
10.Names of potential future users
(Name – Organisation Position – e-mail – contact no – approx time used per day – Method of access – Role in system – Description of tasks carried out – Machine tag numbers if required)
11. Operating system runs on (Windows / Linux / Mac / Wine) If Linux please indicate distro
12. Backend Database type (SQL Server, Oracle, MS Access, Filemaker Pro, Omnis, Fox Pro, DB2, MySQL, PostGres, PostGIS, VoltDB, MongoDB, NoSQL)
13. – unc name of database server(s)
14. – unc name of web server(s)
15. – Important directories on particular servers
16. – name of backend database
17. Has the application been designed specifically to allow for geo referencing ( This is not a question as to whether the back end is spatially enabled ) – (Yes / No)
18. Front End technologies – list
19. Main programming languages
20. Is this a client facing system (Yes / No)
21. Could it be a client facing system (Yes / No)
22. Do individuals presently access this from mobile phones (Yes / No)
23. Do individuals presently access this from tablet devices (Yes / No)
24. Is the information available to the general public if they make a freedom of information request. ( Yes / No )
25. Please indicate the information in the system that would not be distributed even if someone requested it. [Description]
26. Is there a statutory requirement to keep this information ( Yes / No)
27. Was the system originally internally or externally created (Internal / External)
28. Capital cost of original construction (if relevant)
29. Estimated annual revenue cost of system ( not interested in wages of individuals only interested in actual costs paid to outside bodies )
30. Can we get a table structure of the system complete with all field names and field types. (Yes / No) – Please attach if yes
31. Is the process unique to the organisation( Yes / No )
32. How often is the design of the system altered ( As and when required, Changes are batched and pushed through periodically, Only in exceptional circumstances, Never)
33. Who can request changes to the system ( Anyone , only management , there is a working group of disparate users who request changes to an outside company , pretty much no one)
34. If that is an outside company makes the changes how often is that done (monthly, yearly, very rarely, never)
35. Are users presently happy with the system ( Yes / No )
36. Are management presently happy with the system ( Yes / No )
37. Any obvious other systems it could be combined with (No – Yes please list)
38. How is performance (Very good, Good, Adequate, Poor, Very Poor)
39. Can you list other organisations that use the same software (No, list of organisations)
40. Lastly where do you consider the application to be strong and where weak and do you have any specific proposals that could significantly improve your interaction with the system. [description]

Connecting to SQL Server – authentication and QGIS

Within QGIS when you set up a connection to a MS SQL Server instance you are presented with two options. Here’s a bit of clarification on what the two options entail.

* Trusted connection – this is the same thing as using Windows Authentication and authentication is managed by the domain and authorization is handled by SQL Server – This could be handled by an Active Directory Security Group.

* Login – SQL Server can also use its own logins such as a user. These are both authenticated and authorized by SQL Server. They are only viable if SQL Server is configured to run in Mixed Authentication mode.

image001

Attaching Databases to SQL Server 08R2 Express

jigsaw2
It should be noted that the following although the easiest way to get a new database into an instance it should not be used in a production environment. In fact doing so may get you sacked. If experimenting though this method should be fine. If in doubt seek help as in a production environment you would want to look through all the code before attaching anything into an instance.

Go to SQL server management studio and on the Databases tree
Right click and select
Attach…..

Attach database window should appear which will allow you to use the Add… button to navigate to the
\Data\ subdirectory where all the sql server databases are held.

IMPORTANT – prior to loading a file in the database will have needed to have been DETACHED and you should always move anything mdf file that you are wanting to put into a database into the data subdirectory.
This ordinarily is done by going to database in question scrolling down to the database and right clicking on the database

Tasks > – Detach…

WARNING
If a database has not been detached properly it may NOT be possible to re-attach the database this is of course a security feature. So experimenting with simple moving files about will not work…

The listed code below does it at command line but the above works in SSMS

The default location for databases in SQL 08R2 Express is
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA

The default location for database in SQL 2012 is
C:\Program Files\Microsoft SQL Server\MSSQL11.[InstanceName\MSSQL\DATA