| Author |
 Topic  |
|
|
shakilhyd
Starting Member
Canada
16 Posts |
 Posted - 07/26/2008 : 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
Flowing Fount of Yak Knowledge
Germany
8514 Posts |
Posted - 07/26/2008 : 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
India
47099 Posts |
Posted - 07/26/2008 : 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
Canada
16 Posts |
Posted - 07/26/2008 : 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
Flowing Fount of Yak Knowledge
USA
7266 Posts |
Posted - 07/26/2008 : 19:27:05
|
| Do those queries return any result in query analyzer? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 07/27/2008 : 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
Canada
16 Posts |
Posted - 07/27/2008 : 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
India
47099 Posts |
Posted - 07/27/2008 : 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 |
|
|
| |
Topic |
|
|
|
Need Help in DTS-Package
