Using Checkpoints In Nested Packages
Jan 17, 2006
I am building a set of packages to load different things, some of which have relationships with the others. Therefore I want them loaded in a certain order. I have built a main package that executes the set of packages to control the flow of the packages.
Now, I want to implement checkpoints. Ultimately, I only want to deal with the main package that controls everything. So I figure the main package needs checkpoints enabled. When packages are nested and checkpoints are on at the top level package, will the nested package(s) start at the control flow point of failure or will it run the entire nested package? Should checkpoints be implemented within the nested packages as well? Should checkpoints only be implemented within the nested packages? Again, remember that I only want to launch
estart the main package.
Thanks. Any insight would be appreciated.
View 2 Replies
ADVERTISEMENT
Jul 20, 2005
HelloWe ran into a peculiar problem. We copied all of our DTS packagesfrom one server to another server (both of which run SQL 2000 and areon the same Service Pack 3/3a). When we execute an embedded DTSpackage from within anothe DTS package, we get the following error:Error Title: Server BusyError Description: This action cannot be completed because the otheprogram is busy. Choose 'Switch To' to activate the busy program andcorrect the problem.Buttons Available: Swith To..., RetryButtons Disabled: CancelDoes any one know about this error and what to do about it?ThanksJagannathan Santhanam (Jags)
View 1 Replies
View Related
May 17, 2007
I have a fairly simple SSIS project that has nested parent-child packages.
I am trying to find the best way to manage the connections strings so as to make the package portable across machines and environments. Currently there is one "master" package which calls 6 child packages. 1 of these child package calls 3 child packages of its own.
For the database connections, I've settled on creating a standardized .dtsConfig file for each server/login. This is a relatively small number (intially 8) that I don't expect to grow much.
I've taken a different approach for the file-system connections used by Execute Package components that call the child packages. For each package that has child packages, I store all the connection strings (paths) to the child packages in a single .dtsConfig file. This works well for the top-level "master" package where I can pass in the .dtsConfig file (that has the paths to the child packages) as a run-time option in the Execute Package Utility.
However, this approach seems to fall apart for the 2nd generation package that in turn call 3rd generation packages because I don't know how to get the .dtsConfig file (with the 3rd generation .dtsx package paths) path to this downstream dtsx package.
Though I'm sure there are others, the only two solutionsI can think of now are
(a)don't nest packages beyond 1 parent/child relationship -- not really an option or
(b)Store the path of .dtsConfig files for each .dtsx package as an environment variable on each machine. This option is unappealing because it would require adding an environment variable for every .dtsx package that has child packages. I don't think it would take long for this to grow into a large number, that would make managing environment variables cumbersome.
So far my experience with SSIS has been that there was a simple solution for each scenario I had. So this hoop jumping I'm going through seems to indicate I am just missing something.
Is there a better way I am just not getting?
View 1 Replies
View Related
Mar 3, 2008
I want to use a checkpoint in an SSIS package and would require some help.
I have a scenario like this
Task A ------ Task B-----------Task C
------- Task B1
Task A has a precedence constraint which determines if either Task B or Task B1 runs. Task B is run if the condition is met and Task B1 if the condition is not met.
I would like Task B1 to be a script task that is used to fail Task A so that when the package is restarted it will start from task A based on the checkpoint.
Is there any way to do this ?
View 7 Replies
View Related
Jun 14, 2007
I have a package that has 4 Script Tasks that are placed sequentially.
I have Task1--> Task2-->Task3-->Task4
The arrows between them are OnCompletion Arrows as opposed to the Standard OnSuccess arrows.Even if Task2 failed, it would still execute 3 and 4
the catch is that i want it such that when i run the first time and task 2 fails, then all the tasks except task2 should run which is fine, but when i rerun it. I want it such that it realises that task 2 had failed earlier, so it runs just task2.... if both 2 and 4 had failed then it should just run 2 and 4
i tired to implement it with check points, but the problemn is that if it fails at task2 it stops at task2 and does not continue to execute tasks 3 &4... when u rerun it starts at 2 but like i said i would like 3 & 4 to have completed the previous run...
Any suggestions would be helpful
Thanks for any help in advance..
smathew
View 6 Replies
View Related
Dec 4, 2006
I have a package that uses ssis checkpoints. It works well. However, when I try to setup transactions for some task, the chekpoints aren't used.
I read BOL and It states:
"If a package is configured to use checkpoints, Integration Services captures the restart point in the checkpoint file. The type of container that fails and the implementation of features such as transactions affect the restart point that is recorded in the checkpoint file."
But, how checkpoints are affected by transactions? what relation exists between this two components?
View 11 Replies
View Related
Mar 6, 2008
hi people, i have crashed on a this problem. I have a sequence container and on this container I have set "FailPackageOnFailure=true". Now in this container there are 2 tasks. The first one is preceeding the second one. Now both this task have set "FailParentOnFailure=true". Both task are the same and their purpose is to drop table A.
1) I run the package and it fails, because there is no table to drop.
2) I create the table manualy and run package again.
3) I see, that the first task is beeing just SIMPLE OMMITED and the second task runs
In general, everytime any task in a sequence container invokes failure, next time is beeing ommited regardelss of its status. How can this be fixed ? Thanks
View 4 Replies
View Related
May 26, 2007
Hello everyone, I had been studying the relationship between SSIS Checkpoints and SSIS Transactions.
What I want to do is to create a package with different task, where each one task creates a new transaction, and the same time each task be a checkpoint, it€™s in order to restarts the package from the failure task not from the beginning.
The Transaction-Checkpoint solution contains two packages*:
CkeckpointsAndTransactions1.dtsx and CkeckpointsAndTransactions2.dtsx
Package CkeckpointsAndTransactions1 contains four tasks, task three always fail. The package is configured to use checkpoints and each individual task creates a checkpoint. Additionally, each task creates a new transaction. The package has the TransactionOption setting to NoSupported.
In the CkeckpointsAndTransactions1 package there is something wrong, when the third task fails and I restart the package, the package starts from the beginning, this is wrong!!, the package should restart from the failure task.
In order to the package works like is expected it€™s necessary to add a new task between second and third task. It is also necessary that this new task hasn€™t transaction support. This is shown in the CkeckpointsAndTransactions2 package, in this package after package failure, I restart the package and the package restarts from the failure task, like is expected, but the additional task should not be necessary!!
Does anyone what is wrong in my packages?? How can I to create a package with different task, where each task creates a new transaction, and the same time each task be a checkpoint?
*Please download the BIDS solution from hernan93.files-upload.com (Transaction-Checkpoint.zip file)
View 1 Replies
View Related
Aug 23, 2007
Hi there,
I am trying here to get a situation going which includes both transactions and checkpoints to make sure that when something goes wrong I don't get a) data corruption (hence the transactions and b) I don't have to completely restart my 2hr run (hence the checkpoints). However I ran into something of which i cannot see whether it is intended behaviour or simply a bug.
Here's the deal:
I have a SSIS-package in which I enable checkpoints (CheckpointUsage: IfExists and SaveCheckpoints: True).
I have 2 Dataflows which follow eachother (the first dataflow prepares data for the second dataflow to edit).
Because I want to make sure that my data is secure I put a separate transaction on both the dataflows.
And here my problem arises. If I run my package now and the second dataflow breaks then my checkpoint sends me back to the first dataflow and my initial insert is executed again, which isn't meant to happen (I enabled checkpoints to prevent rerunning items). Somehow my checkpoint does not register the fact that the first dataflow has already been executed and it will execute that one again upon rerun.
However: if I put a random task between the 2 transacted dataflows (for example an empty script-task) it will work as intended. Just as long as this inserted item doesnt have a transaction; because if it does then the problem comes back
Now if I execute the package then my checkpoint shows that the first dataflow has already been executed and thus it will not execute this one again and it starts at the second dataflow upon re-execution.
I can work around it (with the empty script-task) but still I am wondering as to why this is happening. I am very interested to hear whether this is really a bug or if it is intended behaviour (and if it is then why is it intended?)
View 1 Replies
View Related
Jul 16, 2007
Hi,
I am using check point in my packages , but i am not able to run my packages where it exactly got failed. The scenario is i am 100 rows at source system and i was loaded 95 records into target and due to the some data formatting issues i got failed at the 96th record. Later i am trying to re-execute the package, Surprisingly my package start run from the 1 st record(nothing but the start point of dataflow task).
How can i achive to run from where it excatly got failed(96th record) ?? is it possible using check points else is there any work-around approach ??please respond this post , it is very helpfull for me..
TQ
Sreenivas
View 1 Replies
View Related
May 2, 2007
I have a package that has a container containing multiple DF Tasks.
The container is set to be Transacted, such that should any of the DF tasks fail the data inserted in any of the previous tasks rolls back.
This works as expected.
However, this container is part of a larger package and so I wanted to have a checkpoint on it, so that should any of the tasks within it fail, the package could be restarted from this container.
However, I would expect the functionality to be that on failure, the checkpoint would cause the whole container to be started again (because the container is transacted all DF task info would be rolled back) so we would expect it to start at task 1 again.
This is not the functionality I see. The package restarts from the failed task within the container every time.
According to the book Prof SSIS, it should start again from the first task and as explained this makes sense on a Transacted container as you would want this to happen.
A previous forum message encountered the same issue it appears:
See SSIS Checkpoints 04 Dec 2006.
This is an extract from it:
"I only experimented a little but my experience was that when I have a transacted container with multiple tasks that are checkpointed, SSIS would try to restart from the task that failed rather than from the first task in the container. The transaction was being rolled back correctly though.
In short, I felt that check points were not aware of transactions.
So, I ended up with this setting and it works for me:
Container is checkpointed and trasacted.
Tasks within the container are not checkpointed.
'FailParentOnFailure' property set to True on the tasks.
That way, if a task failed, it would fail the container and a checkpoint would be created at that level. Transaction would be rolled back as usual."
While this makes sense to me it is not the same properties that the SSIS book has that work.
Additionally, this didn't work for me either !!
I have tried every combination of FailPackageOnProperty and FailParentOnProperty that makes sense but every time the package restarts from the failed container within the task.
The transaction is rolled back correctly every time, but it seems the checkpoint that is created is not used correctly when dealing with transactions within containers.
View 1 Replies
View Related
Oct 14, 2006
Hi,
We are currently facing an issue in ensuring restartability of an SSIS package. The scenario is explained below.
Context:
The SSIS Package has two Data Flow tasks. The Data Flow task named DFT1 is the predecessor for DFT2 and chained with OnSuccess precedence constraint.
OnPreExecute and OnPostExecute event handlers have been implemented for DFT1. Each task in both event handlers as well as DFT1 and DFT2 have FailPackageOnFailure set to True.
Scenario1: Task in OnPreExecute of DFT1 fails.
DFT1 is attempted and succeeded.
OnPostExecute of DFT1 was not attempted.
DFT2 was not attempted.
Checkpoint file was created; however, no entries were made.
When restarted, execution started from first step in Control flow.
Scenario2: Task in OnPostExecute of DFT1 fails.
DFT1 and its OnPreExecute Event were executed.
DFT2 was not attempted.
Checkpoint file was created and entries were made. Entries had DTS:result as 0 for OnPreExecute and DFT1 tasks.
When restarted, DFT2 was executed. OnPostExecute event, which failed during previous execution, was not attempted.
Each task in the package, whether it is in Control flow or as part of an event handler is crucial for seamless execution. But apparently, as explained above, there is no reliability on the event handlers in case of failures. Has anyone encountered similar scenario? Is this behavior as per design of the runtime engine?
Thanks, in advance,
Regards,
Rajesh
View 2 Replies
View Related
Oct 24, 2006
Hi,
I have a master package with a sequence container with around 10 execute package tasks (for child packages), all in parallel. Checkpoints has been enabled in the master package. For the execute package tasks FailParentOnFailure is set to true and for the sequence container FailPackageOnFailure is set to true.
The problem i am facing is as follows. One of the parallel tasks fails and at the time of failure some of the parallel tasks (say set S1) are completed succesfully and few are still in execution (say set S2) which eventually complete successfully. The container fails after all the tasks complete execution and fails the package. When the package is restarted the task which failed is not executed, but the tasks in set S2 are executed.
If FailPackageOnFailure is set to true and whatever be the FailParentOnFailure value for the execute package task, in case of restart the failed package is executed but the tasks in set S2 are also executed.
Please let me know if there is any setting that only the failed task executes on restart.
Thanks in advance
View 1 Replies
View Related
Sep 21, 2015
I have a sequence container in my Package and this sequence has more than one control flow tasks.
Can I create the checkpoints such that only the failed component inside the sequence container runs again and not the other successful components/tasks in the sequence container?
View 3 Replies
View Related
Feb 13, 2007
Hi,
I have a FTP task in my control flow that download files from a FTP server. This ftp task is inside a foreach container that loops over a ADO recordset for the file name. The files that the ftp task pulls are huge. If the FTP task fails then I want the FTP task to restart and only download those files that have not been downloaded. Is this possible?
What possible configurations do I have to make to the foreach container and the filetask?
Thanks a lot in advance for your help and time.
Regards,
$wapnil
View 2 Replies
View Related
Jan 9, 2007
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
View 33 Replies
View Related
Apr 22, 2015
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
View 4 Replies
View Related
Aug 2, 2006
Hi all
Our data management system currently runs DTS packages using DTSPKG.dll.
I am currently looking at the possibliity of replacing the DTS packages and SQL 2000 with DTSX packages using SSIS in SQL 2005.
Do I need a new dll? or will the current dtspkg.dll handle the new DTSX packages?
Many thanks in advance!
View 1 Replies
View Related
Jan 3, 2007
Can you give a whole SQL statement an alias so you can use it later?
Eg.
SELECT * FROM Employees WHERE age < 19
-- Could I call the above statement something like 'statement1' to use below as shown
SELECT * FROM Employees WHERE age < 25 AND NOT IN (statement1)
Soin effect I get a nested statement.
The reason I am asking about aliases is because this would need to be repeated for, E.g. age < 30 Then age < 35 and so on and so forth.
So basically, I just want to alias a qhole SQL statement
Any help would be greatly appreciated - George
View 14 Replies
View Related
Jul 20, 2005
Hi all,I have a query that looks like so:SELECT GLDCT AS [Doc Type], GLDOC AS DocNumber, GLALID ASPerson_NameFROM F0911WHERE (GLAID = '00181913')However by stipulating that GLAID = GLAID I cannot get the person_nameas not all the GLALID fields are filled in. from my reading of thehelpdesk I have a felling that a nested query might be the way to goor a self-join but beyond this I am lost!?Many thanks for any pointers in advance.Sam
View 3 Replies
View Related
Oct 15, 2007
I am trying to do some nested IF ELSE conditions. I get an error saying 'Error near work Begin'. Below is teh query and the variables comes in thru cursor.
Can somebody advise me on this and also let me know the best practices and alternative to this if any.
IF (@CCTable = 'Claiminassoc')
BEGIN
IF ( @ClaimCenterField = 'ClaimID' AND @VALUE ='Claim')
BEGIN
UPDATE dbo.Table SET ColName = 'Y'
WHERE ID = @ID AND CCTable = 'Claiminassoc' AND CCField = 'ClaimID'
AND DWField = 'CatastropheDesignationFlag'
END
END
ELSE IF (@CCTable = 'EmploymentData')
BEGIN
IF (@VALUE ='TRUE')
BEGIN
UPDATE dbo.Table SET ColName = 'Y'
WHERE ID = @ID AND CCTable = 'Claim' AND CCField = 'WagePaymentCont'
END
ELSE IF (@VALUE ='FALSE')
BEGIN
UPDATE dbo.Table SET ColName = 'N'
WHERE ID = @ID AND CCTable = 'Claim' AND CCField = 'WagePaymentCont'
END
END
View 4 Replies
View Related
Apr 2, 2008
Hi gang,
I have a challenge, which seems like it is probably trivial, but SQL chops are not up to the task, and I am hoping one of you hot-shot DBAs can throw me a bone!
I have a query that populates an OLAP Time dimension table (basically one row per day of the year over several years). What I want to do is expand that table to include each hour of each day over the time span.
The CTE I am using for the day population is:
Code Snippet
WITH dates(date)
-- A recursive CTE that produce all dates between 2006 and 2057-12-31
AS
(
SELECT cast('2006' AS dateTime) date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
UNION ALL -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx
SELECT (date + 1) AS date
FROM dates
WHERE
date < cast('2058' AS dateTime) -1
)
What I wanted to do was something like:
Code Snippet
WITH hours(hr)
AS
(
SELECT (DATEPART(hh,date) hr
UNION ALL
SELECT (hr + 1) AS hr
FROM hours
WHERE hr < 24
)
inserted just after
Code Snippet
FROM dates
in the initial CTE. But from what I have read, it seems as though nested CTEs are not allowed.
Can anyone show me how to do this?
Thanks in advance for ANY insight/input!
Cheers,
Chris
View 5 Replies
View Related
Feb 14, 2008
Greetings -
Can someone pls advise the maximum number of nested IIF statements allowed in a VS 2005 report builder layout textbox expression? I seem to be hitting a wall at about 10, but cannot find verification. In case the limitation is by characters, the full expression would run about 3,500. Any other limitations which may have a bearing?
Tks & B/R
View 1 Replies
View Related
Feb 2, 2008
Hi all! I have a problem with my stored procedure, What I'm trying to do here is whenever a user tries to register, the stored procedure will check if the username already exists, and if not it'll now check if the email has already been entered into the database then if not the stored procedure will go ahead and insert the values into the database. *If the username already exists it'll return -1, and if the email already exists then return -2.
SELECT Username FROM UserAccount WHERE Username = @UsernameIF @@ROWCOUNT = 0 SELECT Email FROM UserAccount WHERE Email = @Email
IF @@ROWCOUNT = 0 BEGIN
INSERT INTO UserAccount (Username, Password, Email, FirstName, LastName, Gender, BirthDate, Country, State, Zip, AdditionalInfo)
VALUES (@Username, @Password, @Email, @FirstName, @LastName, @Gender, @BirthDate, @Country, @State, @Zip, @AdditionalInfo)
END
ELSE BEGIN
RETURN -2
END
ELSE BEGIN
RETURN -1
END
END
Thanks!
View 3 Replies
View Related
Nov 8, 2006
Hi, I'm not sure if this is a good place to ask sql questions, so please bear with me here...I have a table like soid, parentidWhat I'm trying to do is to write a self join where given a random ID, it'll give me the whole tree of its decendents. (I don't need its parent)so say I have data liek soid parentid1 null2 13 2when I specificy 1, it'll give me 2 and 3, even though 3 is indirectly related to 1. when I say 2, it'll just give me 3. Thanks a lot. GREATLY appreicate it.
View 1 Replies
View Related
Oct 16, 2007
When running reports from data, is it faster using nested views approx 4 levels deep, or writing data to a temp tables then running the report?
View 3 Replies
View Related
Apr 7, 2008
What's worng, please help? SELECT TTarea,personel,Date FROM person_table WHERE TTarea = (SELECT TTarea FROM TTarea_table WHERE Center='CENTER_office') I have many TTarea and I want to send back from inner SELECT statement but give an error that inner select statement don't return many result.I want to return many result and I display many TTarea in the CENTER_office
View 2 Replies
View Related
Nov 12, 2003
Hi All,
Im inserting some data into a table and grabbing the new UserID with this statement
SELECT @UserID = @@IDENTITY
I would like to use the @UserID to Execute another SP within the same proc..
..something like this
Exec AnotherSP(@UserId)
But this doesnt seem to be working....Its seems to me that this is a much better approach performance wise rather than returning the UserID to the Business Logic Layer and calling another SP....Im I correct in that assumption....any assistance would be greatly appreciated.
Thanks in advance
AMRITZ
View 2 Replies
View Related
Jun 12, 2004
I think I am getting an endless loop here... anyone know how to fix it?
***********************
CREATE PROCEDURE TrigSendPreNewIMAlertP2
@REID int
AS
Declare @RRID int
Declare @ITID int
Declare @FS2 int
Declare @FS1 int
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null
open crReqRec
fetch next from crReqRec
into
@RRID
Declare crImpGrp cursor for
select ITID from RequestRecords where RRID = @RRID
open crImpGrp
fetch next from crImgGrp
into
@ITID
while @@fetch_status = 0
select @FS1 = @@Fetch_Status
EXEC TrigSendNewIMAlertP2 @ITID
FETCH NEXT FROM crImpGrp
into
@ITID
close crImpGrp
deallocate crImpGrp
while @@Fetch_Status = 0
select @FS2 = @@Fetch_Status
FETCH NEXT FROM crReqRec
into
@RRID
close crReqRec
deallocate crReqRec
GO
View 1 Replies
View Related
Apr 30, 2005
Hi,
Although I am quite familiar with MS Access-grade SQL, I am struggling a bit with proper grown up SQL Server. My usual approach to counting things in Access is to first create a query with the conditions on the data, then use this as the basis of a second query that does the actual counting of the presorted data. I believe the way to do this in SQL server is to use a nested query. I want to generate the top 10 highest counts for each pesticide detected (detection is level>0) for a client between two dates.
Currently I am using
<code>
SELECT top 10 Count(Pesticide) AS CountOfPesticide, Pesticide FROM (SELECT tblData.Pesticide, tblData.Level, tblData.Clast, tblData.Client FROM tblData WHERE (((tblData.Day>@sdate) AND (tblData.Day<@edate))) and (tbldata.level>0) and (tbldata.clast=@clast) and (tbldata.client=@client)) as monkeyboy GROUP BY Pesticide ORDER BY Count(Pesticide) ASC"
</code>
The results that the above SQL turns out though are not reliable. For example, if I set the dates to now and 14 days ago, it produces higher counts for some pesticides then if I set the dates to now and 30 days ago. Any pointers or general advice about nested sql is gratefully accepted!
thanks
Mike
View 3 Replies
View Related
Sep 13, 2005
Hi,Please can somone help me with a nested SQL query. I have two tables please see belowTable 1CallIDEmployeeIDCallSummaryCallStatusTable 2CallHistoryIDCallIDDataAddedCallActionI would like to return the CallID, EmployeeID, CallSummary and CallStatus from Table 1, and also display the last CallAction from Table 2.This is a helpdesk database so a Call will have many CallActions i.e. Open, Held, Assigned Internal. How do I return the last CallAction Added against the selected CallID, I know I use the DateAdded but not sure about nested statements.The results I would like to return to the user would look like this:-Call ID: 1EmployeeID: 1Call Sumary: SQL ProblemCall Status: OpenCall Action (Last Action): Assigned Internal.
View 2 Replies
View Related
Sep 26, 2005
Hi all,
I have a temp guy who is doing some work for us (seriously, I never wrote the query).
Now this is the scenario (hold tight). So we have a stored procedure as follows...
ALTER PROCEDURE createSegmentPoints AS
DECLARE @fLat INTEGER
DECLARE @fLon INTEGER
DECLARE @segId INTEGER
-- declare cursor to return the from lat and lon for all segments that do not have address point 109.
DECLARE c1 CURSOR FOR (SELECT From_Latitude, From_Longitude, id AS segment_id FROM Segments WHERE SegmentType != 109)
OPEN c1
FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId
WHILE @@FETCH_STATUS = 0
BEGIN
-- insert into table the segId, from lat, from lon and returned segment id from function.
INSERT INTO test VALUES (@segId,@fLat,@fLon,dbo.points_test(@fLat,@fLon))
FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId
END
CLOSE c1
DEALLOCATE c1
As you can see here I am using a Cursor, which in turn calls a function
with each row that is processed in the recordset. The function that is
called is as follows...
ALTER FUNCTION points_test(@x INTEGER, @y INTEGER)
RETURNS INTEGER
AS
BEGIN
-- function to find the closed segment point with address point 109 to the segment specified in procedure.
DECLARE @tempDistance FLOAT(4)
SET @tempDistance = 1000000
DECLARE @id, @seg, lat, lon INTEGER
DECLARE @distance, @xd, @yd FLOAT
DECLARE c1 CURSOR FOR (SELECT from_latitude, from_longitude, id FROM segments WHERE segmenttype = 109)
OPEN c1
FETCH NEXT FROM c1 INTO @lat, @lon, @id
WHILE @@FETCH_STATUS = 0
BEGIN
-- calucations to get distance.
SET @xd = (@lat-@x)
SET @yd = (@lon-@y)
SET @distance = SQRT((@xd*@xd) + (@yd*@yd))
-- test if you have shortest distance.
IF (@distance < @tempDistance)
BEGIN
SET @tempDistance = @distance
SET @seg = @id
END
FETCH NEXT FROM c1 INTO @lat,@lon, @id
END
CLOSE c1;
DEALLOCATE c1;
RETURN @seg
END
(This function works out an equation to get the shortest distance from
two parameters passed to the function calculated with data from each
row returned within the cursor)
As you can see here, this function contains ANOTHER cursor!! Ouch. The
fact that their is an SQL query in a function is a killer, but having
another embedded cursor there is also a killer - this has virtually
killer the application.
So, how best is it for me to correct this. Should I turn the function
into a stored procudure? But even if I do this, the nested cursor still
remains. I was thinking maybe to have the SQRT equations within the
SELECT expression and then wrapped in a MIN() to maybe get the lowest
value.
Any ideas would be of great help.
Thanks
Tryst
View 2 Replies
View Related
Mar 20, 2002
Guys,
Has anybody out there successfully nested roles?
SQL Server seems to let you do it, but then no permissions are carried up the hierarchy.
Regards,
ChrisH
View 2 Replies
View Related
