Archive for May, 2012

h1

Document SQL Server 2012 with BI Documenter v4.5

May 31, 2012

A while back I wrote a series of blogs on using BI Documenter, and since then there have been some updates to BI Documenter which I believe are worth mentioning. The first new feature, or set of features if you think about it, all of the existing BI Documenter features are now compatible with SQL Server 2012.  This means that as your company migrates to SQL Server 2012 Documenter will be able to make the transition with you, which I think is pretty awesome.

The second feature that I wanted to mention with v4.5 is BI Documenter files can now be outputted to a Word document (.docx). I really like this new feature simply because it’s a file format that so many people are familiar and comfortable with. You can quickly upload the file to a SharePoint document library making it easy to store and share. 

image

The one thing I had been concerned about regarding this feature was, what if I’ve installed documenter on a server and scheduled the snapshot to run from there and I don’t have Word already installed on the server, since it isn’t really needed.  I hate when I have to go and install an application on a server just so I can create the document, even though it will never be consumed there.  Well BI Documenter uses the open xml format, which eliminates the need for Word to be installed on the server to create the document. Furthermore using the open xml format allows other programs that support the .docx format can view the Word document output from BI Documenter. Go and download BI Documenter and test out these features and many more.

Advertisements
h1

Annoying DB Restore Error

May 30, 2012

The other day I was at a client site, and I was trying to set up my development environment to begin working on some SSIS packages.  I was on the next to last restore of 12 separate DB (all told, over 500GB), when the restore failed.  Up to this point I hadn’t had any issues at all.  Then this sucker popped up:

 

TITLE: Microsoft SQL Server Management Studio
——————————
Restore failed for Server.  (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText]&EvtID=Restore+Server&LinkId=20476
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: File ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SomeNewDB.mdf’ is claimed by ‘SomeNewDB_UserData'(3) and ‘SomeNewDB_Primary'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476
——————————
BUTTONS:
OK

The link provided in the first part of the message led absolutely nowhere, and the second one wasn’t much help either.  Strangely enough I read the error message a few times, and for once I was able to determine what the problem was.  Here was the key to the error message:  File ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ SomeNewDB.mdf’ is claimed by ‘SomeNewDB_UserData'(3) and ‘SomeNewDB_Primary'(1).

So what I did was script out the restore and discovered that I was trying to use the .mdf file twice.

RESTORE DATABASE [SomeNewDB]

FROM DISK = N’C:\SQLBackup\SomeNewDB.bak’

WITH FILE = 1,

MOVE N’SomeNewDB_Primary’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB.mdf’,

MOVE N’SomeNewDB_UserData’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB.mdf’,

MOVE N’SomeNewDB_Log’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB_1.ldf’,

NOUNLOAD, REPLACE, STATS = 10

GO

 

So I changed the second file to .ndf and ran the script and it seemed to work.

RESTORE DATABASE [SomeNewDB]

FROM DISK = N’C:\SQLBackup\SomeNewDB.bak’

WITH FILE = 1,

MOVE N’SomeNewDB_Primary’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB.mdf’,

MOVE N’SomeNewDB_UserData’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB.ndf’,

MOVE N’SomeNewDB_Log’

TO N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\SomeNewDB_1.ldf’,

NOUNLOAD, REPLACE, STATS = 10

GO

So if you ever run into that problem make sure your not trying to reference the .mdf file twice inside your restore statement.

h1

BIxPress Now with SQL Server 2012 Support

May 30, 2012

Last week Pragmatic Works launched it’s latest version of BIxPress with some great new features.  At the top of that list of new features was adding support for SQL Server 2012 SSIS Packages.  The Auditing Framework, Notification Framework, and Snippets you’ve come to rely on in your 2008 and 2005 environments are now available in 2012. 

Once you’ve tried out the real time monitoring console from BIxPress, it will change the way you monitor packages in your SSIS environment forever. No longer do you have to watch your package in Debug Mode inside BIDS.  Neither do you have to wait for an error notification to be sent to you if a package fails.  You can view packages that have been deployed to your server as they are running, just as if you had opened them up in bids.

image

You don’t have to worry about conflicts between some of the new features in 2012 and features in BIxPress, because they are either disabled or removed completely in this latest release of BIxPress. 

 

Check out all the new features of BIxPress here.