Trying to extract the results of an sql query to a file, an error is produced when the vb script reaches "objCmd.Execute". Is the number/type of arguments that cause that error?!

Best Regards,
Theodore.

The code follows:

Set objConn = CreateObject("ADODB.Connection")
objConn.Open("PROVIDER=SQLOLEDB;SERVER=TestServer; DATABASE=TestDB;UID=sa;PWD=;")

set objCmd = CreateObject("ADODB.Command")
set objCmd.ActiveConnection = objConn

objCmd.CommandText = "select * from Market FOR XML AUTO, ELEMENTS"
objCmd.CommandType = 4

set objStream = CreateObject("ADODB.Stream")
objStream.Open
objCmd.Properties("Output Stream") = objStream
strXML = objStream.ReadText

objCmd.Execute , , 1024 'adExecuteStream

objConn.Close
Set objConn = Nothing

You don't say what the error is, so all I can do is point out the few things that look strange to me:
1. the items SERVER and DATABASE in your connection string are usually referred to as Data Source and Initial Catalog
2. CommandType 4 is "stored procedure", but you are executing a SQL string so you want CommandType 1.
3. strXML = objStream.ReadText : shouldn't this line be *after* the SQL has been executed?

Finally, this property, objCmd.Properties("XML Root") = "whatever" is useful too because you don't get a root element by default with FOR XML queries.

hth
Phil

Thanks for the advice, I changed CommandType to 1 and worked but seems that there is a problem getting over the string copy&paste with the strXML.
I have changed the way I do this with the following:


Set objConn = CreateObject("ADODB.Connection")
objConn.Open("PROVIDER=SQLOLEDB;SERVER=TestServer; DATABASE=TestDB;UID=sa;PWD=;")

Set rsTitles = CreateObject("ADODB.Recordset")
rsTitles.Open "select * from Market FOR XML AUTO, ELEMENTS", objConn

szFile = "F:\thodoris\TestFile.xml"
rsTitles.Save szFile, adPersistXML

rsTitles.Close
objConn.Close
Set objConn = Nothing


But the format of the xml file I am getting does not look at all like xml! Do you have any idea what might go wrong?!

Best Regards,
Theodore.

Yes, all your XML is being escaped because you are saving an XML string using the adPersistXML parameter.

You should either:
1. execute a "standard" , i.e. not "FOR XML", query and then use the adPersistXML method to get it into XML format in a file, OR
2. use your previous method of executing a "FOR XML" query to an ADO Stream, then use the Stream's SaveToFile method to get it into a file.

You can't mix these methods or you end up with an ugly mess.

hth
Phil

Thank Phil,
I followed the first way and the xml formatted file was created.
The code follows:

Set objConn = CreateObject("ADODB.Connection")
objConn.Open("PROVIDER=SQLOLEDB;SERVER=TestServer; DATABASE=TestDB;UID=sa;PWD=;")

Set rsTitles = CreateObject("ADODB.Recordset")
rsTitles.Open "select * from Market", objConn

szFile = "F:\thodoris\TestFile.xml"
rsTitles.Save szFile, 1 'adPersistXML


Regards,
Theodore.