473,386 Members | 1,706 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

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 4493
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Mark Wilson CPU | last post by:
This must be easy, but I'm missing something... I want to execute a Perl script, and capture ALL its output into a PHP variable. Here are my 2 files: -------------------------------------...
3
by: edgekaos | last post by:
Is method 2 valid? Method 1: wstring input = L"STRING"; wstring output = input; transform(output.begin(), output.end(), output.begin(), towupper); Method 2: wstring input = L"STRING";...
4
by: Kevin Mansel via .NET 247 | last post by:
Ok, basically this is my problem. I'm building a console app tocall a dos program. So i'm using the Shell command to call theprogram, now depending on what happens, I want to read theoutput that...
24
by: kalamantina | last post by:
#include "stdafx.h" #include <stdio.h> #define output( x ) printf( #x "\r\n" );fflush( stdout ) class CMyBase { public: CMyBase() { output( CMyBase() ); f(*this);
0
by: newbie | last post by:
i'm a newbie of c language. can anyone help me to implement the code so that I can get the ciphertext from the output. thanks. #ifndef _3DES_H #define _3DES_H #ifndef uint8 #define uint8 ...
32
by: spibou | last post by:
Is the output of the C preprocessor deterministic ? What I mean by that is , given 2 compilers which conform to the same standard, will their preprocessors produce identical output given as input...
3
by: MatsL | last post by:
Hi, This is seriously driving me crazy, could anyone explain to me why neither of these doesn't produce XHTML compliant output (it is being called in Render() btw): output.WriteLine("<img...
3
by: undshan | last post by:
I am writing a code that needs to open a file, create an output file, run through my function, prints the results to the output file, and closes them within a loop. Here is my code: #include...
5
by: amit.uttam | last post by:
Hey everyone, I've recently jumped big time into python and I'm working on a software program for testing automation. I had a question about proper logging of output. What I would like is: 1....
2
by: gabosom | last post by:
Hi! I've been breaking my head trying to get the output variables from my Stored Procedure. This is my SP code CREATE PROCEDURE GetKitchenOrderDetail( @idService int, --outPut Variables ...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.