Avamar Administrator 6.1 RMAN backup agent not showing instance on client

If you are running an Avamar RMAN backup on Linux Oracle Enterprise you may need to run avregister to re-activate the client with the Administrator server.

Run from /usr/local/avamar/bin

# ./avregister

=== Client Registration and Activation

This script will register and activate the client with the Administrator server.

Enter the Administrator server address (DNS text name or numeric IP address, DNS name preferred): avamarserver.mydomainx.mydomain.com

The following result will take place after prompting for the Administrator server

Enter the Avamar server domain [clients]:

avagent.d Info: Stopping Avamar Client Agent (avagent)… avagent.d

Info: Client Agent stopped.                      [  OK  ]

avagent Info <5008>: Logging to /usr/local/avamar/var/avagent.log avagent.d

Info: Client activated successfully.             [  OK  ]

avagent Info <5008>: Logging to /usr/local/avamar/var/avagent.log avagent Info <5417>: daemonized as process id 13046 avagent.d Info:

Client Agent started.                      [  OK  ] Registration Complete.

Posted in Oracle Posts | Leave a comment

SQL Server: A simple PowerShell script for my crashing log reader agent used in replication.

Over the past few weeks one of my production servers involved in replication has had the log reader agent just stop for no apparent reason. Currently upgrading from 2005 sp2 is not an option on this particular server. So to buy myself some time to troubleshoot why this event is happening, I decided to revisit monitoring processes, and how I get alerted when they don’t exist. If you were to ask how I would have written something like this about a year ago, I would have most likely whipped out some C# code in SSIS. But today I wanted something monitored quickly, and did not want to have to deploy the solution to our production servers.

My criteria: If the log reader agent process is not present I want a notification right away. The process name I need to monitor is “logread”.

Here is the simple powershell script I used on a remote server.

   1: $process=get-process -computer yourserver logread -ErrorAction "silentlycontinue" 

   2: if ($process) {

   3:            "exists"

   4:     }

   5:     else     { 

   6:  

   7:         $CredUser = "user@domain.com"

   8:  

   9:         $EmailFrom = "user@domain.com"

  10:         $EmailTo = "dba@domain.com" 

  11:         $Subject = "SERVERX Logreader is not running"

  12:         $Body = "Please check the replication log reader on ServerX" 

  13:         $SMTPServer = "mail.domain.com" 

  14:         $SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25) 

  15:         #$SMTPClient.EnableSsl = $true 

  16:         $SMTPClient.Credentials = New-Object System.Net.NetworkCredential($CredUser, $CredPassword); 

  17:         $SMTPClient.Send($EmailFrom, $EmailTo, $Subject, $Body)

  18:      

  19:                       

  20:     }

It was as simple as pasting this into a job step on the sql server agent on another 2008 instance. One simple line to retrieve if the process is running or not. Can it get any more simple than this?

PowerShell is quickly becoming my scripting language of choice. Now to figure-out why it suddenly stops, that’s going to be the real difficulty I face.

Update to script above. My SmtpServer is not on port 25. So sending mail with the Net.Mail.SmtpClient was the only way for me to access the Smtp server as –Port option did not seem to work.

As far as the log reader agent issue is concerned. I have alerts enabled on the agent, and yes I have the  -Continuous switch when launching. There are no errors in the eventlog, or on the agent itself. The process just stops every few days. So if you are also running on Sql Server 2005 sp2, and have seen this behavior please feel free to share where my problem may reside.

As Always, “DBA’s don’t fly by the seat of you pants!”

Posted in Sql Server Posts | Leave a comment

SQL Saturday #156: September 15th

Reminder to all. Please register for SQL Saturday #156 in East Greenwich, RI.

THIS SATURDAY!!!!!!

The schedule is here:

http://www.sqlsaturday.com/156/schedule.aspx

What a great opportunity to network and learn from the best.

Hope to see you there!!!

As always DBA’s don’t fly by the seat of your pants.

Posted in Sql Server Posts | Leave a comment

SQL Server: Learning StreamInsight using Arduino with a custom Input Adapter.

Microsoft released StreamInsight with SQL Server 2008R2, and I’ve been putting the learning curve off for quite a long time. So this blog post is my attempt to learn some of its basic capabilities. What better way to learn this technology than to do it in a public forum where it can be reviewed by the experts to offer even more advice on my huge learning curve using this development platform.

StreamInsight provides a powerful platform for developing and deploying complex event processing (CEP) applications. CEP is a technology for high-throughput, low-latency processing of event streams. Typical event stream sources include data from manufacturing applications, financial trading applications, Web analytics, or operational analytics. The StreamInsight stream processing architecture and the familiar .NET-based development platform enable developers to quickly implement robust and highly efficient event processing applications

image

My Ardunio example is based on the following being installed:

Visual Studio 10

StreamInsight 1.2 installed (licensing tied to SQL Server 2008R2).  this is the older version used in this example.

Ardunio Uno or Mega 2560 R3. With the proper USB cable to upload your program (Sketch) to the controller.

Once you have installed StreamInsight a good example to get started with is on the codeplex web site named: “StreamInsight Events to Windows Form”

 http://streaminsight.codeplex.com/releases/view/64805

This example shows six different LINQ queries to run against a typed point input adapter using a fake data source. For the last query in the example the output adapter binds to a web service, and posts the results real time to a chart in a windows forms application. Having this visual representation was the catalyst for me to take the next step and try to replace the fake data source with an Ardunio controller data stream.

The Ardunio communicates to the outside world using a serial port with a maximum speed of 115,200 bits/sec. The Uno can be purchased at Radio Shack, or your favorite electronic hobby store for about $35, and the more powerful Mega 2560 R3 is about $69. Being rich I used the 2560 R3 for this project.

Using the same StreamInsight design structure as the codeplex example, I rewrote the solution, and changed the typed point Input Adapter to have a slightly different payload.

public class ArduinoComEventType
   {
     public   string RequestType { get; set; }
     public   double RowNum { get; set; }
     public   float ivalue { get; set; }
   }

Like the codeplex example I also used RequestType. But instead of representing “Orders” my RequestType is just a three generic ouputs “Output1”,”Output2”,”Output3”. These three different events that we are monitoring are generated from the Ardunio controller.

The RowNum , and ivalue numbers represent changes to values generated from the controller when the RequestType we are interested in is observed on the communication port.

The ArdunioStreamInsight solution used is similar to the codeplex solution not by accident. The rewrite of the example solution offered me the shortest learning path for my solution. The four projects contained in the solution are as follows:

image

The above Arduino project is the windows form for the chart control  (“UI in codeplex example”)

image

The DataPublisher is where the Input Adapter is defined to monitor the COM port input buffer from the Ardunio controller.

image

The ArdunioQuery is the console application which connects to the streaminsight server engine, and allows us to do the LINQ queries against our input stream. This is set as the default project in the solution. Which means you need to manually start the ArdunioStreamInsight form application prior to running the console application. This also assumes you open the soap listener using the listen button on the form. Also as all projects using http endpoints you will have to make sure your namespace endpoints are allowed on your machine by using netsh with your proper permissions.

The two namespaces is first your StreamInsight server endpoint http://localhost:80/StreamInsight/DEFAULT/ 

and then target adapter service endpoint http://+8733/ArduinoStreamInsight/FeedbackListenerService.

image

Lastly the WebOutputAdapter converts the CEP events into XML using a simple XSLT transformation. Then sends the transformed CEP events over the web service to the form application and the final destination the chart control.

On the Ardunio side we have uploaded a very simple sketch to the controller:

The Arduino Sketch used is as follows:

int x = 0;
int row = 0;
void setup() {
  Serial.begin(115200); // opens serial port, sets data rate to 9600 bps

}

void loop() {
  Serial.print("Output1"); 
  Serial.print(",Row="); 
  Serial.print(row); 
  Serial.print(",Value="); 
  Serial.print(sin(x*PI/180));
  Serial.println(",End");
  Serial.print("Output2"); 
  Serial.print(",Row="); 
  Serial.print(row); 
  Serial.print(",Value="); 
  Serial.print(cos(x*PI/180));
  Serial.println(",End");
  Serial.print("Output3"); 
  Serial.print(",Row="); 
  Serial.print(row); 
  Serial.print(",Value="); 
  Serial.print(2*sin(x*PI/180));
  Serial.println(",End");

  row++;
  x++;
  if (row > 360) 
  {
    row=0;

  }
  //delay(100);
}

As you can see from the sketch it simply outputs a sine wave, cosine wave, and another sine wave which is amplified by a factor of 2. The sketch is simple, but if you wanted to use StreamInsight on a robot sensor, or other project the concept would be similar. I chose the math functions because having a controlled source yields a better understanding to what is happening when applying our LINQ queries to the stream. And after all this is about learning StreamInsight so for me it means keeping it simple.

Putting the delay loop into the script helps make the wave cycles visible on the chart. At higher frequencies the waves look like an RF carrier wave instead of the common sine wave we are use too.

The following code is the DataPublisher which was changed to use a COM port from the fake datasource in the original codeplex example.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.IO.Ports;
using System.Threading;

namespace DataPublisher
{
     public class ArduinoComEventType
    {
      public   string RequestType { get; set; }
      public   double RowNum { get; set; }
      public   float ivalue { get; set; }
    }

   public class ArduinoComDataSource
    {

    static bool _continue;

    static SerialPort comm = new SerialPort("COM5", 115200, Parity.None, 8, StopBits.One);

    static ArduinoComEventType oevent = new ArduinoComEventType();

    public ArduinoComDataSource()
    {
        //how long to wait between generated messages
        this.period = 1;
        this.variance = 0;

        // Initalize timer.
        this.timer = new System.Timers.Timer(period);
        this.timer.Elapsed += new System.Timers.ElapsedEventHandler(this.ArduinoData);
        this.timer.AutoReset = true;

        comm.Open();
        Thread readThread = new Thread(Read);
        _continue = true;
        readThread.Start();

    }

    public static void Read()
    {
        string message = "";
        while (_continue)
        {
            try
            {
                message = "";  
              message = comm.ReadLine();
            //  Console.WriteLine(message);

              int startpos = 0;
              int endpos=0;

               if (message.Contains("O") && message.Contains(","))
               {
                   startpos = 0;
                   endpos = 0;

                   if (message.Contains("Output1"))
                   {
                       double r=0;
                       float i=0;
                        oevent.RequestType = "Output1";
                       if ( message.Contains("Row=") && message.Contains("Value=")) {

                           startpos = message.IndexOf("Row=") + 4 ;
                           endpos = message.IndexOf("Value") -1 ;

                        Double.TryParse(message.Substring(startpos, endpos-startpos), out r);
                        oevent.RowNum = r;
                       }
                        if (message.Contains("Value=") && message.Contains("End"))
                        {
                            startpos = message.IndexOf("Value=") + 6;
                            endpos = message.IndexOf("End") -1; 

                            float.TryParse(message.Substring(startpos, endpos -startpos), out i);
                            oevent.ivalue = i;
                        }

                   //    Console.WriteLine(message);
                   }

                   if (message.Contains("Output2"))
                   {
                       double r = 0;
                       float i = 0;
                       oevent.RequestType = "Output2";
                       if (message.Contains("Row=") && message.Contains("Value"))
                       {

                           startpos = message.IndexOf("Row=") + 4;
                           endpos = message.IndexOf("Value") -1;

                           Double.TryParse(message.Substring(startpos, endpos - startpos), out r);

                        oevent.RowNum = r; 
                       }
                       if (message.Contains("Value=") && message.Contains("End"))
                       {
                           startpos = message.IndexOf("Value=") + 6 ;
                            endpos = message.IndexOf("End") -1;

                           float.TryParse(message.Substring(startpos, endpos - startpos), out i);

                            oevent.ivalue = i;
                       } 

                  //     Console.WriteLine(message);
                   }

                   if (message.Contains("Output3"))
                   {
                       double r = 0;
                       float i = 0;
                       oevent.RequestType = "Output3";
                       if (message.Contains("Row=") && message.Contains("Value"))
                       {
                            startpos = message.IndexOf("Row=") + 4;
                           endpos = message.IndexOf("Value") -1;

                           Double.TryParse(message.Substring(startpos, endpos - startpos), out r);

                           oevent.RowNum = r;
                       }
                       if (message.Contains("Value=") && message.Contains("End"))
                       {
                          startpos = message.IndexOf("Value=") + 6;
                            endpos = message.IndexOf("End") -1;

                           float.TryParse(message.Substring(startpos, endpos - startpos), out i);

                            oevent.ivalue = i;
                       }

                 //      Console.WriteLine(message);
                   }

               }

            }
            catch (TimeoutException) {  }
        }
    }

    /// <summary>
    /// Generates data and calls the callback
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    void ArduinoData(object sender,System.Timers.ElapsedEventArgs e)
    {
        lock (this.thisLock)
            {
                // Set a new timer interval.
                this.timer.Interval = Math.Max(new Random().Next((int)this.period - (int)this.variance,
                                                                 (int)this.period + (int)this.variance), 1);

            // push the data into the callback, with current time.
            this.callback( oevent, DateTime.Now);
        }
    }

          /// <summary>
        /// Period at which to produce a new data item.
        /// </summary>
        private uint period;

        /// <summary>
        /// Maximum variance to deviate from the exact period.
        /// </summary>
        private int variance;

        /// <summary>
        /// Timer used to trigger the generation of new data items.
        /// </summary>
        private System.Timers.Timer timer;

        /// <summary>
        /// Mutex for the timer-triggered data generation.
        /// </summary>
        private object thisLock = new object();

    /// <summary>
    /// Sets the callback.
    /// </summary>
    public EventCallback Callback
    {
        set { this.callback = value; }
    }

    /// <summary>
    /// Starts the data generation.
    /// </summary>
    public void Start()
    {
         this.timer.Enabled = true;
    }

    /// <summary>
    /// Stops the data generation.
    /// </summary>
    public void Stop()
    {
        this.timer.Enabled = false;
    }

    /// <summary>
    /// Callback object.
    /// </summary>
    private EventCallback callback;

    /// <summary>
    /// Callback type to push new data to.
    /// </summary>
    /// <param name="data">New data item to push to the callback.</param>
    /// <param name="timestamp">Timestamp of the data.</param>
    public delegate void EventCallback(ArduinoComEventType data, DateTime timestamp);

    }

}

Rather than going into the entire four projects code line by line  I will just briefly explain some of the overall changes I made. And then I will finish up with a couple of example queries, and their resulting appearance on the chart control.

To use the chart control for my ivalue data. I modified the XSLT to match the three CEP events , and then adjusted the  GridUpdater callback on the form receiver side to accept these values .  The chart series was set to use ivalue for the y data value for each of the three Output series.

Now for the fun part. Lets see what results we get from all this work.

First lets look at all of the payload events being graphed:

image

The LINQ query was for all events:

var query1 = from e in inputStream
                       select e;

image

The LINQ query was a simple filter of events where ivalue > .5

var query1 = from e in inputStream
                               .Where(e => (e.ivalue > .5))

On the above query I also decreased the delay on the Arduino side from 500 ms to 50 ms  so you can see this shortened our waves period overall cycle (indicated by an increase in frequency).

This stuff is pretty cool!!!!

image

The following LINQ query generated the number of events on Ouput2 over a 10 second interval.

long PeriodAsTicks = TimeSpan.TicksPerSecond * 10;

var query5 = from e in inputStream
                               .Where(f => f.RequestType == “Output2”)
                               .AlterEventDuration(f => TimeSpan.FromTicks(PeriodAsTicks – (f.StartTime.Ticks % PeriodAsTicks)))
                               .SnapshotWindow(SnapshotWindowOutputPolicy.Clip)
                                 select new
                                 {   RequestType = “Output2”,
                                     ivalue = e.Count()
                                 };

On the above query I also decreased the delay on the Arduino side from 500 ms to 50 ms

image

The above LINQ query was the same as the previous one, but I removed the delay on the Arduino side from 50ms to no delay. So it shows we average 200 Output2 events every 10 seconds.

This is only the beginning of my investigation of StreamInsight.

The possibilities for this technology seem endless, I could easily send feedback to the Arduino based on an particular event happening. Perhaps every time a sensor reached a threshold, I could alter the servo to change the response of my robot. Currently I don’t have an XBee module, which would allow wireless feedback to the controller based on these events. Although, I probably have enough parts lying around to get started on designing my own from scratch. Just joking a little, I’d rather buy that then attempt it from scratch. The days of recreating circuits from scratch that already exist are over for me. Unless its just flashing an LED or something.

If your interested in playing with this solution please leave a message on my site.

Hope you enjoyed this. As always: DBA’s don’t fly by the seat of your pants!

ArduinoStreamInsight

 

Posted in Sql Server Posts | 3 Comments

TSQL: What DOW did the first Fourth of July occur on?

This post is to offer you some insight on my first experience with calculating the DOW (day of the week)  a certain date occurs on.

My experience in this subject dates back to 1980 when I was studying Engineering in college. At that time I was known as a true geek in that I had some of the best tools available at the time to aid in my studies.  I owned, and still own today an HP25 RPN (Reverse Polish Notation) calculator that sold for around  220 bucks.

http://www.hpmuseum.org/hp25.htm

For those who don’t know the specs it was one of the first ever programmable calculators that had a whopping 49 steps of programmable memory. Later I bought the HP41c where the c stood for constant memory. It offered long term memory storage and a change from an LED display to an LCD display and many other options . These HP calculators where great once you got use to the fact that RPN calculators force you to know the rules of precedence in math equations. Unlike regular calculators you must start from the inner parentheses and work outward. Also unlike regular calculators there is no “=”  button on an RPN calculator. It was fun to watch a student’s face who recently borrowed your calculator, when they realized they had no idea how to use it. I’ll get back to the calculator in a moment.

In the late 70’s and early 80’s it was a must for all Engineering students to take computer science classes. So for my school that meant we had to take Fortran IV on the IBM 360

http://en.wikipedia.org/wiki/IBM_System/360

We would get an average of two runs on the system per day. After all this computer was used for all of the school records, and other programing classes as well. So a couple of minutes of processing time a day was all a computer science student could ever expect. So how did we program back then. Well we would design it all on on paper for a few days, than punch the batch cards with the right instructions, and stack the deck with the right Job Control cards, and then submit the cards to the system operator for precompiling and processing. By the way, I almost fainted the first time I saw a  PDP11 terminal screen were you could see results almost immediately.

Diagnosing syntax errors on the cards in the Fortran language was a science that I mastered, and others used me as another precompile step to save time in getting their projects to actually run before their projects due date.

One of  the projects I designed for my Fortran class was a program to generate a calendar based on only the year submitted on the data batch card.

The program was based on the following algorithm:

N(m,d,y) = [365.25 g(y,m)] + [30.6 f (m)] + D – 621049

Where

g(y,m) = y-1 if m – 1 or 2 , y if m >2

f (m)  = m +13 if m=1 or 2, m+1 if m> 2 

[m] represents the integer function E.g. [6.34] = 6

Note: For days from March 1, 1700, to February 28, 1800, 2 days must be added to the value of N calculated by the program. For days from March 1, 1800 to February 18, 1900, 1 day must be added.

The Fortran program was around 300 punch cards, and stored a year in a three dimensional array then offered a printed calendar output of the year submitted. It actually pushed the limits of nested implied writes that the IBM360 was capable of doing in the Fortran language. It was so popular with other students, I later rewrote the program to run on the MVS and VAX 11  systems a few years later. While porting the application to different machines I soon realized that as precision changed and some adjustments needed to be made in the Floor routines. Most students loved to print out the calendar for the year they were born. This feat is something I doubt students today would find very amazing. My I do indeed date myself, but would never give back all the experiences I gained in those exciting early days of electronics and computers. After all Microsoft had 30 employees at that time.

The programs base algorithm was actually taken straight from the HP25 Applications Programs book that came with my HP25 calculator. It only needed 46 steps of the 49 steps that the calculator could handle. Granted the HP25 calculator would only return a digit for the DOW in question, but to me it was amazing that we could do this calculation in just 46 steps.

So today how would I calculate this?

SELECT DATENAME(dw,'07/04/1776' )

Which returns “Thursday” if you don’t have access to SSMS right now.

Happy Fourth Everyone!

Posted in Sql Server Posts | Leave a comment

Talisma: Reading an eml file to obtain track able URL information

When sending campaigns from Talisma it is important to allow recipients the ability to OPT out of receiving any more correspondence. Failure to do so can cause your email alias to get blacklisted and require IT to contact the carrier to unblock further email processing at their site. So an interesting subject is how do you respond to these OPT out recipients to avoid being flagged as a spammer. The solution we are using is perhaps only one method, but is worth documenting in case you do not have a solution already in place. If you have an alternative solution, please share it with me as I would love to implement a less custom solution.

That being said let me describe what we are currently doing to avoid this situation.

First if the user OPT’s out by clicking on the track able URL we need to react to their request. But often they don’t even click on the OPT out link in the template. So we need to know when they are reporting the message as true spam. First indication will be a subject line from your exchange server containing the IP address of the exchange server, and the message with its attachments being reported as spam.

Unfortunately, exchange will not allow you to see the campaign, or even the recipient without using the embedded track able URL. This is because many providers report this as reacted users, and offer no insight into the user that is reporting the message as spam. This privacy is required by law, but since we already know the format of the campaign attachment we can still process the users request.

But if your policy is always to use a track able URL in campaigns there is still hope.

The key is to open the eml bounce back from exchange, and parse the email for the URL that is embedded in the message reported as spam.

Using SSIS we can read these emails by using a script task that can parse the email for the content of the URL. The code base for reading an eml file is based on LumiSoft.Net.Mail assemblies referenced in a script task in SSIS. Here are the details, although this is some what custom to our environment, I hope you can get the concept of what we are doing.

One other note, in our case the exchange administrator has a rule which forwards these messages back to an alias in Talisma. This creates another interaction, but offers better history of the event. The procedure of indicating spam could alternatively be done by reading the emails from a file share, if you are not currently having these events sent back to an alias in Talisma.

The overall SSIS package in our environment looks like the following:

clip_image002

The first query just finds the interactions that are reported as spam in Talisma as new interactions.

select top (?) convert(varbinary(max),a.ifile), convert(varchar(max),convert(varbinary(max),a.ifile)), c.aglobalCaseID, a.tFilename
from tlMain.dbo.tblEvent e
inner join tlMain.dbo.tblAttachment a on e.aEventID=a.nEventID
inner join tlMain.dbo.tblCase c on e.nGlobalCaseID=c.aGlobalCaseID
where e.tSubject like ?
and a.tFilename not like 'ATT%.dat%' and a.tFilename not like '%.html%' and c.bDeleted=0

The first variable is to control how much spam we process on each batch run.

The second variable is the subject line that that indicates the interaction as spam. The where clause insures we are looking at the true eml file, it may need adjusting at your site.

The next step is to enumerate using an ADO recordset the attachments in a for loop.

In our case we have the following variables referenced in for loop container.

image

The UserRowData is the record set itself  data type is object. The UserRowDataS is another reference to the recordset when stored as an eml file. In our environment, old spam emails did not always have attached eml files, but sometimes had plain text.

The other two variables are the interaction ID from the spam email rule forwarding the message, and the attachment name which could be either txt or eml format.

The inner search MIME or text message for CID is the heart of the process.

The script task looks as follows:

/* Microsoft SQL Server Integration Services Script Task Write scripts using Microsoft Visual C# 2008. The ScriptMain is the entry point class of the script. */ using System;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using LumiSoft.Net.Mail;
using LumiSoft.Net;

namespace ST_fce70ad6f6e84845aae9735841ea5f1d.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase {

        #region VSTA generated code
        enum ScriptResults {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion /* The execution engine calls this method when the task executes. To access the object model, use the Dts property. Connections, variables, events, and logging features are available as members of the Dts property as shown in the following examples. To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value; To post a log entry, call Dts.Log("This is my log text", 999, null); To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true); To use the connections collection use something like the following: ConnectionManager cm = Dts.Connections.Add("OLEDB"); cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"; Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. To open Help, press F1. */ static bool IsNumber(string value)
        {
            // Return true if this is a number. int number1;
            return int.TryParse(value, out number1);
        }

        public void Main()

        {
                Int32 startpos = 0;
                Int32 startpos2 = 0;
                Int32 startpos3 = 0;
                Int32 endpos = 0;
                Int32 endpos2 = 0;
                Int32 endpos3 = 0;

                String string1 = "0";
                String string2 = "0";
                String string3 = "0";
                String message = "";
                String from = "";
                String recp = "";
                byte[] arrs = null;

                Dts.Variables["CID"].Value = "0";
                Dts.Variables["OBMID"].Value = "0";
                Dts.Variables["CampaignID"].Value = "0";
                Dts.Variables["From"].Value = "0";
                Dts.Variables["Receipient"].Value = "0";
                String an = "";

                 an=Dts.Variables["AttachmentName"].Value.ToString().ToLower();

                if (an.IndexOf(".eml") <=0)
                {   

                    message = Dts.Variables["RowDataS"].Value.ToString();
                    startpos = message.IndexOf("From:") + 5;
                    endpos = message.IndexOf("To:") - 1;
                    startpos2 = message.IndexOf("To:") + 3;
                    endpos2 = message.IndexOf("Subject") - 1;

                    if (startpos > 0 & endpos > 0 & (startpos < endpos))
                    {
                        from = message.Substring(startpos, endpos - startpos);
                    }
                    if (startpos2 > 0 & endpos2 > 0 & (startpos2 < endpos2))
                    {
                        recp = message.Substring(startpos2, endpos2 - startpos2);
                    }
                    /* following reused lets be safe and zero them out */ startpos = 0;
                    endpos = 0;
                    startpos2 = 0;
                    endpos2 = 0;

                }
                else {
                    arrs = (byte[])Dts.Variables["RowData"].Value;
                    try {
                        Stream s = new MemoryStream(arrs);
                        Mail_Message msg = Mail_Message.ParseFromStream(s);
                        message = msg.BodyText;
                        from = msg.From.ToString();
                        recp = msg.To.ToString();
                        msg.Dispose();
                        s.Dispose();
                    } catch (Exception e)
                    {
                        Console.WriteLine("{0} Exception caught.", e.Message.ToString());
                       // MessageBox.Show(e.Message.ToString(), "Exception Caught"); }
                }

                if (from.Length > 0 & from.ToString() != "") { Dts.Variables["From"].Value = from; }
                else { Dts.Variables["From"].Value = ""; }

                if (recp.Length > 0 & recp.ToString() != "") { Dts.Variables["Receipient"].Value = recp; }
                else { Dts.Variables["Receipient"].Value = ""; }

                startpos = message.IndexOf("CID=") + 4;
                endpos = message.IndexOf("OBMID=") - 1;
                startpos2 = message.IndexOf("OBMID=") + 6;
                endpos2 = message.IndexOf("TID=") - 1;
                startpos3 = message.IndexOf("Campaign=") + 9;
                endpos3 = message.IndexOf("Step=") - 1;

                if (startpos > 0 & endpos > 0 & (startpos < endpos))
                {
                    string1 = message.Substring(startpos, (endpos - startpos));
                }
                if (string1 != "0" & IsNumber(string1))
                {
                    Dts.Variables["CID"].Value = string1;
                }
                else {
                    Dts.Variables["CID"].Value = "0";
                }

                if (startpos2 > 0 & endpos2 > 0 & (startpos2 < endpos2))
                {
                    string2 = message.Substring(startpos2, (endpos2 - startpos2));
                }
                if (string2 != "0" & IsNumber(string2))
                {
                    Dts.Variables["OBMID"].Value = string2;
                }
                else {
                    Dts.Variables["OBMID"].Value = "0";
                }

                if (startpos3 > 0 & endpos3 > 0 & (startpos3 < endpos3))
                {
                    string3 = message.Substring(startpos3, (endpos3 - startpos3));
                }
                if (string3 != "0" & IsNumber(string3))
                {
                    Dts.Variables["CampaignID"].Value = string3;
                }
                else {
                    Dts.Variables["CampaignID"].Value = "0";
                }

            // TODO: Add your code here Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

After the script task the logic depends on if a CID was found. If it was we process the spam email if not we record the spam email as email we are not interested in.

The precedence constraint  for CID existing is as follows:

image

If we process the CID as spam we run the following sql task.

IF EXISTS( select top 1 * from tbSpamRemovals where nCustID=?) begin Print 'Do Nothing User in batch already' end Else begin insert into tbSpamRemovals (nCustID, bDontSendOutBoundMailer) select c.aCustID, 'No' from tlMain.dbo.tblObjectType3_1 ob3
inner join tlMain.dbo.tblCustomer c
on c.aCustID=ob3.nID
where bDontSendOutBoundMailer=0 and fldnumeric21529 in (1,3,6) and c.aCustID=?; end 

The fldnumeric21529 is custom to our site indicating what status the potential student has in Talisma. Example: desisioned, inquiry, ..etc. You will also notice that parsing the track able URL is very position dependent. Please make adjustments based on your environment.

The next step is to insert the interaction even if the track able url is not found.

We insert this history in a table in our tlImport database.

insert into tbSpamRemovalsHist
( interactionid, obmid, ncampaignid,ncustid, attachmentname, [from],recipient,currentstage,currentbdontsendoutboundmailer,currentdtstagechecked,spamremoval) values (?,?,?,?,?,?,?,'','',null,getdate() )

After storing the found emails containing the correct CID and status for removal, we run a standard data import process to toggle the bDontSendOutboundmailer flag within Talisma.

Then we set the bdeleted flag of the spam interaction to put the interaction in the trash can. Very rarely do I recommend updating any Talisma baseline tables. Please insure you test things fully before attempting this step. The trash can flag is relatively safe because we are then depending on Talisma’s basline rules to eliminate the interaction.

I realize this is a rough overview, and was really only meant to give an interested reader a concept of what we are doing. Please respond if you want a little more detail, or have a better alternative.

Hope you enjoyed! By the way I haven’t given up on posting on SQL server centric topics. My promise is more generic SQL Server posts are soon to come.

Posted in Talisma | Leave a comment

General Announcement: New Talisma Technical Users Group now exists!

If your job revolves around supporting Talisma CRM please join the newly formed Talisma Technical Users Group.

The mission of this site is not to replace the linked-in group, but offer a forum to share on a little more technical level our experiences of supporting Talisma on a day to day basis.

The web site is located at the following url:

http://www.taltug.org

Please feel free to join and share in this opportunity to expand our knowledge in day to day support of this application.

Posted in Talisma | Leave a comment