Has anyone used an ADODB Stream to output XML directly form SQL server?
I'm trying to convert some ASP code into CF but its a pain, I have to set the constants that exist in ASP manually and I do not know each value or what they stand for exactly.
The following is what I am trying to convert:
Dim cnDB As ADODB.Connection
Dim cmSProc As ADODB.Command
set cnDB = new ADODB.Connection
cnDB.ConnectionString = connectionString
cnDB.CursorLocation = adUseClient
cnDB.Open
set cmSProc = new ADODB.Command
set cmSProc.ActiveConnection = cnDB
cmSProc.CommandType = adCmdStoredProc
cmSProc.CommandText = objSProc.Name
Dim strXML As String
Dim stXML As ADODB.Stream
Set stXML = New ADODB.Stream
stXML.open
cmSProc.Properties("Output Stream").Value = stXML
cmSProc.Parameters.Add(cmSProc.CreateParameter("@pFormId", adInteger, adParamInput, , FormId))
cmSProc.Execute , , (adExecuteStream + adCmdStoredProc)
strXML = stXML.ReadText(adReadAll)
strXML = <Root> & strXML & </Root>
This is what I have thus far
adUseClient = 1;
adCmdStoredProc = 1;
adInteger = 1;
adParamInput = 1;
FormId = 1;
adExecuteStream = 1;
adCmdStoredProc = 1;
adReadAll = 1;
connectionString = "Provider=SQLOLEDB;Server='168.153.43.6';Data Source=.;Initial Catalog=dbCCDB_development;User ID=#variables.username#;Password=#variables.password#";
cnDB = createObject("com", "ADODB.Connection");
cmSProc = createObject("com", "ADODB.Command");
cnDB.ConnectionString = connectionString;
cnDB.CursorLocation = adUseClient;
cnDB.Open();
cmSProc.ActiveConnection = cnDB;
cmSProc.CommandType = adCmdStoredProc;
cmSProc.CommandText = objSProc.Name;
stXML = createObject("com", "ADODB.Stream");
stXML.open();
cmSProc.Properties("Output Stream").Value = stXML;
cmSProc.Parameters.Add(cmSProc.CreateParameter("@pFormId", adInteger, adParamInput, null , FormId));
cmSProc.Execute( null ,null , (adExecuteStream + adCmdStoredProc));
strXML = stXML.ReadText(adReadAll);
strXML = "<Root> & strXML & </Root>";
This is the error I am getting at this stage (sure there are many more)
An exception occurred when executing a Com method. The cause of this exception was that: AutomationException: 0x80020005 - Type mismatch. in 'Provider'.
PS. I am aware that I can use SQL XML Support for IIS and output directly from MS SQL to the web, but thats not what I am after, I want to find a way to output XML created with FOR XML
---You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
Register now for the 3rd National Conference on Tourism Futures, being held in Townsville, North Queensland 4-7 August - www.tq.com.au/tfconf |
