Solved

Export SQL 2005  view to xml (500 points)

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.

828 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