Microsoft SQL Server

Reset Default Database from Command Line in SQL Server

During a recent consolidation exercise I was migrating one database at a time and then taking the old copy offline, unfortunately one of the DBs had (un-knowingly) been my default database and next time I tried to log in I received the dreaded “Cannot open user default database. Login Failed.” message. 

I tried one of the workarounds proffered by Microsoft for this exact problem only to receive exactly the same error message, thankfully I then came across this excellent post on SQL Server Nation which explains the entire issue from start to end and offers a couple of fixes based on your particular scenario. 

In my case it was to execute the sp_defaultdb stored procedure using the OSQL utility, for example…

osql -Sservername -E -dmaster -Q”sp_defaultdb ‘username’,'master’”

Sincere thanks to Brandon Galderisi from SQL Server Nation for the fix.

How to Enable xp_cmdshell in SQL Server

There’s a great little feature of SQL Server that allows you to execute command-line statements, allowing you to run batch files, get directory listings or call executables.  The feature is accessed via the xp_cmdshell Extended Stored Procedure, the syntax is pretty simple and looks like this…

Register an Extended Stored Procedure in SQL Server 2008 R2

Aside from those integrated into SQL Server (e.g. xp_cmdshell) it’s not often I come across the need to use Extended Stored Procedures but an application I installed recently required that I add their custom procedure to the SQL Server housing the application data.  Having been years since I had last added one it took a little figuring out since I would’ve expected to find it in Management Studio under “Server Objects” or “Management” but it’s somewhere entirely different. 

To add/register an Extended Stored Procedure you’ll need to right-click on…

Databases >> System Databases >> master >> Programability >> Extended Stored Procedures

…and select “New Extended Stored Procedure”.

Next you’ll have to provide a (this will be the name the procedure is called by) and the path to the DLL, you can browse to the the .dll file by clicking on the elipsis. 

The next step is vital since at the moment the only people able to actually use the Extended Stored Procedure would by sysadmins.  All you have to do to remedy that is click on “Permissions”, click “Search”, type “public” in the text box and click OK as follows…

All you have to do now is tick the “Execute” permission to allow the ‘public’ users of the database (effectively everybody) to be able to use the procedure. 

The completed dialog box should look something like this…

Then click “OK” and all should be well.

How to Edit DTS Packages in SQL Server 2008 R2

I’m on the verge of migrating a few DTS packages from a legacy application, there’s no point in re-writing them since the application will be phased out within the next 6-12 months so I figure that if I can migrate them ‘as is’ I’ll save myself some time and a few headaches.  SQL Server Management Studio (SSMS) provides a menu option under Management to Open, Import or Migrate DTS packages…

Initially if you try to use these features this you may receive the error:

“SQL Server 2000 DTS Designer Components are required to edit DTS Packages.  Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature.  (Microsoft.SqlServer.DtsObjectExplorerUI)”

To get around this you’ll need to follow a couple of steps:

1. Install the Microsoft SQL Server 2005 Backward Compatibility Components – you can get these as part of the Microsoft SQL Server 2008 Feature Pack (for some reason it’s not included in the 2008 R2 Feature Pack).  [x86] [x64] [ia64].
2. Install the Microsoft SQL Server 2000 DTS Designer Components – since DTS support was depracated in SQL Server 2008 you have to get these from the Microsoft SQL Server 2005 Feature Pack.  [SQLServer2005_DTS.msi]
3. Ensure that the PATH Environment Variable lists the SQL 2000 directory before the 2008 R2 one – I have to thank Ramoji Ryali’s Fundas for this solution, the correct version of sqlgui.dll will be used as long as:”C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\”
   is before:
   “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\”

After that, everything seemed to work fine – I did happen to log off and log back on again at some point so I’m not 100% sure if that was required or whether it was just coincidence – I definitely did not have to reboot though.

How to Enable 32-bit Runtime Mode in SSIS

If you’re just beginning to play with SSIS on a 64-bit box you’re pretty likely to hit a roadblock if your data sources have 32-bit ODBC drivers.  The error will become obvious immediately when you come to execute your package in BIDS (Business Intelligence Development Studio)…

The ODBC Driver Manager error reported in the Output window will be: ”The specified DSN contains an architecture mismatch between the Driver and Application”.  Thankfully this is a pretty easy one to sort out, all you have to do is…

1. Right-click on the Project name in the Solution Explorer and select Properties…

   

2. Choose “Debugging” in the left-hand pane…

   

3. Change the Run64BitRuntime value to False (by double-clicking on it). 

   

   For reference the help-text states that if the 64-bit SSIS runtime is not installed this setting is ignored so if you’re working on a 32-bit box in all liklihood you wouldn’t experience this issue. 

   

4. Click OK and re-run your package. 

SQLBits 7 – Saturday Conference Rundown

Starting my third day at SQLBits with a hat-trick of talks on technologies I’m unfamiliar with was a bit of a head-bender but an enjoyable one nonetheless. The first talk I chose was a great overview of how to use completely free tools (SQL Server Express 2008 R2, among others) with completely free spacial data (Ordnance Survey’s Open Data) to create spatial reports in Reporting Services. Since it’s not an area I’m working in at the moment I didn’t get any major take-aways but I do have an understanding of what’s possible and how to go about it – if you’re in the same boat you should check out FWTools, Shape2SQL and Grid InQuest.

The second talk was Matt Whitfield‘s ‘CLR Demystified’ and not being a developer I’ll admit that much of it went over my head but I get the basic principles and most importantly I know what’s possible and where I might make use of CLR. The most interesting avenues for me are the ability to write custom aggregate functions and define custom data types – I was also impressed with the opportunity to increase performance in certain text processing / forward log parsing situations.

The next talk I attended was a gentle introduction to PowerShell and James Boother did a good job of showing where it might be useful for admin tasks, in particular the example of purging old backup/log files based on age lit my eyes up. There was also a demo of PowerShell authenticating with Twitter and posting tweets as admin alerts, alas the demo failed but that could well have been a timeout on the Uni’s WiFi network or just plain old demo-gremlins. I must say thou that I still can’t view PowerShell with some sense of disappointment because with all it’s flexibility and power it’s so damned wordy and many of the tasks it performs could be achieved with less code that a good old Bash script.

As with Friday I attended Quest‘s lunchtime session run by Kevin Kline, Ian Kick, Brent Ozar and Buck Woody – they’re some of the most experienced guys in the SQL community and when you get them together they’re funny as he’ll too so I was both entertained and informed in their myth-busting quiz.

My first afternoon talk was Gary Short’s session on NoSQL which predictably sparked a few polite but irate rebuttals from argumentative DBAs but the session itself was an excellent whistle-stop tour of the predominant NoSQL technologies and use cases. I was encouraged to hear from someone experienced in the field that nobody has quite put together all the pieces to hook up BI tools (that traditionally expect relational/dimensional models or OLAP sources) to the NoSQL back-ends, it’s a shame since I might need to do so pretty soon – I guess I’m going to have to get my hands dirty then!

The final talk was from Kevin Kline of Quest who covered SQL Injection, it was an informative talk that gave me pause for thought about a couple ‘best practices’ that I probably ought to harden a little. Kevin recommended a few tools that I’ll definitely be checking out at some point, notably:

• HP Scrawlr
• URLScan
• Source Code Analyser for SQL Injection
• Assessment and Planning Tool
• Discovery Wizard

And a few handy sites/articles:

• SQLSecurity.com
• MSDN ms161953
• MSDN ms998271
• ferruh.mavituna.com

Once more it was a great conference and the free day was every bit as good as the paid day, I can honestly say that I walked out of the event already looking forward to the next one.