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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-09-19 : 07:43:15
|
| Venkatachalam writes "Hi I am Venkatachalam.S. I created a store procedure which gives result in xml. My front end is VB6.0 and back end sql server 2000. I call this store procedure by passing a date parameter. The purpose is my client have 5 branches and by the end of the day all the brach transaction should get updated to the main head office. So by getting the date parameter i call this store procedure from VB 6.0 and i want the results in xml that should be saved in a file. That file will be mailed to the head office and will be updated in their database. So i didnt know how to save the result of the store procedure in a file with extension as .xml. Thank you." |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-09-19 : 10:36:50
|
| this old school VBA should point you in the general direction.Dim con As Connection Dim rs As New ADODB.Recordset Dim sql As String Set con = CurrentProject.Connection sql = "EXEC dbo.DP_PageSummaryDetailPages @productcode = '" & Me!cboDetailPages.Value & "';" rs.Open sql, con If rs.EOF Then MsgBox "Your query returned no results" Else Dim strLine As String Dim fs As Variant Dim a As Variant Dim Item As Variant Dim filename As String Dim counter As Integer counter = 0 filename = "C:\InventoryDetailReportOn" & Replace(CStr(DATE), "/", "") & "for" & Me!cboDetailPages.Value & ".xls" Set fs = CreateObject("Scripting.FileSystemObject") Set a = fs.CreateTextFile(filename, True) strLine = "" counter = 0 For Each Item In rs.Fields If counter <> 0 Then strLine = strLine & rs.Fields.Item(counter).Name & Chr(9) End If counter = counter + 1 Next Item a.WriteLine (strLine) Do Until rs.EOF strLine = "" counter = 0 For Each Item In rs.Fields If counter <> 0 Then 'If counter = 4 Or counter = 16 Then 'Or counter = 26 Or counter = 27 Or counter = 30 Then ' If Not (IsNull(rs.Fields.Item(counter).Value)) Then ' strLine = strLine & CStr(FormatPercent(rs.Fields.Item(counter).Value)) & Chr(9) ' Else ' strLine = strLine & "0" & Chr(9) ' End If 'ElseIf IsDate(rs.Fields.Item(counter).Value) Then ' strLine = strLine & CStr(DatePart("m", rs.Fields.Item(counter).Value)) & "/" & CStr(DatePart("d", rs.Fields.Item(counter).Value)) & "/" & CStr(DatePart("yyyy", rs.Fields.Item(counter).Value)) & Chr(9) 'Else strLine = strLine & rs.Fields.Item(counter).Value & Chr(9) 'End If End If counter = counter + 1 Next Item a.WriteLine (strLine) rs.MoveNext Loop a.Close Set fs = Nothing Shell ("excel.exe " & filename & "") MsgBox "An Excel file just opened on your desktop with your report and it was saved to " & filename & "." End If rs.Close con.Close Set rs = Nothing Set con = Nothing====================================================Regards,Sean Roussy"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?) |
 |
|
|
|
|
|
xml result to be stored in a file