• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

Export SQL 2005 view to xml (500 points)

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
Tiaanvanniekerk
Asked:
Tiaanvanniekerk
  • 2
  • 2
1 Solution
 
regbesCommented:
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
 
TiaanvanniekerkAuthor Commented:
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
 
TiaanvanniekerkAuthor Commented:
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
 
regbesCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now