Wade,
I use an ODBC connection from a MS Server 2003 to a MS SQL Server 64-bit on a different box. When I develop an application I do it on my work station that has the same ODBC connections by name so when I save it over to our production server it all works without any modifications. We have a developer's SQL user which we use for QlikView and MS SSRS. Our techs set up the user to allow for admin level. The first time it was set up, we had to get them to modify it because it was set for read only access. This maybe something you might need to consider, since you are unable to write to both #Temp Tables and Perm Tables.
Example:
This has run every week without fail for over a year.
/****** Object: StoredProcedure [dbo].[Leave_Bal_Term] Script Date: 06/23/2010 08:06:42 ******/
SET ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
procedure [dbo]. [Leave_Bal_Term]
@ts_date
datetime
AS
declare
@FY_CD varchar(4)
select
@FY_CD = year_no_cd from TS_SCHEDULE_PERIOD where end_dt =@ts_date
--pull in hours for currrent period for calculation
create
TABLE #tmp
(EMPL_ID VARCHAR(20), LV_TERM_DT DATETIME, LV_TYPE_CD VARCHAR(4),BIWKHOURS DECIMAL(18,2), Lv_Bal DECIMAL(18,2))
INSERT
#TMP
select empl_id, TERM_DT, ' ',0.0,0.0 from empl where empl_status_cd = 'ACT' and term_dt is not null
CREATE TABLE #TMPHOURS
(EMPL_ID VARCHAR(20), BIWKHOURS DECIMAL(18,2))
INSERT
#TMPHOURS
SELECT EMPL_ID, SUM(ENTERED_HRS) FROM ts_ln
WHERE ts_dt = @ts_date and pay_type = 'R'
AND EMPL_ID IN (select empl_id from empl where empl_status_cd = 'ACT' and term_dt is not null)
GROUP BY EMPL_ID
CREATE TABLE #TMPLVBAL
( EMPL_ID VARCHAR(20), LV_TYPE_CD VARCHAR(4), BEG_BAL DECIMAL(18,2), YTD_ACCRD_HRS DECIMAL(18,2), YTD_USED_HRS DECIMAL(18,2), CRNT_USED_LV_HOURS DECIMAL(18,2))
INSERT
#TMPLVBAL
SELECT EMPL_ID, lv_type_cd, beg_bal_hrs , ytd_accrd_hrs , ytd_used_hrs, 0.0
FROM EMPL_LV_BAL
WHERE lv_id not in ('HOL', 'UPL', 'FML') and lv_yr_no = @FY_CD
and empl_id in (select empl_id from empl where empl_status_cd = 'ACT' and term_dt is not null)
UPDATE
#TMPLVBAL
SET CRNT_USED_LV_HOURS = ENTERED_HRS
FROM TS_LN d
WHERE #TMPLVBAL. EMPL_ID = d. EMPL_ID AND acct_cd in ('95000-021' , '95000-005') AND TS_DT = @TS_DATE
UPDATE
#TMP
SET BIWKHOURS = #TMPHOURS. BIWKHOURS
FROM
#TMPHOURS
WHERE #TMP. EMPL_ID = #TMPHOURS. EMPL_ID
UPDATE
#TMP
SET LV_BAL = BEG_BAL + YTD_ACCRD_HRS - YTD_USED_HRS - CRNT_USED_LV_HOURS,
LV_TYPE_CD = #TMPLVBAL. LV_TYPE_CD
FROM
#TMPLVBAL
WHERE #TMP. EMPL_ID = #TMPLVBAL. EMPL_ID
UPDATE
#TMP
SET LV_BAL = LV_BAL + ((BIWKHOURS/80)*4)
where LV_TYPE_CD = 'PERSONAL'
UPDATE
#TMP
SET LV_BAL = LV_BAL + ((BIWKHOURS/80)*3)
where
LV_TYPE_CD = 'PS'
UPDATE
#TMP
SET LV_BAL = LV_BAL +3
where LV_TYPE_CD = 'LS'
SELECT * FROM #TMP ORDER BY EMPL_ID
DROP TABLE #TMP
DROP TABLE #TMPHOURS
DROP TABLE #TMPLVBAL
GO
Load script
ODBC Connection string
Leave_Bal:
SQL Execute Leave_Bal_Term '6-22-2010';
Hope this helps
Allen
