Cursor not completing when stored procedure runs within it

I am having an interesting problem I haven't seen.
First, here's the code that sets up the cursor, with a select statement
where the exec should be, and the results:
DECLARE @.order_id int,
@.row_id int,
@.qty_rtn int,
@.invoice_id int,
@.date_shipped datetime
DECLARE order_return CURSOR FOR
select r.order_id_display, r.row_id -1, r.quantity, s.line_id,
getdate() from batch..temp_response r, shipment s,
receipt_item i
where isnull(r.status, 0) >= 0 and new_status in ('R', 'U')
and i.i_order_id_display = r.order_id_display
and i.order_id = s.order_id
and i.row_id = r.row_id - 1
and i.upc=r.upc and amount = 1
and i.order_id in ('0FD94RQXB4JL9J8V4R3G5B8CC5') --for
testing purposes I selected one order only
OPEN order_return
FETCH NEXT FROM order_return INTO @.order_id, @.row_id, @.qty_rtn,
@.invoice_id, @.date_shipped
WHILE @.@.FETCH_STATUS = 0
BEGIN
select 'exec process_line_item_shipping', @.order_id, @.row_id, 0,
@.qty_rtn, @.date_shipped, @.invoice_id
-- exec process_line_item_shipping @.order_id, @.row_id, 0, @.qty_rtn,
@.date_shipped, @.invoice_id
FETCH NEXT FROM order_return INTO @.order_id, @.row_id, @.qty_rtn,
@.invoice_id, @.date_shipped
END
CLOSE order_return
DEALLOCATE order_return
This returns
exec process_line_item_shipping 491232 0 0 1
2006-06-16 12:46:19.330 534386
exec process_line_item_shipping 491232 1 0 1
2006-06-16 12:46:19.330 534386
Which is exactly what I'd expect.
HOWEVER... when I remove the comment tag off the actual SP exec
command, then I ONLY get
exec process_line_item_shipping 491232 0 0 1
2006-06-16 12:46:19.330 534386
and only the first exec statement runs.
I've done a select @.@.fetch_status before and after the exec statement,
and it's 0 each time.
The stored procedure run has no cursors within it, just several
calculations, inserts and update statements.
Can someone figure this out for me?DOINK!
Never mind, I think I figured it out. When I changed it to an
INSENSITIVE cursor, all rows were executed -- basically the updates
were invalidating the remaining row's work, and so it wouldn't fetch
anymore rows.
At least I think that's what happened.
dwcscreenwriterextremesupr...@.gmail.com wrote:
> I am having an interesting problem I haven't seen.
> First, here's the code that sets up the cursor, with a select statement
> where the exec should be, and the results:
> DECLARE @.order_id int,
> @.row_id int,
> @.qty_rtn int,
> @.invoice_id int,
> @.date_shipped datetime
> DECLARE order_return CURSOR FOR
> select r.order_id_display, r.row_id -1, r.quantity, s.line_id,
> getdate() from batch..temp_response r, shipment s,
> receipt_item i
> where isnull(r.status, 0) >= 0 and new_status in ('R', 'U')
> and i.i_order_id_display = r.order_id_display
> and i.order_id = s.order_id
> and i.row_id = r.row_id - 1
> and i.upc=r.upc and amount = 1
> and i.order_id in ('0FD94RQXB4JL9J8V4R3G5B8CC5') --for
> testing purposes I selected one order only
> OPEN order_return
> FETCH NEXT FROM order_return INTO @.order_id, @.row_id, @.qty_rtn,
> @.invoice_id, @.date_shipped
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select 'exec process_line_item_shipping', @.order_id, @.row_id, 0,
> @.qty_rtn, @.date_shipped, @.invoice_id
> -- exec process_line_item_shipping @.order_id, @.row_id, 0, @.qty_rtn,
> @.date_shipped, @.invoice_id
> FETCH NEXT FROM order_return INTO @.order_id, @.row_id, @.qty_rtn,
> @.invoice_id, @.date_shipped
> END
> CLOSE order_return
> DEALLOCATE order_return
> This returns
> exec process_line_item_shipping 491232 0 0 1
> 2006-06-16 12:46:19.330 534386
> exec process_line_item_shipping 491232 1 0 1
> 2006-06-16 12:46:19.330 534386
> Which is exactly what I'd expect.
> HOWEVER... when I remove the comment tag off the actual SP exec
> command, then I ONLY get
> exec process_line_item_shipping 491232 0 0 1
> 2006-06-16 12:46:19.330 534386
> and only the first exec statement runs.
> I've done a select @.@.fetch_status before and after the exec statement,
> and it's 0 each time.
>
> The stored procedure run has no cursors within it, just several
> calculations, inserts and update statements.
> Can someone figure this out for me?|||Nope... That's not it... because now the inserts and updates aren't
happening. Argh! Help!
dwcscreenwriterextremesupr...@.gmail.com wrote:
> DOINK!
> Never mind, I think I figured it out. When I changed it to an
> INSENSITIVE cursor, all rows were executed -- basically the updates
> were invalidating the remaining row's work, and so it wouldn't fetch
> anymore rows.
> At least I think that's what happened.
>
> dwcscreenwriterextremesupr...@.gmail.com wrote:|||>> Nope... That's not it... <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
What you did post was awful. You are using SQL cursors, which are the
worst way to use SQL -- orders of magnitude poorer performance, lack of
portability, etc. Read some of the postings here and *any* other SQL
Newsgroup. My rule of thumb is that you should not write more than
five of them in 25 years in IT.
Looking at what you did post, it looks like you missed most of the
basic ideas of RDBMS and building a procedural routine that mimics a
file system. .
1) Why would anyone put the display order into a table? All display
work is done in the front end and not the database.
2) Ignoring design flaw #1, why did you use two different names for the
same data element (I.i_order_id_display = R.order_id_display)? Surely
nobody would put the data type or table on a data element.
3) What is a row_id? If it refers to the physical rows in a table,
then it is wrong. If it refers to the position on the input screen or
original paper form, then it is wrong. You woudl be mimicing a paper
form instead of building a relational model.
4) You use vague data element names Amount of what? It does not seem
to be money. Quantity of what? Ordered or returned or on-hand, or what?
That is like an adjective without a noun.
5) Why don't you follow ISO-11179 naming rules or at least be
consistent? Look at @.date_shipped is "<adj><noun>" while @.invoice_id
is "<noun><adj>" instead.
6) When I see procedure named "Process_Line_Item_Shipping' I worry
that you are going thru each item in an order, one at a time. SQL is a
set-oriented language and you should be working with a sub-set of
items. No loops. No Cursors.
My guess, based on no DDL, is that you need a table for the Orders, for
the Order Details, Shipments and working table of returns. The
returns will be used to update the Order Details with return
quantities and shipping info (perhaps the Orders will need changes).
I have done this in one UPDATE statement for some fairly simple
business rules. The trick was a detail table keyed on (order_nbr, sku,
ship_status, ship_date). Reports are done off of VIEWs (what
percentage of Lawn Gnomes are returned? in how many days? ) and you
needed to watch constraints (you cannot return more than you bought).

Cursor

Hi
I am creating a cursor which runs through a table of database names and
execute a script using xp_cmdshell on every database in that table.
Declare @.DataBaseName varchar(255)
Declare DBcursor CURSOR FOR
Select dbname From master..upgradedb
Declare @.command varchar(255)
OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @.DataBaseName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Upgrading.... ' + @.DataBaseName
set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
c:\temp\Complete(7-9).sql'
exec master..xp_cmdshell @.command
FETCH NEXT FROM DBCursor INTO @.DataBaseName
END
CLOSE DBCursor
DEALLOCATE DBCursor
I get the followinf results - Can anyone guide me in the right direction
please.
Upgrading.... QFM161R23AMEYAA
output
----
----
----
--
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]
NULL
(19 row(s) affected)
Upgrading.... QFM161R23GLSCHOOLS
output
----
----
----
--
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]
NULL
(19 row(s) affected)> set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
c:\temp\Complete(7-9).sql'
If you had printed out the command before trying to execute it for the first
time, you'd have noticed that there's a syntax error in it. A space is
missing before the -i switch.
But I'm just guessing here.
ML|||Check if sp_MSforeachdb System Stored Procedure can be used.
This will simplify the code. U may not require cursor at all.
Rakesh
"Jaco" wrote:

> Hi
> I am creating a cursor which runs through a table of database names and
> execute a script using xp_cmdshell on every database in that table.
> Declare @.DataBaseName varchar(255)
> Declare DBcursor CURSOR FOR
> Select dbname From master..upgradedb
> Declare @.command varchar(255)
>
> OPEN DBCursor
> FETCH NEXT FROM DBCursor INTO @.DataBaseName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Upgrading.... ' + @.DataBaseName
> set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
> c:\temp\Complete(7-9).sql'
> exec master..xp_cmdshell @.command
> FETCH NEXT FROM DBCursor INTO @.DataBaseName
> END
> CLOSE DBCursor
> DEALLOCATE DBCursor
> I get the followinf results - Can anyone guide me in the right direction
> please.
> Upgrading.... QFM161R23AMEYAA
> output
>
> ----
----
----
--
--
> usage: osql [-U login id] [-P password]
> [-S server] [-H hostname] [-E trusted connection]
> [-d use database name] [-l login timeout] [-t query timeout]
> [-h headers] [-s colseparator] [-w columnwidth]
> [-a packetsize] [-e echo input] [-I Enable Quoted Identifi
ers]
> [-L list servers] [-c cmdend] [-D ODBC DSN name]
> [-q "cmdline query"] [-Q "cmdline query" and exit]
> [-n remove numbering] [-m errorlevel]
> [-r msgs to stderr] [-V severitylevel]
> [-i inputfile] [-o outputfile]
> [-p print statistics] [-b On error batch abort]
> [-X[1] disable commands [and exit with warning]]
> [-O use Old ISQL behavior disables the following]
> <EOF> batch processing
> Auto console width scaling
> Wide messages
> default errorlevel is -1 vs 1
> [-? show syntax summary]
> NULL
> (19 row(s) affected)
> Upgrading.... QFM161R23GLSCHOOLS
> output
>
> ----
----
----
--
--
> usage: osql [-U login id] [-P password]
> [-S server] [-H hostname] [-E trusted connection]
> [-d use database name] [-l login timeout] [-t query timeout]
> [-h headers] [-s colseparator] [-w columnwidth]
> [-a packetsize] [-e echo input] [-I Enable Quoted Identifi
ers]
> [-L list servers] [-c cmdend] [-D ODBC DSN name]
> [-q "cmdline query"] [-Q "cmdline query" and exit]
> [-n remove numbering] [-m errorlevel]
> [-r msgs to stderr] [-V severitylevel]
> [-i inputfile] [-o outputfile]
> [-p print statistics] [-b On error batch abort]
> [-X[1] disable commands [and exit with warning]]
> [-O use Old ISQL behavior disables the following]
> <EOF> batch processing
> Auto console width scaling
> Wide messages
> default errorlevel is -1 vs 1
> [-? show syntax summary]
> NULL
> (19 row(s) affected)
>sql

csv import which runs stored procedure

Are there any tools which allow you to take a comma delimted file and run a stored procedure for each line in the file, passing the values in the file as parameters? i.e.

CREATE PROCEDURE add_user
@.name varchar(20),
@.first_name varchar(20),
@.last_name varchar(20)
AS
/* do some lookups */
/* validate input */
insert into users (name, first_name, last_name) values (@.name, @.first_name, @.last_name)

Then a comma delimited file such as
bpeikes,Benjamin,Peikes
rbobly,Robert,Bobly
trichard,Tom,Richards

I need a tool which can execute the stored procedure once for each line in the file. I could write one myself, but I'd like to know if any of the tools which come with SQL Server will handle it.

My preferred method would be to first load the csv file data into a 'staging' table, and then have a procedure that validates, cleanses, massages, and transfers the data to the working table(s).

And that can easily be accomplished using a SQL Agent Job, scheduled to run 'on schedule', or available to run 'as needed'.

|||

This isn't a scheduled job, it's something that I'd like to have as a tool for cases where I need to "quickly" run a stored procedure in batches. It's not necessarily about just importing data.

For instance, one might have two procedures remove_user and add_user which do more than just add the records to a single table. I don't want to have a separate staging tables for each type of "batch" work that I want to do.

I was hoping that there was a utility like bcp that would allow you to execute a SP for each line.

|||

You could add a trigger to your target table that calls your stored procedure, bcp the data into the target table and it should trigger storedproc calls for each insert.

Hope that helps,

John

|||

bpeikes wrote:

This isn't a scheduled job, it's something that I'd like to have as a tool for cases where I need to "quickly" run a stored procedure in batches. It's not necessarily about just importing data.

For instance, one might have two procedures remove_user and add_user which do more than just add the records to a single table. I don't want to have a separate staging tables for each type of "batch" work that I want to do.

I was hoping that there was a utility like bcp that would allow you to execute a SP for each line.

Maybe you should use the sql server integration services SSIS

instead