sql server split query string to key / value pairs

Use Case

Split string like 'applicationId=1&objectId=2' to pairs applicationId = 1, objectId=2 in sql server stored proc

Solution

Following solution works fine for me:


CREATE FUNCTION [dbo].[fn_HashSplit]
(
@String varchar(max),  
@Delimiter1 char(1),
@Delimiter2 char(1)
)
RETURNS
@temptable TABLE (
name varchar(8000) COLLATE Latin1_General_CI_AS_KS_WS,
value varchar(8000) COLLATE Latin1_General_CI_AS_KS_WS
)
AS
BEGIN

declare @idx int      
    declare @slice varchar(8000)      

declare @idx2 int      
    declare @name varchar(8000)      
    declare @value varchar(8000)      
      
    select @idx = 1      
    if len(@String)<1 or @String is null  return;
      
    while @idx!= 0      
    begin      
        set @idx = charindex(@Delimiter1,@String)      
        if @idx!=0      
            set @slice = left(@String,@idx - 1)      
        else      
            set @slice = @String      
          
        if(len(@slice)>0)
begin
select @idx2 = charindex(@Delimiter2, @slice)
if @idx2 != 0
begin
set @name = left(@slice, @idx2-1)
set @value = right(@slice, len(@slice) - @idx2)
insert into @temptable(name, value) values(@name, @value)
end
            end
  
        set @String = right(@String,len(@String) - @idx)      
        if len(@String) = 0 break        
end
  
return      
END

Migrate vm from vmware workstation to vmware esxi

Use Case

Migrate VM from VMWare Workstation v 7.xx to VMWare ESXi v 4.xx

Solution

1. Install and configure VMWare ESXi v4.xx using instructions from official site. The only one thing, I have to change is to allow CPU Virtualization feature in BIOS.

2. Enable local and / or remote administration shell using instructions here: http://itknowledgeexchange.techtarget.com/it-consultant/enabling-ssh-on-a-vmware-esxi-server/

3. Copy your VMs files (*.vmdk)
For weird reason I was not been able to mount additional SATA drive with old virtual machines. The only way to copy files was to use scp and copy files over network.

4. Convert old VMs using vmkfstools utility.
 vmkfstools -i "old_virtual_machine_disk.vmdk" -d thin "new_virtual_machine_disk.vmdk"

5. Create new VM in advanced mode and specify existing drive to newly converted disk.

LDIFDE - Export / Import data from Active Directory

Use Case

Need to migrate Active Directory from one server to another.

Solution

Follow great article at: LDIFDE - Export / Import data from Active Directory - LDIFDE commands

spring.net activemq integration

Use Case

Attach to ActiveMQ messaging system from c#.

Solution

Follow great example at: http://remark.wordpress.com/articles/messaging-with-net-and-activemq/

Ext.net custom panel header

Use Case

You need to place custom controls (e.g. text box, drop down list, buttons, etc) on your panel header.

Solution

As was explained in http://thelampposts.blogspot.com/2008/05/custom-extjs-header-buttons.html, we can simply hide standard panel header and create toolbar element and give it correct style, Cls="x-panel-header".

Dictionary xml serialization

Use Case

Serialize dictionary (preferrable generic) to xml.

Solution

Thanks to Paul Welter for solution: http://weblogs.asp.net/pwelter34/archive/2006/05/03/444961.aspx

using System;

    using System.Collections.Generic;

    using System.Text;

    using System.Xml.Serialization;

    [XmlRoot("dictionary")]

    public class SerializableDictionary<TKey, TValue>

        : Dictionary<TKey, TValue>, IXmlSerializable

    {

        #region IXmlSerializable Members

        public System.Xml.Schema.XmlSchema GetSchema()

        {

            return null;

        }

        public void ReadXml(System.Xml.XmlReader reader)

        {

            XmlSerializer keySerializer = newXmlSerializer(typeof(TKey));

            XmlSerializer valueSerializer = newXmlSerializer(typeof(TValue));

            bool wasEmpty = reader.IsEmptyElement;

            reader.Read();

            if (wasEmpty)

                return;

            while (reader.NodeType != System.Xml.XmlNodeType.EndElement)

            {

                reader.ReadStartElement("item");

                reader.ReadStartElement("key");

                TKey key = (TKey)keySerializer.Deserialize(reader);

                reader.ReadEndElement();

                reader.ReadStartElement("value");

                TValue value = (TValue)valueSerializer.Deserialize(reader);

                reader.ReadEndElement();

                this.Add(key, value);

                reader.ReadEndElement();

                reader.MoveToContent();

            }

            reader.ReadEndElement();

        }

        public void WriteXml(System.Xml.XmlWriter writer)

        {

            XmlSerializer keySerializer = newXmlSerializer(typeof(TKey));

            XmlSerializer valueSerializer = newXmlSerializer(typeof(TValue));

            foreach (TKey key in this.Keys)

            {

                writer.WriteStartElement("item");

                writer.WriteStartElement("key");

                keySerializer.Serialize(writer, key);

                writer.WriteEndElement();

                writer.WriteStartElement("value");

                TValue value = this[key];

                valueSerializer.Serialize(writer, value);

                writer.WriteEndElement();

                writer.WriteEndElement();

            }

        }

        #endregion

    }

Creating custom composite activity

Use Case

Create custom composite activity to be used in sequential workflow.

Solution

Follow excellent guide at: http://msdn.microsoft.com/en-us/library/ms734576(VS.90).aspx and http://msdn.microsoft.com/en-us/library/aa480200.aspx

Pay your attention on how Execute and OnEvent methods are handled. Just filling collection of Activities in constructor won't force child activities to work.

View oracle objects compile errors

Use Case

Check if there were any errors during oracle objects compilation (procedures, functions, packages, triggers, or package).

Solution

Oracle stores information about errors occured during objects compilation in system tables (e.g. USER_ERRORS). To view error run following sql statement:

select * from sys.user_errors where name = 'object name' and type = 'object type'

Please refer following resources for more information:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4315.htm

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1052.htm

and

http://www.techonthenet.com/oracle/sys_tables/index.php

asp.net dynamic embedded resources

Use Case

You have an embedded css stylesheet (main.css) and background image (bg.gif). In css you would like to have smth like:


#header {
    background : #7F99BE url('reference to bg.gif') repeat-x scroll center center;
    border     : solid 1px #122A59;
}

Solution

First, make sure, that main.css and bg.gif are marked as Embedded Resources in property editor.

Then, make sure that they're registered to be included into assembly manifest using commands below:

[assembly: WebResource("XXX.YYY.Content.css.main.css", "text/css", PerformSubstitution=true)]

[assembly: WebResource("XXX.YYY.Content.images.bg.gif", "image/gif")]

Note, 3rd parameter PerformSubsitution=true.

Now in main.css you can use reference to bg.gif as shown below:

#header { 

    background : #7F99BE url('<%= WebResource("XXX.YYY.Content.images.bg.gif") %>') repeat-x scroll center center; 

    border     : solid 1px #122A59;

}

For more information, refer to excellent article about embedding resources into assembly at: 

http://aspalliance.com/850

spring.net send messages to multiply locations using MessageQueueGatewaySupport

Use Case

My service utilizing MessageQueueGatewaySupport need to send messages to multiply locations (queues) which are not known at design time.

Solution

Prepare generic application context configuration (see post: spring.net dynamic configuration. Configure IApplicationContext from GenericApplicationContext and file stream. for more details).

On runtime, you can programmatically register queues as shown below:


            // Get template
            var template = Context["messageQueueTemplate"] as MessageQueueTemplate;

            template.MessageQueueFactory.RegisterMessageQueue(requestQueueName,
                 delegate
                 {
                       var mq = new MessageQueue();
                       mq.Path = requestQueuePath;
                       return mq;
                 }
            );



            template.ConvertAndSend(requestQueueName, request, delegate(Message message)
            {
                   // do smth with your message
                   return message;
            });

spring.net dynamic configuration. Configure IApplicationContext from GenericApplicationContext and file stream.

Use Case

Configure ApplicationContext from stream. This can be helpful when you don't know exact object configuration on designtime and have to create application context on runtime.

Solution

Spring.net gives us ability to manually configure configuration sources.

IApplicationContext ctx = null;
using (var sr = File.OpenRead("some config file")) {
               ctx = new GenericApplicationContext();
               var objectDefinitionReader = new XmlObjectDefinitionReader(ctx);
               objectDefinitionReader.LoadObjectDefinitions(new InputStreamResource(sr, "MsmqSendServiceGatewayConfiguration"));

               ctx.Refresh();

}

var svc = ctx["myService"] as IMyService;

The most important line in the code above is: ctx.Refresh(); By doing this, ApplicationContext is propagates to all objects supporting IApplicationContextAware interface. Without this line, invoking any object from context may throw error, because they require ApplicationContext to be defined on that stage.

You may easily change the code above to get configuration from dynamically built xml, database and any other source.

Understanding Public Clouds: IaaS, PaaS, & SaaS

Excellent article can be found at: http://www.keithpij.com/Home/tabid/36/EntryID/27/Default.aspx

IIS7 - static content is not displayed

Use Case

On fresh Win7 Professional box, static content (images, css, js) is not displayed.

Solution

By default, Static Content Service is turned off in IIS7 installation. Make sure, that you have that service enabled at: Turn Windows Features On and Off > Internet Information Service > World Wide Web Services > Common Http Features > Static Content.

For more information, refer to http://weblogs.asp.net/anasghanem/archive/2008/05/23/don-t-forget-to-check-quot-static-content-service-quot-in-iis7-installation.aspx

System.Transactions with ODP.NET. System.AccessViolationException: “Attempted to read or write protected memory. Data provider internal error(-3000).

Use Case

Oracle 10g
Oracle Data Provider for .NET 2.0 11.2.0.1.2

VS 2008, C#

When trying to open connection to Oracle database inside Distributed Transactions, weird exception appears:

System.AccessViolationException: “Attempted to read or write protected memory. This is often an indication that other memory is corrupt.”

another people reported following exception:

Data provider internal error(-3000) [System.String]

Steps to reproduce.

1. Use explicit CommittableTransaction

            var factory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
            string constr = 'my connection string';

            using (var transaction = new CommittableTransaction())
            {
                using (var conn = factory.CreateConnection())
                {
                    conn.ConnectionString = constr;
                    conn.Open();
                    conn.EnlistTransaction(transaction); <- Exception thrown here
                }
            }

2. Use implicit TransactionScope

            var factory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");

            string constr = 'my connection string';

            using (var scope = new TransactionScope())

            {

                using (var conn = factory.CreateConnection())

                {

                    conn.ConnectionString = constr;

                    conn.Open(); <-- Exception thrown here

                }

            }

Solution

After some time spent to reading documentation and googling, following things became apparent.

First of all, distributed transactions are handled by Distributed Transaction Coordinator service, so, MS DTC service must be running (in major configuration it's start mode is Manual)

Second, behaviour of odp.net depends on exact version of Oracle server and client versions and .net framework version. Recipe shown here wasn't checked on all possible combinations.

Third, there are some special connection string parameters needs to be considered.

As follows from http://download.oracle.com/docs/cd/E11882_01/win.112/e12249/featADO20.htm#CJAEBFEB, when first connection is opened to Oracle Database 10g (or higher), a local transaction is created. When a second connection is opened, the transaction is automatically promoted to a distributed transaction.

Connections, created from transaction context, must have "Promotable Transaction" setting set to "promotable".

Also, if application use System.Transactions, it is required that the "enlist" connection string attribute is set to either "true" (default) or "dynamic".

So, in my case, all what I had to do is just make sure that correct Promotable Transaction and Enlist settings are set.

Simplest way to create user in oracle

Use Case

You need to create new user in Oracle database with ability to connect and manage data objects

Solution

The simplest form is:

create user ecxtenant102 identified by ecxtenant102_secret

where ecxtenant102_secret is user's password

For more comprehensive explanation, please refer to http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_8003.htm

I also had to assign two roles to that user.

grant connect, resource to ecxtenant102

Oracle Sql Developer migrate connectons

Use Case

You've reinstalled OS from a scratch and need to import your Oracle Sql Developer settings

Solution
Copy file "I:\Users\xxx\AppData\Roaming\SQL Developer\system1.5.4.59.40\o.jdeveloper.db.connection.11.1.1.0.22.49.48\connections.xml" to appriate location on your system drive

where I: - your backup disk drive, xxx - your username

More information can be found at: http://www.webxpert.ro/andrei/2008/06/12/oracle-sql-developer-import-connections/

Outlook 2010 gmail imap synchonization issue

Use Case

After adding Gmail account via IMAP, Outlook starts Synchronization process which never ends.

Solution

Easiest solition I've found at: http://social.technet.microsoft.com/Forums/en-US/officesetupdeploy/thread/603cd05f-4f0c-4c39-ab77-6789cfac0577

1. File > Options > Advanced > Send / Receive
2. For IMAP accounts set "Download headers for subscribed folders" (under "Receive mail items" checkbox)

Prevent termination of shell command executing during logoff

Use Case

You've started a process that will take a long time, and didn't specify nohup in front of it, and don't want your command to be terminated on logoff.

Solution

Press Ctrl + Z - this will suspend the job
Enter disown -h %xxx, where xxx is the job number you get after suspending the job
Execute bg to put the job in the background
Disconnect from the shell

More information about bash commands can be found here: http://linux.about.com/library/cmd/blcmdl1_disown.htm

ORA-19809: limit exceeded for recovery files

Use Case

After some database intensive operations following error occured:

ORA-16038: log 1 sequence# 572 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/opt/oracle/oradata/orcl/redo01.log'

Solution

Quick googling gaves a tip: The flash recovery area is full (http://www.dba-oracle.com/t_ora_19809_limit_exceeded_for_recovery.htm)

To verify this, run following sql statement:

select * from v$recovery_file_dest;

In my case space_used was much greater than space_limit (that was self-efficient Oracle 10g VM).

To fix the problem, we can either increase flash recovery area size or clean / backup files from it.

If you have sufficient disk space available you can use following query (IMPORTANT! New flash recovery area size must be greater than space_used in previous query):

conn system/oracle@orcl
alter system set db_recovery_file_dest_size=10G scope=both
alter database open;

To remove files we have to use RMAN. If we will simply remove files from the host operating system, disk space will be emptied but Oracle won't be aware of it. So, something like that can be done:

rman target / catalog sys/oracle 

run { allocate channel t1 type disk; 

backup archivelog all delete input format '/<temp backup location>/arch_%d_%u_%s'; 

release channel t1; 

}

For more information about RMAN in Oracle 10g, follow
http://download.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmintro.htm
and
http://www.oracle-base.com/articles/10g/RMANEnhancements10g.php

sending Enter keystoke to console application process

Use Case.

One console application (A) should open another console application (B) in separate process and send Enter keystroke when work is done.

Solution.

Application B is simple WCF server hosted in console application, which looks like:


        static void Main(string[] args)
        {
            try
            {
                using (ServiceHost host = WorkflowRuntimeHostFactory.Create(typeof(MyService)))
                {
                    host.Open();

                    Console.WriteLine("Press any key to stop server...");
                    Console.ReadLine();

                    host.Close();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                Console.ReadLine();
            }
        }

Application A looks like this:

            string returnvalue = string.Empty;

            string args = "";

            string fileName = "path to application A";

            ProcessStartInfo info = new ProcessStartInfo(fileName);

            info.UseShellExecute = false;

            info.Arguments = args;

            info.RedirectStandardInput = true;

            info.RedirectStandardOutput = false;

            info.RedirectStandardError = false;

            info.CreateNoWindow = false;

            info.WorkingDirectory = Path.GetDirectoryName(fileName);

            try

            {

                using (Process process = Process.Start(info))

                {

                    try

                    {

                        // Do some work with server

                        Thread.Sleep(2000);

                    }

                    finally

                    {

                        // Shutdown console server

                        try

                        {

                            process.StandardInput.WriteLine("close");

                            if (!process.HasExited)

                            {

                                if (process.Responding)

                                {

                                    try

                                    {

                                        process.Close();

                                    }

                                    catch

                                    {

                                        throw new InvalidOperationException("Unable to close process");

                                    }

                                }

                                else

                                {

                                    try

                                    {

                                        process.Kill();

                                    }

                                    catch

                                    {

                                        throw new InvalidOperationException("Unable to kill process");

                                    }

                                }

                            }

                        }

                        catch (Exception e) 

                        {

                            Console.WriteLine("Error occured: " + e.ToString());

                            throw new ServerShutdownException(e);

                        }

                        finally

                        {

                            // Cleanup code

                        }

                    }

                }

            }

            catch (Exception e)

            {

                throw;

            }

The key string in the code above is:

process.StandardInput.WriteLine("close");

The trick is that WriteLine method send also \n symbol required by Application B. This is not elegant solution, but it works fine for me. More investigations pointed me to following code (google for GenerateConsoleCtrlEvent):

GenerateConsoleCtrlEvent(ConsoleCtrlEvent.CTRL_C, process.Id);

but accidentally it was not working properly and Application B was not closed.

Thanks to Jeffrey Tan and his article at: http://groups.google.com/group/microsoft.public.dotnet.languages.csharp/msg/ad9f64840af60e6a?hl=en-US& for solution.

GenerateConsoleCtrlEvent Win32 API has a special limitation. From MSDN: "Only those processes in the  group that share the same console as the calling process receive the signal."

This makes GenerateConsoleCtrlEvent impossible to use in our case.

Parse ODP.NET trace file

To calculate number of opened, closed and disposed connection in ODP.NET trace log file, following combination of UnixTools and batch scripting can be used:


@echo off

grep OracleConnection::Open() .\Trace\trace.log | wc -l > .\Trace\Trace.tmp
set /p openconn= <.\Trace\Trace.tmp
grep OracleConnection::Close() .\Trace\trace.log | wc -l > .\Trace\Trace.tmp
set /p closeconn= <.\Trace\Trace.tmp
grep OracleConnection::Dispose() .\Trace\trace.log | wc -l > .\Trace\Trace.tmp
set /p dispconn= <.\Trace\Trace.tmp
echo Opened connections:  %openconn%, Closed connections: %closeconn%, Disposed connections: %dispconn%

where .\Trace\Trace.log - ODP.NET trace file

You can find ported Unix tools at: http://unxutils.sourceforge.net/

Turn on ODP.NET Debug Tracing

As states in section "ODP.NET Configuration" from Oracle Data Provider .NET Development Guide, place smth like in your .config file.


  <oracle.dataaccess.client>
    <settings>
      <add name="DbNotificationPort" value="-1"/>
      <add name="DllPath" value="C:\oracle\product\11.1.0\client_1\bin"/>
      <add name="DynamicEnlistment" value="0"/>
      <add name="FetchSize" value="131072"/>
      <add name="MetaDataXml" value="CustomMetaData.xml"/>
      <add name="PerformanceCounters" value="4095"/>
      <add name="PromotableTransaction" value="promotable"/>
      <add name="StatementCacheSize" value="50"/>
      <add name="ThreadPoolMaxSize" value="30"/>
      <add name="TraceFileName" value="C:\Trace\trace.log"/>
      <add name="TraceLevel" value="127"/>
      <add name="TraceOption" value="0"/>
   </settings>
  </oracle.dataaccess.client>

Oracle data provider for .net best practices.

Good article could be found at: http://nvtechnotes.wordpress.com/2009/04/13/oracle-data-provider-for-net-best-practices/

Oracle pooled connection is not disposed properly

Good discussion about this topic can be found at: http://forums.oracle.com/forums/thread.jspa?threadID=303380

Check oracle number of connections

To check max number of connections that is allowed for an Oracle database (http://stackoverflow.com/questions/162255/how-to-check-the-maximum-number-of-allowed-connections-to-an-oracle-database)

SELECT
'Currently, '
|| (SELECT COUNT(*) FROM V$SESSION)
|| ' out of '
|| VP.VALUE
|| ' connections are used.' AS USAGE_MESSAGE
FROM
V$PARAMETER VP
WHERE VP.NAME = 'sessions'

To monitor number of connections in Oracle (http://decipherinfosys.wordpress.com/2007/02/10/monitoring-number-of-connections-in-oracle/)

SELECT s.username AS username,

  (

  CASE

    WHEN grouping(s.machine) = 1

    THEN '**** All Machines ****'

    ELSE s.machine

  END)     AS machine,

  COUNT(*) AS session_count

FROM v$session s,

  v$process p

WHERE s.paddr   = p.addr

AND s.username IS NOT NULL

GROUP BY rollup (s.username, s.machine)

ORDER BY s.username,

  s.machine;

To see what SQL users are running on the Oracle database (thanks to http://www.dba-oracle.com/concepts/query_active_users_v$session.htm):

SELECT a.sid,

  a.serial#,

  a.username,

  b.sql_text

FROM v$session a,

  v$sqlarea b

WHERE a.sql_address=b.address;

To see what sessions are blocking other sessions (thanks to http://www.dba-oracle.com/concepts/query_active_users_v$session.htm):

SELECT blocking_session,

  sid,

  serial#,

  wait_class,

  seconds_in_wait

FROM v$session

WHERE blocking_session IS NOT NULL

ORDER BY blocking_session;

Another good articles about session stats can be found at:
http://vsbabu.org/oracle/sect04.html
http://www.shutdownabort.com/dbaqueries/Administration_Session.php

And finally, when "bad" sessions are found, we can kill them using (http://www.oracle-base.com/articles/misc/KillingOracleSessions.php):

ALTER SYSTEM KILL SESSION 'sid,serial#';

Another good queries from http://stackoverflow.com/questions/622289/how-to-check-oracle-database-for-long-running-queries

This one shows SQL that is currently "ACTIVE"

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
/

This shows locks

select
  object_name, 
  object_type, 
  session_id, 
  type,   -- Type or system/user lock
  lmode,     -- lock mode in which session holds lock
  request, 
  block, 
  ctime   -- Time since current mode was granted
from
  v$locked_object, all_objects, v$lock
where
  v$locked_object.object_id = all_objects.object_id AND
  v$lock.id1 = all_objects.object_id AND
  v$lock.sid = v$locked_object.session_id
order by
  session_id, ctime desc, object_name
/

This is a good one for finding long operations (e.g. full table scans). If it is because of lots of short operations, nothing will show up.

COLUMN percent FORMAT 999.99 

SELECT sid, to_char(start_time,'hh24:mi:ss') stime, 
message,( sofar/totalwork)* 100 percent 
FROM v$session_longops
WHERE sofar/totalwork < 1
/

To be continued... More documentation can be found at: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm

Delay Activity in Microsoft Workflow

There are several ways to create "timeouts" in your business logic using sequential or state machine workflows. For sequential workflow it will be combination of While and Listen activity (with HandleExternalEvent and Delay branches). For state workflow it will be Timeout event which makes transition to the same state.

Thanks to Matt Milner and his excellent article at: http://msdn.microsoft.com/en-us/magazine/ee294452.aspx

.net oracle performance counters disabled

During development of complex database applications on .net, we need to have ability to monitor various parameters like number of connections, number of pooled connections, number of pooled groups and so on.

Perfmon.exe comes to rescue. Just setup valid value for PerformanceCounters variable under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\Assembly_Version, where Assembly_Version is is the full assembly version number of Oracle.DataAccess.dll.

For more information please refer to: http://download.oracle.com/docs/html/E10927_01/featConnecting.htm#CJADIIFD

Connecting To xxx.xxx.xxx.xxx...Could not open connection to the host, on port 25: Connect failed.

Many people have that problem. It may be caused by several reasons: firewall, antivirus, security policy of ISP, etc. In my case, it was antivirus software which blocked 25 port.

Steps to identify problem.

1. Telnet host on 25 port. See http://technet.microsoft.com/en-us/library/bb123686.aspx for details.

2. Try to turn off firewall.

3. Try to turn off antivirus. Worked for me! Add the process and / or port to exceptions.