[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

Transaction Queue

I need to build a queue processing program. This program will read records from a SQL Server database. It will use this record to access another table and then launch a program or store procedure. The results of this transaction will then be e-mailed.

This seems like something that someone must have done already. Anyone have any leads on this?
0
flfmdll
Asked:
flfmdll
1 Solution
 
cymbolicCommented:
Yes! The answer is....programming!  You've just described the typical mix of operatons that result in a custom database oriented program.  Recomendation: Use VB5 Enterprise Edition, RDO Access method.  You can easily put your resultset read in a slow loop to pick up rows as they appear, then use those results as keys (whatever) to create another SQL statement to perform your lookup in either the same or another database.  VB also has relatively good tools to launch programs and access email systems using MAPI or DDE.  

A slower and poorer alternative to this would be to use Access as an intermediate tool, link your SQL tables to the Access database, and perform parallel type of code in a VBA module in Access.

Much beyond this and you're asking somebody else to do the programmng for you.  If you have specific questions about how to actually perform some of these steps, ask additional questions here.
0
 
flfmdllAuthor Commented:
Okay. I'm not asking someone else to do the programming for me. That's a given. I've been programming in depth for 14 years. This has covered a range of OSs, languages, business needs, etc. But what I am looking for is someone who has done this sort of thing using SQL Server, VB or VC, CGI, and Store Procedures. I'm not necessarily looking for code, but I am looking for paths to follow or not to follow; little known capabilities of SQL, Transaction Server, and IIS. I'm not looking for an in depth analysis, but if there is something that will make my design and implementation cleaner, more robust, and easier then I would like to know about it.

That's all.

I will be using VC rather than VB.
0
 
JayMerrittCommented:
You could probably do the whole thing in SQL if you want to utilize CURSORS.  It might get pretty thick, though.  SQL does not have a decent debugging mechanism for huge routines.

For the last part, you can use XP_SENDMAIL to send mail.  This will also allow for attaching query results as attachments.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
flfmdllAuthor Commented:
What is XP_SENDMAIL? I guess it's not SQL Servers mail? Is XP_SENDMAIL readily available?
0
 
lekshmikrCommented:
this program reads table from the sql server and prints to a file .
check the code in the line " (dbnextrow (dbproc) != NO_MORE_ROWS)" in the program i have shown .I t could prove usefil to you.

Then about xp_sendmail ,it is an extended stored procedure in MS SQL server which Sends a message, and/or a query results set, and/or an attachment to the specified recipients.


#define DBNTWIN32
#define SERVER "VSOUND"
#define USER   "sa"      
#define PAGELENGTH 40
#include <stdio.h>
#include <windows.h>
#include <sqlfront.h>
#include <string.h>
#include <sqldb.h>
#include <malloc.h>
#include <conio.h>
//#include "PrintTit.h"
// Forward declarations of the error handler and message handler.
int err_handler(PDBPROCESS, INT, INT, INT, LPCSTR, LPCSTR);
int msg_handler(PDBPROCESS, DBINT, INT, INT, LPCSTR, LPCSTR,
                LPCSTR, DBUSMALLINT);
struct Node
{
    struct Node *Prv;
    char TableName[31];
      struct Node *Next;
};

main()
{
    PDBPROCESS  dbproc;    // The connection with SQL Server.
    PLOGINREC   login;     // The login information.
    DBCHAR      name[100];
    DBCHAR      date[100];
      DBCHAR      sDbname[100];
      DBCHAR      Server[30];
      FILE            *ptr;
      int         Exists = 0, i = 0,c, PageNo = 1, LinNo = 0, SlNo = 0;
      struct Node *TableList, *FirstNode, *NodeSave;
    char        typename[31];
    char        length[4];
    char        precision[6],uLine[80];
      char        UserName[30];
      char        PassWord[30];
    char        IndexName[25];
      char        IndexDescription[60];
      char        IndexKeys[260];
    // Install user-supplied error- and message-handling functions.
    dberrhandle (err_handler);
    dbmsghandle (msg_handler);

    // Initialize DB-Library.
    dbinit ();

    // Get a LOGINREC.
    login = dblogin ();
       
   

    // Get a DBPROCESS structure for communication with SQL Server.
      printf("Enter Server   Name :");
      scanf ("%s",Server);
      printf("Enter DataBase Name :");
      scanf ("%s",sDbname);
    printf("Enter User     Name :");
      scanf("%s",UserName);
      printf("Enter Password      :");
      while((c = getch()) != 13 && c != 10)
      {
         if(c == 10 || c == 13)
         {
              break;
         }
         PassWord[i++] = c;
      }
    PassWord[i] = '\0';
      if( (ptr = fopen("dbinfo.txt","w")) == (FILE *) NULL)
      {
       printf("File Open Error \n");
         fclose(ptr);
         dbexit();
         exit(1);
      }
      //printf("%s\n",PassWord);

    DBSETLUSER (login, UserName );
    DBSETLPWD (login, PassWord);
    DBSETLAPP (login, "example");
    if( (dbproc = dbopen (login, Server)) == NULL)
      {
      dbexit();
        printf("Login to %s failed.\n",Server);
        exit(1);
      }
      //PrintTitle(ptr,"Tables and Indexes Listing",1,PageNo);
      //PrintTitle(stdout,"Tables and Indexes Listing",1,PageNo++);
      fprintf(ptr,
            "                          Tables and Indexes Listing    Page : %3d",PageNo++);
      fprintf(ptr,
            "                          --------------------------    Date : %s", "04/04/1997");
    fprintf(ptr,"DATABASE  : %s\n", sDbname);
      fprintf(ptr,"SERVER    : %s\t\tUSER NAME : %s\n", Server, UserName);
      //fprintf(ptr,"USER NAME : %s\n",UserName);
      memset(uLine,'-',79);
    fprintf(ptr,"%s\n",uLine);
      LinNo += 6;

    dbcmd (dbproc, "SELECT name, convert(char, crdate, 103) FROM  Master.dbo.SYSdatabases");
    dbsqlexec (dbproc);


    if (dbresults (dbproc) == SUCCEED)
    {
        // Bind column to program variables.
        dbbind (dbproc, 1, NTBSTRINGBIND, 0, name);
        dbbind (dbproc, 2, NTBSTRINGBIND, 0, date);

        // Retrieve and print the result rows.
        while (dbnextrow (dbproc) != NO_MORE_ROWS)
        {
            if( (strcmp(strupr(name),strupr(sDbname)) == 0) )
                  {
                    Exists = 1;
                  }
        }
    }
      if(Exists <= 0)
      {
      dbexit ();
      printf("No Such Database\n");
        exit(1);
    }

    // Retrieve some columns from the "authors" table in the
    // "pubs" database.

    dbfcmd (dbproc, "use %s",sDbname);
    dbsqlexec (dbproc);
    if (dbresults (dbproc) == SUCCEED)
    {
       while (dbnextrow (dbproc) != NO_MORE_ROWS)
         {
         
         }
    }
    dbuse(dbproc, sDbname);
    // First, put the command into the command buffer.
    TableList = (struct Node *)malloc(sizeof(struct Node));
      TableList->Prv  = (struct Node*)NULL;
      TableList->Next = (struct Node*)NULL;
    FirstNode = TableList;
  /*printf ("%20s  %20s\n", "Table Name", "Created Date");
      printf ("%20s  %20s\n", "----------", "------------");
      fprintf (ptr,"%20s  %20s\n", "Table Name", "Created Date");
      fprintf (ptr,"%20s  %20s\n", "----------", "------------");*/
    dbfcmd (dbproc, "SELECT name, convert(char, crdate, 103) FROM  %s.dbo.SYSOBJECTS WHERE TYPE = 'U'",sDbname);

    dbsqlexec (dbproc);

    // Process the results.
    if (dbresults (dbproc) == SUCCEED)
    {
        // Bind column to program variables.
        dbbind (dbproc, 1, NTBSTRINGBIND, 0, name);
        dbbind (dbproc, 2, NTBSTRINGBIND, 0, date);

        // Retrieve and print the result rows.
        while (dbnextrow (dbproc) != NO_MORE_ROWS)
        {
          /*printf ("%20s %20s\n", name, date);
            fprintf (ptr,"%20s %20s\n", name, date);*/
                  sprintf(TableList->TableName,"%s",name);
                  NodeSave        = TableList;
            TableList       = TableList->Next = (struct Node *) malloc(sizeof(struct Node));
                  NodeSave->Next  = TableList;
                  TableList->Prv  = NodeSave;
                  TableList->Next = (struct Node *)NULL;
        }
            NodeSave->Next = (struct Node *)NULL;
    }
    //getch();
    // Close the connection to SQL Server.
      TableList = FirstNode;
      while(1)
      {
         memset(uLine,'-',79);
       printf("\n\nTable : %-30s\n",TableList->TableName,name);
         printf("%s\n", uLine);
       printf("%-4s %-30s %-30s %-8s %-4s\n","SlNo", "Name","DType", "Length", "Prec");
         printf("%s\n",uLine);
       fprintf(ptr,"\n\nTable : %30s\n",TableList->TableName,name);
         fprintf(ptr,"%s\n", uLine);
       //fprintf(ptr,"%-4s %-30s %-30s %-8s %-4s\n","SlNo","Name","DType", "Length", "Prec");
       fprintf(ptr,"%-4s %-30s %-30s \n","SlNo","Name","DType");
         fprintf(ptr,"%s\n",uLine);
         LinNo += 6;
       //dbfcmd (dbproc,"execute Master.dbo.sp_GetColumns '%s'",TableList->TableName);

         dbfcmd (dbproc, "select c.name, t.name,");
         dbfcmd (dbproc, "length = convert(char(3),c.length),");
       dbfcmd (dbproc, "Prec = convert(char(5),c.prec)");
       dbfcmd (dbproc, " from %s.dbo.syscolumns c, %s.dbo.systypes t", sDbname, sDbname);
         dbfcmd (dbproc, " where c.id = object_id('%s')", TableList->TableName);
       dbfcmd (dbproc, " and c.usertype *= t.usertype");

       dbsqlexec (dbproc);

       // Process the results.
       if (dbresults (dbproc) == SUCCEED)
       {
        // Bind column to program variables.
          dbbind (dbproc, 1, NTBSTRINGBIND, 0, name);
          dbbind (dbproc, 2, NTBSTRINGBIND, 0, typename);
          dbbind (dbproc, 3, NTBSTRINGBIND, 0, length);
          dbbind (dbproc, 4, NTBSTRINGBIND, 0, precision);
              SlNo = 0;
          while (dbnextrow (dbproc) != NO_MORE_ROWS)
          {
             printf("%4d %-30s %-30s %-8s %-4s\n",++SlNo, name,typename, length, precision);
             //fprintf(ptr,"%4d %-30s %-30s %-8s %-4s\n",SlNo, name,typename, length, precision);
             fprintf(ptr,"%4d %-30s %s(%s)\n",SlNo, name,typename, length, precision);
             LinNo ++;
                     if (LinNo > PAGELENGTH)
                   {
                        //fprintf(ptr,"\n%c\n",12);
                //PrintTitle(ptr,"Tables and Indexes Listing",1,PageNo);
                  //PrintTitle(stdout,"Tables and Indexes Listing",1,PageNo++);
                         fprintf(ptr,"%c\n",12);
                              fprintf(ptr,
            "                          Tables and Indexes Listing    Page : %3d",PageNo++);
                    fprintf(ptr,
            "                          --------------------------    Date : %s", "04/04/1997");

                fprintf(ptr,"DATABASE  : %s\n", sDbname);
                        fprintf(ptr,"SERVER    : %s\t\tUSER NAME : %s\n", Server, UserName);
                  //fprintf(ptr,"USER NAME : %s\n", UserName);
                     LinNo = 6;
                    }
          }
              //getch();
         }
         dbfcmd (dbproc, "execute sp_helpindex %s",TableList->TableName );
         printf (    "\nSlNo Index Name           Index Description                                        Index Keys\n");                                                                                                                                                                                                                                                      
       printf (      "---- --------------- -------------------------------------------------------- --------------------------------------------------\n");
         fprintf(ptr,"\nSlNo Index Name           Index Description                                        Index Keys\n");                                                                                                                                                                                                                                                      
       fprintf(ptr,  "---- --------------- -------------------------------------------------------- --------------------------------------------------\n");

       dbsqlexec (dbproc);

       if (dbresults (dbproc) == SUCCEED)
       {
          dbbind (dbproc, 1, NTBSTRINGBIND, 0, IndexName);
          dbbind (dbproc, 2, NTBSTRINGBIND, 0, IndexDescription);
          dbbind (dbproc, 3, NTBSTRINGBIND, 0, IndexKeys);
              SlNo = 0;
          while (dbnextrow (dbproc) != NO_MORE_ROWS)
          {
             printf ("%4d %-20s %-56s %-50s\n",++SlNo, IndexName, IndexDescription, IndexKeys);
             fprintf(ptr,"%4d %-20s %-56s %-50s\n",SlNo, IndexName, IndexDescription, IndexKeys);
             LinNo ++;
                     if (LinNo > PAGELENGTH)
                   {
                        fprintf(ptr,"\n%c\n",12);
                //PrintTitle(ptr,"Tables and Indexes Listing",1,PageNo);
                  //PrintTitle(stdout,"Tables and Indexes Listing",1,PageNo++);
                              fprintf(ptr,
                         "                          Tables and Indexes Listing    Page : %3d",PageNo++);
                      fprintf(ptr,
                        "                          --------------------------    Date : %s", "04/04/1997");

                fprintf(ptr,"DATABASE  : %s\n", sDbname);
                        fprintf(ptr,"SERVER    : %s\t\tUSER NAME : %s\n", Server, UserName);
                  //fprintf(ptr,"USER NAME : %s\n", UserName);
                     LinNo = 6;
                    }
              }

         }

         if( (TableList = TableList->Next) == (struct Node *) NULL)
              break;

      }
    fclose(ptr);
    dbexit ();
    return (0);
}



int err_handler (PDBPROCESS dbproc, INT severity,
    INT dberr, INT oserr, LPCSTR dberrstr, LPCSTR oserrstr)
{
    printf ("DB-Library Error %i: %s\n", dberr, dberrstr);
    if (oserr != DBNOERR)
    {
        printf ("Operating System Error %i: %s\n", oserr, oserrstr);
    }
    return (INT_CANCEL);
}


int msg_handler (PDBPROCESS dbproc, DBINT msgno, INT msgstate,
    INT severity, LPCSTR msgtext, LPCSTR server,
    LPCSTR procedure, DBUSMALLINT line)
{
    printf ("SQL Server Message %ld: %s\n", msgno, msgtext);
    return (0);
}


0
 
flfmdllAuthor Commented:
Thank you very much.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now