This is not likely to work very well. ODBC will chop the XML document
after each 2033 character. See KB 275583.
So you would need to get the XML document to the client, and have the
client to create the file and put it where it belongs. Which probably
is better from a security perspective as well.
Thanks Erland,
I'm not using Query Analyzer so I don't think KB 275583 applies to me.
I'm calling the following procedure via ADO
CREATE PROCEDURE procGenerateXML
@CheckRequestID int
AS
declare @sql nvarchar(4000)
set @sql= 'bcp "EXEC TestDB..proctest ' + CONVERT(varchar(8),@ID) + '"' + '
queryout test.xml -SServer1 -T -c -r -t'
exec master..xp_cmdshell @sql
GO
The procedure proctest looks like:
CREATE PROCEDURE proctest
@ID int
AS
SELECT...
FROM...
WHERE...
FOR XML AUTO, ELEMENTS
GO
I then call the procedure like this
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim Param1
Dim ID As Integer
Dim provstr As String
Dim myfrm As Form
Dim dbs As Database
Set dbs = CurrentDb()
ID = Me.ID
cn.Provider = "sqloledb"
provstr = "Server=Server1;Database=TestDB;Trusted_Connection =Yes"
cn.Open provstr
Set cmd.ActiveConnection = cn
cmd.CommandText = "dbo.procGenerateXML"
cmd.CommandType = adCmdStoredProc
Set Param1 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append Param1
Param1.Value = ID
Set rs = cmd.Execute
I'm looking for guidance on the following:
-Can I use this xp_cmdshell method without giving my end users execute
permissions on xp_cmdshell and if not;
-Are there alternatives that don't use xp_cmdshell
Thanks
