write result set

bmains · March 8th, 2004, 09:15 AM

Hello,

Is there a stored procedure in SQL Server that will write a result set returned from a select statement and write it to a file on a server?

Thanks,

Brian Mains

pgtips · March 8th, 2004, 09:27 AM

Do you want it in text format? Do you have a text driver installed (e.g Microsoft Jet)? If so you can use OPENROWSET or OPENDATASOURCE to insert the results into a text file.

bmains · March 8th, 2004, 09:35 AM

Yes, I want it in a text format. Thanks.

bmains · March 8th, 2004, 10:02 AM

Do I use an insert statement to get the data in there, such as:

Code:

insert into OpenDataSource('Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\mydoc.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')
...Select statement...

This isn't my example, it is one that I've found. Is that correct?

Thanks.

pgtips · March 8th, 2004, 10:43 AM

Yes that's the idea. Here's the syntax you'll need for a text file (this statement inserts three fields from the Northwind categories table into a text file c:\temp\new.txt):

Code:

INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'Text;Database=c:\Temp',new#txt)
SELECT CategoryID, CategoryName, [Description]
FROM Categories

One problem I've come across while experimenting with this is that the text file must exist first

I tried a SELECT INTO query to create the text file, like this:

Code:

SELECT CategoryID, CategoryName, [Description]
INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'Text;Database=c:\Temp',new#txt)
FROM Categories

but it doesn't like the OPENROWSET there...

Anyone else have any ideas about how to create the text file as well?

Maybe DTS would be a better option?

bmains · March 8th, 2004, 10:50 AM

I've looked into it; actually, I should have stated that I'm using the FOR XML EXPLICIT option to return an XML result set. That is what I'm trying to write to a file out on a server. I have the SQL Statement to output the XML in a stored procedure.

joefawcett · March 8th, 2004, 11:20 AM

Quote:

quote:Originally posted by bmains
I've looked into it; actually, I should have stated that I'm using the FOR XML EXPLICIT option to return an XML result set. That is what I'm trying to write to a file out on a server. I have the SQL Statement to output the XML in a stored procedure.

In that case you maybe better off creating a DomDocument using one of the OLE stored procedures and saving the xml that way. OpenRowset only deals with tabular results, not xml structured ones.

--

Joe

pgtips · March 8th, 2004, 11:24 AM

Your options are even more limited then, since you can't use FOR XML in an INSERT statement.

Maybe better to get the XML into an ADO Stream and use the Stream's SaveToFile() method to save it off to disk?

bmains · March 8th, 2004, 01:29 PM

Could the OpenXML function be used in conjunction with an insert statement?

bmains · March 8th, 2004, 03:04 PM

I created this script to be used in a DTS package:

Code:

set objConn = CreateObject("ADODB.Connection")
objConn.Open(m_strConnectionString)

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

objCmd.CommandText = "xmlSelectData"
objCmd.CommandType = 4

set objStream = CreateObject("ADODB.Stream")
objStream.Open

objCmd.Properties("Output Stream") = objStream
'adPersistXML
objCmd.Execute , , 1

strXML = objStream.ReadText

'... Close objects

But the file contains no XML when written out to the file. Is this correct?