Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  See if you can figure this one out!

Author  Topic  sonic Starting Member 11 Posts Posted - 2001-03-01 : 18:22:35 Hey all. Thanks for reading this! Here the deal. I am running SQL 7, not 2000, or this would be easier. I want a stored procedure to fire by trigger or sced job that outputs a resultset to an XML file path that I provide. The only way (I believe) for SQL 7 to export in XML format is to pump the result set thru the ADO Stream or Recordset object (with the adPersistXML format) and then invoke the save method to my file name. Heres the catch. Since the stored proc is already up, firing, and returning my result set, I want IT to invoke the ADO object(s) and save the file using the following procedures: EXEC @hr = sp_OACreate 'ADODB.Recordset', @ObjectID EXEC @hr = sp_OAMethod @ObjectID, 'Open', 'SELECT * FROM Authors' EXEC @hr = spOAMethod @ObjectID, 'Save', @xmlfilePath, 1[adPersistXML] EXEC @hr = spOADestroy @ObjectID The problem lies in the 'SELECT * FROM Authors' statement. I dont want ADO to try and execute it (requiring a Connection object, et.) because this result set will have already been returned by parent stored procedure. Is there a way to pass the result set into ADO and then invoke the save method. The way its coded now wont work, because the ADO Recordset needs an active connection to the database, and it will just try to execute the T-SQL statement itself anyways.. hmmm.. I wonder about the Stream object? I would like to avoid having a stored procedure call up a dll i created that then needed its own connection to the database to get this done. Anyones thoughts would be great. Thanks for your help!! Sincerely, Jason C. Cannelos jcannelos@tidemark.com J'son