Iterating

Mar 30, 2005

Hi,

I have a 6 different textboxes in my web application. I have 6 different tables in my database such as tbl1,tbl2,tbl3 etc.

When the user clicks the submit button I have to check whether the values in the textboxes match the value in the database. (if in txt1 the user enters 3 I need to go to tbl1 and check if there is such a value).

What is the most efficient way to perform such a check? Will I need to write 6 select statements or can I use a loop and if I can use a loop I would appreciate an example

Thanks

View 2 Replies


ADVERTISEMENT

Iterating Through Table

Jul 10, 2007

how do you iterate through each record in a table within a user-defined function...

bit new to sql server so need some help asp. thanks

View 9 Replies View Related

Iterating Over A Set Of Dates

May 7, 2008

Hello,

I have an SQL task which returns a set of dates, and I would like to iterate over this set, re-assigning the date to a global variable each time (User::CurrentDate), so that I can perform a number of tasks based on this date.

Can someone show me how this is possible in SSIS?

Thanks,

Simon

View 1 Replies View Related

Iterating Data Movement

Mar 28, 2006

Hi all

I am wanting to continuously monitor a source table throughout the day and as data becomes available, process it and insert it into one of a number of tables.

I have tried achieving this using a FOR LOOP and setting the halt condition such that it is not stisfiable. However, this has a couple of problems:

1) It runs in a tight loop and consequently degrades system performance enormously.

2) I can't get transactions to work. I would like each iteration of the loop to spawn a new transaction under which the tasks in the loop can run. Therefore, if one of the tasks fails during such an iteration, only the updates affected by that iteration are lost.

Ideally, I would like to be able to put a wait statement within the loop container so that it runs every couple of seconds. And would also like to implement transactions as described above.

All help is appreciated.

Jays :-)

View 2 Replies View Related

Iterating A Column When A New Row Is Inserted

Oct 2, 2007

Hello everyone, I have a table in which I need to iterate field, possibly several rows, when I enter a new record with the same item ID number. An example will make this much clearer.

ItemID CurrentLocation Iter
A01 Inventory 1
A01 Cutting 0
A01 WIP 2
B01 WIP 0
B02 WIP 1
B02 Inventory 0


I dont want to delete any old rows so that I can keep a history of where each item has been. The iterative column is in reverse order so that 0 is the newest value (location) and higher numbers are older locations. An item could go through a CurrentLocation several times.

Now, if I insert a row with ItemID = A01 and Current Location = Polishing, I want the Iter field of all previous rows to iterate by +1 and this new row to have Iter = 0.

What would be the easiest, best way to do this? Use a stored procedure or do it in my code or what? I'm pretty new at SQL server so if i'm missing a better way to accomplish the same thing, then please point me in that direction. Thanks for your help and/or time.

Scott

View 3 Replies View Related

Iterating Over Table Rows In MSSQL

Sep 23, 2004

Hello all,

I have recently started working on a project which involves using MSSQL to access a simple database. I have worked with Postgres SQL before, so I have a general idea of what SQL can be used for, but I'm having some difficulties applying that knowledge to MSSQL.

Currently, I would like to do the following (in abstract terms):

declare tmp record
select column1 from tableA into tmp
for each entry from above selection do
insert into tableB values (tmp[column1], 0, 0, 0)

I remember doing something like this fairly easily in postgres. Trying to put that into MSSQL, I have:

CREATE FUNCTION dbo.newDay (@mDate datetime)
RETURNS int
AS
BEGIN
DECLARE @id int
DECLARE item_cursor CURSOR FOR
SELECT id FROM tblKitchenCat
OPEN item_cursor
FETCH NEXT FROM item_cursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO tblKitchenList VALUES (@id, 0, 0, 0, 0, 0, @mDate)
FETCH NEXT FROM item_cursor INTO @id
END
CLOSE item_cursor
DEALLOCATE item_cursor
RETURN 0
END
GO

I get a syntax error next to AS... what is it?

Can somebody please help me out here... any articles related to moving to MSSQL from Postgres would also be highly appreciated.

In addition to that, I would like to schedule a particular function to run once a day, say at 2am. Is there a way to do this using MSSQL?

Thanks in advance.
Cheers,
Michael

View 3 Replies View Related

Iterating Updated Fields Within A Trigger

Jul 23, 2005

Hi,I want to log updates to specific fields, storing the new and oldvalues. Is there any way I can iterate the collection of updatedfields within a trigger in order accomplish this?Thanks in advance,Julie Vazquez

View 4 Replies View Related

Iterating The Rows Of A Script Component

Feb 1, 2007

Hi There,

Can someone please let me know what is the best way to iterate the output rows of a script component and stick in those ids in a where clause of a select query (to retrieve additional info from a database)? Is this possible at all? If not, what is the best way to deal with this situation?

Thanks a lot!!

View 20 Replies View Related

Iterating Based On The Data Within A Flat File

Apr 2, 2008

Hi All,

In one of my interfaces ,Source is flat file which has field called StoreID in the Detail Record.
StoreID can be Multiple.Now I have to generate different files for Each StoreID present in the Source file.
To achieve this first I populate the data from the file into a Temp Table and use ForEach ADO Enumerator to iterarate based on StoreID and produce different files.This is giving a satisfactory result.

But now i have to change the flow so that Temp table is not used,i.e i have to iterate directly from the flat file.
Do we have a built in enumerator to achieve this.
or should we do this in Script task only??
any other Options??

Thanks in Advance...

cheers

Srikanth

View 4 Replies View Related

Transact SQL :: Iterating Through A Schedule With Recursions And Factors?

Jul 30, 2015

I have a scenario in which a schedule is recorded like the top table below. Notice the start and end times, the meeting length, and the fact that you could book more than 1 meeting (book factor) during the times slot. The second table is the result needed. I have it working using the dreaded cursor, but I know there's got to be a more elegant solutions.

empID
bookFactor
mtgLen
mtgStart
mtgEnd

1
2
15
7/1/2015 8:00

[code]....

View 8 Replies View Related

SSIS Script Component - Iterating Through Input Columns

Nov 26, 2007



I'm trying write a reusable script component that takes data from rows that were rejected from a SQL Destination operation and put them into a common SQL error table.

This script would basically function to take the input columns selected in the script, and build a delimited string, (similar to the 'Flat File Source Error Output' that is contains redirected rows from reading a flat file) and insert this string into a SQL table called 'SourceData' to store errors.

I'm trying to script the component to iterate through all input columns (as selected in the input columns screen) and build a simple string.






Code Block
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'Use the incoming error number as a parameter to GetErrorDescription
Row.ErrorDescription = ComponentMetaData.GetErrorDescription(Row.ErrorCode)
Try

Row.ErrorColumnName = ComponentMetaData.InputCollection(0).InputColumnCollection(Row.ErrorColumn).Name
Catch ex As Exception

Row.ErrorColumnName = String.Concat("Column Name retrieval failure. Details", ex.Message)
End Try
'
'Build input data
'
Dim inData As String

For Each inputCol As IDTSInputColumn90 In ComponentMetaData.InputCollection(0).InputColumnCollection
inData = String.Concat(inData, "~", inputCol.Name) 'I don't want the name, but the value.
Next
Row.SourceData = inData
'
End Sub


I've only got as far as iterating the names of columns in the input buffer, but how do i get the values?

The result i'm trying to achieve is :
Selected columns in 'Input Column' screen : Name, Address, Phone
OutPut column 'SourceData' value : Harry~Melbourne~None

I don't want to write the code as:



Code Block
inData = Row.Name
indata = String.Concat(inData,"~",Row.Address)
indata = string.concat(inData,"~",Row.Phone)

as this make my code not very reusable. I've got some tables which are 100+ columns long and I don't wish modify the code too much

I have also tried overriding the ProcessInput() function of the script component to iterate through the buffer columns :



Code Block
Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

MyBase.ProcessInput(InputID, Buffer)

Dim iCnt As Integer = 0
Dim inData As String

If Buffer.ColumnCount > 0 Then

For iCnt = 0 To Buffer.ColumnCount - 1

inData = String.Concat(Buffer.Item(iCnt)) 'Error thrown here: PipelineBuffer has encountered an invalid row index value.
Next
End If
End Sub





but i get an error when i run it.

Please help.

View 18 Replies View Related

Integration Services :: Iterating Over Object In SSIS With Fix Number Of Results Each Time

Aug 11, 2015

I've got this issue with a query in SSIS. From a table in SQL Server I'm getting over 25000 different identifiers. These identifier are associated to many values in a table in  one Oracle Database. This is the schema that I have implemented for doing this.

The problem is that some days the identifiers can be over 45000, and at this point perform a loop for every one is not the best solution (It can take to much time to get the result). Previously I have performed another query where from the SQL statement.

I am creating and sending a unique row with all the values concatenated and then I have recover this unique string from an object and use it to create the query in the ODBC Source that invoke the table in Oracle: something like this:
'Select * from Oracle_table' + @string_values

with @string_values = 'where value in (........)'. It works good because the number of values is small enough to be used, like 250. But in this case I can not use this approach because the number is really big and obviously the DBA of Oracle is going to cancel the query.

So I wonder, how can I iterate over the object getting only a few number of values everytime, something like 300 or maximum 500, to avoid the cancellation of the query but at the same time doing the minimum number of loops.

View 5 Replies View Related

Programmatically Iterating Tasks/components In The Data Flow Portion Of A Package.

Mar 6, 2007

HI All,

In several threads there has been discussion regarding adding connection managers to a package's data flow, etc. My challenge is that I have a large solution that contains many packages, and I need to change the connection manager linked to the data flow in all of the packages. When the solution was initially designed, data sources were used, and it has become a tedious maintenance issue to keep those in sync. We want to use a standard OLEDB connection manager, but adding a connection manager to each package and editing the corresponding data flow tasks in each package to use that new connection manager is a daunting task. I've coded a .Net module to access the packages, remove the old connection manager (data source) and add the new OLEDB data source. However, as I traverse the objects in the package hierarchy, when I come to the data flow object, the innerobject is not a dts object, but rather a _com object.. I can't seem to find any documentation/examples as to how to iterate the tasks within a data flow and change the connection manager. If you have any information, that would be quite helpful. If you reply with a code sample, if you would be so kind as to relate it to one of the sample packages provided with SSIS so I can run it, that would be great.

Thank you.

Steve.

View 1 Replies View Related







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