Error in .exe if its launched with CreateProcess

Error in .exe if its launched with CreateProcess

Post by Fidel Davila Villagome » Tue, 22 Feb 2000 04:00:00



hello everybody:

  - Is a C programm and connect to MsSql 7.0 trough 7.0 using ODBC

  - The Server configuration is 8GB of memory, 2 Hard drives (1 for
data, 1 for log),
    2 processors (Can remember the speed)

   - Sqlserver installed on Windows Nt Server 4.0 SP4

  - Compiling with Visual Studio.

    Waht is happenning is the following:

- I execute 3 stored procedures that have to recover infomacion from a
series of tables
 (Select statement) and inserted that info in a table (INSERT INTO) ie:

 INSERT INTO EP_TEMP_GAP_ANAL_RESULT_DET (
  EP_REPORT_LINE_DATE        ,EP_CURRENCY_CODE,
  EP_SYSTEM_DEAL_ID          ,EP_INSTRUMENT_ID,
  EP_AMOUNT,                  EP_PAY_REC_INDICATOR,
  EP_INTEREST_RATE           ,EP_PRINCIPAL_AMOUNT,
  EP_PRIN_INT_INDICATOR      ,EP_YIELD_CURVE_CODE,
      EP_STRID,EP_MATDATE,
  EP_INTEREST_BASIS_CODE,EP_CONTRIBUTING_AMOUNT)

 (SELECT ae_value_date, ae_currency_code,
  -1,  ae_instrument_id,
  SUM(ae_cashflow_amount * ag_position_amount) AS theFlowAmount,'P' AS
PayRec,

  ae_interest_rate, SUM(ag_position_amount) AS pamount,
  ae_prin_int_indicator,cb_yield_curve_code,'inst' AS strid,
  an_maturity_date,cd_interest_basis_code,
  (case
   when ae_currency_code <> 'EUR' THEN
SUM(ag_position_amount)/cd_spot_rate_mid
   else SUM(ag_position_amount)
   end) as contamount

 FROM   ae_inscashflow as ae
 JOIN   ag_insposition as ag ON (ae.ae_instrument_id =
ag.ag_instrument_id)
 JOIN   ad_ins AS ad ON (ad.ad_instrument_id = ae.ae_instrument_id)
 JOIN   cb_inst AS cb ON (ad.ad_instrument_type_id =
cb_instrument_type_id)
 JOIN   an_binfo as an ON (an.AN_INSTRUMENT_ID = ae.ae_instrument_id)
 JOIN   cd_curr as cd ON (ae_currency_code = cd_currency_code)

 WHERE  ae_indicator = 'Y' AND

ae_value_date is null)
 GROUP BY ae_value_date,ae_currency_code,
  ae_instrument_id,
  ae_interest_rate,ae_interest_basis_code,
  ae_interest_period_end_date,
  ae_prin_int_indicator,

cb_yield_curve_code,an_maturity_date,cd_interest_basis_code,cd_spot_rate_mid
)

- 2 of the stored procedure insert nearly 100000 rows each

- 1 of the stored procedures insert nearly 7000000 rows.
- As a result in the table we have  9000000 to 1000000 (one millon)
rows.

When I execute the .exe from the command prompt the program executes
perfectly, but
when I execute it using an API call CreateProcess, the insercion is not
done properly
sometimes insert 1000 or 10000 but never all of them. I have to say that
the return code
from the call to the stored procedure does not said that the procedure
has failed (At least
partially, because the isnerted rows are right) also I cannot see in the
sql server log
something that could show that the procedure has failed or something.

The call to the exe that use the CreateProcess is done by a service that
is installed and
executed  in the same pc where the database resides, that service is
running using the local
system account.

For more information the call is done like this:

       /* Structure for the process  */
      STARTUPINFO startup;

      PROCESS_INFORMATION eso_pinfo;

      /* initialization */
      startup.cb               = sizeof (STARTUPINFO);
      startup.lpReserved       = 0;
      startup.lpDesktop        = NULL;
      startup.lpTitle          = NULL;
      startup.dwX              = 0;
      startup.dwY              = 0;
      startup.dwXSize          = 0;
      startup.dwYSize          = 0;
      startup.dwXCountChars    = 0;
      startup.dwYCountChars    = 0;
      startup.dwFillAttribute  = 0;
      startup.dwFlags          = 0;
      startup.wShowWindow      = 0;
      startup.cbReserved2      = 0;
      startup.lpReserved2      = 0;

          CreateProcess(NULL, /* lpszimagename */
                       ac_exeparam, /* commandline */
                       NULL, /* atributos de seguridad */
                       NULL, /* lpsathread */
                       TRUE, /* INHERIT HANDLE CREATE_NEW_CONSOLE| */
                       CREATE_NEW_PROCESS_GROUP |
REALTIME_PRIORITY_CLASS,
                       NULL, /* ENVIRONMENT */
                       NULL, /* LPSZCURDIR */
                       &startup,
                       &eso_pinfo);

- THE ARE NOT MULTIPLE PROCESS WORKING IN THE SAME TABLE
- The definition of EP_TEMP_GAP_ANAL_RESULT_DET is the following

CREATE TABLE [dbo].[EP_TEMP_GAP_ANAL_RESULT_DET] (
 [EP_REPORT_LINE_DATE] [datetime] NULL ,
 [EP_SYSTEM_DEAL_ID] [numeric](10, 0) NULL ,
 [EP_INSTRUMENT_ID] [varchar] (10) NULL ,
 [EP_CURRENCY_CODE] [char] (3) NULL ,
 [EP_CONTRIBUTING_AMOUNT] [numeric](18, 4) NULL ,
 [EP_AMOUNT] [numeric](19, 4) NULL ,
 [EP_PAY_REC_INDICATOR] [char] (1) NULL ,
 [EP_INTEREST_BASIS_CODE] [varchar] (16) NULL ,
 [EP_INTEREST_RATE] [numeric](18, 8) NULL ,
 [EP_PRINCIPAL_AMOUNT] [numeric](19, 4) NULL ,
 [EP_PRIN_INT_INDICATOR] [char] (1) NULL ,
 [EP_YIELD_CURVE_CODE] [varchar] (6) NULL ,
 [EP_STRID] [varchar] (20) NULL ,
 [EP_MATDATE] [datetime] NULL ,
 [EP_LEG_SEQ_NO] [varchar] (4) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EP_TEMP_GAP_ANAL_RESULT_DET] WITH NOCHECK ADD
 CONSTRAINT [DF_EP_TEMP_GAP_ANAL_RESULT_DET_EP_SEQ_NO] DEFAULT ('NA')
FOR
[EP_LEG_SEQ_NO]
GO

 CREATE  INDEX [INDX_SDIDCCPRRD] ON
[dbo].[EP_TEMP_GAP_ANAL_RESULT_DET]([EP_SYSTEM_DEAL_ID],
[EP_INSTRUMENT_ID],
[EP_CURRENCY_CODE], [EP_PAY_REC_INDICATOR], [EP_REPORT_LINE_DATE]) ON
[PRIMARY]
GO

- Each time that i execute those stored procedure (wherever i run it)
they use
a lot of memory, finishing the physical memory and using also the paging
file,
causing that the performance of those stored procedures drop on each
execution.

- Is a specific problem taht a service pack for mssql can solve?

- THE WORSE THING IS THAT RUNNING THE .EXE FROM THE COMMAND
PROMPT NEVER FAILS.

WHAT CAN BE WRONG!!!!!!

THANKS FOR YOUR HELP.

Fidel Davila Villagomez.
Comit Gruppe

 
 
 

Error in .exe if its launched with CreateProcess

Post by Neil Pik » Wed, 23 Feb 2000 04:00:00


Fidel- I would use SQL Trace/Profiler to compare the executions of the two
runs.  SQL shouldn't care where the commands are coming from....

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (412 entries) see

http://forumsb.compuserve.com/vlforums/UK/default.asp?SRV=MSDevApps
 or www.ntfaq.com/sql.html (+ ntfaq download)
 or http://www.sql-server.co.uk

 
 
 

1. textcopy.exe in c++ with CreateProcess

I run the following command in dos and it works fine

C:\mssql7\binn\textcopy.exe /Slocalhost /Uusername /Ppassword
/Ddatabase /Ttable /Ccolumn /W"Where ID='198300' "
/F"C:\Temp\asdfasdf" /I

it works fine in dos then I added it to my c++ applike this

String command="C:\mssql7\binn\textcopy.exe /Slocalhost /Uusername
/Ppassword /Ddatabase /Ttable /Ccolumn /W"Where ID='198300' "
/F"C:\Temp\asdfasdf" /I";

CreateProcess( NULL, command, NULL,NULL,FALSE,0, NULL,NULL,&si,&pi )

The command executes and the sql profiler shows it runs, but No data
is stored when using the C++ version.
Permissions are fine on the dirs allowing all to read just not storing
the data.

the only difference I have found from a sql profiler is that the dos
command line returns this additional line

-- writetext bulk table.column 0xf6ff14c602000000e531000001004a00
timestamp = 0xf6ff14c602000000 with log

Anyone know what could be up? I really thought I had this working, I
can get the data to come out not problem in C++ , but not in?

2. Error 429 "You do not have an appropriate licence to use this functionality"

3. Scheduling a DTS launching a .exe with extra parameters

4. D3 Linux newbie question

5. launching an EXE from within a VB DLL

6. @ ... GET Popups

7. vb exe launch with shell

8. OLEDB for IDS 9.2

9. Launch exe file from trigger

10. FP 2.6 exe launch with FP Screen first

11. launch an Activex Exe with arguments?

12. Launching an exe from FoxPro