SQL Server upgrade to 2008R2 and “Wait on the Database Engine Recovery handle failed”

  By | Random errors and weird experiences
Leave a comment

Recently, I was tasked with doing an in place upgrade of SQL Server from 2005 to 2008R2.  My environment was virtualized so I had the luxury of snapshot’ing the server before I performed the upgrade.  This was fortunate, as it took me three tries to be successful. My first attempt failed with the following, non-descript message: “Wait on the Database Engine Recovery handle failed.  Check the SQL Server error log for potential causes.” Put that phrase into Google and you’ll… Continue reading →

Getting information from Oracle into SQL Server using SSIS

  By | Working with Oracle
Leave a comment

I needed to populate a SQL Server database with information from an Oracle database, but found when I created a linked server and ran the INSERT statement manually, I was getting errors regarding truncation and datatypes.  I had already run this successfully using the IMPORT data wizard (and had my application owner confirm the data) so was a bit confused.  SSMS wasn’t reporting which row or column was problematic.  Overall, my goal was to run a nightly job to run… Continue reading →

Create a linked server to an Oracle database (and not lock up the instance)

  By | Random errors and weird experiences, SQL Server w/o the GUI, Working with Oracle
6 Comments

I’ve created numerous linked servers to Oracle from SQL Server and what should be a straightforward process using SSMS has caused me some headaches.  For whatever reason, when using the GUI, I’ve actually locked up the entire SQL Server instance and had to restart the service when trying to create a linked server to Oracle.  This is not good when working in production. The following will outline the do’s and don’ts for this process. To begin: download, install and configure… Continue reading →

SQL Server service won’t start after Reporting Services and Analysis Services install

  By | Random errors and weird experiences
Leave a comment

I received an error after trying to start the SQL Server service for my 2005 default instance:   “WMI Provider Error [call to WMI Provider returned error code: 0x800742a2]”   In the event viewer, I got:   “initerrlog: Could not open error log file ‘D:\sqlserverdata\MSSQL.1\MSSQL\LOG\ERRORLOG’. Operating system error = 5(Access is denied.).”   A search of the web brought me a few different hits for solutions, one being a Microsoft KB: http://support.microsoft.com/kb/955496 Unfortunately, this wasn’t exactly my problem, but it… Continue reading →

Get information about your databases

  By | SQL Server w/o the GUI
Leave a comment

Let’s begin with the catalog view ‘sys.databases’. It has lots of useful information: — find the name, owner, create date and recovery mode of all user databases SELECT name, SUSER_SNAME(owner_sid), — SUSER_SNAME() returns the login when passed a ‘sid’ create_date, recovery_model_desc FROM sys.databases WHERE database_id > 4;– find the name, owner, create date and recovery mode of all user databases SELECT name, SUSER_SNAME(owner_sid), — SUSER_SNAME() returns the login when passed a ‘sid’ create_date, recovery_model_desc FROM sys.databases WHERE database_id > 4;… Continue reading →

Working with SQL Server logins

  By | SQL Server w/o the GUI
Leave a comment

SQL Server ‘logins’ are what’s known as a server level ‘principal’. From BOL, “Principals are entities that can request SQL Server resources.” There are two types of logins, one where the credentials are stored in SQL Server and one where the credentials come from Windows, either locally or through Active Directory. So let’s begin with a list of our logins. Not surprising, the catalog view ‘sys.server_principals’ works nicely: — List logins on the instance SELECT name, sid, type_desc, create_date, default_database_name… Continue reading →