469,090 Members | 1,089 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

XML Output

I've Proc1 and Proc2, the output of Proc2 can be in XML or thru a
normal Select statement, depending upon the input parameter specified
(@xmflag). The default of @xmflag = 0 which means non-XML output. Now
Proc1 is calling Proc2 and inserting the output of Proc2 in a temp
table without specifing the @xmflag parameter. The message coming is
"The FOR XML clause is not allowed in a INSERT statement". I don't
understand why this msg is coming when the Select statement FOR XML is
not executing.
Anybody knows the reason?

Thanks in advance.

Subodh
Jul 20 '05 #1
4 4369
Subodh (sg****@agline.on.ca) writes:
I've Proc1 and Proc2, the output of Proc2 can be in XML or thru a
normal Select statement, depending upon the input parameter specified
(@xmflag). The default of @xmflag = 0 which means non-XML output. Now
Proc1 is calling Proc2 and inserting the output of Proc2 in a temp
table without specifing the @xmflag parameter. The message coming is
"The FOR XML clause is not allowed in a INSERT statement". I don't
understand why this msg is coming when the Select statement FOR XML is
not executing.
Anybody knows the reason?


Probably the cause is that the FOR XML clause is detected when the
procedure is invoked. SQL Server cannot know off-hand whether that
FOR XML will be executed or not, so it will assume that it has.

Move the XML bit of Proc2 to a sub-procedure which you call if @xmlflag is
set. I would expect that to do it.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
I cannot write the FOR XML RAW part in another sub-SP as the data is
lying in a temp table and I cannot use global temp table. Is there any
setting in SQL Server 2000 which can override this problem. Sometimes
Proc1 executes well but starts giving error msg all of a sudden. Any
thoughts?

Subodh

Probably the cause is that the FOR XML clause is detected when the
procedure is invoked. SQL Server cannot know off-hand whether that
FOR XML will be executed or not, so it will assume that it has.

Move the XML bit of Proc2 to a sub-procedure which you call if @xmlflag is
set. I would expect that to do it. Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn*********************@127.0.0.1>... Subodh (sg****@agline.on.ca) writes:
I've Proc1 and Proc2, the output of Proc2 can be in XML or thru a
normal Select statement, depending upon the input parameter specified
(@xmflag). The default of @xmflag = 0 which means non-XML output. Now
Proc1 is calling Proc2 and inserting the output of Proc2 in a temp
table without specifing the @xmflag parameter. The message coming is
"The FOR XML clause is not allowed in a INSERT statement". I don't
understand why this msg is coming when the Select statement FOR XML is
not executing.
Anybody knows the reason?


Jul 20 '05 #3
Hi

I would look at re-designing this, as FOR XML can not be used in a derived
table of an INSERT statement, even if you move it into a procedure used in
the execute clause it will still fail.

John

"Subodh" <sg****@agline.on.ca> wrote in message
news:90**************************@posting.google.c om...
I cannot write the FOR XML RAW part in another sub-SP as the data is
lying in a temp table and I cannot use global temp table. Is there any
setting in SQL Server 2000 which can override this problem. Sometimes
Proc1 executes well but starts giving error msg all of a sudden. Any
thoughts?

Subodh

Probably the cause is that the FOR XML clause is detected when the
procedure is invoked. SQL Server cannot know off-hand whether that
FOR XML will be executed or not, so it will assume that it has.

Move the XML bit of Proc2 to a sub-procedure which you call if @xmlflag is set. I would expect that to do it.

Erland Sommarskog <es****@sommarskog.se> wrote in message

news:<Xn*********************@127.0.0.1>...
Subodh (sg****@agline.on.ca) writes:
I've Proc1 and Proc2, the output of Proc2 can be in XML or thru a
normal Select statement, depending upon the input parameter specified
(@xmflag). The default of @xmflag = 0 which means non-XML output. Now
Proc1 is calling Proc2 and inserting the output of Proc2 in a temp
table without specifing the @xmflag parameter. The message coming is
"The FOR XML clause is not allowed in a INSERT statement". I don't
understand why this msg is coming when the Select statement FOR XML is
not executing.
Anybody knows the reason?


Jul 20 '05 #4
Subodh (sg****@agline.on.ca) writes:
I cannot write the FOR XML RAW part in another sub-SP as the data is
lying in a temp table and I cannot use global temp table. Is there any
setting in SQL Server 2000 which can override this problem. Sometimes
Proc1 executes well but starts giving error msg all of a sudden. Any
thoughts?


No, there is no setting you can change.

I guess the occasion when Proc1 executes well, is when there already
is a plan in the cache.

Anyway, I have an article on my web site discusses alternatives to
INSERT-EXEC, you may get some ideas there, see
http://www.sommarskog.se/share_data.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Mark Wilson CPU | last post: by
3 posts views Thread by edgekaos | last post: by
4 posts views Thread by Kevin Mansel via .NET 247 | last post: by
24 posts views Thread by kalamantina | last post: by
reply views Thread by newbie | last post: by
3 posts views Thread by MatsL | last post: by
5 posts views Thread by amit.uttam | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.