September 22, 2006 at 3:15 pm
This is my first DTS package. The Stored Procedure parsed correctly but when I try to put in DTS (so I can ftp the output) I receive the following error message. Please help.
Error: Context: Error calling GetColumnInfo. Your provider does not support all the interfaces/methods required by DTS.
After some reading, I added the dbname.dbo in front of all tables on FROM and UPDATE stmts but still received the same error.
DECLARE
@seqnum int,
@student_id int,
@err varchar (256),
@student_bal money,
@facstaff_id int,
@facstaff_bal money,
@dt DATETIME, --Get current date
@dt2 VARCHAR (10), --Format current date as mmddyyyy
@amount numeric
set @seqnum=(select MAX(uni_seq)+1 from acu_uni_seq)
set @dt=(select CAST(LEFT(GETDATE()-DAY(GETDATE())+1,11) AS datetime))
set @dt2=(select REPLACE(CONVERT(VARCHAR(10), @dt, 101), '/', '') AS datetime)
set @amount=(select sum(technology_purse + tuition_purse+user_purse)*-1 from pharos.dbo.users where (technology_purse + tuition_purse + user_purse) < 0.10 and type = 'user' and group_id = '35192')
--insert into ACU_TEST..ACU.UNIPRINT_TEMP (BANNER_ID, DETAIL_CODE, UNI_FEED, AMOUNT, TIMESTAMP, BATCH_ID)
select 'UNIHEADER', 'HEAD', 'UNI_FEED', sum(technology_purse + tuition_purse + user_purse)*-1, '@DT2', 'UNI'+RIGHT('000' + CAST(@seqnum as varchar), 6)
from pharos.dbo.users
where (technology_purse + tuition_purse + user_purse) < 0.10
and type = 'user'
and group_id = '35192'
UNION ALL
select
card_id as Banner_ID, 'CPCG' as CPCG, 'UNI_FEED' as UNI_FEED,
(technology_purse + tuition_purse + user_purse)*-1 as Amount, '@DT2' as DATE, 'UNI'+RIGHT('000' + CAST(@seqnum as varchar), 6)
from pharos.dbo.users
where (technology_purse + tuition_purse + user_purse) < 0.10
and type = 'user'
and group_id = '35192'
insert into pharos.dbo.acu_uni_seq
values (@seqnum, getdate(), @amount)
select @err=@@error
if @err = 0
begin
--Reset student balances to 0
select @student_id = user_id from pharos.dbo.people where id = 'student' and type = 'Group'
update pharos.dbo.people_balances set user_purse = 0
from pharos.dbo.people_balances, pharos.dbo.people p
where people_balances.user_id = p.user_id
and p.group_id=@student_id and p.type='User'
and
(technology_purse + tuition_purse + user_purse) <= -0.10
and type = 'user'
and group_id = '35192'
update pharos.dbo.people_balances
set tuition_purse = 0
from pharos.dbo.people_balances, pharos.dbo.people p
where people_balances.user_id = p.user_id
and p.type='User'
and
(technology_purse + tuition_purse + user_purse) <= -0.10
and type = 'user'
and group_id = '35192'
update pharos.dbo.people_balances
set technology_purse = 0
from pharos.dbo.people_balances, pharos.dbo.people p
where people_balances.user_id = p.user_id
and p.group_id=@student_id and p.type='User'
and
(technology_purse + tuition_purse + user_purse) <= -0.10
and type = 'user'
and group_id = '35192'
--Reset FAC/STAFF and STU balances to 15.00 and 50.00 on Months 1,6, 8
if (SELECT DATEPART(month, GETDATE())) IN ('1','6','8')
begin
select @student_bal = balance from pharos.dbo.acu_start_balance where acu_group = 'student'
select @facstaff_bal = balance from pharos.dbo.acu_start_balance where acu_group = 'facstaff'
select @student_id = user_id from pharos.dbo.people where id = 'student' and type = 'Group'
update pharos.dbo.people_balances
set user_purse = @student_bal
from pharos.dbo.people_balances, pharos.dbo.people p
where people_balances.user_id = p.user_id
and p.type='User'
update pharos.dbo.people_balances
set tuition_purse = 0
from pharos.dbo.people_balances, pharos.dbo.people p
where people_balances.user_id = p.user_id
and p.type='User'
update pharos.dbo.people_balances
set technology_purse = 0
from pharos.dbo.people_balances, pharos.dbo.people p
where people_balances.user_id = p.user_id
and p.type='User'
select @facstaff_id = user_id from pharos.dbo.people where id = 'faculty_staff' and type = 'Group'
update pharos.dbo.people_balances
set user_purse = @facstaff_bal
from pharos.dbo.people_balances, pharos.dbo.people p
where people_balances.user_id = p.user_id
and p.type='User'
Update pharos.dbo.people_balances
set tuition_purse = 0
from pharos.dbo.people_balances, pharos.dbo.people p
where people_balances.user_id = p.user_id
and p.type='User'
update pharos.dbo.people_balances
set technology_purse = 0
from pharos.dbo.people_balances, pharos.dbo.people p
where people_balances.user_id = p.user_id
and p.type='User'
September 25, 2006 at 8:00 am
This was removed by the editor as SPAM
September 25, 2006 at 1:37 pm
In the DTS Package, which task did you use ? Did you remember to put in the connection?
If you wanted to exec the procedure, it seemed to me the procedure had a parameter, did you put in the parameter in the exec statement?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply