Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Export SQL 2005  view to xml (500 points)

Posted on 2006-11-16
4
Medium Priority
?
303 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

885 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