Stored Procedures And For Loops

Jun 2, 2004

Hi, I'm trying to call a stored procedure in a for loop within an ASPX page. My code runs fine but it seems to skip over the function which uses the stored procedure. The procedure basically copies files from the the client to the server. I have the upload portion working which physically copies the files from the client to server but when it comes to copying the path into a field in a table it totally skips it.





Some one suggested that the for loop is operating too fast for the stored procedure to work.





Any suggestions

View 1 Replies


ADVERTISEMENT

Loops In Stored Procedures

Jan 20, 2008

How can I create a query and loop through all its records in a stored procedure?

View 2 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

Using Do Until Or Do While Loops

Jan 24, 2002

Does anyone know if you can use do while or do until loops in stored procedures? If so what is the syntax?

thanks

Wheels

View 1 Replies View Related

For Loops?

Oct 24, 2004

Hi all.

Im trying to create a Stored Procedure that inserts multiple rows. But I can't get it to work.

Here's how I would like it to work

for test in (select myid from tblPlayers)
begin
Insert into tblMatches VALUES(test.myid, 2)
end

but obviously it does not work. Any ideas?

View 1 Replies View Related

Loops In SQL

May 6, 2008

Can i have equvivalent loop in SQL for the following C lannguage Loop.
I am not able to generate Loop of this kind in MSSQL Store procedures.

i=10;
Do { Statement 1 ;
Statement 2 ;
i-- ';
} while i=0 ;

View 2 Replies View Related

Help With While Loops

Aug 23, 2007

hi,
I am trying to
1)get all the names of a table that match another table
2)if count=0, then I want to insert into another table 'group' after getting its key.

I am totally lost, can somebody point me in the right direction.

how do I get the individual name so that I can insert into the group table,
I tried 'select @name=name, that gave no results'.
Thanks

while (SELECT name FROM names WHERE name in (select name from Group) and status = 'M' )=0
begin
insert into group(group_id,name,action) values (@key, name, 'play' )
end

View 5 Replies View Related

Help With While Loops

Aug 23, 2007

hi,I am trying to1)get all the names of a table that match another table2)while count=0, then I want to insert into another table 'group'after getting its key.I am totally lost, can somebody point me in the right direction.how do I get the individual name so that I can insert into the grouptable,I tried 'select @name=name, that gave no results'.Thankswhile (SELECT name FROM names WHERE name in (select name from Group)and status = 'M' )=0begininsert into CAll(group_id,name,action) values (@key, name, 'play' )endhow do i get the individual names to insert into another table ..

View 2 Replies View Related

Stored Procedure Being Saved In System Stored Procedures

Apr 7, 2006

We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.

For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.

I can't update the sys.Objects or sys.Procedures views in 2005.

What effect will this flag (is_ms_shipped = 1) have on my stored procedures?

Can I move these out of "System Stored Procedures" and into "Stored Procedures"?

Thanks!

View 24 Replies View Related

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?



How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related

SSIS And Stored Procedures Results Stored In #Tables

Mar 26, 2008

Hello
I'm start to work with SSIS.

We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:


SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'

EXEC @RETVAL = sp_executeSQL @SQLSTRING


How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)

I found a way but I think i'ts only a workaround:

1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)

Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?

Thanks for an early Answer
Chaepp

View 9 Replies View Related

Too Many Loops For Query?

Aug 17, 2006

Hi, I have below psuedo code ... it will display correct result, however, it takes a bit longer time to display all results. I think it's because we have so many loops, and each record from the loop will do a query from database. I need some advice about how to speed up the process time. Thanks in advance.
...
Do While NOT RS.EOF
        SQL_1 = "SELECT * FROM TB1" ;
        Set RS2 = Conn.Execute(SQL_1);
         Do WHILE NOT RS2.EOF
                 SQL_2 = "SELECT * FROM TB2 WHERE NAME=" + RS2.FIELDS("Name");
                 Set RS3 = Conn.Execute(SQL_2);                    
                 DO WHILE NOT RS3.EOF
                          SQL_3 = "SELECT * FROM TB3 WHERE AGE=" + RS3.FIELDS("Age");
                          Set RS4 = Conn.Execute(SQL_3);                    
                          DO WHILE NOT RS4.EOF
                                     Response.Write RS4.FIELDS("VAL");
                         loop
                   loop
        loop 
loop
...

View 1 Replies View Related

Help With Nested Loops

Jun 27, 2007

Hi. newbie here.
Also new to coding in SQL.

Struggling with how to implement the following psuedo-code in SQL server 2000.
** Can you use more than one CURSOR variable?
If yes, when use FETCH_STATUS is it for cur1 or cur2 ??

Sample data is at the bottom.
Thanks for ANY suggestions !!

** Assume TABLE 1 is sorted by Record_Type, Order_no, Order_line_no

************************************************** ***
dim @rectyp
dim @ord#
dim @lin#

Fetch (?) 1st record in TABLE1
While Still Records in TABLE1
Set sub_line# = 0
set @rectyp = Record_Type,
set @ord# = Order_no,
set @lin# = Order_line_no

while @rectyp = Record_Type and
@ord# = Order_no and
@lin# = Order_line_no
Set sub_line# = sub_line# + 1
update TABLE1 set line_ctr = sub_line#
get next record
end inner WHILE

end outer WHILE

************************************************** ****************************
Sample data :
Data as it currently exists:
Record_type ......Order No......Order line no ......Line Ctr
OP.....................458001................5.... ...............0
OP .....................458001..............5 .................. 0
OP..................... 458001..............5..................0
OP .....................458001..............5........ ..........0
OP.....................458191..............1 ..................0
OP.....................458191..............1 .................. 0
OP..................... 458308..............73..................0
OP .....................458308..............73....... ........... 0
OP.....................458308..............73..... .............0
OP.....................458308..............73..... .............0


Want data to look like this after executing code:
Record_type ......Order No......Order line no ......Line Ctr
OP.....................458001................5.... ...............1
OP .....................458001..............5 .................. 2
OP..................... 458001..............5..................3
OP .....................458001..............5........ ..........4
OP.....................458191..............1 ..................1
OP.....................458191..............1 .................. 2
OP..................... 458308..............73..................1
OP .....................458308..............73....... ........... 2
OP.....................458308..............73..... .............3
OP.....................458308..............73..... .............4


************************************************** *********************

View 2 Replies View Related

Loops And Cursors

Oct 26, 2007

I have a stored procedure that I am trying to write. I want it to Grab a list of ids from one table, then loop through that list and select a group of records from a second table based on the first list. The 2nd list can have 1 + records per item from the first list. With in that record, I need to check the values in 2 fields, if the values = something I update a third field and move on to the next. So I need to be able to loop thru the second set also. Currently I have a cursor with in a cursor, but was told that was slow and not a good idea, so I am trying to figure out what other options I have.

Thanks in Advance

Swoozie

View 9 Replies View Related

Cursor Vs Loops

Feb 21, 2008

Hello all.
I'm mostly a VB.Net developer. I've been working on a intranet app that allows poeple in our company to self regisiter for access to Tools/Systems.

The data stucture: 1 Request with many Users requesting access.. Also on the same request, many applications requested for those same people.

After the application routes some 'Manager Reviews' and Updated the tblRequest, approving the request, I combine the Information into a tblRegistrations.

So there is a Matrix created.
The Users requested are (User1, User2, UserX....)
The Apps requested are (App1, App2, Appx....)

I created a Stored Proc that reads the List of USers, and inserts a record into tblRegistered for each App that was requested.
User1, App1
User1, App2
User1, App3
User1, Appx
User2, App1
User2, App2
User2, App3
User2, AppX
UserX, AppX
...., ....

I user 2 cursors, Nested.
The outer Cursor is the List of Users, and the inner Cusros is the list apps.

I appreciate if somone can show me how to do this with some other looping structure, or if not, examine the Decalrative statement of the Cursor, and define the propteries to make it most efficient, ie Static Local Forward Only, ect.

As I said, I don't DB Developer Experience.

Below is the copy of the Working Stored Proc:

ALTER PROCEDURE [dbo].[sp_Insert_Registration]
-- Add the parameters for the stored procedure here
@Request_IDINT
,@SessionIDnvarchar(150)

AS

DECLARE @Request_user_FullName nvarchar(150)
DECLARE @Request_User_IonName nvarchar(150)
DECLARE @Request_App_ID INT
DECLARE @Request_App_Role nvarchar(50)
DECLARE @Reg_Date DateTime

BEGIN TRY


--Local Scalar Values
Select @Reg_Date=Request_Date From dbo.tblRequest Where Request_ID=@Request_ID


--First Cursor

DECLARE curRequest_Users CURSOR LOCAL STATIC FOR
SELECT Request_User_FullName, Request_User_IonName
From dbo.tblRequest_Users
Where Request_ID =@Request_ID AND request_User_Session_ID=@SessionID

Open curRequest_Users


--Second Cursor

DECLARE curRequest_Applications CURSOR LOCAL Static FOR
SELECT Request_App_ID, Request_App_Role
From dbo.tblRequest_Applications
Where Request_ID =@Request_ID
AND Request_Apps_SessionID=@SessionID

FETCH curRequest_Users INTO @Request_user_FullName, @Request_User_IonName
WHILE @@FETCH_STATUS = 0
BEGIN
--Insert the Row Into tblRegistrations
--Need to get the Application ID's the User(s) Has Been Approved For

Open curRequest_Applications


FETCH curRequest_Applications INTO @Request_App_ID, @Request_App_Role
WHILE @@FETCH_STATUS = 0
BEGIN
Insert Into dbo.tblRegistrations
(Request_ID
,FUllName
,IonName
,Application_ID
,Application_Role
,Reg_Date
,Approved
,Approval_Date
)
Values (
@Request_ID
,@Request_user_FullName
,@Request_User_IonName
,@Request_App_ID
,@Request_App_Role
,@Reg_Date
,'True'
,getdate()
)
FETCH curRequest_Applications INTO @Request_App_ID, @Request_App_Role

END
--Close the Inner Cursor
CLOSE curRequest_Applications

FETCH curRequest_Users INTO @Request_user_FullName, @Request_User_IonName

END




DEALLOCATE curRequest_Applications

CLOSE curRequest_Users
DEALLOCATE curRequest_Users


END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;

END CATCH;

View 2 Replies View Related

For Loops In Tsql

Jan 29, 2007

Can somebody please tell me how can i write a tsql statement in sql server 2000.

Same time how can i get the last digit of a inteager variable through tsql .What i want is to write 'right(intVariable,4) which is in vb .I want that in sql server 2000



Thank you

View 8 Replies View Related

Loops And Transaction Log

Apr 2, 2008



I brought my server to it's knees by creating 499.9GB of transaction log on a 500GB drive. Oops.

The db recovery model is SIMPLE.

I want to loop through some code, and minimize the transaction log file size. My second query here has an explicit transaction. Assume this code will loop about....1/2 Billion times. Is one (or both) of these going to record ALL 500,000,000 update statements in the Transaction Log (remember SIMPLE recovery)? Will the second one of these record a single transaction 500,000,000 time but not overwhelm the Log file due to simple recovery?

Any thoughts anyone?




Code Snippet
declare @i bigint
select @i = min(ID) from <MyTable>
While @i is not null

begin

update <MyTable> set <SomeField> = <SomeValue> where ID = @i
select @i = min(id) from <MyTable> where ID > @i
end






Code Snippet
declare @i bigint
select @i = min(ID) from <MyTable>
While @i is not null

begin

BEGIN TRANACTION


update <MyTable> set <SomeField> = <SomeValue> where ID = @i
COMMIT
select @i = min(id) from <MyTable> where ID > @i
end

View 1 Replies View Related

MS SQL Stored Procedures Inside Another Stored Procedure

Jun 16, 2007

Hi,
 Do you know how to write stored procedures inside another stored procedure in MS SQL.
 
Create procedure spMyProc inputData varchar(50)
AS
 ----- some logical
 
 procedure spMyProc inputInsideData varchar(10)
AS
   --- some logical
  ---  go
-------

View 5 Replies View Related

Calling Stored Procedures From Another Stored Procedure

May 8, 2008

I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.

The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?

Thanks in advance

John

View 5 Replies View Related

Help With Where Not Exists / Avoiding Loops

Mar 18, 2008

I am trying to figure out an efficient way of comparing two tables of identical structure and primary keys only I want to do a join where one of the tables reveals values for records which have been modified and/or updated.

To illustrate, I have two tables in the generic form:

id-dt-val

For which the 'val' in table 2 could be different from the 'val' in table 1 - for a given id-dt coupling that are identical in both tables.

Does anyone know of an efficient way I could return all id-dt couplings in table 2 which have values that are different from those with the same id-dt couplings in table 1?

NOTE: I am asking this because I am trying to avoid explicit comparisons between the 'val' columns. The tables I am working with in actuality have roughly 900 or so columns, so I don't want this kind of a monster query to do (otherwise, I would simply do something like where a.id = b.id and a.dt = b.dt and a.val <> b.val) - but this won't do in this case.

As a sample query, I have the following script below. When I attempt the where not exists, as you might expect, I only get the one record in which the id-dt coupling is different from those in table 1, but I'm not sure how to return the other records where the id-dt coupling is the same in table 1 but for where modified values exist:


create table #tab1
(
id varchar(3),
dt datetime,
val float
)
go

create table #tab2
(
id varchar(3),
dt datetime,
val float
)
go


insert into #tab1
values
('ABC','01/31/1990',5.436)
go
insert into #tab1
values
('DEF','01/31/1990',4.427)
go
insert into #tab1
values
('GHI','01/31/1990',7.724)
go


insert into #tab2
values
('XYZ','01/31/1990',3.333)
go
insert into #tab2
values
('DEF','01/31/1990',11.111)
go
insert into #tab2
values
('GHI','01/31/1990',12.112)
go


select a.* from #tab2 a --Trouble is, this only returns the XYZ record
where not exists
(select b.* from #tab1 b where a.id = b.id and a.dt = b.dt)
go

drop table #tab1
drop table #tab2
go

I really dont' want to have to code up a loop to do the value by value comparison for inequality, so if anyone knows of an efficient set-based way of doing this, I would really appreciate it.

Any advice appreciated!

-KS

View 7 Replies View Related

Loops Temp Tables

Nov 16, 2007

I need help creating the following report.

I need to modify the folliwng report to produce a weekly version of it.
To get a weekly version, simply find the first day of the week for the @dtm1 value :

should be something like @firstofweek=dateadd(day,(datepart(dw,@dtm1)*-1)+1,@dtm1) to get the Sunday date

I think i will then then need to loop through from firstofweek to last of week (last of week will be the Saturday)

would suggest having a dayofweek column on your temporary table.



CREATE PROCEDURE rpt_siteMealListWeekly


@dtm1 AS DATETIME

,@cmb1 AS VARCHAR(100)

WITH ENCRYPTION

AS


--DECLARE @dtm1 AS DATETIME


DECLARE @siteid as integer

SELECT @siteid=siteid from site where sitename=@cmb1

--SELECT @dtm1='2007-09-13',@siteid=1

--select convert (char(11),@dtm1,113)


DECLARE @mybit AS INTEGER

SET @mybit=10

SELECT @mybit = CASE datepart(dw,@dtm1)


WHEN 1 THEN 1 -- 'Sunday'

WHEN 2 THEN 2 -- 'Monday'

WHEN 3 THEN 4 -- 'Tuesday'

WHEN 4 THEN 8 -- 'Wednesday'

WHEN 5 THEN 16 -- 'Thursday'

WHEN 6 THEN 32 -- 'Friday'

WHEN 7 THEN 64 -- 'Saturday'

END

CREATE TABLE #tmp_table(


childid INTEGER null

,br BIT null

,di BIT null

,te BIT null

,type integer default 0

)

INSERT #tmp_table


SELECT DISTINCT sa.childid

,CASE WHEN sha.br & @mybit>0 THEN 1 ELSE 0 END

,CASE WHEN sha.di & @mybit>0 THEN 1 ELSE 0 END

,CASE WHEN sha.te & @mybit>0 THEN 1 ELSE 0 END

,0

FROM

sessionAttendance sa

,simplehoursassignment sha

,session s

,child c

WHERE


sha.childid=sa.childid

AND

sha.siteid=sa.siteid

AND

s.siteid=sa.siteid

AND

c.siteid = sa.siteid

AND

c.childid = sa.childid

AND

sa.siteid=@siteid

AND

s.identityid=sa.identityid

AND

s.dayofweek=datepart(dw,@dtm1)

AND

s.siteid=sa.siteid

AND

@dtm1 between cast(floor(cast(sa.datefrom as float))as smalldatetime) and cast(floor(cast(sa.dateto as float))as smalldatetime)

AND

sa.userdefid=0

AND

(

--check not a company holiday

not exists

(select

1

from

companyholidays ch

where

siteID=@siteID

and

@dtm1 between cast(floor(cast(ch.datefrom as float))as smalldatetime)

and

cast(floor(cast(ch.dateto as float))as smalldatetime)

)

)

and

(

sa.childid in

View 3 Replies View Related

How To Avoid Cursors And Loops

Sep 26, 2006

hi,

My problem is basically i need to call a stored proc for each entry in a table, i.e, basically a for loop calling stored procs with parameter coming from the table. I know two ways of doing this .. using cursor and using while loop with temp table. I dont like both approaches. Is there any good practice for this situation..

declare mycur cursor fast_forward for select ID from sometable


open mycur
FETCH NEXT FROM mycur
INTO @AID

WHILE @@FETCH_STATUS = 0
begin
exec dbo.storedproc @AID

FETCH NEXT FROM mycur INTO @AID
end

CLOSE mycur
DEALLOCATE mycur



View 13 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

Nested Loops In The Control Flow

Jan 11, 2006

I have a problem when using nested loops in my Control Flow. The package contains an outer Foreach Loop using the Foreach File Enumerator which in my test case will loop over two files found in a directory. Inside this loop is another Foreach Loop using the Foreach Nodelist Enumerator. Before entering the inner loop a variable, xpath, is set to a value that depends on the current file, i e /file[name = '@CurrentFileName']/content. The Nodelist Enumerator is set to use this variable as its OuterXPATHString. Now, this is what happens:

First Iteration:
The first file is found and the value of xpath = /file[name = 'test1.txt']/content. When the inner loop is entered it iterates over the content elements under the file with name test1.txt as expected.

Second Iteration:
The second file is found and the value of xpath = /file[name = 'test2.txt']/content. When the inner loop is entered it unexpectedly still iterates over the content elements under the file with name test1.txt.

My question is: Should it not be possible to change the loop condition of an inner loop in an outer loop such that the next time it is entered it will be done based on the new condition? It seems that the xpath variable is read once, the first time, and never again. If that is the case, does anyone know of a workaround?

Regards,
Lars Rönnbäck

View 8 Replies View Related

PPV's, Loops &&amp; Child Packages - Bug Or By Design

Jun 29, 2006

It would appear that if a Child package is called more than once from a Parent using the 'Execute Package' task, then after the first execute the Parent Package Variables are not applied to child package. I.E we build dimensions in a master database and these are then loaded to a number of topic specific datamarts. We simply pass Parent variables to the child that hold source & target connection strings, the first time the package is called the correct database is accessed, subsequent Executes ignore the variables and use the original values. Manipulating the (our) event queue to run the package once results in the correct behaviour

Are packages cached when they are called from a Parent? if so is there a flag that I have missed to force a reload each time a child is executed?

This has just become a big problen for us so any guidance would greatly appreciated.



Paul

View 6 Replies View Related

Loops And Building Comma Delimited Strings

Oct 9, 2006

The problem:

I have 2 tables, with a one to many relationship - lets say customers, and order items.

Each order record has a field that is meant to be a comma delimited list (they are reference numbers) that is driven by the quantity field. So, say in the order record, an item has a quantity of 3. The reference number will look like this:

1, 2, 3

And if the next order item for that customer has a quantity of 4, the reference number value is

4, 5, 6, 7

And the final item with quantity of 2:

8, 9

Reference numbers can either be auto assigned (and are in my web application) or manually set. If manually set they will NOT be numeric.

In my web application, it is possible for users to return to a customer's order and edit a line item. My problem is when users changes the quantity of an item, and I have to reset the reference numbers.

If the quantity of line item 2 changes from 4 to 3, I need to reset all the values for that, and any other, order item that comes after it:

4, 5, 6 (2nd)
7,8 (3rd with same quantity of 2).

I felt a cursor would be the best way to handle this. But I am having trouble re-assigning my variable to be the next number in the series when the cursor is running.

This is what I have so far. The print lines and hard coded values are for debugging purposes only.

DECLARE @NumberingType varchar(10)
DECLARE @TotalSum int
DECLARE @DoorLineItemID int
DECLARE @Quantity int
DECLARE @SeedInt int


SET @SeedInt = 1

SELECT @TotalSum = SUM(Quantity) FROM DoorLineItems WHERE UniversalOrderID = 12345

DECLARE UpdateRefCursor CURSOR FOR
SELECT DoorLineItemID, Quantity FROM DoorLineItems WHERE UniversalOrderID = 12345 AND NumberingType = 1

OPEN UpdateRefCursor

FETCH NEXT FROM UpdateRefCursor INTO @DoorLineItemID, @Quantity
DECLARE @RefNumberLine varchar(1024)
SET @RefNumberLine = ''

WHILE @@FETCH_STATUS = 0
BEGIN

WHILE @SeedInt <= @Quantity
BEGIN

SET @RefNumberLine = @RefNumberLine + CONVERT(varchar, @SeedInt, 101) + ', '
SET @SeedInt = @SeedInt + 1

END

PRINT @RefNumberLine

SET @SeedInt = @Quantity + @SeedInt
PRINT 'new seed: ' + CONVERT(varchar, @SeedInt, 101) + 'Quantity ' + CONVERT(varchar, @Quantity + @SeedInt, 101)


FETCH NEXT FROM UpdateRefCursor INTO @DoorLineItemID, @Quantity


END

CLOSE UpdateRefCursor
DEALLOCATE UpdateRefCursor


This returns the same delimited string for X number of items. So I'm getting this:

1,2,3
1,2,3
1,2,3


When I really want the results described above.

What am I doing wrong?

Thanks!

View 2 Replies View Related

Cursor In Sproc To Kill Spids Loops Forever

Mar 20, 2000

I need a stored proc to kill spids, but the following sproc loops infinitely with the same [correct] spid being printed out. What am I doing wrong?

The select statement, when I execute it via the query grid, returns the correct and finite number of spids.

Any help greatly appreciated.
Judith


CREATE PROCEDURE rasp_KillDBProcess
@dbname varchar(128)
AS
declare @KillSpid smallint
declare @SQL varchar(1000)
--
declare DBCursor cursor Forward_only for SELECT distinct l.spid
FROM master.dbo.syslocks l INNER JOIN
master.dbo.sysdatabases d ON l.dbid = d.dbid
WHERE (d.name = N'coj_pcisdata')
open DBCursor
--
Fetch next from DBCursor into @Killspid
--
While (@@Fetch_status <> -1)
Begin
If (@@Fetch_status <> -2)
begin
print 'spid = ' + cast(@killspid as varchar(12))
--exec ('kill ' + @killspid)

end
--
end
Fetch next from DBCursor into @Killspid
--
close dbcursor
deallocate dbcursor
print 'end'

return

View 1 Replies View Related

Fast Date Ranges Without Loops In SQL Server 2000

Sep 28, 2005

The trick is to use a pivot tableCheck out the code herehttp://sqlservercode.blogspot.com/2...ops-in-sql.html

View 3 Replies View Related







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