Does anyone know if there is a way to generate a valid xml
document from a stored procedure called by a trigger and
write it to a file?

Any help would be greatly appreciated,

Joe

Joe,
the trigger could call a stored procedure (spA) that, using the bcp utility,
calls another stored (spB) wich generate xml document.

Try this:

Stored procedure spA:
....
set @cmd = 'bcp "exec <dbname>..spB" queryout
"c:\test.xml" -c -S<servername> -U<username> -P<password>'
exec master..xp_cmdshell @cmd
....

Stored procedure spB:

....
select ...
for xml ...

I hope it is useful

Ruggero


"Joe" <anonymous (AT) discussions (DOT) microsoft.com> ha scritto nel messaggio
news:637f01c3e5cb$27f2ef20$a601280a (AT) phx (DOT) gbl...

Hey Thanks, I really appreciate that.

I tried it out but it doesn't seem to work, I'm just using
a simple select statement for the procedure that is called
by the bcp utility.

DECLARE @cmd varchar(200)
set @cmd = 'bcp "exec EDB_JB.DBO.GET_NAMES" queryout
"c:\test.xml" -S"EDM-HEN-123" -U"dbo" -P"mypassword"'
exec master ..xp_cmdshell @cmd

No file is created and all I get for an output in
QueryAnalyzer is:

output
-----------------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
-------------------
usage: bcp {dbtable | query} {in | out | queryout |
format} datafile
[-m maxerrors] [-f formatfile] [-e
errfile]
[-F firstrow] [-L lastrow] [-b
batchsize]
[-n native type] [-c character type] [-w
wide character type]
[-N keep non-text native] [-V file format version] [-q
quoted identifier]
[-C code page specifier] [-t field terminator] [-r
row terminator]
[-i inputfile] [-o outfile] [-a
packetsize]
[-S server name] [-U username] [-P
password]
[-T trusted connection] [-v version] [-R
regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL

(12 row(s) affected)

Any ideas? Also, I noticed there is a NULL listed in this
result, what the heck is that for?

Thanks again,

Joe

You don't have to quote all bcp parameters
Try this:

DECLARE @cmd varchar(200)
set @cmd = 'bcp "exec EDB_JB.DBO.GET_NAMES" queryout "c:\test.xml"' +
' -SEDM-HEN-123 -Udbo -Pmypassword'
exec master ..xp_cmdshell @cmd

Ruggero

"Joe" <anonymous (AT) discussions (DOT) microsoft.com> ha scritto nel messaggio
news:650601c3e681$c56d2520$a101280a (AT) phx (DOT) gbl...

Thanks, that fixed one problem, now I'm getting a couple
more.

The first one:

output
NULL
Enter the file storage type of field FIRST_NAME [char]:

Then I tried to create a format file and attach it to the
bcp utility like -fC:\Format.fmt

Here is th guts of the Format.fmt file:

1 SQLCHAR 0 40 "," 1 First_Name General_Cp437_BIN
2 SQLCHAR 0 40 "," 2 Last_Name 1_General_Cp437_BIN

Then I got this error:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open
BCP host data-file
NULL

Any ideas?

Thanks so much for the help,

Joe

It seems to me that bcp can't write to the specified file.
Do you use a local path or a network path? Does the path exists before you
try to write? Does bcp has the wrigth to write in the specified location?
I used this tecnique to export from SQL Server to a linux server using
samba, and before I call bcp, I run this command:
xp_cmdshell "net use \\server\condivision"
You can check bcp command printing the command instead of running it and
verify that it is correct (using query analizer).

I hope this hint can solve your problem.

"Joe" <anonymous (AT) discussions (DOT) microsoft.com> ha scritto nel messaggio
news:6bf901c3e68c$95d52690$a401280a (AT) phx (DOT) gbl...

Thanks.