Accessing MS Access Databases from SQL Server

I recently installed WinTV 8.5, and after scanning TV channels, I noticed that editing channel numbers was incredibly cumbersome.

According to the FAQ, it would involve a lot of scrolling, and 4 clicks – *per channel*. I also noticed a couple other peculiarities in the UI, such as the EPG viewer, which made me research where the program’s data is stored.

Actually it’s all in the FAQ: it’s an MS Access database called hcwChanDB_5.mdb residing in a directory set during installation.

To save your channel database go to:

C:\Users\Public\WinTV\Channel Database (Windows 7 and Vista)

C:\Documents and Settings\All Users\Shared Documents\WinTV\Channel Database (Windows XP)

Copy the  hcwChanDB_5 file to a place  for safe keeping. To replace saved Channels just copy HcwChanDB_5 back to Channel Database Dir.

When I double-click the .mdb file, a nice reminder shows up that I only have a Test edition of MS Access, and not the fully activated Professional edition. Essentially this means that I can view the data, but cannot change it.

Time to start up SSMS, link the Access database and browse the data from SSMS. … which turned out to be not so easy.

Linked Server to MS Access via ODBC System DSN

WinTV creates an ODBC System DSN called HCW_mdb, and you can easily create a linked server in SSMS referencing this DSN. Running

exec sp_catalogs 'hcw'

on the linked server returns the path names of the MS Access databases, but using them as catalog names in a SELECT * FROM statement only raises an error message

OLE DB provider “MSDASQL” for linked server “hcw” returned message “[Microsoft][ODBC Microsoft Access Driver] Syntaxfehler (fehlender Operator) in Abfrageausdruck ‘`Tbl1002`.`id` `Col1005`’.”.
Msg 7321, Level 16, State 2, Line 4
An error occurred while preparing the query “SELECT `Tbl1002`.`id` `Col1005`,`Tbl1002`.`service_id` `Col1006`,`Tbl1002`.`preferred_name` `Col1007`,`Tbl1002`.`preferred_number` `Col1008`,`Tbl1002`.`description` `Col1009`,`Tbl1002`.`audio_lang` `Col1010`,`Tbl1002`.`subtitle_lang` `Col1011`,`Tbl1002`.`audio_mode` `Col1012`,`Tbl1002`.`irblaster_data` `Col1013`,`Tbl1002`.`channel_type` `Col1014`,`Tbl1002`.`language` `Col1015`,`Tbl1002`.`genre` `Col1016`,`Tbl1002`.`duplicate_channel_id` `Col1017`,`Tbl1002`.`duplicate_priority` `Col1018`,`Tbl1002`.`blocked_flag` `Col1019`,`Tbl1002`.`last_used` `Col1020`,`Tbl1002`.`epg_source` `Col1021`,`Tbl1002`.`preferred_minor` `Col1003` FROM `D:\data\WinTV\Channel Database\hcwChanDB_5`.`hcwChannels` `Tbl1002`” for execution against OLE DB provider “MSDASQL” for linked server “hcw”.

However, you can query the database using OPENQUERY()

select * from openquery(hcw, 'select * from hcwChannels')

where hcwChannels is one of the tables in the MS Access database.

Linked Server to MS Access via OLEDB provider

I kept searching, and fortunately found a forum post on social.MSDN in which the last comment provided a hint to another workable solution:

Note: The same issue is present using Sql Server 2012 RC0, and still forcing the use of the deprecated MSDASQL provider (ms ole db provider for odbc drivers) as a workaround.

Update: besides using msdasql, it works, in some cases, to do the following before creating the linked server:
right-click and select “properties” for “Microsoft.ACE.OLEDB.12.0” under Server objects, Linked servers, Providers in SSMS.  Check “nested queries” and “allow inprocess.”   Then create the linked server for access accdb using the ACE.OLEDB.12.0 provider, with the access filename under data source.

So I checked the OLEDB provider settings and changed them accordingly. Then I created a linked server using Microsoft.ACE.OLEDB.12.0 referencing the hcwChanDB_5.mdb database.

While I could not enumerate the catalogs of the new linked server, the database could now be queried using standard SELECT statements such as

select * from hcwole...hcwChannels

I could now create a SYNONYM for each table inside the MS Access database.

See my WinTV repository on GitHub for the SQL files.

 

The great Database Diagram mystery

You may (or may not) know that the Database Diagrammer of SSMS stores the created diagrams in a table called dbo.sysdiagrams.

This table has always been a mystery to me, especially the column “definition”, as it contains binary data, and documentation cannot be found about its format.

So when I tried to figure out what all the bytes meant, and my standard editor plus hex viewer plug-in (read: Notepad++) could not help me any further, I searched and found FlexHEX which promised to provide the functionality to annotate and structure binary data. Somehow this did not work out as expected – I could not figure out how to create and edit “structures” – but as I clicked around, I hit the menu “Open OLE Compound File…” as a last resort. What could go wrong.

To my great surprise, this command actually opened the file and displayed a file structure, with each of the sections containing some data.

As it turned out, the section DSREF-SCHEMA-CONTENTS was the one I was looking for, as it contains, as I later found out, all the textual information in the diagram:

•  (original) diagram name
• connection string
• list of all tables in the diagram (as of last save)

This section is stored as a Microsoft Compound File or Compound File Binary Format or Compound Document File, and the easiest way to process it in C# is the nuget package OpenMcdf (source on GitHub).

Writing a small tool, I verified that the sections from the binary data really have those names, and are not artifacts of the hex editor.

Once I got hold of the data contained in section DSREF-SCHEMA-CONTENTS, making sense of the structure of this data was quite straight-forward.

Which version of SQL Server and SSMS can be installed on which Windows version?

As my Windows installation ages, it collects various versions of development tools, such as SQL Server, SQL Server Management Studio (SSMS), or Visual Studio.

But for every version of Windows the question is: will the next version of product X be installable on Windows Y?

So here is the answer for SQL Server and SSMS on Windows versions 7 (32-bit), 8.1 (64-bit) and 10 (64-bit)

SQL/SSMS	Version	Win7-32	Win8.1-64	Win10-64
2008	10	Y	?	?
2012	11	Y	Y	Y
2014	12	Y	Y	Y
SSMS 2016	13	Y	Y	Y
SQL 2016	13	–	Y	Y
SSMS 17	17	–	Y	Y
SQL 2017	14	–	Y	Y
SSMS 18	18	–	Y	Y
SQL 2019	15	–	–	Y

 

Handling SMO Errors in SSMS View Dependencies

I tried to retrieve the dependencies of a T-SQL function in SQL Management Studio 2008, when SSMS – after some database querying – displayed the following error message:

SSMS: Discover dependencies failed. (Microsoft.SqlServer.SMO)

The message reads

TITLE: Microsoft SQL Server Management Studio
——————————

Discover dependencies failed.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Discover+dependencies+DependencyWalker&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&LinkId=20476

——————————

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
The statement has been terminated.
Duplicate key was ignored.
Duplicate key was ignored.
Duplicate key was ignored.
The statement has been terminated.
Duplicate key was ignored.
Duplicate key was ignored.
Duplicate key was ignored.
The statement has been terminated.
Duplicate key was ignored. (Microsoft SQL Server, Error: 515)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.5512&EvtSrc=MSSQLServer&EvtID=515&LinkId=20476

Clicking on the “Additional Details” button, the SMO exception tree is displayed

===================================

Discover dependencies failed.  (Microsoft.SqlServer.Smo)

——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Discover+dependencies+DependencyWalker&LinkId=20476

——————————
Program Location:

at Microsoft.SqlServer.Management.Smo.DependencyWalker.DiscoverDependencies(Urn[] urns, Boolean parents)
at Microsoft.SqlServer.Management.SqlManagerUI.ObjectDependencies.DiscoverDependenciesAsync()

===================================

An exception occurred while executing a Transact-SQL statement. (Microsoft.SqlServer.Smo)

——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&LinkId=20476

——————————
Program Location:

at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetDependencies(DependencyRequest dependencyRequest)
at Microsoft.SqlServer.Management.Smo.DependencyWalker.DiscoverDependencies(Urn[] urns, Boolean parents)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————
Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.Execute(StringCollection query)
at Microsoft.SqlServer.Management.Smo.SqlEnumDependencies.EnumDependencies(Object ci, DependencyRequest rd)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.EnumDependencies(Object connectionInfo, DependencyRequest dependencyRequest)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetDependencies(DependencyRequest dependencyRequest)

===================================

Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
Cannot insert the value NULL into column ‘object_type’, table ‘tempdb.dbo.#t2_________________________________________________________________________________________________________________000000000052’; column does not allow nulls. INSERT fails.
The statement has been terminated.
Duplicate key was ignored.
Duplicate key was ignored.
Duplicate key was ignored.
The statement has been terminated.
Duplicate key was ignored.
Duplicate key was ignored.
Duplicate key was ignored.
The statement has been terminated.
Duplicate key was ignored. (.Net SqlClient Data Provider)

——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.5512&EvtSrc=MSSQLServer&EvtID=515&LinkId=20476

——————————
Server Name: NIIGATA
Error Number: 515
Severity: 16
State: 2
Line Number: 311

——————————
Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)

Off to the internets, I found this Script to fix dependencies in the MSSQLWIKI and gave it a try. It soon pointed to a couple of views and stored procedures that the Dependency Viewer (or the underlying SQL statements) thought contained invalid dependencies, however I opened them and re-compiled them without errors. Only when I extended the script to also include triggers, I found an obsolete dependency and fixed it.

Probably re-compilation fixed internal dependency tracking as the script completed without errors in the end.

What the script actually does is iterate through all T-SQL modules, i.e. stored procedures, functions, view definitions (and, after editing, also triggers), and run sys.sp_refreshsqlmodule on each of them. The script’s magic lies in finding the correct order to process the modules.

Fixing the script

These are my changes to the script:

First, all temporary tables are DROPped, since they stay in the database in case the script (or rather, the call to sp_refreshsqlmodule) causes an error:

if (OBJECT_ID('tempdb..#t_excluded_modules') is not null)
    drop table #t_excluded_modules
    
if (OBJECT_ID('tempdb..#t_modules_refreshed_in_end') is not null)
    drop table #t_modules_refreshed_in_end
    
if (OBJECT_ID('tempdb..#t_user_views_or_tables') is not null)
    drop table #t_user_views_or_tables

if (OBJECT_ID('tempdb..#t_dependency_table') is not null)
    drop table #t_dependency_table

Second, I extended to SELECT statement which retrieves the modules to be checked to also include triggers

        select object_id from sys.objects where
            type in ('V', 'FN', 'IF', 'TF', 'TR')

After a couple of runs, I was able to invoke the View Dependencies dialog again without errors.

Surprises Changing the Length of a Character Field in SSMS

I needed to convert a couple of CHAR and NCHAR columns to their VARCHAR/NVARCHAR equivalent, and noticed a peculiar behavior of the SSMS (2008 R2) table designer:

For example, when the original column was defined as CHAR(10), and I added the characters VAR at the beginning of the Data Type field and tabbed to the next column, the editor would not keep the resulting VARCHAR(10), but change the data type to VARCHAR(50), as is the default length for VARCHAR and NVARCHAR columns in the designer.

I found that this behavior was discussed in this SO question as occurring in SSMS 2005, confirmed in 2008, and, by personal experience, still exists in 2008R2 and 2012.

It seems that MS either does not care, or that it does not want to fix weird bugs due to “compatibility issues”.

Taking a Database Offline in SQL Server Management Studio

To take a database offline in SSMS, simply right-click the database, select Tasks, Take Offline, and you’re done.

Except, if it does not work because there are still open connection to the database.

In this case, you need to find out the connections’ SPID

SELECT SPId FROM master..SysProcesses 
WHERE DBId = DB_ID('MyDatabase') AND SPId <> @@SPID

and kill each of the connections by executing

KILL [spid from query above]

Finally, you can take the database offline from its context menu, or, as you already work in a SSMS Query window, simply type

ALTER DATABASE [MyDatabase] SET OFFLINE WITH ROLLBACK IMMEDIATE

I just found this script today, so I’m afraid I cannot give you any sources of this magic wisdom.

I did however find an older post here dealing with SSMS Express Edition.

T-SQL ‘GO’ and SqlCommand

Try to execute a T-SQL script which contains several SQL batches separated by the GO keyword using .Net’s SqlCommand, and you will receive an error message

Incorrect syntax near ‘GO’.

What’s going on here? Why does it work from SSMS or sqlcmd, but not in SqlCommand?

The reason for this behavior is that ‘GO’ is not a statement of T-SQL, but rather

a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor

(MSDN). You think you might simply remove all occurrences of GO inside your statement, but that will not always work: Some statements require to be the first statement in a batch

‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.

So how do you execute a set of SQL statements containing ‘GO’ in C#?

The “lazy” way is to simply split the SQL string at each occurrence of ‘GO’ using string.Split(), but this is really lazy.

Why? Because of the complexity of the language that the T-SQL shells are capable of processing:

• string literals ” and N”
• single line comments —
• multi-line comments /* */
• nested multi-line comments (!)

The characters ‘GO’ must not be recognized if they occur inside any of these constructs, and they need not be the first characters in a line – the line may also contain spaces or whitespace or comments (just give it a try in SSMS, it’s truly amazing).

The more practical way (mentioned here) to execute an arbitrary SQL string is to use SMO’s Server.ConnectionContext.ExecuteNonQuery().

Or, you write a parser using ANTLR, define the correct grammar to split at the relevant GO keywords, and execute the list of resulting SQL batches separately. 😉

Restoring a SQL Server Database Backup Created by a Newer Version of SQL Server

It is widely known that you can only restore a database backup using the same or a newer version of SQL Server.

There is however a workaround to this restriction. The workaround consists of scripting database and contents and importing the scripts using sqlcmd.

Here’s my scenario:

A database is being developed on SQL 2008 [A], and goes live on SQL 2008 R2 [B]. There is no problem copying the database from one system [A] to the other [B].

Then an error occurs, or something must be debugged or analysed, but developers only have SQL 2008 installed.

A newer version is available [C], but not on the develop machines.

So the SQL Server versions can be summarized as: [A] < [B] <= [C]

And this is the workaround:

• Create a full database backup on [B]
• Restore the backup in a new database on [C]
• In SSMS, locate the new database, and right-click
• Tasks/Generate Scripts

First, we create the DDL script

• Save scripts to a specific location
• Save to file: Single file, Save As: Unicode text

Advanced:

• Generate Script for Dependent Objects: True
• Schema qualify object names: True
• Script Collations: if you need them
• Script Defaults: True
• Script CREATE
• Script for Server Version: select version for [A]
• Script Logins, Owner, Permissions, etc: as required
• Types of data to script: Schema only
• Script Check Constraints, Foreign Keys, Full-Text Indexes, Indexes, Primary Keys, Triggers, Unique Keys: True

Next, Next until DDL file is being generated

Next, we create the Data script. Same steps as above, but

• Types of data to script: Data only

This may take a while…

In the meantime, we can edit the original DDL file. Open the file (I’ll refer to it a script.sql) in a Unicode-capable text editor.

Since we want to create the database on a dev machine, I tend to skip the DDL statements creating the database:

• Starting from USE [master] up to the [USER <mydatabase>]. Depending on the contents of the database, you may also want to skip the CREATE USER and CREATE ROLE statements.
• Eventually, you’ll find the first CREATE (PROCEDURE, TABLE, VIEW, etc.) statement. This is where is gets interesting for us.
• This CREATE section lasts until you find the first CREATE INDEX or ALTER (TABLE) statement.
• Copy this section to a new file, as save it (say, script1.sql).
• In script.sql, select from the first CREATE INDEX or ALTER statement down to the last statement before USE [master]. Save this section as script2.sql.

We end up having these files:

• script.sql .. the original Schema-only file
• script1.sql .. the CREATE statements
• script2.sql .. the indexes and constraints
• data.sql .. the original Data-only file

The DDL scripts are typically not so large, and can be executed from inside SSMS. The Data file may be more problematic, as even a small database can generate a huge (several GB!) data SQL file.

If SSMS is capable of handling all the SQL files, the correct sequence is:

• script1.sql .. the CREATE statements
• data.sql .. the original Data-only file
• script2.sql .. the indexes and constraints

Why do we split the original Schema-only file? Well, the schema defines constraint, such as foreign keys. Typically, the generation of the data script does not take care of such foreign key constraints. That’s why we apply the constraints only after adding the data.

What to do if the data file is too big for SSMS? We’ll run it from the command line:

Open the command prompt (Win+Run, cmd.exe) and try to connect to your SQL Server [A] using sqlcmd. Type sqlcmd -? to get the list of options.

If you have Windows authentication enabled and SQL Server runs on your local machine, the statement

sqlcmd -d [mydatabase] -Q "SELECT 1"

verifies you can connect to SQL Server. If successful, the expected result will be displayed:

-----------
          1

(1 rows affected)

Use the -i command line switch to declare the data.sql as input file:

sqlcmd [all other switches] -i c:\path\to\data.sql

Again, this command may be busy for quite some time. If you have no idea what is going on, and really really want to know, use this script to watch the inserts from SSMS.

Counting Records of All Tables in SSMS

The Amazing FMTONLY Trick

I created a stored procedure to generate data for a report which involved several temp tables. To check the intermediate results, I thought I’d output the results of the various temp tables only during development, but skip the temp tables in production:

DECLARE @dev BIT = 0;    -- set to 1 during development
IF @dev = 1 SELECT * from @temp

This works fine in SSMS.

But trying to set the SP as report DataSource in Report Designer (.rdl Editor), VS offered the columns of the first SELECT statement, ignoring the value of @dev.

As you may know, when you define a data source in a VS IDE tool, the IDE invokes the SQL statement using the SET FMTONLY ON setting to retrieve information on the columns of the data source.

One feature of the FMTONLY mode is how IF statements are processed: BOTH the IF and the ELSE branch are analyzed independent of the result of the IF condition. This explains why the IF … SELECT always returns the column data of every SELECT statement in the SP.

I searched and found this article on SQLServerCentral on how to output only specific data sets in FMTONLY mode. What I did not know was that you can set FMTONLY anywhere inside a T-SQL procedure!

My variant of the code in the referenced article looks like this:

– Detect whether FMTONLY is set (only then the IF(1=0) will be executed):

DECLARE @fmtonlyON BIT = 0;
IF (1=0) SET @fmtonlyON = 1;
SET FMTONLY OFF;

– Create and SELECT temp tables as you like, IF statements will be handled correctly

– Reset FMTONLY where required

IF @fmtonlyON = 1 SET FMTONLY ON;

A comment on the article also mentions that this method allows #Temp tables in SPs without raising the error message

Msg 208, Level 16, State 0, Line {line}
Invalid object name '#TempTable'.

*Yes, the title of this blog was inspired by this great video. Enjoy 😉