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