Weird Stored Procedures

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.
Who is Participating?
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.
Can you post your sproc?
Can you post code to repro the problem?
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

gbentleyAuthor Commented:
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

CREATE PROC bc_postpay_sp AS    
@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 */
      @yy = datepart(yy,getdate()),
      @mm = datepart(mm,getdate()),
      @dd =  datepart(dd,getdate())

EXEC APPJULDT_SP @yy, @mm, @dd, @jul_date_entered OUTPUT

      SELECT      periodend,
      FROM      bc_payrolldata

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

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

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

/* 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,
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 */
(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,
 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
      SELECT @sequence_id=1

            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
                  select @error_flag=14
            select @gl_account="",

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

      END /* while */

if @error_flag=0
      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

if @error_flag<>0
      return @error_flag
      commit tran

CLOSE 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)
      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

/*      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
       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

DROP TABLE bc_payrolldata


GRANT  EXECUTE  ON dbo.bc_postpay_sp  TO public

GRANT  EXECUTE  ON dbo.bc_postpay_sp  TO gb

GRANT  EXECUTE  ON dbo.bc_postpay_sp  TO jds

GRANT  EXECUTE  ON dbo.bc_postpay_sp  TO rgh

GRANT  EXECUTE  ON dbo.bc_postpay_sp  TO rak

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.

gbentleyAuthor Commented:
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.
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
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.
gbentleyAuthor Commented:
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?

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.
gbentleyAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.