Undocumented SQL Server 2012 Express LocalDB

As a developer of Microsoft Visual Studio, SQL Server 2012 Express LocalDB probably has gotten onto your machine without your notice. I will document some of my exploration on LocalDB in this post.

Installation and Location

There are three ways to get LocalDB onto your machines:

  1. Install together when installing Microsoft Visual Studio 2013 (this is my case);
  2. Install by using SqlLocalDB.msi found in SQL Server 2012 Express
  3. Install by downloading from Microsoft Download Centre directly (here).

The installation location is default to C:\Program Files\Microsoft SQL Server\110\LocalDB\Binn where sqlserv.exe is the main application.

The tools (utilities) to operate the LocalDB are SqlLocalDB, SQLCMD and bcp which are located at C:\Program Files\Microsoft SQL Server\110\Tools\Binn. Make sure to include it into your PATH.

SqlLocalDB

This is the utility to administrate the localdb instances.

  • to get help: sqllocaldb -?
  • to print the version: sqllocaldb versions
  • to create an instance: sqllocaldb create “YourInstanceName”
  • to delete an instance: sqllocaldb delete “YourInstanceName”
  • to start an instance: sqllocaldb start “YourInstanceName”
  • to stop an instance: sqllocaldb stop “YourInstanceName”
  • to share an instance: sqllocaldb share “YourInstanceName”
  • to unshared an instance: sqllocadbl unshare “YourInstanceName”
  • to list all your instances: sqllocaldb info
  • to list the status of an instance: sqllocaldb info “YourInstanceName”
  • to set the trace on and off: sqllocaldb trace on|off

If you’ve used VS 2013 to connect to LocalDB, VS 2013 would have created an instance for you (in my case is v11.0).

Even your instance is stopped, it will be auto-started when you try to connect to it first time either via VS 2013 or SQLCMD.

SQLCMD, BCP, process ‘sqlservr’

Both SQLCMD and BCP are well documented. The only difference between LocalDB and SQL server is that we need to put a bracket ‘()’ to indicate it is a LocalDB instead of hostname for the named instance like:

sqlcmd -S ‘(LocalDB)\v11.0’

This is also applied to SSMS and VS 2013 connections. There is only one process “sqlservr” related to LocalDB. It is very lightweighted by using about 12MB RAM on my machine.

Some Examples

The following is executed in powershell:

Windows PowerShell
Copyright (C) 2013 Microsoft Corporation. All rights reserved.

PS C:\Users\henry> sqllocaldb info
Projects
v11.0
PS C:\Users\henry> sqllocaldb info "v11.0"
Name:               v11.0
Version:            11.0.3000.0
Shared name:
Owner:              PolarBear\henry
Auto-create:        Yes
State:              Stopped
Last start time:    12/31/2013 2:37:39 PM
Instance pipe name:
PS C:\Users\henry> sqllocaldb start "v11.0"
LocalDB instance "v11.0" started.
PS C:\Users\henry>  ps | where-object {$_.ProcessName -match 'sqlservr'}

Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName
-------  ------    -----      ----- -----   ------     -- -----------
    492      20    67780      17140   311     3.83   2248 sqlservr


PS C:\Users\henry> sqllocaldb stop "v11.0"
LocalDB instance "v11.0" stopped.
PS C:\Users\henry> sqlcmd -S "(LocalDB)\v11.0"
1> use test
2> go
Changed database context to 'test'.
1> select count(*) from HR.Employees
2> go

-----------
          9

(1 rows affected)
1> shutdown
2> go
Server shut down by request from login PolarBear\henry.
1> exit
PS C:\Users\henry>

Sharing or Not

From A TechNet Article
When sharing a SqlLocalDB instance with a non-owner, you must re-start the instance for the other users to be able to see the instance you have shared. A non-owner cannot start an instance, so if you are going to share an instance with other users who can access your machine, you also need to be sure it has been started. When you create an instance you can do this as follows:

sqllocaldb create “MyInstance”
sqllocaldb share “MyInstance” “OurInstance”
sqllocaldb start “MyInstance”

You should add users explicitly when connected to the instance as the owner, e.g.

CREATE LOGIN [Domain\User] FROM WINDOWS;
GRANT CONNECT TO [Domain\User];
— other permissions…

In general, though, the purpose of SqlLocalDB is to serve as a sandbox for an individual developer on a machine, not to serve as a development environment for multiple users. Each user should be able to create, start, administer and use his/her own LocalDB instances.

Virtualbox: How to sync time VM client in Windows

I am running a few Virtual Machines on my PC by using Oracle Virtualbox. There is always a need to sync the time on VM clients. The following commands are used in my Windows 8 VM client to sync to the time server in Canada by ‘run as Administrator’:

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Windows\system32>net stop w32time
The Windows Time service is stopping.
The Windows Time service was stopped successfully.

C:\Windows\system32>w32tm /debug /disable

C:\Windows\system32>w32tm /unregister
W32Time successfully unregistered.

C:\Windows\system32>w32tm /register
W32Time successfully registered.

C:\Windows\system32>net start w32time
The Windows Time service is starting.
The Windows Time service was started successfully.

C:\Windows\system32>w32tm /config /manualpeerlist:time.nrc.ca,0x8, /syncfromflags:manual /update
The command completed successfully.

C:\Windows\system32>w32tm /resync
Sending resync command to local computer
The command completed successfully.

Delete older files using Powershell?

One of thing I like linux (unix) is the scripting. The one I like most is to remove files older than 10 days:

find /yourpath/yourfiles* -type f -mtime +10 -exec rm '{}' \;

But I  need to do this in Windows Powershell, and how? Here is the answer:

$Now=get-date

$LastWrite = $Now.AddDays(-10)

Get-ChildItem \yourpath\yourfile* |Where {$_.LastWriteTime -le "$LastWrite"}|remove-item -recurse

Blackberry Development 5: FileConnection APIs

Refer to http://developers.sun.com/mobility/apis/articles/fileconnection/ for the API.

FileConnectionTest.java

package com.henry416.fileconnection;

import net.rim.device.api.ui.UiApplication;

public class FileConnectionTest extends UiApplication {

    public FileConnectionApplication() {
        FileConnectionScreen screen = new FileConnectionScreen();
        pushScreen(screen);
     }

    public static void main(String[] args) {
        FileConnectionTest app = new FileConnectionTest();
        app.enterEventDispatcher();
     }

}

FileConnectionScreen.java

package com.henry416.fileconnection;

import net.rim.device.api.ui.MenuItem;
import net.rim.device.api.ui.component.Menu;
import net.rim.device.api.ui.component.ObjectListField;
import net.rim.device.api.ui.container.MainScreen;
import net.rim.device.api.util.StringProvider;

public class FileConnectionScreen extends MainScreen {

    private ObjectListField fileList;
    private String currentPath = "file:///";

     public FileConnectionScreen() {
         setTitle("FileConnection");

        fileList = new ObjectListField();

        fileList.set(new String[] {"store/", "SDCard/"});

        add(fileList);
     }

    protected void makeMenu(Menu menu, int instance) {
        super.makeMenu(menu, instance);
        menu.add(new MenuItem(new StringProvider("Select"), 10, 10) {
            public void run() {
                loadFile();
            }
        });
     }

    private void loadFile() {
        currentPath += fileList.get(fileList, fileList.getSelectedIndex());
        try {
            FileConnection fileConnection = (FileConnection)Connector.open(currentPath);
            if (fileConnection.isDirectory()) {
                Enumeration directoryEnumerator = fileConnection.list();
                Vector contentVector = new Vector();
                while(directoryEnumerator.hasMoreElements()) {

                    contentVector.addElement(directoryEnumerator.nextElement());
                }
                String[] directoryContents = new String[contentVector.size()];
                contentVector.copyInto(directoryContents);

                fileList.set(directoryContents);
            }

        } catch (IOException ex) {

        }
    }
}

For more samples are here.

Blackberry Development 3: Single Threading and Event Testing

The BlackBerry UI API is single threaded. This means that all UI updates and events are handled by the same thread—or more precisely, they must be handled while holding the event lock, which most of the time is held by the UI thread.

First, let’s change labelFieldto be a member variable instead of a variable local to the constructor, and add a method to append text to it:

public class HelloWorldMainScreen extends MainScreen {

    private LabelField labelField;

    public HelloWorldMainScreen() {
        labelField = new LabelField("Hello World");
        add(labelField);
    }

    public void appendLabelText(String text) {
        labelField.setText(labelField.getText() + "\n" + text);
    }

}

Because appendLabelText calls LabelField.setText, the call can only be made from the event thread. If you attempt to call this method directly from another thread, an exception will be thrown.

Now, we’ll define the thread class that will actually do the testing. It will loop from 1 to 10. In each iteration, it will wait 5 seconds and then add some text to the LabelField. Create a new class called MainScreenUpdaterThread that extends java.lang.Thread. The full source code follows:

package com.henry416;

import net.rim.device.api.ui.UiApplication;

public class MainScreenUpdaterThread extends Thread {
    HelloWorldMainScreen mainScreen;

    public MainScreenUpdaterThread(HelloWorldMainScreen mainScreen) {
        this.mainScreen = mainScreen;
    }

    public void run() {
        for (int i = 0; i < 10; i++) {

            try {
                 Thread.sleep(5000);
            } catch (InterruptedException ex) {

            }
            // Queue a new task on the event thread
            UiApplication.getUiApplication().invokeLater(new Runnable() {
                 public void run() {
                    mainScreen.appendLabelText("Testing");
                 }

            });

        }
     }
}

To actually update the UI, we’re using an anonymous inner class, which is a class that we define at the point where we instantiate it. Our anonymous inner class calls the one method that needs to be called on the event thread: appendLabelText (which calls LabelField.setText).

We’ll start our thread in the HelloWorldMainScreen constructor as follows:

    public HelloWorldMainScreen() {
        labelField = new LabelField("Hello World");

        add(labelField);

        MainScreenUpdaterThread thread = new MainScreenUpdaterThread(this);
        thread.start();
    }

Finally, running this application will produce the following results:

HelloWorld
Testing
Testing
Testing
Testing
Testing

Try to modify the above method with the following method:

    public void run() {
        for (int i = 0; i < 10; i++) {
            try {
                Thread.sleep(5000);
            } catch (InterruptedException ex) {

            }
            // Ensure we have the event lock
            synchronized(UiApplication.getEventLock()) {
                mainScreen.appendLabelText("Testing Again");
            }
        }
    }

Blackberry Development 2: Steps for an app

A step guide for a simple app development by using Eclipse and Blackberry Plug-In. This is the follow up of my previous post Blackberry Development 1: setup environment.

2.1. create a project in Eclipse

To create a new BlackBerry project, click the File menu, and choose New Image from book BlackBerry Project. Name your project HelloWorld, and click Finish.

What is created?

The IDE will create the following:

  • src folder: Where all our source files will reside
  • res folder: Where all resource files (i.e., images) will reside
  • JRE System Library: The BlackBerry runtime library containing the BlackBerry API (by default, it is OS 7)
  • BlackBerry_App_Descriptor.xml file: A file where you can configure your application, including the name of the application and the icon your application will use

 2.2. Create a main application class

Right-click the HelloWorld project icon in the Package Explorer, and from the pop-up menu, select New Image from bookClass. In the dialog, type the following values:

  • Package: com.henry416
  • Name: HelloWorldTest (you can leave off the .java file extension)
  • Superclass: net.rim.device.api.ui.UiApplication
  •  
 What is created?

The application class is created and performs the following:

  • Create an instance of the application

  • Create the main screen and push it onto the display stack

  • Start the event dispatch thread

package com.henry416;

import net.rim.device.api.ui.UiApplication;

public class HelloWorldTest extends UiApplication {

    public HelloWorldTest() {
        // TODO Auto-generated constructor stub
    }

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub

    }

}

2.3. Create a main screen class

Click New Image from book Class again (or right-click the package in the tree view and select New Image from bookClass, and you won’t have to reenter the package name). Fill in the following values:

  • Package: com.henry416

  • Name: HelloWorldMainScreen

  • Superclass: net.rim.device.api.ui.container.MainScreen (or type MainS, and press Ctrl+spacebar)

 What is created?
package com.henry416;

import net.rim.device.api.ui.container.MainScreen;

public class HelloWorldMainScreen extends MainScreen {

}

2.4. Add code to the Hello World Classes

Go back to HelloWorldTest, our main class. Fill in the constructor of HelloWorldApp. This will create the main screen and push it onto the display stack:

class HelloWorldTest extends UiApplication {
    HelloWorldTest() {
        HelloWorldMainScreen mainScreen = new HelloWorldMainScreen();
        pushScreen(mainScreen);
    }
}

The main method will create an instance of our application and start the event dispatcher, which is the mechanism that does all the drawing to the screen, and listens for all user interaction for our application.

class HelloWorldTest extends UiApplication {
   …
    public static void main(String[] args) {
        HelloWorldTest app = new HelloWorldApp();
        app.enterEventDispatcher();
    }
}

The enterEventDispatcher method will never return as long as the application is running. Essentially, the thread that entered the main application becomes the event dispatch thread. We’ll explore this in greater depth later, but for now, just remember that the method won’t return during the application’s normal life cycle.

2.5. Code the Main Screen Classes

Add some GUI components to our main screen class with the following code for HelloWorldMainScreen.java:

public class HelloWorldMainScreen extends MainScreen {
    public HelloWorldMainScreen() {
        net.rim.device.api.ui.component.LabelField labelField = new
net.rim.device.api.ui.component.LabelField("Hello World");

        add(labelField);
    }
}

Note that we subclass MainScreen instead of Screen because MainScreen gives us a couple of things automatically—namely a basic layout manager (to position our UI controls on the screen) and a default menu.  

2.6. Test the app in the simulator

Click the arrow next to the debug icon on the Eclipse toolbar, and select Debug Configurations. Each configuration can have different debug parameters, and as you develop applications, you’ll likely end up with a few different configurations for debugging different OS versions, screen sizes, and so on.

Select the BlackBerry Simulator icon on the left side, and click the New button on the toolbar in the dialog window.

On the Project tab, check the newly created HelloWorld project. Then click the Debug button at the bottom of the dialog. The simulator will launch with your application deployed.

After you click the Debug button, the simulator will start up. When it is finished, you will see your HelloWorldTest on the BlackBerry simulator home screen,

Click the app icon to test…

Microsoft Platform – MSDN

http://msdn.microsoft.com/en-us/library/ms123401.aspx First of all, here is the core: .NET Development. Then, the following are the areas for deep diving.
 
Where to start? The Beginner Developer Learning Center. More professional learning is here.