Solved

Export SQL 2005  view to xml (500 points)

Posted on 2006-11-16
4
269 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 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

773 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