MSPSS: is there life after the helpdesk?

sharing solutions to uncommon IT problems

Posts Tagged ‘SQL Server

Querying a MS Access 2003 database from SQL Server 2008

with one comment


I spent the last few days trying to get an SQL Server 2008 to work and sync with queries hosted on an old, heavy and complicated MS Access 2003 (~1Gb of data).

This is not a success story but after wasting so much time on a failure, I feel like sharing my findings.

Let’s start saying that you can’t install Jet 4.0, the native MS Access 2003 driver, on a 64bit SQL Server but you can install SQL Server 32bit (Express edition) on a Windows Server 2008 R2 (they only make this in the x64 flavour).

It doesn’t make a difference if you create the linked server with a straight connection to Access (using the ACE OLEDB 12 driver ) or if you use an ODBC connection that use that driver (it might be obvious for some, not for me…)

So first I thought I’d simply install Access Database Engine 2010 and use the ACE OLEDB 12 driver to retrieve my data, you will have to make queries as this one:

select *
from openquery([LinkedServer], ‘select * from AccessQuery’)

This works for simple queries to tables or even MS Access queries but, as soon as the query uses a custom function (i.e.: developed in VBA), you’ll get the following error:

OLE DB provider “MSDASQL” for linked server “SMGODBC” returned message “[Microsoft][ODBC Microsoft Access Driver] Undefined function ‘customfunction’ in expression.”.

FYI: you get the same error if you have a MS Access built-in function such as replace or Mid but you can get around this by disabling the sandbox mode.

Some people, problems like this could be fixed by installing Access Database Engine 32bit (Note that you will have to uninstall every 64bit Office app you have on your machine)… but I got the same errors anyway.

So I thought I’d try to install Jet 4.0 hoping the queries would get executed “server side” (on MS Access) thus recognising the “custom function”.

I installed an SQL Server Express x86 (32bit) on my server and now, installing Access Database Engine 2010 you will also see the MS Jet 4.0 provider. Unfortunately, in my case, using this provider to exec queries resulted in a timeout every time… but maybe this is due to the exceptional size of our database (so if your DB is smaller do give it a try and let me know).

Then I tried using the ACE 12 driver and replacing custom functions with SQL Server functions (I only had 6 to create/convert from VBA), but I got another blocking problem, 3 of the 28 queries I had to retrieve from MS Access used linked tables to the same server I was querying from (ironic isn’t it??).

I could have re-engineered the queries eliminating joins to linked tables in the MS Access queries and joining them directly in the openquery statement but these queries are rather complex and this task would be time consuming so in the end, due to time constraints, I resolved to push the queries directly from Access to SQL Server tables using the TransferDatabase method which I scheduled to execute once a day.

From all this I feel we can extrapolate a couple of guidelines to consider before you waste too much time trying to do the same:

  1. The Jet 4.0 driver seems to time-out with big MS Access databases
  2. You can use the ACE OLEDB 12 driver to query Access from any version of SQL Server but watch out for custom functions and/or linked tables joins as you will have to modify your queries both on MS Access and SQL Server to get it working.



Written by zantoro

November 11, 2013 at 9:21 am

Posted in MS Access, SQL Server

Tagged with ,

Restore All DBs in a folder (even files with multiple backups in it)

leave a comment »

Ok, this is pretty cool…

I’ve used for a long time a script that allows me to restore all databases from every backup file stored in a folder.

The script was the edited and adapted version of something another guy did for SQL Server 2000 (thank you again sir, wherever you are).

This script proved itself super-useful for migrations, QA/TEST env refresh etc.

Its big limit was that it only allowed one backup per file while I like to group my backups by customer (to avoid having a mess in my backup folder).

Today, I finally found the time to work on it again and now it works no matter the number of backups per file (and the original script was a SP while this is a straight query).

I think it’s pretty cool…

Here it comes:

DECLARE @SourceDirBackupFiles VARCHAR(200) = 'D:\Migration\'
DECLARE @dirfile VARCHAR(300)
DECLARE @LogicalName NVARCHAR(128)
DECLARE @PhysicalName NVARCHAR(260)
DECLARE @DbName sysname
DECLARE @position INT
CREATE TABLE #dbfiles(
LogicalName NVARCHAR(128)
,PhysicalName NVARCHAR(260)
,Type CHAR(1)
,FileGroupName NVARCHAR(128)
,Size numeric(20,0)
,MaxSize numeric(20,0)
,FileId INT
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0)
,UniqueId uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes INT
,SourceBlockSize INT
,FilegroupId INT
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly INT
,IsPresent INT
,TDEThumbprint NVARCHAR(128)
BackupName NVARCHAR(128)
,BackupDescription NVARCHAR(255)
,BackupType smallint
,ExpirationDate datetime
,Compressed tinyint
,Position smallint
,DeviceType tinyint
,UserName NVARCHAR(128)
,ServerName NVARCHAR(128)
,DatabaseName NVARCHAR(128)
,DatabaseVersion INT
,DatabaseCreationDate datetime
,BackupSize numeric(20,0)
,FirstLSN numeric(25,0)
,LastLSN numeric(25,0)
,CheckpointLSN numeric(25,0)
,DatabaseBackupLSN numeric(25,0)
,BackupStartDate datetime
,BackupFinishDate datetime
,SortOrder smallint
,CodePage smallint
,UnicodeLocaleId INT
,UnicodeComparisonStyle INT
,CompatibilityLevel tinyint
,SoftwareVendorId INT
,SoftwareVersionMajor INT
,SoftwareVersionMinor INT
,SoftwareVersionBuild INT
,MachineName NVARCHAR(128)
,Flags INT
,BindingID uniqueidentifier
,RecoveryForkID uniqueidentifier
,Collation NVARCHAR(128)
,FamilyGUID uniqueidentifier
,HasBulkLoggedData INT
,IsSnapshot INT
,IsReadOnly INT
,IsSingleUser INT
,HasBackupChecksums INT
,IsDamaged INT
,BegibsLogChain INT
,HasIncompleteMetaData INT
,IsForceOffline INT
,IsCopyOnly INT
,FirstRecoveryForkID uniqueidentifier
,ForkPointLSN numeric(25,0)
,RecoveryModel NVARCHAR(128)
,DifferentialBaseLSN numeric(25,0)
,DifferentialBaseGUID uniqueidentifier
,BackupTypeDescription NVARCHAR(128)
,BackupSetGUID uniqueidentifier
,CompressedBackupSize NVARCHAR(128)
CREATE TABLE #files(fname VARCHAR(200),depth INT, file_ INT)
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1
SELECT fname FROM #files
SELECT LogicalName, PhysicalName, Type FROM #dbfiles
SELECT Position, DatabaseName FROM #bdev
OPEN files
SET @dirfile = @SourceDirBackupFiles + @fname
INSERT #bdev
EXEC ('RESTORE HEADERONLY FROM DISK = ''' + @dirfile + '''')
SET @Multiple = (select COUNT(*) from #bdev)
OPEN infiles
FETCH NEXT FROM infiles INTO @position, @dbname
if @Multiple > 1
SET @sql = 'RESTORE DATABASE ' + @DbName + ' FROM DISK = ''' + @dirfile + ''' WITH FILE = ' + cast(@position as varchar(10)) + ', MOVE '
SET @sql = 'RESTORE DATABASE ' + @DbName + ' FROM DISK = ''' + @dirfile + ''' WITH MOVE '
if @Multiple > 1
INSERT #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + ''' WITH FILE = ' + @position)
INSERT #dbfiles
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
IF @type = 'D'
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirDbFiles + @LogicalName + '.mdf'', MOVE '
ELSE IF @type = 'L'
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirLogFiles + @LogicalName + '.ldf'', MOVE '
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
SET @sql = substring(@sql,1,LEN(@sql)-6)
PRINT @sql
CLOSE dbfiles
FETCH NEXT FROM infiles INTO @position, @dbname
CLOSE infiles
CLOSE files
DROP TABLE #dbfiles

.csharpcode, .csharpcode pre
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
background-color: #f4f4f4;
width: 100%;
margin: 0em;
.csharpcode .lnum { color: #606060; }



Written by zantoro

June 13, 2013 at 5:52 pm

Posted in SQL Server

Tagged with , ,

SQL Server: Point in time restore using transaction logs

leave a comment »


if you don’t have a DBA on call at all times it might be useful to keep at hand a simplified procedure on how to restore a database pointing to a specific point in time.

The first assertion we have to make is that: “point in time restore” is only possible if the database is set to “Full or Bulk recovery” mode (DB properties -> Options).

If the DB is set to simple, your only option is restore the last Full + Differential (if any) backup.

I suggest transaction log backups should be stored on a remote server (in case your SQL Server has a hardware failure).

The reasons why you might like to restore a database to a particular Point in time can be (but not limited to):

  1. A publication was made which compromised the integrity of data (therefore you will want to restore to 1 minute before the publication had been executed).
  2. Hardware failure (in which case, if the disk is compromised, you might not be able to backup the tail of the log thus, the latest version of the db will be the last t-log backup performed.

Transaction log restore procedure:

  1. Copy all backup files to the SQL Server: the latest Full backup and all t-log backups after that.
  2. Restore the latest Full backup (paying attention to select the NORECOVERY option). I usually restore the database with a different name (e.g.: dbname_temp) and rename it to the PROD name once I’m sure everything is ok)Image
  3. Right click on the database (which should be labelled as “Recovering…”) Tasks -> Restore -> Transaction logs
  4. Here comes the tricky part: with t-log restores, you are only allowed to select backups taken after the time and date of the last restore. If you like me, store all t-logs of a day in a single file, you’ll not be able to use the UI as it doesn’t allow to pick which ever t-log you want but it automatically selects all backups before the one you selected and you’ll end up with the following error:
    The log in this backup set terminates at LSN 9386000024284900001, which is too early to apply to the database. A more recent log backup that includes LSN 9417000002731000001 can be restored.
    What you can do is:

    • take note of the number of the first valid t-log backup
    • select all t-logs
    • click “script”

    this will produce a script with one transaction for each t-log backup, delete the ones taken before the full backup and execute (using the number you’ve noted earlier).

  5. If you have more t-log backups to restore (e.g.: the failure occurred the day after the Full Backup restore) remember to set again the NORECOVERY option
  6. When you have reached the t-log that contains the point in time to which your restore should stop, select all t-logs including the one that contains your “point in time” and then select the exact “point in time” at the bottom of your “Restore Transaction log” window
  7. If your “point in time” is too recent and it has not been backed up yet, make a transaction log backup of your production database (assuming you still have access), restore all previous t-logs and then restore the latest with option “point in time” set.
  8. Also remember to change the recovery option to RECOVERY when restoring the last t-log in order to make it “ready to use”



Written by zantoro

January 14, 2013 at 1:22 pm

Posted in SQL Server

Tagged with , , ,