SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Need Help in DTS-Package
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shakilhyd
Starting Member

Canada
16 Posts

Posted - 07/26/2008 :  11:50:50  Show Profile  Reply with Quote
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
8764 Posts

Posted - 07/26/2008 :  12:41:22  Show Profile  Visit webfred's Homepage  Reply with Quote
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...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/26/2008 :  14:04:48  Show Profile  Reply with Quote
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.
Go to Top of Page

shakilhyd
Starting Member

Canada
16 Posts

Posted - 07/26/2008 :  19:12:16  Show Profile  Reply with Quote
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


Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 07/26/2008 :  19:27:05  Show Profile  Reply with Quote
Do those queries return any result in query analyzer?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/27/2008 :  02:08:16  Show Profile  Reply with Quote
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?
Go to Top of Page

shakilhyd
Starting Member

Canada
16 Posts

Posted - 07/27/2008 :  02:33:42  Show Profile  Reply with Quote
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?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/27/2008 :  13:47:45  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000