How To Exit My Outer Cursor Correctly

Apr 19, 2002

Using nested cursors, I want to exit the outer cursor, all inner cursors
have been closed.

??Where do I add the...
----------
if @c1 > 1000
break
else
continue
----------

Do I add it at the beginning of my cursor????
--------- begin ----------
open cur
fetch next... from into....
while @@fetch_status = 0
begin
if @c1 > 1000
break
else
continue

open cur

fetch next.... from into....

do some business nested inner cursors.....
set @c1 = @c1 + 1 -- increment the counter...
fetch next... from into....
end
close cur
deallocate cur
--------- end ----------
Will the 'break' take be to my end?

TIA

JeffP...
cross posted in ms.pub.sqlserver.programming

View 1 Replies


ADVERTISEMENT

Sending Email Reminder To Multiple Users - Cursor Not Working Correctly?

Sep 18, 2014

I am tring to send an email reminder to multiple users using a cursor.

declare emailCursor cursor for
select name,info,default_email, @MyMessage
from My_Temp

open emailCursor
fetch next from emailCursor into @Name,@info, @Recipients,@MyMessage

while(@@FETCH_STATUS = 0)
begin

-- select name,info,ins_default_email,@MyMessage
--from temp

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Hello',
@recipients = @Recipients,
@from_address = '<address.com>',
@Body = @MyMessage,
@subject = 'Subject'

fetch next from emailCursor into @Name,@info, @Recipients,@MyMessage

end
close emailCursor
deallocate emailCursor

Currently I have two users in my table that I want to send email to, but when the stored procedure runs I got two emails for the last user in the table instead one for each user.

View 2 Replies View Related

Problems With The Query, ResultSet Property Not Set Correctly, Parameters Not Set Correctly, Or Connection Not Established Cor

Oct 22, 2007



I have the following query in an ExecuteSQL Task:

Insert Into Table2
Select * From Table1 Where Column1Val = '4'


As you can see, I don't need any parameters so I havent configured any. Also, there should not be any result set so I shouldnt need to configure a resultset parameter.

Why is the above query failing with

Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly

View 4 Replies View Related

Transact SQL :: Difference Between Outer Apply And Outer Join

May 10, 2010

what is difference between outer apply and outer join ,both return rows from left input as well as right input . isnt it?

View 3 Replies View Related

SQL-92 Outer Join Vs T-SQL Outer Join (6.5 Or 7.0) - Test Script Included

Apr 26, 2002

Take the following scenario:

We have two tables that have somewhat of a parent-child relationship. We are trying to use a SQL-92 outer join that returns the same results as a TSQL *= outer join. The difficulty we are having is that some of the parent records do not have any corresponding child records, but we still want to see those parent records with 0 (zero) for the count. How can we accomplish this with a SQL-92 compliant join (if it is even possible)? In the query results below, we would like the first set of results.

Thanks in advance for any help.
-David Edelman

Test script below, followed by results
===========================================
create table parent (p_id int NOT NULL)
go
create table child (p_id int NOT NULL, c_type varchar(6) NULL)
go
insert parent values (1)
insert parent values (2)
insert parent values (3)
insert parent values (4)
insert parent values (5)
insert parent values (6)
insert parent values (7)
insert parent values (8)
insert parent values (9)
insert parent values (10)
go

insert child values (1, 'AAA')
insert child values (1, 'BBB')
insert child values (1, 'CCC')
insert child values (2, 'AAA')
insert child values (4, 'AAA')
insert child values (4, 'DEF')
insert child values (4, 'AAA')
insert child values (4, 'BBB')
insert child values (5, 'AAA')
insert child values (5, 'AAA')
insert child values (6, 'AAA')
insert child values (7, 'AAA')
insert child values (7, 'BBB')
insert child values (7, 'CCC')
insert child values (7, 'DDD')
insert child values (10, 'AAA')
insert child values (10, 'CCC')
go

select p.p_id, count(c.p_id) as num_rows
from parent p, child c
where p.p_id *= c.p_id
and c.c_type in ('AAA', 'BBB')
group by p.p_id

select p.p_id, count(c.p_id) as num_rows
from parent p left outer join child c on p.p_id = c.p_id
where c.c_type in ('AAA', 'BBB')
group by p.p_id

=========================================
Results:
(T-SQL *= outer join)
p_id num_rows
----------- -----------
1 2
2 1
3 0
4 3
5 2
6 1
7 2
8 0
9 0
10 1

(SQL-92 outer join)
Warning: Null value eliminated from aggregate.
p_id num_rows
----------- -----------
1 2
2 1
4 3
5 2
6 1
7 2
10 1

View 1 Replies View Related

Exit SP

Mar 30, 2007

hi all,

any keyword for exit SP? like the exit sub.. which stop the Sp from next statement if one condition occured?
like :
if NOT exists (select blabla)
exit SP execution
else
(blablaba)

~~~Focus on problem, not solution~~~

View 2 Replies View Related

Transact SQL :: STATIC Defines A Cursor That Makes Temporary Copy Of Data To Be Used By Cursor

Aug 12, 2015

In MSDN file I read about static cursor

STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications

It say's that modifications is not allowed in the static cursor. I have a  questions regarding that

Static Cursor
declare ll cursor global static
            for select  name, salary from ag
  open ll
             fetch from ll
 
              while @@FETCH_STATUS=0
               fetch from ll
                update ag set salary=200 where 1=1
 
   close ll
deallocate ll

In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says  modifications is not allowed in the static cursor.But I am able to update the data using static cursor.

View 3 Replies View Related

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance

Jul 20, 2005

Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View 1 Replies View Related

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.

Sep 20, 2007

I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.


Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.

here is the stored procedure:


Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval



GO


example useage:


EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View 7 Replies View Related

Exit DTS Package

Sep 7, 2006

Is there a way to exit an executed DTS package using Microsoft TSQL without causing an error or does this need to be in VB?

View 4 Replies View Related

Graceful Exit...

Nov 9, 2006



I need to exit from an entire package if a data pump task fails in a sequence container.

I thought I'd use a script task to do this but I'd be glad to learn of any way to do this.

Thanks!

Enantiodrom

View 4 Replies View Related

Force Exit

Sep 11, 2007

Hi,

I have a package that goes out and picks up a file off of a ftp server using the ftp task. How do I force the package to stop running if the file is not there?

View 5 Replies View Related

Exit Stored Procedure

Jul 12, 2006

Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as follows:
 
Exec Sp1
   Inserts, updates
   Exec Sp2
       Inserts, upadtes
       Exec Sp3
           If error Exit Exec Sp1
 
Can I end Sp1 if I catch an error in Sp3

View 1 Replies View Related

SQL Analyzer Hanging On Exit

Jul 1, 2004

Has anyone seen this?

When I exit out of Query Analyzer - it hangs and gives the following error message in Event logger
Hanging application isqlw.exe, version 2000.80.760.0, hang module hungapp, version 0.0.0.0, hang address 0x00000000.

View 1 Replies View Related

Can Insert Same Exit Value For Entry

Nov 24, 2011

i have data like...

userid startdate in_out
1 2011-11-20 9.30 1
1 2011-11-20 10.30 0
1 2011-11-20 12.30 0
1 2011-11-20 12.45 1
1 2011-11-20 6.30 0

See 1-Entry 0-exit for 2011-11-20 12.30 is exit but entry time of that exit is missing....

Can i insert same exit value for entry?

View 3 Replies View Related

SQL Stmt To Exit From A Job In The ELSE Block

Mar 5, 2008

Hi experts,

I have a job that has several steps.
One of the steps is of type T-SQL. I have an IF...ELSE block in it. If the if statement is true...perform an action.
Else exit from the job reporting failure.

I would like to know what sql stmts should i use to exit from the job (in the ELSE block)
I tried to use EXIT in the else stmt but it is not exiting from the job step.


Thanks in advance

View 4 Replies View Related

Exit Stored Procedures

Jul 12, 2006

Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as follows:

Exec Sp1
Inserts, updates
Exec Sp2
Inserts, upadtes
Exec Sp3
If error Exit Exec Sp1

Can I end Sp1 if I catch an error in Sp3

View 7 Replies View Related

Possible Setup.exe Exit Codes?

May 8, 2007

As part of our application deployment we have wrapped and automated the SQL Server Express install by launching the setup.exe with our parameters and waiting around for the process to exit.

I had been checking for a Exit Code != 0 to detect errors returned by setup.exe but this does not appear to be foolproof. Several customers' deployments have returned non-zero exit codes, but SQL Express seems to be successfully installed.

I have not found a reference of possible codes returned and their meaning that I could use to fine tune our checking. Does one exist? Or am I going about this the wrong way and should just check for the existence of our instance after setup is complete?


thank you for any input.

View 1 Replies View Related

Exit The Package Conditionally

Jan 4, 2007

I'm inside a "dataflow" and

i have a conditional spliter

 

one of the condition say "ok"

and the the other says "not ok"

 

when  my flow goes to the "not ok" condition

I want to Abort  the entire package execution

I want to make it sure that the package is terminated

and could not  triggers the next control in the control flow.

 

i want to kill/ abort the execution of all my SSIS processes

for the entire package within the current dataflow

 

 

View 3 Replies View Related

How To Exit A Package On Certain Conditions?

Feb 29, 2008

I have a package that runs. Inside the package I want to see if a specific table exists. If it does, I go to the next step inside the package. If it does not, I want to exit that package without errors or even warnings.

Any ideas?

Thanks.

View 6 Replies View Related

The Process Exit Code Was 128

Apr 1, 2008

Folks,
I have a SSIS package that works 3 out of 4 times but it does fail occasionally with the following error.

The return value was unknown. The process exit code was 128. The step failed.
I have logging enabled ..but mysteriously the log has no information on the failure..

Any thoughts ?

thanks

View 6 Replies View Related

The Process Exit Code Was 128.

Mar 20, 2008

Hi,

I am running a Sql Server 2005 job and this job is having 25 steps. Each step is calling the .dtsx packae from D: drive location. While running the job i found this error

"The return value was unknown. The process exit code was 128. The step failed."


This error occurs not for any perticular step. It occurs randomally at any step.

I am running this job on
1. Windows 2003 server with SP2
2. Sql Server 2005 (Developer Edition) with SP2

Can any one please help me out resolving this issue.

Amit

View 2 Replies View Related

Exit For Loop In SSIS

Jan 24, 2008



Hi All,

I have a SSIS package which contains For Loop Container. when a task in the for loop container fails iam setting loop value to 0, even though iam not able to exit the loop. can some help me

Note: when ever the a task in the Loop fails the loop should stop and package should stop.

View 3 Replies View Related

Help -- How Do I Exit A Foreach Loop

Jun 8, 2006

I have a foreach loop that is processing files in a directory. One of the tasks is the execute sql task that calls a stored proc. If this stored proc returns a certain value then I need to exit the loop and not process any more files. But I have been unable to figure out how to make this happen.

Any help would be greatly appreciated.

Thanks!

GN

View 5 Replies View Related

Need To Alert All Users To Exit Sql Server

Aug 2, 2001

How do I configure the database so that it will send an alert to all users who are logged in and using SQL*Server, when I need them all to get out of the database ??? Or, what do I need to look up in the documentation ??? I have looked under SQL*Mail, but that doesn't look like it will work for what I am trying to do.
Any help you can give me will be greatly appreciated.
Thanks,
Nancy

View 3 Replies View Related

Stored Proc Error And Exit

Sep 15, 2000

I come from an Access/VB background where in an error trapping routine or testing for an error your statement "exit sub" ended the process. WHen using a stored procedure and you come across an error what is the best method to exit/end the stored proc?

View 3 Replies View Related

SQL Server Does Not Exit Or Access Denied

Sep 28, 2004

I am using sql server 2000 with service pack 3a

I have install same copy for database server at two windows 2000 server box with SP4.

I have been connected both server on LAN and replicate our required data from one to another. It's working fine.

In actual implementation both server would be connected via ISDN Dialup

Total distance between two server is maximum 40 KM.

I apply same method like lan on ISDN Dial up.

1. Given all permission equvallent to administrator to new user
2. Login by new user
3. Install database server at both end.
4. Dial by one place to another.
5. Ping each other by ip address.
6. By dialup place , I can ping new static IP address given by dial.
7. Dial in place I can ping both IP address newok card as well as new dialip IP
8. Search IP address (RAS Base)., Ping also to remote IP.

I didn’t ping physical network IP address. I can’t brows simply, I can’t ping domain name but I am able to ping remote IP.

I got error at the time of registration - 'Specify Sql server not found, Connection Open Connected()'

10. I try to user client network utility again get error as under-

Add 2nd server by client network utility at the another

Try to connect at got error-
Unable to connect to server test
Server msg 17 level 16 srare 1
ODBC Sql server driver [TCP/IP Socket] sql server doesn't exit

I didn't understand why I getting error in registraion of one server to another, while both can be registered to each other in LAN environment.

Let me help , I am trying to that for last 4day and night but not yet got any conclussion

Thank in advance for all of you

R.Mall

View 1 Replies View Related

Forcing Dbsqlexec To Exit Cleanly

Jul 23, 2005

I am developing a simple DB-Library program in C calling SQL Server 2000 onwindows 2003 and NT 4. I have some T-SQL code that checks for the existenceof a table and want to abort the program if the table doesn't exist. I issuea raiserror if the table doesn't exist and then call RETURN.I construct the string using sprintf and pass it dbfcnd and dbsqlexec. Sincethe commands work, there is no error to halt the execution of the program.Is there an easy, clean way to force dbsqlexec to fail? Do I need a storedprocedure to return an error code and then deal with that?Thanks for any advice,-Gary

View 3 Replies View Related

Sqlservr.exe Exit Code Of 259 (0x103) - What Does It Mean?

Mar 20, 2007

Can someone please explain what exit code 259 means as it appears in the debug output below?

When running a C# Stored Procedure within the VS 2005 IDE I receive the following Debug Output:

Auto-attach to process '[1152] [SQL] enterprise' on machine 'enterprise' succeeded.

Debugging script from project script file.

The thread 'enterprisesqlexpress [56]' (0x150) has exited with code 0 (0x0).

The thread 'enterprisesqlexpress [56]' (0x1314) has exited with code 0 (0x0).

The thread 'enterprisesqlexpress [56]' (0x1314) has exited with code 0 (0x0).

'sqlservr.exe' (Managed): Loaded 'C:WINDOWSassemblyGAC_32mscorlib2.0.0.0__b77a5c561934e089mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'sqlservr.exe' (Managed): Loaded 'c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnSqlAccess.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'sqlservr.exe' (Managed): Loaded 'C:WINDOWSassemblyGAC_32System.Data2.0.0.0__b77a5c561934e089System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'sqlservr.exe' (Managed): Loaded 'C:WINDOWSassemblyGAC_MSILSystem2.0.0.0__b77a5c561934e089System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'sqlservr.exe' (Managed): Loaded 'C:WINDOWSassemblyGAC_32System.Transactions2.0.0.0__b77a5c561934e089System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'sqlservr.exe' (Managed): Loaded 'C:WINDOWSassemblyGAC_MSILSystem.Security2.0.0.0__b03f5f7f11d50a3aSystem.Security.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

Auto-attach to process '[1152] sqlservr.exe' on machine 'enterprise' succeeded.

'sqlservr.exe' (Managed): Loaded 'C:WINDOWSassemblyGAC_MSILSystem.Xml2.0.0.0__b77a5c561934e089System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'sqlservr.exe' (Managed): Loaded 'SQL2005_Test', No symbols loaded.

'ENTERPRISE;.Net SqlClient Data Provider;4400' (Managed): Loaded 'C:WINDOWSassemblyGAC_MSILSystem.Configuration2.0.0.0__b03f5f7f11d50a3aSystem.Configuration.dll', No symbols loaded.

CurrencyCode Name ModifiedDate

------------ -------------------------------------------------- -----------------------

eee MyCurr4 3/20/2007 12:58:38 PM

(1 row(s) affected)

(1 row(s) returned)

Finished running sp_executesql.

The thread 'enterprisesqlexpress [56]' (0x1314) has exited with code 0 (0x0).

The program '[1152] [SQL] enterprise: enterprisesqlexpress' has exited with code 0 (0x0).

The program '[1152] sqlservr.exe: Managed' has exited with code 259 (0x103).



I would also appreciate hearing back from users as to why I only have two choices in my VS 2005 IDE Output window (Build and Debug). From what I've read, 'Database' should be another choice for 'Show output from:'.



My environment consists of the following:

Database:

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I have verified the SQL Server connection has 'Allow SQL/CLR Debugging' enabled.

The Visual Studio 2005 IDE is:

8.0.50727.762 (SP.050727-7600)

Running Microsoft .Net Framework version 2.0.50727

View 4 Replies View Related

Process Exit Code:1073741819

Jul 13, 2006

   I have changed a SSIS package last week. Now, I met the problem that the package sometimes failed both in SQL agent and the BIDs.

  SQL agent  just give the detail: Process Exit Code -1073741819. The step failed.


   In BIDs,when I Check the log,there is even no error. But the last Infomation in the output windows is the package canceled. It just like somebody stop running package manually.

   The more strange thing is the package sometimes succeed with running the same data.

 

     Thanks for your help

 

View 18 Replies View Related

The Process Exit Code Was -1073741510

May 9, 2007

I did a few searches and found nothing. Any ideas what this error means?

View 2 Replies View Related

Process Exit Code Of Successful Command

Jul 5, 2000

I wish to have a batch file exit with a return code of 3 if it fails.
In the batch file I have the single command: Exit 3
On SQL Server the Process Exit Code of a successful command is set to 0.

Yet, when I run the job SQL Server says that it is successful.

View 1 Replies View Related

Problem With Process Exit Code (Cmdexec)

Mar 25, 2003

Can someone tell me how i can generate an exit code (Cmdexec). Ive tried running an .exe file in a job but when i do that the job hangs. When i check the history it says the exit code is somewhere in 27000. Now i'm wondering if it is possible for me to generate an exit code if the executable closes.
PS. When i run the file from a batch file it works great

View 1 Replies View Related







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