Solved

Weird Stored Procedures

Posted on 1998-12-08
9
221 Views
Last Modified: 2012-06-27
I have the following scenario. I have a stored procedure that refers to a table of data. Processes that data, inserting records into other tables,and then drops the original table.

What is happening is that the stored procedure is somehow getting bad data when it reads the table. I say this because the data entered into the other tables is wrong but if you look at the original table it is OK. Specifically, it is reading floats wrongly. Other varchar fields are OK.

If I create the table, recompile the stored procedure and then run it, it works. However, the next table breaks it again.

The procedure has to be compiled while the data table that will be processed is in existence. Note this must be the real data. An empty table of the same structure doesn't work.

How can I get the stored procedure to work repeatedly. I have tried creating the procedure with recompile, this makes no difference.
0
Comment
Question by:gbentley
  • 4
  • 3
  • 2
9 Comments
 
LVL 2

Expert Comment

by:tschill120198
Comment Utility
Can you post your sproc?
0
 
LVL 2

Expert Comment

by:tschill120198
Comment Utility
Can you post code to repro the problem?
0
 
LVL 5

Author Comment

by:gbentley
Comment Utility
OK, here is the text. Please note I didn't write the code. It was supplied by the vendors of the accounting system. I cannot therefore significantly change the procedure.

if exists (select * from sysobjects where id = object_id('dbo.bc_postpay_sp') and sysstat & 0xf = 4)
      drop procedure dbo.bc_postpay_sp
GO

CREATE PROC bc_postpay_sp AS    
DECLARE  
@journal_ctrl_num      varchar(16),
@yy                  int,
@mm                  int,
@dd                  int,
@ayy                  int,
@amm                  int,
@add                  int,
@jul_date_entered      int,
@gl_account            varchar(255),
@amt                  float,
@tot_bal            float,
@sequence_id            smallint,
@cnt_invalid_acct      int,
@comp_code            varchar(12),
@cur_code            varchar(12),
@journal_type            varchar(12),
@comp_id            smallint,
@date                  datetime,
@qty_float            float,
@qty                  varchar(16),
@description            varchar(255),
@jd                  integer,
@y                  varchar(2),
@m                  varchar(2),
@d                  varchar(2),
@yi                  int,
@mi                  int,
@di                  int,
@period_end_date      int,
@jrnl_desc            varchar(40),
@periodend            varchar(255),
@error_flag            smallint,
@batch_ctrl_num            varchar(16),
@user_name            varchar(10),
@batch_total            float,
@start_time            int,
@complete_time            int,
@next_num            int,
@num_mask            varchar(16),
@ec                  int



select @start_time=1200
select @complete_time=1201
select @comp_code="BCP"
select @comp_id=3
select @cur_code="AUD"
select @journal_type="PI"
select @jrnl_desc="Payroll Trxs"

/* get todays date */
SELECT
      @yy = datepart(yy,getdate()),
      @mm = datepart(mm,getdate()),
      @dd =  datepart(dd,getdate())

SET ROWCOUNT 0
EXEC APPJULDT_SP @yy, @mm, @dd, @jul_date_entered OUTPUT


DECLARE mp_crs CURSOR
FOR
      SELECT      periodend,
            glcode,
            timeunits,
            fee,
            narrative
      FROM      bc_payrolldata

OPEN mp_crs
FETCH mp_crs INTO @periodend, @gl_account, @qty_float, @amt, @description

IF (@@fetch_status = -1)
BEGIN
      close mp_crs
      deallocate mp_crs
      RAISERROR ('Empty cursor', 1, 2)
      return 1
END

IF (@@fetch_status = -2)
BEGIN
      close mp_crs
      DEALLOCATE mp_crs
      RAISERROR ('Unsuccessful', 1, 2)
      return 2
END


/* error checking */

if @periodend is null or @periodend=""
      return 3

if @gl_account is null or @gl_account=""
      return 4

if @qty_float is null
      return 5

if @amt is null
      return 6

if @amt =0 and @qty_float=0
      return 7

if @description is null
      select @description=""



/* get the next journal control number */
EXEC GLNXTTRX_SP  @journal_ctrl_num OUTPUT

IF EXISTS      (SELECT journal_ctrl_num  
            FROM gltrx
            WHERE journal_ctrl_num = @journal_ctrl_num)
      RETURN 8


/* get the next batch number */
select      @next_num=0,@num_mask="",@batch_ctrl_num=""
select      @next_num=next_batch_ctrl_num,
      @num_mask=batch_ctrl_num_mask
from      glnumber

update      glnumber
set      next_batch_ctrl_num=next_batch_ctrl_num+1

exec fmtctlnm_sp @next_num, @num_mask, @batch_ctrl_num output, @ec output

if @batch_ctrl_num="" or @batch_ctrl_num is null
      return 9

IF EXISTS      (SELECT batch_ctrl_num  
            FROM batchctl
            WHERE batch_ctrl_num = @batch_ctrl_num)
      RETURN 10



/* convert the periodend to a Julienne */
select @y=substring(@periodend,5,2)
select @m=substring(@periodend,3,2)
select @d=substring(@periodend,1,2)
select @yi=convert(int,@y)+1900
select @mi=convert(int,@m)
select @di=convert(int,@d)
exec appjuldt_sp @yi,@mi,@di, @jd output

/* get the apply date */
select      @period_end_date=min(period_end_date)
from      glprd
where      period_end_date>=@jd

if @period_end_date=0 or @period_end_date is null
      return 11


SELECT      @user_name=SUSER_NAME()
if @user_name="" or @user_name is null
      return 12


select @error_flag=0

begin tran

/* insert into gl tables */
INSERT INTO gltrx
(journal_type, journal_ctrl_num,  journal_description,  date_entered,  date_applied,
 recurring_flag,  repeating_flag,  reversing_flag,  hold_flag,  posted_flag,  date_posted,
 source_batch_code,  batch_code,  type_flag,  intercompany_flag,  company_code,  app_id,
 home_cur_code,  document_1,  trx_type,  user_id,  source_company_code,  process_group_num,
oper_cur_code)
 values   (@journal_type, @journal_ctrl_num, @jrnl_desc, @jul_date_entered,
 @period_end_date,  0,  0,  0,  0,  0,  0,  " ",  @batch_ctrl_num,  0,  0,  @comp_code,  6000,  
 @cur_code, "",  111,  1,  "",  " ", @cur_code)

if @@error<>0
      select @error_flag=13
else
begin
      SELECT @sequence_id=1

      WHILE (@@FETCH_STATUS=0)
      BEGIN
            select @qty=convert(varchar(16),@qty_float)

            INSERT INTO gltrxdet (journal_ctrl_num, sequence_id ,  rec_company_code,  company_id ,
             account_code,  description,  document_1,  document_2,  reference_code,  balance,
             nat_balance,  nat_cur_code,  rate,  posted_flag,  date_posted,  trx_type,
             offset_flag,  seg1_code,  seg2_code,  seg3_code,  seg4_code,  seq_ref_id,
            balance_oper, rate_oper, rate_type_home, rate_type_oper)
             values       (@journal_ctrl_num,  @sequence_id,  @comp_code,  @comp_id,  
            isnull(@gl_account," "),
             @description,  @qty,  @journal_ctrl_num,  " ",  isnull(@amt,0), isnull(@amt,0),
            @cur_code, 1,  0,
             0,  111,  0,  substring(@gl_account,1,5), substring(@gl_account,6,6),  
            substring(@gl_account,12,5),  "",  0, isnull(@amt,0), 1, "BUY", "BUY")
            
            if @@error<>0
            begin
                  select @error_flag=14
                  break
            end
            
            select @gl_account="",
                  @qty_float=0,
                  @amt=0,
                  @description="",
                  @sequence_id=@sequence_id+1

            FETCH mp_crs INTO @periodend, @gl_account, @qty_float, @amt, @description

      END /* while */
end

if @error_flag=0
begin
      select @batch_total=0
      SELECT      @batch_total = sum(balance)
      FROM      gltrxdet
      WHERE      journal_ctrl_num=@journal_ctrl_num
      and      balance>0

      /* insert into batchctrl table */
      insert into batchctl ( batch_ctrl_num, batch_description, start_date, start_time,
      completed_date, completed_time, control_number, control_total, actual_number,
      actual_total, batch_type, document_name, hold_flag, posted_flag, void_flag,
      selected_flag, number_held, date_applied, date_posted, time_posted, start_user,
      completed_user, posted_user, company_code, selected_user_id)
      values (@batch_ctrl_num, @jrnl_desc, @jul_date_entered, @start_time, @jul_date_entered,
      @complete_time, 1, @batch_total, 1, @batch_total, 6010, "Standard Transactions", 0, 0, 0,
      0, 0, @period_end_date, 0, 0, @user_name, @user_name, "", @comp_code, 0)

      if @@error<>0
            select @error_flag=15
end

if @error_flag<>0
      return @error_flag
else
      commit tran



CLOSE mp_crs
DEALLOCATE mp_crs


/* place errors on hold */

SELECT      @cnt_invalid_acct = count(*)
FROM      gltrxdet
WHERE      journal_ctrl_num=@journal_ctrl_num
AND      account_code not in (select account_code from glchart)
      
IF (@cnt_invalid_acct>0)
BEGIN
      UPDATE      gltrx
      SET      hold_flag = 1,
            journal_description=journal_description+"-INV GL AC"
      WHERE      journal_ctrl_num=@journal_ctrl_num

      UPDATE      gltrxdet
      SET      description=description+"-Invalid GL Acct"
      WHERE      journal_ctrl_num=@journal_ctrl_num
      AND      account_code not in (select account_code from glchart)

      update      batchctl
      set      hold_flag=1
      where       batch_ctrl_num=@batch_ctrl_num
END

/*      Ensure that the journal balances  */

select @tot_bal=0
SELECT      @tot_bal = sum(balance)
FROM      gltrxdet
WHERE      journal_ctrl_num=@journal_ctrl_num

if abs(@tot_bal) > 0.005
begin
       UPDATE      gltrx
      SET      hold_flag = 1,
            journal_description=journal_description+"-OUT OF BAL"
      WHERE      journal_ctrl_num=@journal_ctrl_num

      update      batchctl
      set      hold_flag=1
      where       batch_ctrl_num=@batch_ctrl_num
end

DROP TABLE bc_payrolldata

RETURN 0
GO

GRANT  EXECUTE  ON dbo.bc_postpay_sp  TO public
GO

GRANT  EXECUTE  ON dbo.bc_postpay_sp  TO gb
GO

GRANT  EXECUTE  ON dbo.bc_postpay_sp  TO jds
GO

GRANT  EXECUTE  ON dbo.bc_postpay_sp  TO rgh
GO

GRANT  EXECUTE  ON dbo.bc_postpay_sp  TO rak
GO

The table bc_payrolldata is exported to SQL from Access and then the SP is called. This will fail. If I comment out the "drop" at the end and then run the process, it still fails. If I then re-compile the SP and run it, it works.

0
 
LVL 7

Accepted Solution

by:
tchalkov earned 200 total points
Comment Utility
When you create a stored procedure SQL Server stores information about the tables it references. For example if your stored procedure executes select * from table1 and you add columns to table1 even after recompile it will not show the new columns,  you have to recreate the stored procedure. So if you want your code to work you must recreate your stored procedure everytime you drop and create again your table. this can be done by executing the code you supplied above.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 5

Author Comment

by:gbentley
Comment Utility
How do you mean by using the code above.

This process needs to be completely transparent to the user. It is a regular job that gets run over and over. Note that it is driven from an Access frontend that exports the table and then exec's the SP.

Can you be more precise in your answer?

Also could you explain why it sometimes works given the above explanation. That is I have successfully run the SP on a few occasions. Without having to recompile.
0
 
LVL 7

Expert Comment

by:tchalkov
Comment Utility
Put the code you supplied(including if exists...) into a text file. Then every time you want to recreate the stored proc you have to execute:
isql -E -S yourserver -d yourdatabase -i yourfilename
or
isql -S yourserver -U login -P password -d yourdatabase -i yourfilename
The first is if you use integrated security.
You can execute it by xp_cmdshell or by creating a task.
About your second question - I don't know why it sometimes works - BooksOnline says that it should not work. May be the reason is that the new table has absolutely the same structure as the old one.
0
 
LVL 5

Author Comment

by:gbentley
Comment Utility
The tables always have exactly the same structure so if that is the reason it sometimes works, it should always work. I didn't ask just to be difficult but because I am concerned that the solution is bullet proof as the users have no SQL skills at all and I would like to be able to go on holidays sometimes! This is a mission critical process.

As to your solution. I understand the idea, however, please tell me if this can be done as a pass-through query from Access. If so what would be the exact syntax to pass to SQL to reload the procedure.

Note that this is done on several databases, so I guess I need a different text file for each database. I would have to store the names of those in an Access table and then construct the SQL string on the fly, I guess? This is because there will be more DBs in the future and I would like to build a long term solution without hardwired DB names.

One last point, isn't this what "with recompile" is supposed to achieve?


0
 
LVL 7

Expert Comment

by:tchalkov
Comment Utility
You can easy run this from access using pass-through query but there is also another way - run isql directly from access using VBA - Shell function. For example
Shell 'isql -S yourserver -U login -P password -d yourdatabase -i yourfilename'
Or from a macros - RunApp
With recompile is not the same as recreating the procedure. It seems that SQL Server uses internal pointers to the tables which are used by stored procedure and they are not updated when recompile is used.
0
 
LVL 5

Author Comment

by:gbentley
Comment Utility
Thanks for that.

I have decided to make the table permanent. Each time the SP runs it will delete the records. Access can then refill the table for the next run.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now