Need Help in DTS-Package - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need Help in DTS-Package

Author

Topic

shakilhyd
Starting Member

16 Posts

Posted - 2008-07-26 : 11:50:50

Hi,

I am working on DTS-Package and got error message. I think DTS-package does not allow temporary table and in the following script #ter table is being used. Temp table #ter is being used in the INSERT INTO. How can we fix it and avoid temp table which is #ter. Can you please help in this script.

SET @START = dateadd(day, -7, getdate()) --- Last running date, last Thursday
SET @END = getdate() --- Running (today)'s date : each Thursday

----Find employees who are terminated
select c.date, m.*
into #ter
from member m
join member_h mh on m.clientid = mh.clientid and m.empid = mh.empid and m.changeid = mh.histchangeid
join change c on m.changeid = c.changeid
where m.status = 'T'
and c.tablename = 'member'
and c.date >= @start
and c.date <= @end

--select empid,* from #ter

INSERT INTO temp_feed (clientid, division, empid, sin, ee_add, ben_type, ben_opt, policy, cat)
SELECT t.clientid, t.division, t.empid, t.sin, 'Y', c.type, c.optcode, pol.policy, c.cat
FROM #ter AS t
left JOIN curben AS c ON t.clientid = c.clientid and t.empid = c.empid
left JOIN planctrl AS pc ON t.empstat = pc.empstat AND t.plancode = pc.plancode AND t.division = pc.division
left JOIN policy1 AS pol ON pol.polind = pc.polind AND c.type = pol.type AND c.optcode = pol.optcode

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-26 : 12:41:22

quote:
Originally posted by shakilhyd

Hi,

I am working on DTS-Package and got error message. I think DTS-package does not allow temporary table and in the following script #ter table is being used. Temp table #ter is being used in the INSERT INTO. How can we fix it and avoid temp table which is #ter. Can you please help in this script.

Please show error message.
DTS allows temp table.

Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-26 : 14:04:48

quote:
Originally posted by shakilhyd

Hi,

I am working on DTS-Package and got error message. I think DTS-package does not allow temporary table and in the following script #ter table is being used. Temp table #ter is being used in the INSERT INTO. How can we fix it and avoid temp table which is #ter. Can you please help in this script.

SET @START = dateadd(day, -7, getdate()) --- Last running date, last Thursday
SET @END = getdate() --- Running (today)'s date : each Thursday

----Find employees who are terminated
select c.date, m.*
into #ter
from member m
join member_h mh on m.clientid = mh.clientid and m.empid = mh.empid and m.changeid = mh.histchangeid
join change c on m.changeid = c.changeid
where m.status = 'T'
and c.tablename = 'member'
and c.date >= @start
and c.date <= @end

--select empid,* from #ter

INSERT INTO temp_feed (clientid, division, empid, sin, ee_add, ben_type, ben_opt, policy, cat)
SELECT t.clientid, t.division, t.empid, t.sin, 'Y', c.type, c.optcode, pol.policy, c.cat
FROM #ter AS t
left JOIN curben AS c ON t.clientid = c.clientid and t.empid = c.empid
left JOIN planctrl AS pc ON t.empstat = pc.empstat AND t.plancode = pc.plancode AND t.division = pc.division
left JOIN policy1 AS pol ON pol.polind = pc.polind AND c.type = pol.type AND c.optcode = pol.optcode

Thanks

whats the error message? I dont think there's any problem with use of temporary tables in dts. Also you could first try running the query in Query Analyser to see the error before putting this to DTS. or even wrap it in a stored procedure and call it from dts.

shakilhyd
Starting Member

16 Posts

Posted - 2008-07-26 : 19:12:16

Hi,
Error message is
No rowset was returened from the SQL Statement

Please help me about this DTS-Package.
Thanks.

quote:
Originally posted by shakilhyd

Hi,

I am working on DTS-Package and got error message. I think DTS-package does not allow temporary table and in the following script #ter table is being used. Temp table #ter is being used in the INSERT INTO. How can we fix it and avoid temp table which is #ter. Can you please help in this script.

SET @START = dateadd(day, -7, getdate()) --- Last running date, last Thursday
SET @END = getdate() --- Running (today)'s date : each Thursday

----Find employees who are terminated
select c.date, m.*
into #ter
from member m
join member_h mh on m.clientid = mh.clientid and m.empid = mh.empid and m.changeid = mh.histchangeid
join change c on m.changeid = c.changeid
where m.status = 'T'
and c.tablename = 'member'
and c.date >= @start
and c.date <= @end

--select empid,* from #ter

INSERT INTO temp_feed (clientid, division, empid, sin, ee_add, ben_type, ben_opt, policy, cat)
SELECT t.clientid, t.division, t.empid, t.sin, 'Y', c.type, c.optcode, pol.policy, c.cat
FROM #ter AS t
left JOIN curben AS c ON t.clientid = c.clientid and t.empid = c.empid
left JOIN planctrl AS pc ON t.empstat = pc.empstat AND t.plancode = pc.plancode AND t.division = pc.division
left JOIN policy1 AS pol ON pol.polind = pc.polind AND c.type = pol.type AND c.optcode = pol.optcode

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-26 : 19:27:05

Do those queries return any result in query analyzer?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-27 : 02:08:16

quote:
Originally posted by shakilhyd

Hi,
Error message is
No rowset was returened from the SQL Statement

Please help me about this DTS-Package.
Thanks.

quote:
Originally posted by shakilhyd

Hi,

I am working on DTS-Package and got error message. I think DTS-package does not allow temporary table and in the following script #ter table is being used. Temp table #ter is being used in the INSERT INTO. How can we fix it and avoid temp table which is #ter. Can you please help in this script.

SET @START = dateadd(day, -7, getdate()) --- Last running date, last Thursday
SET @END = getdate() --- Running (today)'s date : each Thursday

----Find employees who are terminated
select c.date, m.*
into #ter
from member m
join member_h mh on m.clientid = mh.clientid and m.empid = mh.empid and m.changeid = mh.histchangeid
join change c on m.changeid = c.changeid
where m.status = 'T'
and c.tablename = 'member'
and c.date >= @start
and c.date <= @end

--select empid,* from #ter

INSERT INTO temp_feed (clientid, division, empid, sin, ee_add, ben_type, ben_opt, policy, cat)
SELECT t.clientid, t.division, t.empid, t.sin, 'Y', c.type, c.optcode, pol.policy, c.cat
FROM #ter AS t
left JOIN curben AS c ON t.clientid = c.clientid and t.empid = c.empid
left JOIN planctrl AS pc ON t.empstat = pc.empstat AND t.plancode = pc.plancode AND t.division = pc.division
left JOIN policy1 AS pol ON pol.polind = pc.polind AND c.type = pol.type AND c.optcode = pol.optcode

Thanks

what was result when you ran it in query analyser? also do this step has some preceding steps which populates some tables used by this?

shakilhyd
Starting Member

16 Posts

Posted - 2008-07-27 : 02:33:42

The following query working pefectly in Query analyzer but problem is in DTS.
Hi,
I am working on DTS-Package and got error message.
The error message is
No rowset was returened from the SQL Statement.

I think the problem is in #ter but i am not sure. Please advise.

Script is
----------
I have declared start and end.

SET @START = dateadd(day, -7, getdate()) --- Last running date, last Thursday
SET @END = getdate() --- Running (today)'s date : each Thursday

----Find employees who are terminated
select c.date, m.*
into #ter
from member m
join member_h mh on m.clientid = mh.clientid and m.empid = mh.empid and m.changeid = mh.histchangeid
join change c on m.changeid = c.changeid
where m.status = 'T'
and c.tablename = 'member'
and c.date >= @start
and c.date <= @end

--select empid,* from #ter

INSERT INTO temp_feed (clientid, division, empid, sin, ee_add, ben_type, ben_opt, policy, cat)
SELECT t.clientid, t.division, t.empid, t.sin, 'Y', c.type, c.optcode, pol.policy, c.cat
FROM #ter AS t
left JOIN curben AS c ON t.clientid = c.clientid and t.empid = c.empid
left JOIN planctrl AS pc ON t.empstat = pc.empstat AND t.plancode = pc.plancode AND t.division = pc.division
left JOIN policy1 AS pol ON pol.polind = pc.polind AND c.type = pol.type AND c.optcode = pol.optcode

Thanks.

quote:
Originally posted by visakh16

quote:
Originally posted by shakilhyd

Hi,
Error message is
No rowset was returened from the SQL Statement

Please help me about this DTS-Package.
Thanks.

quote:
Originally posted by shakilhyd

Hi,

I am working on DTS-Package and got error message. I think DTS-package does not allow temporary table and in the following script #ter table is being used. Temp table #ter is being used in the INSERT INTO. How can we fix it and avoid temp table which is #ter. Can you please help in this script.

SET @START = dateadd(day, -7, getdate()) --- Last running date, last Thursday
SET @END = getdate() --- Running (today)'s date : each Thursday

----Find employees who are terminated
select c.date, m.*
into #ter
from member m
join member_h mh on m.clientid = mh.clientid and m.empid = mh.empid and m.changeid = mh.histchangeid
join change c on m.changeid = c.changeid
where m.status = 'T'
and c.tablename = 'member'
and c.date >= @start
and c.date <= @end

--select empid,* from #ter

INSERT INTO temp_feed (clientid, division, empid, sin, ee_add, ben_type, ben_opt, policy, cat)
SELECT t.clientid, t.division, t.empid, t.sin, 'Y', c.type, c.optcode, pol.policy, c.cat
FROM #ter AS t
left JOIN curben AS c ON t.clientid = c.clientid and t.empid = c.empid
left JOIN planctrl AS pc ON t.empstat = pc.empstat AND t.plancode = pc.plancode AND t.division = pc.division
left JOIN policy1 AS pol ON pol.polind = pc.polind AND c.type = pol.type AND c.optcode = pol.optcode

Thanks

what was result when you ran it in query analyser? also do this step has some preceding steps which populates some tables used by this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-27 : 13:47:45

have you tried the query after dispensing with the temp table? somethink like

SET @START = dateadd(day, -7, getdate()) --- Last running date, last Thursday 
SET @END = getdate() --- Running (today)'s date : each Thursday 

----Find employees who are terminated 

--select empid,* from #ter

INSERT INTO temp_feed (clientid, division, empid, sin, ee_add, ben_type, ben_opt, policy, cat)
SELECT t.clientid, t.division, t.empid, t.sin, 'Y', c1.type, c1.optcode, pol.policy, c1.cat
FROM 
(
select c.date, m.* 
from member m
join member_h mh on m.clientid = mh.clientid and m.empid = mh.empid and m.changeid = mh.histchangeid
join change c on m.changeid = c.changeid
where m.status = 'T'
and c.tablename = 'member'
and c.date >= @start 
and c.date <= @end
) AS t
left JOIN curben AS c1 ON t.clientid = c.clientid and t.empid = c1.empid
left JOIN planctrl AS pc ON t.empstat = pc.empstat AND t.plancode = pc.plancode AND t.division = pc.division
left JOIN policy1 AS pol ON pol.polind = pc.polind AND c1.type = pol.type AND c1.optcode = pol.optcode

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources