Monday, 17 November 2008

If you are working with Sql Server Management Studio 2008 and trying to manage a database on a hosted server, you probably have run into this error when opening the Databases node in Object Explorer.

Error 916 - Server principal "userx" is not able to access the db "dbwhatever" under current security context

Searching around we see it's a known issue but no real workaround. The only clue I had of what was going on was that the reason this was happening was because of the Object Explorer Details view of the databases which gives a ton of information on each database.  Unfortunately, in a shared environment, you don't have permissions to gather this data from most databases.  The error isn't handled cleanly and I think it has something to do with a setting for the Auto_Shutdown...but regardless, the bug exists and seemingly no fix for us using shared servers.

However, I've found a workaround!!  After the error comes up, click ok through the error.  You will still get access to the system tables.  This allows you to click on the Databases node to bring up the Object Explorer Details screen.  From there you can right-click on the header to change the viewable columns.  Deselect everything except for the name field.  Refresh the view and all the tables will come up.  In a shared environment, I could care less about just about everything but the name, so I'm not really losing anything. 

Problem solved, error worked around and we don't have to wait on and hope that MS does something about the error.

Monday, 17 November 2008 11:52:02 (Eastern Standard Time, UTC-05:00) | Comments [0] | SQL Server#
Friday, 21 March 2008

OK....this may be well known, but I seem to forget it all the time.   If while browsing data you want to set a field to NULL, Ctrl-0 will do the trick.  Very simple and easy.  I know it works in Management Studio 2005, but I think it works in Enterprise Manager 2000 as well.

Friday, 21 March 2008 14:55:41 (Eastern Daylight Time, UTC-04:00) | Comments [0] | SQL Server#
Monday, 08 January 2007

Again, another post with something that seems obvious at the moment and something I've done many times and it works like a charm everytime.  Last week I ran into a situation where I was performing a SqlBulkCopy on some data up to a Sql Server on a shared host.  This process has run nightly for over a year, however Friday the structure changed slightly so my client called me in to make the changes.  Changes are made and the process runs fine. 

Moving forward about 6 hours....suddenly an exception is generated in the SqlBulkCopy (don't recall the exact wording on the exception) which seems to indicate that either my structures between source (Access) and destination are out of sync or perhaps there is some corrupt data.  Since it worked fine previously in the day, I opted towards looking at the latter.

Quick once-over on the source data didn't show any problems and after doing some more preliminary appeared to be fine, but there were tons of records and I'm sure I missed something.  So what was my secret tool in solving this problem...

   Select Top 10 from myData

Just simply ran the the above select when selecting my source data, changing the Top XX to different values to zero in on the record that was causing the error.  Once I narrowed it down to that one record, it was a simple matter to scan the data looking for a problem.  Within seconds, I found the problem (calculated field which had a division by zero error), fixed the field and all was well.

Monday, 08 January 2007 05:59:57 (Eastern Standard Time, UTC-05:00) | Comments [0] | SQL Server#
Admin Login
Sign In
Pick a theme: