Speaking at 24 Hours Of PASS: Growing Our Community

PASS will organize a 24-hour online conference about SQL Server at 24-25 of June, 2015. I’m happy to say that I’m one of the 24 lucky persons who will present their sessions to the big community.

24HOP_GOC_SpeakerButton

My session is Offline Database Developing and Unit Testing with SSDT. It will be on 6:00 GMT on 25 of June. In this session I’ll talk about how to create a database project, create database objects such as tables, views, stored procedures, include scripts to fill tables with initial data and deploy it to the SQL Server. And all these are possible using only one tool – Visual Studio. To be correctly, it’s a component of Visual Studio named SQL Server Data Tools.

Also we’ll take a look at a new feature of SSDT – database unit testing. Now SSDT allows to create unit tests for the most important parts of database, especially T-SQL code in stored procedures, functions and triggers. SSDT even can debug the code from within the unit test.

So join us at 24 Hours of PASS! Don’t forget to check the full schedule of the event http://www.sqlpass.org/24hours/2015/goc/Schedule.aspx. I hope you’ll find something interesting to you.

Deleting Database Connections in SSDT Comparison Tools

When you work with SSDT comparison tools like Data or Schema comparison, you might enter many connections to databases. So your list of connections looks like as shown on the picture below. But there’s no button to delete unnecessary connections.

Too much db connections in SQL comparison tools

 

I saw an advice to choose a connection, click Edit and change to any already existing connection. Visual Studio will store only one of them, thus the unnecessary connection will be deleted. Fine! But Visual Studio needs to open this connections before you can edit it, If the connection is unavailable, so VS won’t open it.

There’s a more radical method via Windows registry. Open the branch HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\12.0\SSDT\ConnectionMruList and delete ConnectionN and ConnectionNameN records there.

 

Offline Help for SQL Server 2014

To use Help in SQL Server 2014 in offline mode you should perform the following steps:

1) install Documentation Components – at least you need the Help Viewer to see the content of help library

Documentation components

2) download the installer from Microsoft site Product Documentation for Microsoft SQL Server 2014 for firewall and proxy restricted environments

3) run the installer SQLServer2014Documentation_August2014_EN.exe

4) unzip files to a folder (I prefer to manually create a folder and unzip files there)

5) launch Help Settings

This can be made from Windows Start menu (Microsoft SQL Server 2014 -> Documentation & Community -> Manage Help Settings) or from SQL Server Management Studio (Help -> Manage Help Settings)

6) click Install content from disk in the Help Library Manager window

Install content from disk

7) locate the folder to which you have unzipped the files at step 4 and select the file HelpContentSetup.msha, then press Open

8) press Next in the Help library Manager

9) click the links in Actions column to select the desired sections, then press Update

Help sections

10) after you’ll see a message “Finished updating” press Finish button, then press Exit

11) now you can delete the folder with unzipped files

Tip: to check that you are really using the local help start Manage Help Settings again, click the first link Choose online or local help, and ensure the setting is correct.

Local help setting

View Instance and Database Versions

You can get the following characteristics about your SQL Server:

  • product version (for example, 12.x for SQL Server 2014)
  • product level (RTM, Service Pack or CTP)
  • edition (standard, enterprise, developer, etc.)

Product Version Product Level Edition
-------------------- -------------------- ------------------------------
12.0.2495.0 RTM Developer Edition (64-bit)

(1 row(s) affected)

The full list of server properties can be seen in MSDN article SERVERPROPERTY (Transact-SQL)

For the database you can get the current internal version number and compatibility level. With some extra effort you can get starting database version number, i.e. the version at which the database was created originally.

Internal Version Number
------------------------------
782

(1 row(s) affected)

COMPATIBILITY_LEVEL
-------------------
120

(1 row(s) affected)

To see the starting internal database number you should use DBCC DBINFO command.

DBINFO STRUCTURE:

DBINFO @0x000000000E29D8E0

dbi_version = 782 dbi_createVersion = 661 dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)
dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000)
...

Look at the first line starting with dbi_. The dbi_version is the current internal database version number (782 is SQL Server 2014), the dbi_createVersion is that of the moment of database creation (661 is 2008 R2). The list of these numbers can be found at this post.

Implicit Database Upgrade and Internal Database Version Numbers

When you run a database from previous version on a SQL Server instance with a higher version, the database is automatically upgraded to the version of the instance. It happens on:

  1. attaching a database from older version;
  2. restroring a database from older version;
  3. and, of course, when you make an in-place upgrade of SQL Server.

For example, when I attach a database from SQL Server 2008 R2 in SQL Server 2014, I’ll get these messages telling about database upgrade.
Converting database 'test' from version 661 to the current version 782.
Database 'test' running the upgrade step from version 661 to version 668.
Database 'test' running the upgrade step from version 668 to version 669.
...
Database 'test' running the upgrade step from version 781 to version 782.

The version numbers you see in Messages window is internal database version numbers. The list of these numbers can be found at this post.

Be aware this is a one-direction upgrade. There’s no possibility to downgrade the database nor open it in SQL Server with original version or any intermediate version prior to upgraded one. This means that if your database was created in SQL Server 200, then you opened it in SQL Server 2014, you never can open in SQL Server version from 2005 till 2012. The error message is self-explanatory.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'test'. CREATE DATABASE is aborted.
Msg 948, Level 20, State 1, Line 1
The database 'test' cannot be opened because it is version 782. This server supports version 663 and earlier. A downgrade path is not supported.

Another issue is that you can NOT prevent SQL Server from upgrading the database. If you make the primary database file read-only in order to get a read-only database and try to open it in a higher version, you’ll get an error 3415.
Msg 3415, Level 16, State 2, Line 3
Database 'test' cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery.

Windows 8.1 Touchpad Driver for ASUS Notebooks

I’m working on Asus K53sd notebook. After upgrade to Windows 8.1 (mainly to use Hyper-V) I’ve faced some problems with touchpad – I can’t disable it in gracious manner except disabling it totally at driver settings level. I’ve tested all drivers that are listed on product support page. Beside that I could not disable touchpad when I plugg in a mouse.

It took me some time to find the right link. It’s a page devoted to touchpad. And it really works!

I don’t know why they did not include it in product page. Maybe it’s a question for some scientists 😉

Link to Asus touchpad driver http://support.asus.com/Download.aspx?SLanguage=en&m=Touchpad&os=30

Hope the driver for Windows 10 will be there…

[UPDATE 2016-05-31]
That’s incredible, but Asus had changed the search engine, so the link above does not find anything. But Elantech driver for Windows 8.1 was found on another page http://www.asus.com/support/Download/3/589/0/21/41/

The direct link for version 11.5.16.2 is http://dlcdnet.asus.com/pub/ASUS/nb/DriversForWin8.1/Touchpad/Touchpad_Elantech_Win81_64_VER115162.zip

I’ve installed it on Windows 10, and yes, Fn + F9 disables notebook touchpad.