Access SQL Server 2008 R2 in Single User mode

It’s happened to all of us – a SQL Server off on the outskirts of the environment is suddenly acting up and you realize that you don’t have a domain account with administrative privileges and maybe the sa account is disabled for good measure.

No problem, you think – I’ll just start the server in single user mode (with the usual trick of adding -m; to the advanced properties of the service in Configuration Manager) and then connect using sqlcmd.

But then:
C:\Users\Joe>SQLCMD -S myServer\myInstance
Msg 18461, Level 14, State 1, Server myServer\myInstance, Line 1
Login failed for user ‘Domain\Joe’. Reason: Server is in single user mode. Only one administrator can connect at this time.

Okay, fair enough, I didn’t make sure the agent was off, so I’ll just turn it off and
C:\Users\Joe>SQLCMD -S myServer\myInstance
Msg 18461, Level 14, State 1, Server myServer\myInstance, Line 1
Login failed for user ‘Domain\Joe’. Reason: Server is in single user mode. Only one administrator can connect at this time.

Ok, ok – I’ll use the -m’SQLCMD’ option so that only SQLCMD can connect in case some other services are conn….
C:\Users\Joe>SQLCMD -S myServer\myInstance
Msg 18461, Level 14, State 1, Server myServer\myInstance, Line 1
Login failed for user ‘Domain\Joe’. Reason: Server is in single user mode. Only one administrator can connect at this time.

Now this is ridiculous – no one else is connecting with SQLCMD, is this a bug?

“Hey John, check this out – I think I found a bug”
“Did you open the command prompt with Run as Administrator?”
*Opens command prompt with Run as Administrator*
C:\Windows\system32>sqlcmd -S myServer\myInstance
1> select ‘Oh goddammit’;
2> GO
————
Oh goddammit

(1 rows affected)

Basic Restore/Recover in SQL Server/Oracle

Oracle

SQL Server

 

Creating Stored Procedures SQL Server/Oracle

SQL Server (2008 R2)

Oracle

References:

http://technet.microsoft.com/en-us/library/ms187926.aspx

 

Drop object if it already exists (Oracle/SQL Server)

SQL Server

To recreate a table (drop if exists, then create)

if object_id(‘dbo.yourtable’) is not null
begin
drop table dbo.yourtable
end

create table ….

OR

To alter a table if it exists, otherwise create it – this is preferable as it preserves permissions

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N’[dbo].[vw_myView]‘))     EXEC sp_executesql N’CREATE VIEW [dbo].[vw_myView] AS SELECT ”This is a code stub which will be replaced by an Alter Statement” as [code_stub]‘ GO

ALTER VIEW [dbo].[vw_myView] AS SELECT ‘This is a code which should be replaced by the real code for your view’ as [real_code] GO

 

Oracle

declare
c int;
begin
select count(*) into c from user_tables where table_name = upper(‘table_name’);
if c = 1 then
execute immediate ‘drop table table_name’;
end if;
end;

OR

BEGIN
EXECUTE IMMEDIATE ‘DROP TABLE mytable’;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;

OBIA/OBIEE/WLS log locations

One of the most irritating tasks I deal with when troubleshooting our new OBIA stack is locating the numerous and poorly named logs that record the events for the various system components.

Weblogic:

$MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/<server name>/logs

OBIEE:

$MIDDLEWARE_HOME/instances/instance1/diagnostics/logs/<component>

 

References:

http://www.biconsultinggroup.com/oraclebiblog/2011/3/14/log-files-in-obiee-11g.html

http://oraclebisolutions.blogspot.com/2012/12/obiee-11g-file-locations.html

‘OraOLEDB.Oracle.1′ provider is not registered on the local machine

Ran into this issue recently after moving to a new Win 7 system – I’d installed the Oracle 32-bit full client and had been running Toad and SQLPLUS successfully, so I knew the client itself (and the usual slew of environment variables, etc.) was working more or less properly.

Issue occurred when I attempted to load an SSIS package from a different system on which both the 64 and 32 bit clients had been installed, so I’d initially planned on installing the 64-bit client, but after searching around a bit it turned out that was unneccesary as the fix turned out to be surprisingly simple:

C:\>cd oracle\product\11.2.0\client_32\BIN
C:\oracle\product\11.2.0\client_32\BIN>c:\Windows\System32\regsvr32.exe OraOLEDB11.dll

Expanding VirtualBox volume in Linux guest OS

Simple set of instructions placed here to remind myself how to expand volumes of the Oracle VMs I use for testing, development, playing around, and general shenanigans. While the example is using an Oracle Developer Day vmdk, this should apply equally to any other vmdk linux VM.

Many of the basic Oracle Developer Day VMs downloadable from the Oracle site and pre-installed with the latest and greatest technologies are often woefully undersized, sometimes sporting an Oracle Base volume of less than 10G. The process for expanding these volumes is relatively simple, consisting of (shutdown the VM first, of course)

1) Converting the downloaded .vmdk disk file to a .vdi (VirtualBox cannot expand a .vmdk at this time)

VBoxManage.exe clonehd "Oracle_Developer_Day-disk2.vmdk" "Oracle_Developer_Day-disk1.vd2" --format vdi
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

Note that I’m only cloning the disk2 of the download, as this is the disk on which the /home volume is mapped

2) Expanding the generated .vdi to an appropriate size for your needs

VBoxManage.exe modifyhd "Oracle_Developer_Day-disk2.vdi" --resize 40000
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%

3) Remove the vmdk from the VM in settings->storage and add the converted and expanded vdi
2013-08-29_1155
2013-08-29_1156
2013-08-29_1156_001

4) Expanding your volume to take advantage of the new space
Note that this method will be dependent on the tool you use to manage volumes as well as whether logical volumes and groups are defined or as is the case with the Developer Day VMs, just partitions.

I will be using the free download GParted to manage this:

a) Download GParted Live CD and mount it in your VM’s settings (make sure that GParted is the only CD/DVD mounted):

2013-08-30_0940

b) Start your VM and immediately hit F12 to get into the Boot Menu, select c) CD-ROM

c) You should now be in the GParted launcher, select the first option and then accept defaults (Don’t touch Keymap, language, etc.)

d) Once GParted loads, select the partition you are going to extend:

2013-08-30_0944

e) Then right-click and select resize, after which dragging the bar to add all of the new space you plan to allocate:

2013-08-30_0945

f) Hit apply, then once that’s complete restart the system

2013-08-30_0945_001

Oracle Developer Day VMs and scp

If you’ve ever tried to use scp to connect to an Oracle Developer Day VM you may have encountered an issue where, after entering the remote password for the target VM you get the message:

oracle@localhost]$ scp test.tgz oracle@192.168.56.104:/home/oracle
oracle@192.168.56.104's password: oracle 
<strong>All passwords are oracle or noted if otherwise.</strong>

And nothing happens, no files get copied, no error messages appear.

The issue is that in the Developer Day VMs the .bashrc and .bash_profile (in /home/oracle) contain commands that output to the console, and this is problematic for scp.

The simple solution is to comment out the following lines (note that this needs to be done only on the remote VM, ie. not the VM from which you are intiating scp):
/home/oracle/.bashrc

#cat ~/Desktop/README.txt
#/sbin/ifconfig | grep "inet addr"

/home/oracle/.bash_profile

#echo IP Address is:
#/sbin/ifconfig | grep "inet addr"

Oracle’s response to serious issues with their software: I don’t know, try some stuff?

The latest and greatest from those incredible fucktards at Oracle Corp. – how’s this for fun, release a ‘security update’ with a major breaking change, then when people desperately call your expensive support lines to patch your patch, give them the incorrect patch number to install. A day later, it turns out your patch on top of your security update has STILL left a major breaking change (to Materialized Views, no less, which are already buggy and a pain in the ass to deal with – note to Oracle: an Invalid Object should not be synonymous with This MV hasn’t been refreshed in a day; also, don’t fucking tell me to rewrite my SQL using Oracle’s NON ANSI compliant (+) notation because MVs have ‘problems’ with ANSI joins). Now when people contact you complaining that the patch on top of the patch that you provided is STILL NOT WORKING, give them yet ANOTHER patch. For added fun, make this patch incompatible with the previous patch, so people need to perform a patch rollback (something even Burleson calls ‘buggy’). And for 10x the fun, make this impossible to do with your ‘cloud-enabled’ pile of shit aka EM 12c and then act surprised that people are actually trying to use EM for patching in the first place.

« Older Entries