Endless Looping Job!!!!

Jan 29, 2004

i have created a job that i have scheduled to run every 10 min everything is configured well since i have tested preety everything their is to be tested and found that it was my last step wich as a fetch in it so i imagine that this fetch is making it loop over and over again. the job goes trought all the steps and starts back at the first step and keep going like that till i disable it here is my fetch statement and if you have any clue any help would be widely apreciated.


PS: i suspected it to be that fetch statement causing the havoc ;)



DECLARE
@TransactionNb varchar(10),
@EqId varchar(10)

DECLARE TransactionNb_cursor CURSOR
FOR
SELECT TransactionNb, EqId
FROM DetCom
WHERE UpdCode = 'C'

OPEN TransactionNb_cursor

FETCH NEXT FROM TransactionNb_cursor
INTO @TransactionNb, @EqId

WHILE @@FETCH_STATUS <> -1
BEGIN
-- Vérifier s'il existe une transaction avec le UpdCode = 'C' dans EntCom
IF (SELECT UpdCode
FROM EntCom
WHERE TransactionNb = @TransactionNb and EqId = @EqId) = 'C'
BEGIN
CONTINUE
END
ELSE
BEGIN
RAISERROR (50006, 10, 0, @TransactionNb, @EqId)
END

FETCH NEXT FROM TransactionNb_cursor
INTO @TransactionNb, @EqId
END

CLOSE TransactionNb_cursor
DEALLOCATE TransactionNb_cursor

View 1 Replies


ADVERTISEMENT

Endless Loop

May 14, 2004

*** edited by: master4eva ***
Please enclose your code in </ code> tags (without the space between the "</" and "code"). This will make your code easier to read online; therefore, encouraging a response to be faster. It is to your own benefit for your question to be answered in future.

I have already done the editing to include the <code></ code> tags.
*********

I have a trigger that fires this stored procedure during an update event. But i think i am getting an endless loop. Any idea? Thanks in advance....
<code>
ALTER PROCEDURE TrigRetReqRecIDP1
@REID int

AS

Declare @RRID int
Declare @intREID varchar(20)
Declare @intIMID varchar(20)
Declare @RetValint
Declare cr cursor
for
select RRID from RequestRecords where REID=@REID and RRStatus = 'PE'
open cr

fetch next from cr
into
@RRID

while @@fetch_status = 0
Begin
select @intIMID = (select IMID from ImplementationGroup where ImGrpName = 'Help Desk')
insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@intIMID,
'2',
GetDate()
)
SET @RetVal = @@IDENTITY
Update RequestRecords
set ITID = @RETVal, RRStatus = 'IA'
where REID = @REID and RRID = @RRID


end

close cr
deallocate cr


GO

View 6 Replies View Related

SQL 7 Endless Loop!

Feb 17, 2000

I have an SQL script that runs as part of a batch process in order to number line items in timecards. It basically consists of 2 nested cursors with a number increment and update inside the inner cursor. The problem is that it gets stuck (seemingly at random) in a loop (incrementing and updating within the inner cursor) until I get an arithmetic overflow and it just moves on to the next card. I'm new to my job as a dba and the boss is breathing down my neck.....any assistance would be GREATLY appreciated!!

Thanks..Nick

Here is the code for the script:

declare @SEQ int,
@NUM smallint,
@SUM smallint,
@TSQ int,
@TLI int

declare LINES_OUT_OF_SEQ_CSR cursor for
select TIMECARD_SEQ_NBR
from EMPL_TMCD_VER_DETL
where NEW = '1'
order by TIMECARD_SEQ_NBR

open LINES_OUT_OF_SEQ_CSR

fetch next from LINES_OUT_OF_SEQ_CSR into @SEQ

while @@fetch_status = 0
begin
declare LINES_TO_CHG_SEQ_CSR cursor for
select TIMECARD_SEQ_NBR,
LINE_ITEM_SEQ_NBR
from EMPL_TMCD_VER_DETL
where TIMECARD_SEQ_NBR = @SEQ
order by LINE_ITEM_SEQ_NBR

select @NUM = 0

open LINES_TO_CHG_SEQ_CSR

fetch next from LINES_TO_CHG_SEQ_CSR into @TSQ, @TLI

while @@fetch_status = 0
begin
select @SUM = @NUM +1

select @NUM = @SUM


update EMPL_TMCD_VER_DETL
set TIMECARD_VERSION_ITEM_NBR = @NUM
where LINE_ITEM_SEQ_NBR = @TLI
and TIMECARD_SEQ_NBR = @TSQ

fetch next from LINES_TO_CHG_SEQ_CSR into @TSQ, @TLI
end

deallocate LINES_TO_CHG_SEQ_CSR

fetch next from LINES_OUT_OF_SEQ_CSR into @SEQ
end

deallocate LINES_OUT_OF_SEQ_CSR
go

View 2 Replies View Related

Endless Subqueries

Aug 16, 2007

I have a table with two columns: OID and Cumulative (witch is the same type as OID)
Each OID can have one or more Cumulatives.

Example of data:
OID Cumulative
167 292
167 294
167 296
168 292
169 302
169 304


The cumulation of each OID don't stop at one cumulation, but can be endless (theoretical).
Example: 167->292->590
So the table would have on more row:
OID Cumulative
295 505


I would like to represent this strucuture in a tree view and I'm looking for a query that could give me a table with this structure:
OID Cumul1 Cumul2 Cuml3 Cuml4 .... Cumuln
in the way I can read the row and have as many child nodes as I have values in the columns. The number of columns depends on the row with most cumulations.

How can I do the query?
Is there a better way as my table with n columns?

Thanks for suggestions

View 1 Replies View Related

How To Simplify My Endless Query. Thanks.

Jul 23, 2005

Hello group!I am having a problem with simplying my query...I would like to get customers' balance info based on how many monthssince they opened their accounts. The tricky part here is accountsstarting with '28' are treated differently than other accounts, theyare given 3 months grace period. In other words, for all otheraccounts, their month0 balance is the balance of their open_month, andmonth1 balance is the balance after the account is opened 1 month, andso on. But accounts starting with '28' month0 balance would be thebalance after the account is opened 3 months, and month1 balance wouldbe the balance after the account is opened 4 months, and so on.My query below works, but since some customers are more than 10 yearsold (more than 120 months), my query is endless! Does anyone know abetter way to do the same job? Many thanks!create table a(person_id int,account int,open_date datetime)insert into a values(1,200001,'11/15/2004')insert into a values(2,280001,'8/20/2004')create table b(account int,balance_date datetime,balance money)insert into b values(200001,'11/30/2004',700)insert into b values(200001,'12/31/2004',800)insert into b values(200001,'1/31/2005',900)insert into b values(200001,'2/28/2005',1000)insert into b values(280001,'8/30/2004',7000)insert into b values(280001,'9/30/2004',8000)insert into b values(280001,'10/31/2004',9000)insert into b values(280001,'11/30/2004',10000)insert into b values(280001,'12/31/2004',15000)insert into b values(280001,'1/31/2005',20000)insert into b values(280001,'2/28/2005',30000)--Ideal output--person_idacc_nomonth0_balancemonth1_balancemonth2_balancemonth3_balance1200000170080090010002280000110000150002000030000select a.person_id,a.account,month0_balance=casewhen a.account like '2%' and a.account not like '28%'thensum(case datediff(mm, a.open_date, balance_date) when 0then b.balance else 0 end)else sum(case datediff(mm, a.open_date, balance_date)when 3 then b.balance else 0 end)end,month1_balance =casewhen a.account like '2%' and a.account not like '28%'thensum(case datediff(mm, a.open_date, balance_date) when 1then b.balance else 0 end)else sum(case datediff(mm, a.open_date, balance_date)when 4 then b.balance else 0 end)endfrom a as ajoin b as bon a.account=b.accountgroup by a.person_id, a.account

View 3 Replies View Related

Foreach Endless Loop

Feb 28, 2007

I'm having a problem where I'm using a Execute SQL Task to retrieve a dataset and storing that in an object variable. Then on success of that execute sql task I use a foreach loop task to go through the dataset and do 2 tasks inside the foreach loop. When I execute this package I have ~12 records in the dataset however when I get to the foreach loop in the 2nd iteration it keeps repeating it. It acts like it is stuck on the second record (tuple) and never goes on. I'm using an ForEach ADO Enumerator in the foreach. I've even set a breakpoint on each iteration and no task fails in side the foreach loop. I'm completely perplexed why it will iterate to the 2nd record but get stuck there in an endless loop. I've tried this on 2 different computers (with different data values) and the same thing happens. Does anyone have any suggestions?

View 40 Replies View Related

Endless Running Package Using A Proxy

Jan 15, 2007

I have a package which runs fine, when I execute it with my account (e.g. double-click on the .dtsx file and run it).

Now I would like to establish a job, which starts the package. I created first a credential for my Account (which is a domain administrator account also for the box, where SQL Server is running on), then I defined a proxy to this credential.

In the job definition I changed the Run as... to this Proxy (it is a SSIS Proxy) and then I started the job.

Th job does NOT abend, it runs forever! So I have to stop it manually.

In the log I can see as last entry : "operation complete". It stops at a "Execute process task" where I call the bcp utility.



Does anyone has an idea, why a package can run forever?!?!

Regards

Norbert

View 1 Replies View Related

Endless Query With Custom Function - DATEADD

Apr 11, 2015

I created a function to use in a View, works similar to DATEADD but only with my company's Business days Monday-Thursday.

I am running a query but it never ends to run.

This is the function:

USE [RA_dev]
GO
/****** Object: UserDefinedFunction [Production].[GBDATEADD] Script Date: 4/11/2015 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[Code] ...

This is how I am trying to use it in the View:

Production.GBDATEADD(Production.Schedule.START_DATE, Production.Operations_Data_Pool.MFG_DAY - 1) AS SCH_DATE

View 2 Replies View Related

Nesting A Looping Query Withing A Looping Query

Mar 28, 2008

Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005
TABLES
     Category                    SubCategory           (PK)CategoryName      (PK) SubCategoryNameCategoryID                    SubCategoryIDDate                                Date                      (Just shows the date inserted)                                  (FK)CategoryID
On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?

View 5 Replies View Related

Looping In T-SQL

Mar 19, 2002

I want to loop through a recordset and do inserts into another table based on each record.

The way I have been doing it is copy my key data into a temp table,
Loop through temp finding the max ID
Doing what I need to do, deleting the max, then finding the new max and looping until no records exist.

I know there has to be a better way. The table I am working with is millions of records.
Thanks in advance,
Chris Reeder

View 2 Replies View Related

DTS And Looping

Mar 28, 2002

I need to loop through a set of tables and move the data through a data pump from one server to another. This set of tables is dynamic so I have greated a global recordset and the looping is working fine.

During the looping process I need to change the transformations for each table so the source, destination, and transformation of the datapump are correct for the next table in the loop. I am using a VBS to handle this right now but cannot get the transformation to change. I essentially want to auto-remap using a vbs script. Is this possible?

Thanks for any help.

View 1 Replies View Related

Help With Looping

May 23, 2008

Hello clever people
I have a table that holds duplicates that I want to change into a table that has no duplicates. The current table is this
name
compound_id integer
name varchar(150)
name_type integer

This table stores chemical names. There is no primary key in the table so there are multiple compound_id's. I think the original idea was to have four name-types
1 = chemical name
2 = a description of the chemical
3 = a synonym of the chemical
4 = a formula of the chemical

I have created a new table called compound_name with this structure

id int primary key (auto identity)
compound_id int used as a foreign key
compound_name varchar(150)
compound_desc varchar(250)
compound_synonym varchar(150)
compound_formula varchar(50)
compound_trade_nme varchar(50)

I have also started to populate the new table by running this code
insert into compound_name(compound_id,compound_name)
SELECT DISTINCT compound_id, name
FROM dbo.name
WHERE (name_type = 1)

Now I need to somehow loop through the name table getting distinct compound_id's, and perform a case when name_type = 2 (which is synonym name_type) Then inside the loop update compound_name.compound_synonym for each compound_id which matches name_type 2
Then case 3 do the same for name_type 3 which is the name_type for descripton
Then case 4 do the same for name_type 4 which is the formula

Any help would be appreciated

View 6 Replies View Related

Help With Looping!

Sep 29, 2004

Hi there, I am new to SQL and am having trouble looping a script. I have the following script that needs to be refreshed a large number of times, or needs to be looped indefinitely until stopped:

select
df.tablespace_name "Tablespace",
block_size "Block Size",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
dba_tablespaces ts,
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from dba_free_space
group by tablespace_name) fs
where
ts.tablespace_name = fs.tablespace_name
and
df.tablespace_name = fs.tablespace_name(+)
;

I know this question may have a very easy solution, but I have no idea how to solve it.

Thanks in advance

View 4 Replies View Related

Looping

Mar 21, 2007

hi guys,

I have a table like this,
S D C


Bi Ar Ar
Bi Ar Ch
Bi Ar Ma
Bi Au Ar
Bi Au Ch
Bi Au Ma
As Ar Ar
As Ar Ch
As Ar Ma
As Au Ar
As Au Ch
As Au Ma
As Au Ma


I have 3 columns S, D, C. i have text values in it. I need to write a query such that it will check each row for distinct value.For ex, all the rows are distinct except the last one. so i need to see all the duplicate entries. can anyone help me?

View 4 Replies View Related

Looping

Jun 15, 2007

Hi guys,

Can anybody tell me how to do looping in SQL?

Regards,
David

View 6 Replies View Related

A Question About Looping

Sep 6, 2007

Hi All,
 I would like to know the best way to approach the following requirement:
I have an ASP.net 2 web site which gets its data from SQL 2005.
I am trying to run a series of 'rules' which are SQL where statements stored in a table, against rows stored in another table. I open the 'Rules' table looping through all records. I copy each rule to a string and put it on the end of the SQL statement so that the rule will only be appended if it passes the rule... this may be a little confusing.
The rules process will fire when the details have been submitted to the database.
Table containg rules would contain something like:
ID, RuleSQL
1, (ClientAge >18)
2, (ClientIncome>10000)
3 Etc...
This a very simplified version of the table but gives the general idea.
I currently use ASP.NET 2 and sqlconnections/datareaders to do this. I would like to know if there is a way of doing the same thing server side using Transact SQL because that would (I believe) speed up the time taken to perform all the tests as i wouldn't need to rely on ASP to open all recordsets and append the data.
If the ASP route would be the standard way of doing it and is not likely to have a detremental effect on performance then i am fine to stick with it because i know it works.
any comments or suggestions would be welcomed.
Thanks,
Ian

View 2 Replies View Related

Looping Parameters?

Jan 17, 2008

I have an array (12,2) of values plus a profile variable that I want to pass as parameters while writing to a database.  I've been told that I've set up the parameters wrong, and they cannot be changed every time I loop using the method I'm using. But I have no idea how to use any other method. Please... I'm down to the wire in terms of deadline here. I have until midnight to get it uploaded and running online.  [CODE]Sub WriteClasses(ByVal CreditsArray)        Dim i As Integer        Dim EnrollDb As SqlConnection        Dim cmdEnroll As SqlCommand        EnrollDb = New SqlConnection("Server=LONNASQLEXPRESS;Integrated Security=True;database=LGordonTouroReg")        cmdEnroll = New SqlCommand("INSERT INTO Enrollment (SectionID, Semester, Year, ClassID, StudentID) VALUES (@SectionID, 'Fall', '2007', @ClassID, @StudentID)", EnrollDb)                EnrollDb.Open()        For i = 0 To 12        cmdEnroll.Parameters.AddWithValue("@SectionID", CreditsArray(i, 2))
        cmdEnroll.Parameters.AddWithValue("@ClassID", CreditsArray(i, 0))
        cmdEnroll.Parameters.AddWithValue("@studentID", Profile.StudentID)            If Not CreditsArray(i, 0) = "" Then                cmdEnroll.ExecuteNonQuery()                Response.Write(CreditsArray(i, 0) & " has been added to your schedule.<br/>")            End If        Next i        EnrollDb.Close()    End Sub[/CODE]

View 6 Replies View Related

Looping Through Sqlparameters

Apr 11, 2008

lets say i have a stored procedure (for insert command) which i am calling in my code to execute.  The user provided data is being stored in a array. My class takes the stored procedure name and also takes parameters name and types.  Is there any way to loop through the parameters,  (various columns in the table which is of diffrent data type ie varchar, int, etc).  How to implement it?

View 1 Replies View Related

Looping Through One Row At A Time

Sep 14, 2004

Hoping for a little help... I'm attemting to call a stored proc, pass parameters, and display the data 1 record at a time. I need to be able to show the data in a series of lables or text boxes. So the user will see one record, pushed into the lables, click a button and go to the next record...so on and so forth.

I think I have the code to get the data correct, it's the displaying data in lables and looping through the recordset the has me clueless.


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then ' IF This is the first page load
Dim UserID As String = Request.QueryString("UserID")
' parameter for stored procedure
Dim RoleID As String = Request.QueryString("RoleID")


Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter

MyConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionString"))
MyCommand = New SqlDataAdapter("getdirective", MyConnection)
MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@roleID", SqlDbType.NVarChar)).Value = RoleID

Try
DS = New DataSet
MyCommand.Fill(DS)

'Display data in a series of lables or highly formated datagrid

Catch ex As Exception
Response.Write("<font color=red>Error: " & ex.Message & "</font>")

End Try


Else 'IF the page is being reloaded


End If


End Sub


Private Sub cmdAck_Click(...) Handles cmdAck.Click
'This need to loop through the records


End Sub



Thanks for any help!!!

View 1 Replies View Related

Looping With Updates

Jul 11, 2000

I'm pretty new to T-SQL and have an *easy* problem, for you experts, that I can't get seem to get solved. I'd like to loop through a list of items in TABLE "Items". I then want to use that list to loop through and SUM SALES and QTY for each item from a TABLE called "Shipments". As I loop through each item, I want to UPDATE the "Items" table with the Summary data. So, logically I'd do something like this:

SELECT item_no
FROM Items

BEGIN

SELECT SUM(sales) AS Total_Sales, SUM(qty) AS Total_Qty
WHERE item_no=@item_no

UPDATE Items
SET Sales=@Total_Sales,
Qty=@Total_Qty
WHERE item_no=@item_no

END

I've tried somewhat successfully to use cursors to create my loop query, but I cannot seem to get the SELECT and UPDATE correct in the loop itself. Can anyone steer me in the right direction (or better yet, provide a solution)?

Thanks for the help,
Chris

View 2 Replies View Related

Looping Through A Recordset In A SP

Aug 30, 1999

What I have to do is loop through a table with about 900 records, do computations on each record and update a database, can someone help me out.

here is the code that I am using right now on another site with coldfusion but i want to convert it all to a stored proc for speed reasons.

<!--- Query the Stores Table --->
<cfquery name="Zip2" datasource="#application.data#" username="#application.username#" password="#application.password#">
exec Stores_GetStoreZipInfo
</cfquery>

<!--- Loop through the STORE table --->
<cfloop query="zip2">
<cfset Lat1 = #zip1.lat#>
<cfset Lg1 = #zip1.long#>
<cfset Lat2 = #zip2.lat#>
<cfset Lg2 = #zip2.long#>

<!--- Do the actual distance calculation between the user's zipcode each store's zipcode --->

<cfset DistLat = 69.1 * (Lat2-Lat1)>
<cfset DistLong = 69.1 * (Lg2-Lg1) * #cos(Lat1 / 57.3)#>
<cfset Dist = ((#abs(DistLat)# * 2) + (#abs(DistLong)# * 2)) * 0.5>

<!--- Update the DISTANCE field on STORE table --->
<cfquery name="UpdateZips" datasource="#application.data#" username="#application.username#" password="#application.password#">
exec Stores_UpdateZipSeachInfo '#Dist#', '#zip2.zipcode#'
</cfquery>
</cfloop>

View 1 Replies View Related

Looping Sql Query?

Aug 16, 2005

I am not sure if what I wish to do is possible, but I shall ask anyway;

My project examines a database log of all the pages of an online teaching tool. Once the user has completed all the pages they are to be issued a certificate. Users may complete the teaching tool in any order, and the pages are always stored whenever they are acccessed, regardless of certification.
I have created a number of views that extract the data into a list of all the possible completion dates; i.e. where all the pages have been completed within any 12 month period. I need to write a query/view that uses the view to extract the first possible user completion date followed by every completion 12 months after that, then after that etc. to present day.
Can I do this?
Am I making sense ?

A no is acceptable in this case; I know I can do this with multiple queries from withing an application. I'd just rather not.

View 4 Replies View Related

Looping Through Recordset

Aug 2, 2004

hello,

i have a select query that returns multiple rows (within a cursor). How do i loop through the rows to process it (in a stored proc)? I donot want to use nested cursors. a code sample is requested.

thanks!

View 3 Replies View Related

To Avoid Looping

Nov 16, 2005

Hi All,

Very Ad-hoc requirement I am having.

I've got one table with two columns.
Column Name Data Type
1) Id Integer Identity
2) RemDate DateTime

I've to write one SP/JOB in that there will be an integer input parameter @numofday.

Say value of @numofday is 5 then.... in SP/Job I need to insert 31 - 5 = 26 records to above-mentioned table where date starting from 1st of current month.

This logic can be achieve through looping but if anyone can suggest some better way to achieve this functionality without use of looping.

Thanks in advance,
Jai

View 2 Replies View Related

SQL Query Looping

Apr 9, 2008

This is the TABLE I have

IDBRANCH STATUS

1A ACTIVE
1BINACTIVE
1CINACTIVE
2BINACTIVE
2CINACTIVE
2DINACTIVE
3A ACTIVE
3B ACTIVE
3C ACTIVE
4B ACTIVE
5DINACTIVE
----------------------------------------------------------------
Following is the desired View that I need for the above table.
Any ID which has atleast one ACTIVE branch will have ACTIVE status and any company which have all of its branches INACTIVE will have INACTIVE status. Thanks for your help

IDSTATUS

1 ACTIVE
2INACTIVE
3 ACTIVE
4 ACTIVE
5INACTIVE

View 2 Replies View Related

Looping Through Tables

Apr 12, 2008

I want to loop through all tables in a database for the given qeury

SELECT TABLE_NAMe
FROM INFORMATION_SCHEMA.TABLES

how to intialize the counter and what should be the boolean expression
Thank you and Best Regards

View 5 Replies View Related

Looping Through Sys Objects

May 12, 2008

I want to apply the same query for 250 tables (see below).
It would be very painful to code 250 individual queries.
Is there a way to loop through all 250 tables using "sysobjects WHERE type = 'U'", and applying the below code for each table (users table would remain constant)?


select co.*
from company table_type
where not exists(select * from users u
where (u.users_id = type.rn_create_user) or (u.users_id = type.rn_edit_user)
)

View 11 Replies View Related

Looping Though Tables

Feb 2, 2007

Hi,

wondering if anybody could help i wondered if there is a way i could loop through a number of tables and append them to a new database at the moment, i'm writing a SQL statment for each table and when there's about 50 this becomes slightly tedious......

this is my SQL statment in a SP,

INSERT INTO DB2.dbo.datatable1
SELECT *
FROM DB1.dbo.datatable1;

INSERT INTO DB2.dbo.datatable2
SELECT *
FROM DB1.dbo.datatable2;

.
.
.

Is there a way to loop??

View 3 Replies View Related

SSIS Looping

Mar 19, 2007

Hi All,

I have 12 files pertaining to 12 months in a folder(ex: Jan.txt,Feb.txt... Dec.txt).

I want to loop through these file in order (Jan,Feb...dec).

How can implement this in SSIS?

Thanks,

Prakash.P

View 2 Replies View Related

SSIS Looping

Mar 19, 2007

Hi All,

I have 12 files pertaining to 12 months in a folder(ex: Jan.txt,Feb.txt... Dec.txt).

I want to loop through these file in order (Jan,Feb...dec).

How can implement this in SSIS?

Thanks,

Prakash.P

View 3 Replies View Related

Looping Cursor

Jul 9, 2007

Hi,

I need to create a cursor that will loop through my customer database to return matching rows of data based on my select statement criteria. I have written most of it based on what I remember from my limited SQL exposure at a previous job afew years ago, but I can't remember how to make the @cust_id varaible increment by 1 and loop to the end of the customer table.

Can anyone steer me in the right direction here please?

DECLARE @cust_id INT
SET @cust_id = 371
DECLARE my_cursor CURSOR FOR
SELECT CUSTOMER_ID, FULL_NAME, ADDRESS_LINE1, SUBURB, STATE, POSTCODE
FROM CUSTOMER_LANGUAGE_DETAILS
WHERE POSTCODE IN (SELECT POSTCODE
FROM CUSTOMER_LANGUAGE_DETAILS
WHERE CUSTOMER_ID = @cust_id
AND INACTIVE = 0 )
AND CUSTOMER_ID <> @cust_id

SELECT CUSTOMER_ID, FULL_NAME, ADDRESS_LINE1, SUBURB, STATE, POSTCODE
FROM CUSTOMER_LANGUAGE_DETAILS
WHERE CUSTOMER_ID = @cust_id
AND INACTIVE = 0
OPEN my_cursor
--SET @cust_id = @cust_id + 1
FETCH NEXT FROM my_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM my_cursor
END
CLOSE my_cursor
DEALLOCATE my_cursor

View 4 Replies View Related

Looping Through A File

Sep 4, 2007



Hi,

Hopefully someone can assist me with what appears to be a simple issue - and hopefully I am just looking in the wrong location - because I am almost going nuts trying to work this out!!


I am wanting to pickup a file from a given folder, do some transformations on the data within the file, and then dump each row within the original file as a seperate txt file into a new folder.

I have managed to get it working - well all except having each row as a seperate txt file. Currently all the rows are outputed into the same txt file. argh


As it stands I have a For Each Loop Container, within this i have a Data Flow Container; which in itself, just has the source, some derived columns, and then an output.


How can I get this to pull each row from the source and put it as a seperate txt file. If someone can just nudge me in the right direction it would be much appreciated.


Thanks in advance

Troy

View 5 Replies View Related

Looping A Query

Aug 14, 2007

Can anyone give me a hand on how to loop this query until there are no results left, this is going to be part of a stored procedure by the way.







Code Snippet

declare @strcharid varchar(21)

set @strcharid = (select top 1 struserid from userdata where class = '108' and authority = '1')

update tb_user set strauthority = '255', BanReason = 'Master Skill Hack', TermDate = getdate() where straccountid in (select top 1 straccountid from ip_tracker where strcharid = @strcharid)
update userdata set loyalty = '0' where struserid = @strcharid

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved