Skip to Content
0
Former Member
Dec 02, 2008 at 04:23 PM

sql stored proc problem

36 Views

i have a stored procedure as follows;

the problem is it doesnt create the tables when it is run as a whole,

it will only work if i run each one seperate

any ideas why? sql 2000

CREATE TABLE #temp_VENDOR_CHANGES

(HISTORY_ID VARCHAR(8),

VENDOR_KEY varchar(10),

VENDOR_NAME varchar(30),

REQUESTED_BY VARCHAR(255),

EN_REMT_KEY varchar(10),

REMIT_NAME varchar(30),

ADDRESS_KEY INT,

ADDRESS_NAME VARCHAR(30),

ADDRESS_1 varchar(30),

ADDRESS_2 varchar(30),

ADDRESS_3 varchar(30),

ADDRESS_CITY varchar(20),

ADDRESS_STATE varchar(10),

ADDRESS_ZIP varchar(10),

STATUS varchar(10),

VEND_TERMS VARCHAR(2),

REMIT_TERMS VARCHAR(2),

VEND_PRIORITY INT,

REMIT_PRIORITY INT,

VEND_PAYTYPE INT,

REMIT_PAYTYPE INT,

VEND_1099 INT,

REMIT_1099 INT,

VEND_HOLD INT,

REMIT_HOLD INT,

VEND_CRT_DATE DATETIME,

VEND_CHG_DATE DATETIME,

VEND_AUD_DATE DATETIME,

REMIT_CRT_DATE DATETIME,

REMIT_CHG_DATE DATETIME,

REMIT_INV_DATE DATETIME,

DBName varchar(30),

HISTORY_UPDATE_DATE DATETIME)

/* ********************************************************************************** */

/* STEP 3: Insert into Temp table. */

/* ********************************************************************************** */

SET NOCOUNT ON

INSERT INTO #temp_VENDOR_CHANGES

SELECT distinct

YEAR(Getdate())10000 + MONTH(Getdate())100 + DAY(Getdate())history_ID,

en_vend_tbl.en_vend_key VENDOR_KEY ,

en_vend_tbl.en_vend_name VENDOR_NAME ,

'' REQUESTED_BY ,

en_remt_tbl.en_remt_key EN_REMT_KEY ,

en_remt_tbl.en_remt_name REMIT_NAME ,

im_adres_tbl.im_adres_key ADDRESS_KEY ,

im_adres_tbl.im_adres_name ADDRESS_NAME ,

im_adres_tbl.im_adres_line1 ADDRESS_1 ,

im_adres_tbl.im_adres_line2 ADDRESS_2 ,

im_adres_tbl.im_adres_line3 ADDRESS_3 ,

im_adres_tbl.im_adres_city ADDRESS_CITY ,

im_adres_tbl.im_adres_state ADDRESS_STATE ,

im_adres_tbl.im_adres_pczip ADDRESS_ZIP ,

en_vend_tbl.en_stats_key STATUS ,

en_vend_tbl.en_terms_key VEND_TERMS ,

en_remt_tbl.en_terms_key REMIT_TERMS ,

en_vend_tbl.en_vend_pri VEND_PRIORITY ,

en_remt_tbl.en_remt_pri REMIT_PRIORITY ,

en_vend_tbl.en_vend_paytp VEND_PAYTYPE ,

en_remt_tbl.en_remt_paytp REMIT_PAYTYPE ,

en_vend_tbl.en_vend_1099f VEND_1099 ,

en_remt_tbl.en_remt_1099f REMIT_1099 ,

en_vend_tbl.en_vend_holdf VEND_HOLD ,

en_remt_tbl.en_remt_holdf REMIT_HOLD ,

en_vend_tbl.en_vend_crtdt VEND_CRT_DATE ,

en_vend_tbl.en_vend_chgdt VEND_CHG_DATE ,

en_vend_tbl.en_vend_auddt VEND_AUD_DATE ,

en_remt_tbl.en_remt_crtdt REMIT_CRT_DATE ,

en_remt_tbl.en_remt_crtdt REMIT_CHG_DATE ,

en_remt_tbl.en_remt_invdt REMIT_INV_DATE ,

DB_Name() DBName ,

GETDATE() HISTORY_UPDATE_DATE

FROM en_vend_tbl

INNER JOIN

en_remt_tbl ON

en_vend_tbl.en_vend_key = en_remt_tbl.en_vend_key

INNER JOIN

en_rmtad_tbl ON

en_remt_tbl.en_remt_key = en_rmtad_tbl.en_remt_key

INNER JOIN

im_adres_tbl ON

en_rmtad_tbl.im_adres_key = im_adres_tbl.im_adres_key

/* ********************************************************************************** */

/* STEP 4: UPDATE VENDOR CHANGES HISTORY TABLE */

/* ********************************************************************************** */

/----


/

/ -Insert new statistics. --


/

/----


/

SELECT distinct

YEAR(Getdate())10000 + MONTH(Getdate())100 + DAY(Getdate())history_ID,

VENDOR_KEY,

VENDOR_NAME,

REQUESTED_BY,

EN_REMT_KEY,

REMIT_NAME,

ADDRESS_KEY,

ADDRESS_NAME,

ADDRESS_1,

ADDRESS_2,

ADDRESS_3,

ADDRESS_CITY,

ADDRESS_STATE,

ADDRESS_ZIP,

STATUS ,

VEND_TERMS,

REMIT_TERMS,

VEND_PRIORITY,

REMIT_PRIORITY,

VEND_PAYTYPE,

REMIT_PAYTYPE,

VEND_1099,

REMIT_1099,

VEND_HOLD,

REMIT_HOLD,

VEND_CRT_DATE,

VEND_CHG_DATE,

VEND_AUD_DATE,

REMIT_CRT_DATE,

REMIT_CHG_DATE,

REMIT_INV_DATE,

DBName,

getdate() HISTORY_UPDATE_DATE

into dbo.VENDOR_CHANGES_HISTORY

FROM #Temp_VENDOR_CHANGES

/----


/

/ -Insert update statistics. --


/

/----


/

insert into dbo.VENDOR_CHANGES_HISTORY

SELECT distinct

YEAR(Getdate())10000 + MONTH(Getdate())100 + DAY(Getdate())history_ID,

VENDOR_KEY,

VENDOR_NAME,

REQUESTED_BY,

EN_REMT_KEY,

REMIT_NAME,

ADDRESS_KEY,

ADDRESS_NAME,

ADDRESS_1,

ADDRESS_2,

ADDRESS_3,

ADDRESS_CITY,

ADDRESS_STATE,

ADDRESS_ZIP,

STATUS ,

VEND_TERMS,

REMIT_TERMS,

VEND_PRIORITY,

REMIT_PRIORITY,

VEND_PAYTYPE,

REMIT_PAYTYPE,

VEND_1099,

REMIT_1099,

VEND_HOLD,

REMIT_HOLD,

VEND_CRT_DATE,

VEND_CHG_DATE,

VEND_AUD_DATE,

REMIT_CRT_DATE,

REMIT_CHG_DATE,

REMIT_INV_DATE,

DBName,

getdate() HISTORY_UPDATE_DATE

FROM #Temp_VENDOR_CHANGES

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO