Creating the DTS - 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

General SQL Server Forums

New to SQL Server Programming

Creating the DTS

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-03-26 : 19:17:47

Hi.I have some codes with coldfusion which works fine:

<CFQUERY name="getInactiveProducts" datasource="#session.eManager.datasource#">
SELECT product_id,product_active FROM tbl_product
</CFQUERY>
<CFLOOP query="getInactiveProducts">
<CFQUERY name="getCats" datasource="#session.eManager.datasource#">
SELECT *
FROM tbl_configurator c
WHERE c.product_id = #getInactiveProducts.product_id#
</CFQUERY>
<cfif getCats.RecordCount EQ 1>

<CFQUERY name="getCat3" datasource="#session.eManager.datasource#">
SELECT * FROM tbl_configurator WHERE cat_level3_id = #getCats.cat_level3_id#
</CFQUERY>
<cfif getCat3.RecordCount EQ 1 AND getInactiveProducts.product_active EQ 0>
<CFQUERY name="updateCat3" datasource="#session.eManager.datasource#">
UPDATE tbl_cat_level3
SET cat_level3_active = 0
WHERE cat_level3_id = #getCats.cat_level3_id#
</CFQUERY>
</cfif>
<cfif getCat3.RecordCount EQ 1 AND getProduct.product_active EQ 1>
<CFQUERY name="updateCat3" datasource="#session.eManager.datasource#">
UPDATE tbl_cat_level3
SET cat_level3_active = 1
WHERE cat_level3_id = #getCats.cat_level3_id#
</CFQUERY>
</cfif>
</cfif>
</cfloop>

but when I want to create a DTS from it.I dont know how should I do it.
I just know the basics of creating the DTS.
for instance I have create the Execute SQL task for the first query:
SELECT product_id,product_active FROM tbl_product

but now I dont know how should I loop it to run the next query which is :

SELECT *
FROM tbl_configurator c
WHERE c.product_id = #getInactiveProducts.product_id#
would u plz help me.I've already check some websites for it & they gave me some simple structures which is not enough for me.
thanks in advanced

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-27 : 05:50:16

You can combine these queries together. Put them into a stored procedure and just call the stored procedure as a job.

UPDATE tbl_cat_level3
SET cat_level3_active = 0
WHERE cat_level3_id IN
(
SELECT cat_level3_id
FROM tbl_configurator c
WHERE c.product_id IN
(
SELECT product_id FROM tbl_product
-- is product_active used in query?
--if it it is it can be used in a WHERE statement here

)
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources