Configuring SSIS to non default instance

If you are running SSIS on a server without a default instance, you may have seen the following error when attempting to use Object explorer in SSMS to view packages:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer

This happens because the configuration file for SSIS has a local instance as the default configuration.

The location of the configuration file by default for SQL Server 2008 is here:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

Eg:

    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.</ServerName>
    </Folder>

To change to a named instance, insert the instance details in the xml config file:

    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>ServerName\InstanceName</ServerName>
    </Folder>

Virtual Box Error Code 0xc0000225 when installing Windows Server 2012 VM

Got the following error when attempting to Install Windows Server 2012 in Virtual box:

Windows failed to start.
A recent hardware or software change might be the cause.
Status: 0xc0000225

To fix, Open the settings for the virtual machine:

System > Enable IO APIC

The following was taken from the Virtualbox manual, chapter 3:

In any host, you should enable the I/O APIC for virtual machines that you intend to use in 64-bit mode. This is especially true for 64-bit Windows VMs. See the section called “"Advanced" tab”. In addition, for 64-bit Windows guests, you should make sure that the VM uses the Intel networking device, since there is no 64-bit driver support for the AMD PCNet card; see the section called “Virtual networking hardware”.

I probably came across this issue because i select [Other Windows] as a template for creating my VM as Windows Server 2012 was not on the list, so none of the options were pre-selected.

Apache Rewrite Rules

I recently worked on Migrating an Application from one oracle database to another whch included transferring over to the new htmldb apex.

I wanted to append a redirect rule onto a web link to add HTMLDB path to send users to the correct application.

Amending Apache Conf:

cd /u01/app/oracle/product/10.1/htmldb/Apache/Apache/conf/includes
more myappconfig

[LIVE]:/u01/app/oracle/product/10.1/htmldb/Apache/Apache/conf/includes : more myappconfig

<VirtualHost *>
        ServerName myapp.gen.mycompany.net
        ServerAlias myapp.live.mycompany.net
        ServerAlias myapp.live.dc1.mycompany.net
        ServerAlias myapp.live.dc2.mycompany.net
        ServerAdmin webmaster@mycompany.co.uk
        DocumentRoot "/u01/app/oracle/product/10.1/htmldb/Apache/Apache/htdocs"

        RewriteEngine    on
        RewriteLogLevel  0

        RewriteRule ^/pls/htmldb/f   /pls/htmldb2/f?p=152:1 [R=301]
        RewriteRule ^/$ /pls/htmldb2/f?p=152:1 [R=301]
</VirtualHost>

A rewrite rule of

RewriteRule ^/$ /pls/htmldb2/f?p=152:1 [R=301]

Will append the ‘/pls/htmldb2/f?p=152:1’  anyone who goes to any of the server alias

A rewrite rule of

RewriteRule ^/pls/htmldb/f   /pls/htmldb2/f?p=152:1 [R=301]

Will change any addresses which come in with ‘^/pls/htmldb/f’ to change to ‘/pls/htmldb2/f?p=152:1’

To Add logging to the Rewrite config add the following:

       #RewriteLog  /u01/app/oracle/product/10.1/htmldb/Apache/Apache/logs/myapp.rewrite.log
       #ErrorLog    /u01/app/oracle/product/10.1/htmldb/Apache/Apache/logs/myapp.error.log
       #CustomLog   /u01/app/oracle/product/10.1/htmldb/Apache/Apache/logs/myapp.access.log combined

–to see database connectivity for apache and htmldb

/u01/app/oracle/product/10.1/htmldb/Apache/modplsql/conf : more marvel.conf

Deinstall an Oracle Home

In my experience, the Deinstall utility which ships with the full client or db download is a bit flakey, and there is a cleaner, more reliable alternative available.

There is an External Deinstall Utility available for download from OTN here.

For full instructions on how to use the Deinstall Utility see metalink note How To Deinstall/Uninstall Oracle Home In 11gR2 [ID 883743.1]

De-installing Oracle Home using external de-install utility

1. Download and unzip de-install utility in some folder outside Oracle Home.
2. Go to the deinstall folder.
3. Invoke deinstall utility with the -home option specifying the full path of the Oracle Home you want to deinstall.

Example:

In linux

% ./deinstall -home /u01/app/oracle/product/11.2.0/dbhome_2

in Windows:

start deinstall.bat -home E:\oracle\product\11.2.0\dbhome_1

4. If you want to do a non-interactive deinstall you can use the -silent option with a parameter file.

Example:

%./deinstall -home /u01/app/oracle/product/11.2.0/dbhome_2 -silent <path for response file>

An example response file is located in /deinstall/response/deinstall.rsp.tmpl. You can modify and use it.

5. Check the logs created under <central_inventory>/logs folder for any issue.
6. Check the inventory is updated properly.
7. Check if Oracle Home folder is deleted or not. If not then you have to remove it manually.

LOG LOCATION
– deinstall tool creates logs as per this logic:

a) If there are other oracle home (OUI based) in this Host except one which you are installing then it will create log under <central_inventory>/log folder.
b) If this is last Oracle Home to be deinstalled then current central inventory will also get deleted along with deinstall , therefore in this case it will choose log locations in the following order:
– It will create log directory in current location where deinstall utility is launched.
– If current folder is not writable or remotely mounted (permission issues ) then it will create user home directory (/home/oracle) as next option.
– If all criteria above is not met then it will proceed to create log directory under user TEMP location (/tmp).

######################## CLEAN OPERATION START ########################

Enterprise Manager Configuration Assistant START

EMCA de-configuration trace file location: E:\deinstall\\logs\emcadc_clean.log

Updating Enterprise Manager ASM targets (if any)
Updating Enterprise Manager listener targets (if any)
Enterprise Manager Configuration Assistant END
Database de-configuration trace file location: E:\deinstall\logs\databasedc_clean2831.log

Network Configuration clean config START

Network de-configuration trace file location: E:\deinstall\logs\netdc_clean2832.log

De-configuring Single Instance listener(s): LISTENER

De-configuring listener: LISTENER
    Stopping listener: LISTENER
    Listener stopped successfully.
    Deleting listener: LISTENER
    Listener deleted successfully.
Listener de-configured successfully.

De-configuring Listener configuration file…
Listener configuration file de-configured successfully.

De-configuring Naming Methods configuration file…
Naming Methods configuration file de-configured successfully.

De-configuring Local Net Service Names configuration file…
Local Net Service Names configuration file de-configured successfully.

De-configuring backup files…
Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

Oracle Configuration Manager clean START
OCM clean log file location : E:\deinstall\\logs\\ocm_clean645.log
Oracle Configuration Manager clean END
Removing Windows and .NET products configuration START

Removing Windows and .NET products configuration END
Oracle Universal Installer clean START

Stopping service ‘OracleOraDb11g_home1TNSListener’ on the local node : Done

Removing service ‘OracleOraDb11g_home1TNSListener’ on the local node : Done

Failed to delete the file ‘E:\oracle\product\11.2.0\dbhome_1\BIN\oci.dll’. The file is in use.
Failed to delete the directory ‘E:\oracle\product\11.2.0\dbhome_1\BIN’. The directory is not empty.
Failed to delete the directory ‘E:\oracle\product\11.2.0\dbhome_1’. The directory is not empty.
Delete directory ‘E:\oracle\product\11.2.0\dbhome_1’ on the local node : Failed<<<<

Removing oracle home ‘E:\oracle\product\11.2.0\dbhome_1’ from PATH variable on the local node : Done

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END

Oracle install clean START

Oracle install clean END

Moved default properties file E:\deinstall\response\deinstall_noHomeName.rsp as E:\deinstall\response\deinstall_noHomeName.rsp1

######################### CLEAN OPERATION END #########################

####################### CLEAN OPERATION SUMMARY #######################
Following Single Instance listener(s) were de-configured successfully: LISTENER
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
Successfully stopped service ‘OracleOraDb11g_home1TNSListener’ on the local node
.
Successfully removed service ‘OracleOraDb11g_home1TNSListener’ on the local node
.
Failed to delete directory ‘E:\oracle\product\11.2.0\dbhome_1’ on the local node
.
Successfully removed oracle home ‘E:\oracle\product\11.2.0\dbhome_1’ from PATH variable on the local node.
Oracle Universal Installer cleanup was successful.

Oracle install successfully cleaned up the temporary directories.
#######################################################################

############# ORACLE DEINSTALL & DECONFIG TOOL END #############

‘Saving Auto Recovery Information’ Window hang in SSMS

Came across this a couple of weeks ago in that while writing some T-SQL in Management studio 2008, the SSMS window started hanging intermittently due to ‘Saving Auto Recovery Information’.

This looks to have been a similar issue in SSMS 2005 – http://connect.microsoft.com/SQLServer/feedback/details/125840/how-to-turn-off-auto-recovery-in-sql-server-management-studio-2005

As far as i know, the only way to turn off this option is to edit the registry. (Editing the registry is dangerous – All the standard caveats about editing the registry apply! Do at your own risk)

Within Regedit, go to:

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\General\AutoRecover

Edit the AutoRecover Enabled Key to be 0.

To re-enable this feature, set the key to 1

OPENQUERY using Linked Server: SQL to Oracle

Handy T-SQL for manipulating data at destination oracle db from SQL Server.

Once the Linked Server has been created, the following DML can be used:

--SELECT
select * from openquery([ORADB.MYSCHEMA], 'select * from nvdimage')

--INSERT
insert openquery([ORADB.MYSCHEMA], 'SELECT IMA_IMAGEID,IMA_IMAGE FROM nvdimage')
SELECT IMA_IMAGEID, IMA_IMAGE FROM nvdimage

--DELETE
delete openquery([ORADB.MYSCHEMA], 'select * from nvdimage')

--UPDATE
update openquery([ORADB.MYSCHEMA], 
'select column_a, column_b from myschema.table_name where pk = pk_value')
SET column_a = 'value1', column_b = 'my_value2'