Can SQL server 2000 Write an XML file to an IIS server? 1

[Stunner01225]

Can SQL server 2000 Write an XML file to an IIS server?
I have a vast amount of data that can change quite often, but everyone needs access to it. I can get XML output from SQL server and I can write the file with asp.net. The problem im having is detecting when the data has changed so that the XML has to be re written. I want to do this on a SQL server trigger. But any other suggestions are welcome.
Thank you

 

[NoCoolHandle]

in SQL2005 you could write a vb or c# trigger....

no seriously... how about a webtask, or writing a trigger that does build an xml file.. maybe using xp_cmdshell and osql with an output parameter...


another option might be a trigger to dts task.

Just random thoughts

Rob

 

[JohnDTampaBay]

Thought about using BCP?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[obislavu]

Here is a solution:
Assume your xML data file is retrieved from table1, table2, table3 etc...using some process, let name it CreateXML.
It could be a stored procedure, a .EXE or a DLL.
1- Define a table, let say _tEventXML table in which you have:
dtlastXMLcreated, dtlastmodified
2- When the XML is created by that process update back the dtlastXMLcreated to the datetime of XML extraction.
3- Define a trigger on each table1, table2 , table3 ... when data that you are looking get updated
Let say, Trg_table1_Modified which will be executed when table1 is modified and this trigger will update the _tEventXML.dtlastmodified with datetime of modification.
4- Create a stored procedure , sp_checkXML which will read _tEventXML and if _tEventXML.dtlastXMLcreated < _tEventXML.dtlastmodified
then call the process to create the XML file else return.
5-Use Management->SQL Server Agent->Jobs to create a job and schedule it to call the sp_checkXML at a given interval, let say every 15 minutes.
Use Job Proporties->Notifications to for log or failure notifications.
Now, how will work ?
At every 15 minutes the scheduled job call sp_checkXML procedure which read _tEventXML table and call the process that creates the XML file if the dtlastmodified is later then the _tEventXML.dtlastXMLcreated.
-obislavu-

 

[Stunner01225]

Thank you for your posts.
But..
What is BCP ?
and
Can DTS write XML files?
Thanks

 

[NoCoolHandle]

BCP = bulk copy program....
it is a console (command prompt) application that takes arguements. It can be used to extract data from or insert data into a database.

Switches can be a pain to get correct, but once you do, you won't need to again. (it could create an XML file via the "For XML" clause)

And Yes(DTS).. specify an output file and then the query uses the for XML clause or you build your own xml output manually..

 

[Stunner01225]

I have used DTS a few times , but mainly for transfering data between sources.

How do I specify an XML file for output?

Thanks

 

[NoCoolHandle]

Have you ever used the "For XLM ..." Clause in a sql statement?

If not, try issueing a "Select * from northwind.dbo.employees for xml auto"

At that point you have to specify a datasource (your server and database), specify the query in a "data transform task", then specify a data source destination of a "text file (destination)"