ADODB Connection string for LocalDB and SQL Server Native Client

The limit on Access MDB file size is 2GB. To get around that I wanted to try out the lightweight LocalDB server, rather than SQLExpress.
SqlLocalDB.msi is 38MB
sqlncli.msi is 3MB for win32, 5MB for win64

It took a while to get the right connection string, so to save others the wasted time, here’s what I found:

http://msdn.microsoft.com/en-us/library/hh510202.aspx
SQL Server 2014 Express LocalDB
says under “Connecting to the Automatic Instance”:

The easiest way to use LocalDB is to connect to the automatic instance owned by the current user by using the connection string “Server=(localdb)\MSSQLLocalDB;Integrated Security=true”. To connect to a specific database by using the file name, connect using a connection string similar to “Server=(LocalDB)\MSSQLLocalDB; Integrated Security=true ;AttachDbFileName=D:\Data\MyDB1.mdf”.
Note
The first time a user on a computer tries to connect to LocalDB, the automatic instance must be both created and started. The extra time for the instance to be created can cause the connection attempt to fail with a timeout message. When this happens, wait a few seconds to let the creation process complete, and then connect again.

The string uses “Integrated Security=true;”, but as you can see below that failed for me.

You will see other results in google using
Server=(localdb)\v11.0;
or
Server=(localdb)\v12.0;
and you can check the version at a command prompt;
C:\> sqllocaldb v
Microsoft SQL Server 2014 (12.0.2000.8)

but MS have now gone to a version independent name
Server=(localdb)\MSSQLLocalDB;
This is not yet mentioned on
https://www.connectionstrings.com/sql-server/

As I am connecting from Excel using ADODB, my connection string will also need a Provider, which is not mentioned in the MSDN article above.

I use the SQL Server Native Client :
http://msdn.microsoft.com/en-us/library/ms131321.aspx
you can download SQLNCLI11 directly from
http://go.microsoft.com/fwlink/?LinkID=239648&clcid=0x409

I initially used “Provider=SQLNCLI;…” but got an error 3706
Provider cannot be found. It may not be properly installed.
The windows script I posted earlier gives a list of what providers are already installed on your PC.
so I find I need to use the version number in this case:
“Provider=SQLNCLI11;…”
If you want to check if a specific ADO provider is installed, check it in registry at path HKEY_CLASSES_ROOT\[Provider_Name] which gives you its CLSID.

Another potential source of confusion is the use of equivalent names in the connection string
eg Trusted_Connection=true is ODBC and Integrated Security=SSPI is OLEDB.

http://stackoverflow.com/questions/3077412/what-is-the-difference-between-trusted-connection-and-integrated-security-in-a-c
gives a handy table showing that for example, these are the same:

    +----------------------+-------------------------+
    | Value                | Synonym                 |
    +----------------------+-------------------------+
    | extended properties  | attachdbfilename        |
    | timeout              | connect timeout         |
    | server               | data source             |
    | database             | initial catalog         |
    | trusted_connection   | integrated security     |
    +----------------------+-------------------------+

Error messages

Here I used the wrong version, 11 instead of 12:
“Provider=SQLNCLI11;Server=(localdb)\v11.0;Integrated Security=SSPI;”
-2147467259 SQL Server Network Interfaces: Cannot create an automatic instance.

This is correct; if you get a timeout error, simply retry:
“Provider=SQLNCLI11;Server=(localdb)\v12.0;Integrated Security=SSPI;”
-2147467259 Unable to complete login process due to delay in opening server connection
I got a timeout error on my PC after 20 seconds so now add “timeout=30;” to the string
I imagine that time might improve with Windows prefetch.

This is the version-independent string:
“Provider=SQLNCLI11;Server=(localdb)\MSSQLLocalDB;Integrated Security=SSPI;”

The MS article uses “Integrated Security=true;”, but that failed for me:
“Provider=SQLNCLI11;Server=(localdb)\MSSQLLocalDB;Integrated Security=true;”
-2147217887 Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

http://www.connectionstrings.com/sql-server-native-client-11-0-oledb-provider/
confirms that it should be “Trusted_Connection=yes;”

This is what I finally used:
“Provider=SQLNCLI11;Server=(localdb)\MSSQLLocalDB;Trusted_Connection=yes;timeout=30;”
I am posting this here to provide a google hit for that string, in the hope this helps someone.

Patrick

Posted in Excel/VBA | Tagged , , , , , , , | 3 Comments

VBScript to list installed OLEDB Providers

I found that the script I got by googling for that title failed on the line:
CreateObject(“RegEdit.Server”)
Error: ActiveX component can’t create object: ‘RegEdit.Server’

So here’s my own VBScript :


'List of installed OLEDB providers on local computer
Option Explicit
Const HKEY_CLASSES_ROOT = &H80000000

Dim OutText, Key, strComputer, objRegistry, arrKeys
Dim strKeyPath, strValueName, strValue, uValue

strComputer = "."

Set objRegistry = GetObject("winmgmts:\\" & strComputer & "\root\default:StdRegProv")

objRegistry.enumKey HKEY_CLASSES_ROOT, "CLSID", arrKeys
Wscript.Echo "Number of Keys to be searched: " & 1+ UBound(arrKeys) & vbCrLf & "Click OK and wait a minute" ' 7645

OutText=""
for each key in arrKeys
strKeyPath = "CLSID\" & key
strValueName = "OLEDB_SERVICES"
if objRegistry.GetDWordValue (HKEY_CLASSES_ROOT,strKeyPath,strValueName,uValue)=0 then ' that value name exists
'get the (Default) value which is the name of the provider
objRegistry.GetStringValue HKEY_CLASSES_ROOT,strKeyPath,"",strValue
OutText=OutText & strValue & vbcrlf
' and the expanded description
objRegistry.GetStringValue HKEY_CLASSES_ROOT,strKeyPath & "\OLE DB Provider","",strValue
OutText=OutText & " " & strValue & vbcrlf
end if
next
Wscript.Echo OutText

'Windows Script Host
'---------------------------
'SQLOLEDB
' Microsoft OLE DB Provider for SQL Server
'
'Microsoft.ACE.OLEDB.12.0
' Microsoft Office 12.0 Access Database Engine OLE DB Provider
'
'Microsoft.ACE.OLEDB.15.0
' Microsoft Office 15.0 Access Database Engine OLE DB Provider
'
'SQLNCLI10
' SQL Server Native Client 10.0
'

Posted in Productivity | Tagged , , , , , | 2 Comments

Dumb email bounces

A spammer is sending mail to Polish addresses faking an address from one of the domains I administer.
Nothing unusual in that. What’s more annoying is all the recipient postmasters replying to the APPARENT from-address, ie my address, saying the mail is spam.
At least one of them is smart enough to acknowledge :

The message carried your return address, so it was either a genuine mail
from you, or a sender address was faked and your e-mail address abused
by third party, in which case we apologize for undesired notification.

What’s more, some will even provide a trace to the sender IP address, often in .cn, .kz, .ir, etc.
But if they know that why spam me with bounces? Really, guys, think a bit further.

Posted in Uncategorized | Tagged , , , , , | Leave a comment

Irish Computer Society evening talk on Spreadsheet Controls

My talk on “Spreadsheet Management and Controls” is next month at the ICS.
Monday 18:00 – 19:30 9th Jun

This session outlines current concerns about risks from the uncontrolled use of spreadsheet-based information systems.
The session will cover how to assess risk in the context of spreadsheet criticality, and a process of high level and detailed reviews. Good practices are presented for error detection, correction, and prevention.

ICS further info and booking

Free to ICS members, 25 euro otherwise.

Posted in Excel/VBA, Risk, Software Quality, Software Testing | Tagged , , , | Leave a comment

The Art of the Cell

I liked this post on the Excel-L list today.

On 07/05/2014 10:21, Laurent Wright wrote:
Reminds me of a question, many years ago, that I got asked.
In Layman’s terms, what is the difference between Access and Excel?
My reply was Access is Meccano and Excel is Lego
With Meccano you are stuck into building rigid and 'structured Models', 
with Lego, you can pretty much build what you like.

On 07/05/2014 10:34, John Nurick wrote:

The other relevant difference is that once you’ve built Meccano it stays built until someone comes along with the skill to use a screwdriver and spanner and, hopefully, the insight to put it back together better. But a Lego model can be spoilt by any passing toddler or puppy.

Posted in Uncategorized | Tagged , , | Leave a comment

Eusprig 2014 Delft – For Excel Builders and Inspectors

Thursday 3 July 2014, Delft, the Netherlands. And it’s FREE thanks to our sponsors!

This is for the two sides of the house – those who use spreadsheets to solve their problems, and those who are concerned about the risks introduced by the use of spreadsheets.

Unlike the serious keynotes of previous years, this year begins with, of all things, Excel and Music. It should start the day for us in good humour!

Both the Eusprig day and the SEMS workshop on the 2nd feature Ray Panko on spreadsheet error detection and auditing.

There is also a short demo session on the Thursday where everyone who wants to can present a little demo of their software.

And if that’s not enough, check out the Amsterdam Excel Summit on May 14th.
http://topexcelclass.com/blog/?page_id=26

Delft July 2014 – Spreadsheet Quality Days

http://spreadsheetlab.org/2014/03/31/program-eusprig-2014-announced/

Thursday July 3rd
Time Room A: Plenary and Risk track Room B: Building track
9:00 Opening
9:30 SheetMusic – Thomas Levine
10:00 Financial optimization and long term control of complex real estate projects – Jaap Labrie(sponsored)
10:30 Coffee break
11:00 Spreadsheet development for FP&A : Evolution of a 360 degree approach – Paula Jennings Zero Effort Excel Models – Henk Vlootman
11:30 Exploring the limits of Excel – Eloy Caballero Tony De Jonker – Scenario Tools in Excel
12:00 Mapping of the financial modeling ecosystem in large scale infrastructure projects – Haydn Palliser, Rickard Warnelid Bjoern Stiel – SpreadGit
12:30 Lunch
13:30 The FAST standard – Emily Cowan 13:30 Using XML for data storage and –validation in Excel apps: a plea – Frans Bus
14:00 Spreadsheet Model error experiences – Jack Avon Spreadsheet vs ERP – where does the balance lie? – John Kidd
14:30 Demo track 
15:00 Coffee break
15:30 Spreadsheet Inspection (Auditing): Why, How, and How Much? – Ray Panko
16:00 What’s new in Excel 2013 – Steve Kraynak, Leif Brenne
16:45 Closing and drinks

The academics get a special day of their own, July 2nd:
http://spreadsheetlab.org/2013/12/19/first-workshop-on-software-engineering-methods-in-spreadsheets/

http://spreadsheetlab.org/2014/04/10/sems-accepted-papers-published/
SEMS 14:

•Birgit Hofer, Dietmar Jannach, Thomas Schmitz, Kostyantyn Shchekotykhin and Franz Wotawa. Tool-supported fault localization in spreadsheets: Limitations of current research practice
•Dietmar Jannach, Thomas Schmitz and Kostyantyn Shchekotykhin. Toward Interactive Spreadsheet Debugging
•Raymond Panko. Improving Methods in Spreadsheet Error Research
•Richard Paige, Dimitris Kolovos and Nicholas Matragkas. Spreadsheets are models too
•Birgit Hofer and Franz Wotawa. On the Usage of Dependency-based Models for Spreadsheet Debugging
•Daniel Kulesz. A Spreadsheet Cell-Type Model for Testing
•Pablo Palma. SBBRENG: Spreadsheet Based Business Rule Engine
•Peter Sestoft. End-user development via sheet-defined functions
•Sohon Roy and Felienne Hermans. Dependence Tracing Techniques for Spreadsheets: An Investigation
•Jácome Cunha, Joao Fernandes, Jorge Mendes, Rui Pereira and João Saraiva. MDSheet – Model-Driven Spreadsheets
•Thomas Levine. Automatically assessing the relevance, quality, and usability of spreadsheets
•Thomas Levine. Sheetmusic: Making music from spreadsheets
•Raymond Panko. Are We Overconfident in Our Understanding of Overconfidence?
•Joeri van Veen and Felienne Hermans. Anonymizing Spreadsheet Data and Metadata with AnonymousXL
•Bas Jansen and Felienne Hermans. Using a Visual Language to Create Better Spreadsheets

 

Posted in Uncategorized | Tagged , , , , , , , | 9 Comments

Dumb autoresponders

I went on holidays before Christmas and set up an email autoresponder to say I was out of the office.
So I left and next day decided to use the free wifi to check the email. Not something one should normally do on holidays, but I was curious. I found about a thousand repeated emails from noreply@eu-supply.com which had sent out an email at 02:49 and received an autoresponse to which it replied “this email address is not monitored” and to which it received an autoresponse … etc.
After 1400 of these I got my ISP to turn off the autoresponder for me (thanks, Blacknight support people).
I would have thought this would be a well known issue that any email system would handle.
I would have set up the autoresponder to not reply to any messages with “noreply” in the sender, but that could not be done.
eu-supply.com said they would consider a future feature to detect email loops.
Has any else had that problem and how did you resolve it?
According to wikipedia:

http://en.wikipedia.org/wiki/Email_loop
Prevention
The mail system should retain headers of incoming email while performing any type of auto-forwarding operation.
Auto Responder: Do not send more than ‘x’ replies to the same sender.

Posted in Uncategorized | Tagged , | Leave a comment

Notes from SoftTest “Kick Starting Automated Testing”

Belfast 19 Sep

Paul Gerrard’s talk was a call to review requirements.
He distinguished definitions of Check vs Test.
In Safety tests, they speak of a check for the part being “in good working order”
Look up Behaviour Driven Devt – a requirements driven structured language eg Cucumber, and DefoSpam.
You don’t want to end up with a long, brittle chain of unmaintainable test scripts.
Alternative: robot framework, write your own interface
See BusinessStoryMethod.com, SP.QA free story platform

John McHale, Storm tech, presented Microsoft ALM, VS Ultimate 2012
MS Test Center for manual testing with recording and playback.
Test Impact: What LOC were tested, to see what tests might be impacted by a change in code.

Dorothy Graham DorothyGraham.co.uk and on blogspot.com
Efficiency of finding bugs:
Regression testing 9%
Manual testing 28%
Exploratory testing 58%

See BCS Grove report “Experience-driven Test automation”
High maintenance cost of testware leads to abandonment. What happens to all your scripts if you change tools?
Solution: Separate tests from scripts. (I am reminded of the saying that there is no problem in computer science that cannot be solved by another level of indirection / interface)
Tools don’t replace testers, they support them.
Tester don’t need to learn coding: lose a good tester and gain a poor programmer.
Test automation is a developer’s job.
Deadline pressure usually pushes you back into manual testing.

David Evans, Neuri:
Attitude of learning from mistakes
Testing is never finished, merely stopped; defend your stopping heuristics.
Choosing which features to test = What do we have to let out that we assume is not working?
A bug report does not necessarily cause a bug fix to be added to a work queue.
Every bug (discovered in exploratory testing) is evidence of a missing (unit) test.
Value of a test is speed – timeliness to take action.
He is not fond of end-to-end testing – but I think a counter-example might be the Wobbly Bridge.
Transport analogy: the speed of the bus is never the point, but the service. So, neither is the speed of testing (goes against value argument?)
“Testing is slowing down development” –> Slowing the production of non-functioning code.
Coverage: lines of code are not paths.

Posted in Software Testing | Tagged | Leave a comment

Kick Starting Automated Testing – 19 Sep programme

SoftTest Ireland Inaugural Conference
19th Sept 2013, The Holiday Inn, Ormeau Avenue, Belfast.
Booking: http://www.eventbrite.co.uk/event/6793478477/eorgf

Programme
Sessions Time Main room (Titanic 1&2) Syrian Venetian
0830 – 0900 Registration and Coffee
0900 – 0915 Opening remarks Brian Lambert – Chair, Softest
0915 – 1015 Paul Gerrard, How to Eliminate Manual Test Lab Feature Checking
1015 – 1115 Tom Roden, Building a lean, mean, BDD test automation machine
John MacHale,Borland Test Lab Easily created automated tests from manual test runs using Microsoft’s ALM tool suite
1115 – 1145 Break – Coffee Exhibition and Test Lab
1145 – 1245 Dorothy Graham Borland Intelligent Mistakes in Test Automation
1245 – 1400 Lunch Demonstration by SQS
1400 – 1500 David Evans, What testers & developers can learn from each other
John MacHale Borland Test Lab Easily created automated tests from manual test runs using Microsoft’s ALM tool suite
1500 – 1530 Break – Coffee Exhibition and Test Lab
1530 – 1630 Graham Abell ios-driver – Automate your iOS applications
1630 – 1715 Plenary Session / Q&A All speakers
1715 – 1730 Closing Remarks Brian Lambert – Chair, Softest
Format – There will be talks and demonstrations taking part in the Syrian and Venetian Rooms during the running of the main speaker programme and during lunch. Delegates are free to choose which session they would like to attend.
To join our group simply subscribe via our website http://www.softtest.ie

Booking: http://www.eventbrite.co.uk/event/6793478477/eorgf

THE IRISH SOFTWARE TESTING SPECIAL INTEREST GROUP

SPEAKERS

Paul Gerrard
Paul Gerrard is a consultant, teacher, author, webmaster, developer, tester, conference speaker, rowing coach and a publisher. He has conducted consulting assignments in all aspects of software testing and quality assurance, specialising in test assurance. He has presented keynote talks and tutorials at testing conferences across Europe, the USA, Australia, South Africa and occasionally won awards for them. Educated at the universities of Oxford and Imperial College London, in 2010, Paul won the Eurostar European Testing excellence Award. In 2012, with Susan Windsor, Paul recently co-authored “The Business Story Pocketbook”. Paul is Principal of Gerrard Consulting Limited and is the host of the UK Test Management Forum.

Tom Roden
Tom Roden is the Head of Agile Services at SQS UK where he works as a coach, tester, scrum master, trainer, consultant and general quality enthusiast. Tom has spent the many years as a consultant working with project teams, supporting on-going improvement and transition through process and practice refinement, influenced by agile and lean principles. With a background in testing and test management he specialises as a test and quality coach, setting strategies for testing and working to empower teams to harness the benefits of both exploratory testing and scripted automated testing using test driven practices such as specification by example. Tom has presented at software quality and testing events across the UK and internationally.

Dorothy Graham
Dorothy Graham has been in software testing for 40 years, and is co-author of 4 books: Software Inspection, Software Test Automation, Foundations of Software Testing and Experiences of Test Automation. She is currently working on a new book and wiki with Seretta Gamba on Test Automation Patterns. Dot was programme chair for two EuroSTAR conferences and is a regular speaker at testing conferences worldwide. She has been on the boards of conferences and publications in software testing. She was a founder member of the ISEB Software Testing Board and was a member of the working party that developed the ISTQB Foundation Syllabus. She was awarded the European Excellence Award in Software Testing in 1999 and the first ISTQB Testing Excellence Award in 2012.

David Evans
David Evans is partner at Neuri Consulting. He is a veteran of agile testing and an active member of the agile community, with over 22 years of IT experience. A thought-leader in the field of agile testing, he has trained and provided consulting for clients in the UK, Ireland, Sweden, Germany, Australia, Israel, South Africa and Singapore. David is a regular speaker at international conferences and has had a number of articles published in professional journals.

Graham Abell
Graham is Technical Test Lead at Paddy Power. He has 8 years’ experience in the Financial sector, having worked in Insurance, Poker, Banking and Sports Betting. Graham has worked with many test frameworks – commercial (HP’s QTP, Microsoft’s Coded UI, Froglogic’s Squish) and open source (Selenium, jBehave) In this time he has used Java, Python, C#, Objective C and VBScript. Graham is a core contributor to the ios-driver tool, which allows automation on iOS devices using the Selenium Wire Protocol. In his spare time he cooks pizzas, mills wood and is building a 3D printer to create robots.

We would like to thank all our speakers and contributors to today’s event for
giving freely of their time at this not for profit event.

Booking: http://www.eventbrite.co.uk/event/6793478477/eorgf

Soft Test Ireland would like to thank our sponsors Borland, HP, Inter Trade
Ireland, Sogeti, ISA Software Skill- net, and SQS for their continued support.

Posted in Software Testing | Tagged , , | Leave a comment

Conference : Kick Starting Test Automation, Belfast

SoftTest Ireland, the community for software testers in Ireland, is pleased to announce its inaugural whole day event.
The focus of this conference is Automated Testing and will provide a platform for delegates to learn strategies, techniques and practices from peers and leaders in the field. So come along and join us for the day!

http://softtest.ie/softtest-one-day-conference-kick-starting-automated-testing-19th-september-belfast/

You are invited to attend:
Kick Starting Test Automation, Belfast
Kindly Sponsored by Borland, HP, InterTradeIreland, ISA Software Skillnet, Sogeti, and SQS

Thursday 19th September

There will be a range of interesting presentations and workshops including our keynote speakers below:

Dorothy Graham: Intelligent Mistakes in Automation

David Evans: What Testers and Developers Can Learn From Each Other

Paul Gerrard: How to Eliminate Manual Feature Checking

Participants Include: Sogeti, SQS, Borland, Microsoft and HP.

To Book see: http://www.eventbrite.co.uk/org/822579027

Delegate Rate £55 – Early Bird Rate – £30 (Before 16th August)
Holiday Inn,
Ormeau Avenue,
Belfast

Posted in Software Testing | Tagged , , , , | Leave a comment