MSPSS: is there life after the helpdesk?

sharing solutions to uncommon IT problems

Archive for the ‘SQL Server’ Category

Querying a MS Access 2003 database from SQL Server 2008

with one comment


Hello,

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.

HTH,

Roberto.

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:

SET NOCOUNT ON
DECLARE @SourceDirBackupFiles VARCHAR(200) = 'D:\Migration\'
DECLARE @DestDirDbFiles VARCHAR(200) = 'D:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA\'
DECLARE @DestDirLogFiles VARCHAR(200) = 'D:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\DATA\'
DECLARE @fname VARCHAR(200)
DECLARE @dirfile VARCHAR(300)
DECLARE @LogicalName NVARCHAR(128)
DECLARE @PhysicalName NVARCHAR(260)
DECLARE @type CHAR(1)
DECLARE @DbName sysname
DECLARE @position INT
DECLARE @sql NVARCHAR(1000)
DECLARE @Multiple 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)
)
 
CREATE TABLE #bdev(
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
 
DECLARE files CURSOR FOR
SELECT fname FROM #files
DECLARE dbfiles CURSOR FOR
SELECT LogicalName, PhysicalName, Type FROM #dbfiles
DECLARE infiles CURSOR FOR
SELECT Position, DatabaseName FROM #bdev
 
OPEN files
FETCH NEXT FROM files INTO @fname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dirfile = @SourceDirBackupFiles + @fname
TRUNCATE TABLE #bdev
INSERT #bdev
EXEC ('RESTORE HEADERONLY FROM DISK = ''' + @dirfile + '''')
 
SET @Multiple = (select COUNT(*) from #bdev)
OPEN infiles
FETCH NEXT FROM infiles INTO @position, @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
if @Multiple > 1
BEGIN
SET @sql = 'RESTORE DATABASE ' + @DbName + ' FROM DISK = ''' + @dirfile + ''' WITH FILE = ' + cast(@position as varchar(10)) + ', MOVE '
END
ELSE
SET @sql = 'RESTORE DATABASE ' + @DbName + ' FROM DISK = ''' + @dirfile + ''' WITH MOVE '
 
TRUNCATE TABLE #dbfiles
 
if @Multiple > 1
BEGIN
INSERT #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + ''' WITH FILE = ' + @position)
END
ELSE
INSERT #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''')
 
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
 
WHILE @@FETCH_STATUS = 0
BEGIN
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
END
 
SET @sql = substring(@sql,1,LEN(@sql)-6)
PRINT @sql
CLOSE dbfiles
 
FETCH NEXT FROM infiles INTO @position, @dbname
END
CLOSE infiles
 
FETCH NEXT FROM files INTO @fname
END
CLOSE files
DEALLOCATE files
DEALLOCATE dbfiles
DEALLOCATE infiles
 
DROP TABLE #files
DROP TABLE #bdev
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; }

HTH,

Roberto.

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 »


Hello,

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”

HTH,

Roberto.

Written by zantoro

January 14, 2013 at 1:22 pm

Posted in SQL Server

Tagged with , , ,

SQL Server: not able to access the database under the current security context

leave a comment »


Hello,

lately I had to find a quick, reliable way to track down some database access failures we were experiencing after we had moved a database from a remote site to our central database server (before it was referenced as linked_server and thus all connections were using the same username).

Several applications reported the following error: “The server principal “username” is not able to access the database “databaseName” under the current security context.”

After a bit of searching I found out that I could use SQL Profiler to log each failure:
In the events section select “User Error Message”.
In the column filter enter “equal to” 916.
Remember to show all columns and make sure that all columns are selected.
Save the output in a table and query it regularly (or you could even set up a insert trigger that sends you an email).

HTH,
Roberto.

Written by zantoro

October 3, 2012 at 12:27 pm

Posted in SQL Server

Windows 2008 R2, Vmware and Driver Locked memory

with 4 comments


Hello,
we have had serious performance issues on a server for some time now.
This server is heavily used and we always assumed the problems we were having were simply due to the coexistence of 3 memory and CPU hungry applications on the same box (SQL Server, IIS and Coldfusion).
We tried everything to tweak resources so that one process would not overcome the others.
Nothing seemed to work.
One thing that always bugged us was that the physical memory would go rapidly exhausted although we had increased the memory progressively to 16Gb.
What’s even more strange is that the sum of the memory of each process was far from the “Physical Memory in use” we saw in Task Manager (or process explorer).
I also tried to limit memory usage for both Coldfusion and SQL Server to 5Gb each with no success.
Then I found this application from sysinternals (M. Russinovich rocks): RAMMap
This app gives you a detailed overview of the memory allocation and strangely I had 10Gb allocated to “Driver Locked”.
Google is my friend and I found out that this is usually due to a misconfiguration of VMware.
Apparently in VMware there are two config for each resource:
1. The Hardware configuration: the actual configuration of the VM where you define the physical resources allocated (in my case I had 16Gb for RAM).
2. The resource allocations: here you can play with the VMWare resources to limit the resources configured in point 1.
I found out that in the resource allocations tab we had limited memory to 4096Mb which means that for as much as we added memory to the VM, the available memory for that machine would be always the same and the rest would be “locked” in the vmware tools driver.
I opened that tab (you’ll find it in the VM settings) and selected unlimited and now task manager shows 13Gb of available memory.

I hope this helps,
Roberto.

Written by zantoro

September 15, 2012 at 2:04 pm

T-SQL: Grant access rights to enable a user to send email with SP_OACreate

leave a comment »


I know, it is not the best practice and it will be soon discontinued by MS but it is good if you, like me, have many legacy apps and no time to do what’s right (update the code).

Here’s the script to grant the necessary rights (remember to grant public access to the user before executing the script:

GRANT EXECUTE ON sp_OASetProperty TO UserX;
GO
GRANT EXECUTE ON sp_OASetProperty TO UserX;
GO
GRANT EXECUTE ON sp_OAGetErrorInfo TO UserX;
GO
GRANT EXECUTE ON sp_OAMethod TO UserX;
GO
GRANT EXECUTE ON sp_OASetProperty TO UserX;
GO
GRANT EXECUTE ON sp_OACreate TO UserX;
GO
GRANT EXECUTE ON sp_OADestroy TO UserX;
GO

Written by zantoro

September 5, 2012 at 1:58 pm

Posted in SQL Server

Fix all Orphaned users in selected databases (or all DBs if you want)

leave a comment »


Hello,

I know someone else out there already wrote this procedure but I needed it and I couldn’t find it online so I re-wrote it (and hopefully improved it).

Frankly I wouldn’t use it on a COMPLEX production environment (for obvious security reasons), I wrote it to help me in a migration through 4 DEV environments.

If you think the code can be improved, do not hesitate to post a comment.

Create the following procedure in the master DB:

CREATE PROCEDURE SP_FixOrphans
AS
BEGIN
 SET NOCOUNT ON;
DECLARE @username VARCHAR(30);
CREATE TABLE #tempTable (sUserName Varchar(30), UserSID varchar(200));
INSERT INTO #tempTable (sUserName, UserSID) EXEC sp_change_users_login 'report';
DECLARE db_cursor CURSOR FOR select sUserName from #tempTable;
OPEN db_cursor ;
FETCH NEXT FROM db_cursor INTO @username  ;
WHILE @@FETCH_STATUS = 0  BEGIN   ;
 EXEC sp_change_users_login 'update_one', @username, @username;
FETCH NEXT FROM db_cursor INTO @username  ;
END   ;
CLOSE db_cursor   ;
DEALLOCATE db_cursor  ;
DROP table #tempTable;
END
GO

Then you can execute it on every DB or some of them by executing this:

 
declare @DBroot varchar(20)
declare @DBRootLen varchar(20)
declare @cmd1 varchar(200)
declare @cmd2 varchar(200)
set @DBroot = 'YOURDB' -- Root of databases whose logins you want to fix
set @DBRootLen = len(@DBroot)
set @cmd1 = 'if substring(''?'',1,' + @DBRootLen + ') = ''' + @DBroot + ''' print ''?''';
set @cmd2 = 'if substring(''?'',1,' + @DBRootLen + ') = ''' + @DBroot + ''' BEGIN ' + CHAR(13);
set @cmd2 = @cmd2 + 'USE ? ' + CHAR(13);
set @cmd2 = @cmd2 + 'exec SP_FixOrphans ;' + CHAR(13);
set @cmd2 = @cmd2 + 'END ;' + CHAR(13);
exec sp_MSforeachdb @command1=@cmd1,@command2=@cmd2
 
Good luck.

Written by zantoro

August 18, 2010 at 8:14 am

Posted in SQL Server