Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Export SQL 2005  view to xml (500 points)

Posted on 2006-11-16
4
Medium Priority
?
307 Views
Last Modified: 2012-05-05
HI
I wrote this code as a dts package on 2000
can some one please assist me to convert this to a storedproc so I acn use this in sql 2005 as a job?

Function Main()
  Dim objADOStream
  Dim objADOCmd

  Const adExecuteStream = 1024

  Set objADOCmd = CreateObject("ADODB.Command")

  Set objADOStream = CreateObject("ADODB.Stream")
  objADOStream.Type = 2 'Text
  objADOStream.Open

  objADOCmd.ActiveConnection = "PROVIDER=SQLOLEDB.1;SERVER=TIAAN\HEAT;UID=sa;PWD=sa;DATABASE=NTCIPSERVERDB;"
  objADOCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

  objADOCmd.CommandText = "<Incidents xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
    "<sql:query>" & _
        "SELECT IncidentID,IncidentDisposition,IncidentDuration,IncidentDateTimeStamp,GISCoordX,GISCoordY,LocationDescription,IncidentStatus,IncidentType,IncidentStatusID,IncidentImpact  from incidents FOR XML AUTO, BINARY BASE64 " & _
    "</sql:query>" & _
    "</Incidents>"
 
  objADOCmd.Properties("Output Stream") = objADOStream

  objADOCmd.Execute , , adExecuteStream

  objADOStream.SaveToFile "c:\temp\incidents.xml"
   
  objADOStream.Close
  Set objADOStream = Nothing

  Main = DTSTaskExecResult_Success
End Function
0
Comment
Question by:Tiaanvanniekerk
  • 2
  • 2
4 Comments
 
LVL 11

Expert Comment

by:regbes
ID: 17963276
Hi Tiaanvanniekerk,

Unless you have/want to re write the package simply inport it into 2005 and schedule it via the agent

HTH

R.
0
 

Author Comment

by:Tiaanvanniekerk
ID: 17963372
I tried but it keeps on crashing,thats why i thought of may looking for assistance to create a storedproc and schedule the stored proc to save the file to a specified path

I am currently busy with:

SELECT [IncidentID]
      ,[IncidentDisposition]
      ,[IncidentDuration]
      ,[IncidentDateTimeStamp]
      ,[GISCoordX]
      ,[GISCoordY]
      ,[LocationDescription]
      ,[IncidentStatus]
      ,[IncidentType]
      ,[IncidentStatusID]
      ,[IncidentImpact]
  FROM [NTCIPSERVERDB].[dbo].[incidentsview]

            ORDER BY [IncidentID]

            FOR XML auto, xmldata ,BINARY BASE64


Now only to put this in a create stored proc code and specify the path

Any ideas?
0
 

Author Comment

by:Tiaanvanniekerk
ID: 17963508
if this is my proc

USE [NTCIPSERVERDB]
GO

CREATE PROCEDURE sp_SANRAL_INCIDENTS
AS

SELECT [IncidentID]
      ,[IncidentDisposition]
      ,[IncidentDuration]
      ,[IncidentDateTimeStamp]
      ,[GISCoordX]
      ,[GISCoordY]
      ,[LocationDescription]
      ,[IncidentStatus]
      ,[IncidentType]
      ,[IncidentStatusID]
      ,[IncidentImpact]
  FROM [NTCIPSERVERDB].[dbo].[incidentsview]

            ORDER BY [IncidentID]

            FOR XML auto, xmldata ,BINARY BASE64
GO


How can I save this file to a directory?

 


0
 
LVL 11

Accepted Solution

by:
regbes earned 1500 total points
ID: 17963537
Tiaanvanniekerk,

if you are not going to use DTS or SSIs tho only (ugly) way i know of is this

exec xp_cmdshell 'osql -Q "your query" -O"Yourfile.txt"'
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question