Hi all
I have often come across discussions on this forum saying that CURSORs are expensive in time (processing power?).
Having used CURSORs to processing a mere 2000+ record (not much at all) which took a fair while to complete, I now realize why you guys are saying CURSORs are expensive.
But is there alternatives to using CURSORs in the situation where I try to process every records returned by a particular query?
Say for example, i want to update columns that comes from different tables for every record that is returned by a SELECT JOIN query. there is no way that i can do that with a single UPDATE statement cause i can't do JOIN with UPDATE query.
All comments welcome
James :)well u can :
update a set aa= b.bb
from table_a a
inner join table_b b on a.key = b.key
since in cursors u use loops try :
loop on numeric key in table
select @.i = Min(int_key) From Tablename
while @.i <= (select @.Max(int_key) From Tablename)
begin
.
.
end|||yes you can join tables in the from clause of an update statement
[BOL] UPDATE (described)
check out example 'C' at the bottom of the help document|||Thank you guys.
Is it standard ANSI to use join in an update query? Although it would make sense that it is. I have tried it before without success for some reason. :( I will try it again.
James :)|||No, JOIN operations in an UPDATE are explicitly forbidden by the ISO, and were never addressed by ANSI. While JOIN operations in an UPDATE can be convenient, they violate most of the rules of relational algebra. Sybase and Microsoft are the only commercially successful engines I can think of that support them.
-PatP|||Really?
So in Oracle, for instance, you can't execute a statement like:
update A
set A.Column = NewValue
from A
inner join B on A.Key = B.Value
?
ANSI or not, that's pretty simple and pretty convenient too.|||Originally posted by Pat Phelan
No, JOIN operations in an UPDATE are explicitly forbidden by the ISO, and were never addressed by ANSI. While JOIN operations in an UPDATE can be convenient, they violate most of the rules of relational algebra. Sybase and Microsoft are the only commercially successful engines I can think of that support them.
-PatP
Nope...even DB2 OS/390 can do it now...it's just extremely painful...
But we did have a very good thread where we discussed how I "crossed the line" and broke the rules...
I gotta look it up...|||Here it is...
Bookmarked it...
http://www.dbforums.com/showthread.php?threadid=989508|||subqueries are useful here, as they provide for the referencing of tables.
normally in a complex update or delete i will create a query that doesnt change the data and after i recieve the correct results, i will use it as a subquery for the update\delete stmt. especially if the sarg is a dynamic value.
update t1
set c2 = x
where col3 in (select col3 from t2
where col4 = x)
however, be carefull with subqueries as they can have some definite disadvantages. specifically correlated subqueries
in addition when you join tables in an update\delete, the sql optimizer has a great deal of flexibility with the join operations where in the subquery the inner and outer queries kind of restrict the optimizers options.|||Originally posted by blindman
Really?
So in Oracle, for instance, you can't execute a statement like:
update A
set A.Column = NewValue
from A
inner join B on A.Key = B.Value
?
ANSI or not, that's pretty simple and pretty convenient too. I rarely think of Oracle, or at least I try not to.
I didn't realize that DB2 supported this form of blaspheme. I'm sure that it is great fun listening to Celko on this topic!
-PatP|||It's just so damn useful...in the (DB2) old days when it didn't, you had to either use a cursor, or genrate the satements and then execute them in a batch...|||As far back as I can remember, DB2 supported sub-queries. Sub-queries are safe to use in an UPDATE as long as they are stochastic and deterministic. I don't know when DB2 added support for JOIN operations within an UPDATE.
-PatP|||I think it was back in V5...
and whoah...
had to look that one up
http://www.hyperdictionary.com/dictionary/stochastic|||Ooops, my bad. I meant non-stochastic. Sorry.
-PatP|||Thought it was kind of like oil and water...
Showing posts with label across. Show all posts
Showing posts with label across. Show all posts
Sunday, February 12, 2012
Alternative to Temporary table to store stored procedure results
I have come across the error "INSERT EXEC statement cannot be nested"
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.
Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegro ups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>
|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:[vbcol=seagreen]
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegro ups.com...
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.
Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegro ups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>
|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:[vbcol=seagreen]
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegro ups.com...
Alternative to Temporary table to store stored procedure results
I have come across the error "INSERT EXEC statement cannot be nested"
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:[vbcol=seagreen]
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:[vbcol=seagreen]
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
Alternative to Temporary table to store stored procedure results
I have come across the error "INSERT EXEC statement cannot be nested"
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
> >I have come across the error "INSERT EXEC statement cannot be nested"
> > when trying to store the results of a stored procedure in a temporary
> > table. I understand why this is happening - because there is already
> > an INSERT EXEC in the stored procedure I am executing - but I need to
> > be able to store the results in some way.
> >
> > Unfortunately re-writing the stored procedure I am calling is not an
> > option, and I was wondering if there is another way for me to evaluate
> > the results from my stored procedure.
> >
> > I have looked into table variables and functions, but they do not work
> > here either.
> >
> > I would appreciate anyone's input on this.
> >
when trying to store the results of a stored procedure in a temporary
table. I understand why this is happening - because there is already
an INSERT EXEC in the stored procedure I am executing - but I need to
be able to store the results in some way.
Unfortunately re-writing the stored procedure I am calling is not an
option, and I was wondering if there is another way for me to evaluate
the results from my stored procedure.
I have looked into table variables and functions, but they do not work
here either.
I would appreciate anyone's input on this.Are you refering to something like this:
USE PUBS
GO
CREATE PROC USP_TEMPPROC
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST1
DROP TABLE #TEST2
EXEC USP_TEMPPROC
--OR SOMETHING LIKE THIS:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC BYROYALTY 100
SELECT * FROM #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
Both seem to work.
HTH
Jerry
<c.williamson@.dialaphone.com> wrote in message
news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
>I have come across the error "INSERT EXEC statement cannot be nested"
> when trying to store the results of a stored procedure in a temporary
> table. I understand why this is happening - because there is already
> an INSERT EXEC in the stored procedure I am executing - but I need to
> be able to store the results in some way.
> Unfortunately re-writing the stored procedure I am calling is not an
> option, and I was wondering if there is another way for me to evaluate
> the results from my stored procedure.
> I have looked into table variables and functions, but they do not work
> here either.
> I would appreciate anyone's input on this.
>|||More like the second example, but slightly different:
USE PUBS
GO
CREATE PROC USP_TEMPPROC_2A
AS
CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
INSERT #TEST1
EXEC USP_TEMPPROC_2B
DROP TABLE #TEST1
CREATE PROC USP_TEMPPROC_2B
AS
CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
INSERT #TEST2
EXEC BYROYALTY 100
SELECT * FROM #TEST2
DROP TABLE #TEST2
EXEC USP_TEMPPROC_2A
When running the last line I get "An INSERT EXEC statement cannot be
nested", because in both stored procedures I am trying to store results
from the sp in a temporary table.
I cannot re-write the second stored procedure, so I need some way to
work with the results in the first stored procedure.
Thank you
Christian
Jerry Spivey wrote:
> Are you refering to something like this:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST1
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC
> --OR SOMETHING LIKE THIS:
> USE PUBS
> GO
> CREATE PROC USP_TEMPPROC_2A
> AS
> CREATE TABLE #TEST1 (AU_ID VARCHAR(25))
> INSERT #TEST1
> EXEC BYROYALTY 100
> SELECT * FROM #TEST1
> EXEC USP_TEMPPROC_2B
> DROP TABLE #TEST1
> CREATE PROC USP_TEMPPROC_2B
> AS
> CREATE TABLE #TEST2 (AU_ID VARCHAR(25))
> INSERT #TEST2
> EXEC BYROYALTY 100
> SELECT * FROM #TEST2
> DROP TABLE #TEST2
> EXEC USP_TEMPPROC_2A
> Both seem to work.
> HTH
> Jerry
> <c.williamson@.dialaphone.com> wrote in message
> news:1128098603.949372.70830@.z14g2000cwz.googlegroups.com...
> >I have come across the error "INSERT EXEC statement cannot be nested"
> > when trying to store the results of a stored procedure in a temporary
> > table. I understand why this is happening - because there is already
> > an INSERT EXEC in the stored procedure I am executing - but I need to
> > be able to store the results in some way.
> >
> > Unfortunately re-writing the stored procedure I am calling is not an
> > option, and I was wondering if there is another way for me to evaluate
> > the results from my stored procedure.
> >
> > I have looked into table variables and functions, but they do not work
> > here either.
> >
> > I would appreciate anyone's input on this.
> >
Thursday, February 9, 2012
Alternative to Merge Replication?
Is there a simpler alternative to merge replication? I am concerned about the splash of GUID across the DB, performance issues, and the complexity of the deployment.What version of SQL server are you using? Look into transactional and peer to peer replication. FYI, these will require a primary key field to be set on each table.
|||I am using SQL 2005. Your solution does NOT require a GUID?|||Correct, but it does require every table to have a primary key. You will need to read more on both technologies to determine what works best in your environment.
|||Sorry, meant to post this earlier...
|||I am using SQL 2005. Your solution does NOT require a GUID?|||Correct, but it does require every table to have a primary key. You will need to read more on both technologies to determine what works best in your environment.
|||Sorry, meant to post this earlier...
Info about the different types of replication, http://msdn2.microsoft.com/en-us/library/ms152531.aspx
|||
there are many articles and whitepapers about replication deployments on the net, maybe we can ease you if you describe your scenario and deployment concerns. i.e. how many subscribers are we talking about?
as far as GUIDs, if you specify correctly column lists for all your insert statements, you shouldn't have any application breaks, it should be seamless.
|||My scenerio is:
Networked offices with 5-30 internal users, modifying a central DB in SQL Server 2005
1-6 Field workers who need to modify a subset of the tables in the field (disconnected) and then connect to the network and upload their changes.
Low (though possible) change of conflicting changes between syncs, as field people tend to work in different areas. Field device might be away from the central DB for 1-10 days.
Last-one-wins would be OK for conflicting changes. I've been considering a home-grown system that inserts new records to the central DB, when executes an Audit trail for the changes.
I know it sound like Merge replication. I'm not sure why I'm so afraid of actually using Merge replicaiton. It seems so complex, and heavy, and trigger happy, and if something goes wrong it looks like a nightmare to troubleshoot.
|||According to http://msdn2.microsoft.com/en-us/library/ms151718.aspx, Updatable Subscriptions for Transactional Replication DOES require a GUID. "Replication adds the msrepl_tran_version column to published tables for tracking purposes"
And Peer-to-Peer seems to be for Server-2-server, not for Server-2-DisconnectedClient.
|||
Peer 2 Peer does not currently handle conflicts (it will in the next version of sql server), so you can count that out for now. Merge replication does exactly what you need, there are many many references to customers using Merge replication with hundreds, if not thousands, of subscribers. My only suggestion to you is to try it out in a test environment. Yes it does require guid columns on every table as that is the mechanism used to track changes.
If you can be patient, you can also wait for OCS to come out early next year with the next release of Visual Studio, you can read about it on steve lasker's blog, search for OCS http://blogs.msdn.com/stevelasker/default.aspx.
Alternative to Merge Replication?
Is there a simpler alternative to merge replication? I am concerned about the splash of GUID across the DB, performance issues, and the complexity of the deployment.What version of SQL server are you using? Look into transactional and peer to peer replication. FYI, these will require a primary key field to be set on each table.|||I am using SQL 2005. Your solution does NOT require a GUID?|||Correct, but it does require every table to have a primary key. You will need to read more on both technologies to determine what works best in your environment.|||Sorry, meant to post this earlier...
Info about the different types of replication, http://msdn2.microsoft.com/en-us/library/ms152531.aspx|||
there are many articles and whitepapers about replication deployments on the net, maybe we can ease you if you describe your scenario and deployment concerns. i.e. how many subscribers are we talking about?
as far as GUIDs, if you specify correctly column lists for all your insert statements, you shouldn't have any application breaks, it should be seamless.
|||My scenerio is:
Networked offices with 5-30 internal users, modifying a central DB in SQL Server 2005
1-6 Field workers who need to modify a subset of the tables in the field (disconnected) and then connect to the network and upload their changes.
Low (though possible) change of conflicting changes between syncs, as field people tend to work in different areas. Field device might be away from the central DB for 1-10 days.
Last-one-wins would be OK for conflicting changes. I've been considering a home-grown system that inserts new records to the central DB, when executes an Audit trail for the changes.
I know it sound like Merge replication. I'm not sure why I'm so afraid of actually using Merge replicaiton. It seems so complex, and heavy, and trigger happy, and if something goes wrong it looks like a nightmare to troubleshoot.
|||According to http://msdn2.microsoft.com/en-us/library/ms151718.aspx, Updatable Subscriptions for Transactional Replication DOES require a GUID. "Replication adds the msrepl_tran_version column to published tables for tracking purposes"
And Peer-to-Peer seems to be for Server-2-server, not for Server-2-DisconnectedClient.
|||
Peer 2 Peer does not currently handle conflicts (it will in the next version of sql server), so you can count that out for now. Merge replication does exactly what you need, there are many many references to customers using Merge replication with hundreds, if not thousands, of subscribers. My only suggestion to you is to try it out in a test environment. Yes it does require guid columns on every table as that is the mechanism used to track changes.
If you can be patient, you can also wait for OCS to come out early next year with the next release of Visual Studio, you can read about it on steve lasker's blog, search for OCS http://blogs.msdn.com/stevelasker/default.aspx.
Subscribe to:
Posts (Atom)