CODEWITHSUNDEEP
sql-server-2008
NiteOwls
NiteOwls

Reputation: 309

Save a SQL query result to an XML file without SSIS or xp_cmdshell

I've been searching for days on a solution for the problem I have.

I have an SQL server query that produces some output I require in XML so this is the query:

SELECT FinishCode, reason, COUNT(reason) as Numbers
  FROM [I3_Dialer].[dbo].[I3_UKPIPELINEWORKFLOW_CH0]
 WHERE callplacedtime > (select cast(convert(varchar(10), getdate(), 110) as datetime)) and reason = 'Success'
 GROUP BY reason, finishcode
 ORDER BY Numbers
 FOR XML PATH('FinishCode'), ROOT('UK_Products_Pipeline');

That output produces an XML, but I need it stored as a file and to run as a job every xx minutes.

All solutions I found sofar use BCP with xp_cmdshell or SSIS. The only thing I have found that is available on the SQL server is BCP (to my surprise) and the server has not enough resources to install SSIS, neither does the SQL admin want to enable or install additional software.

I tried to run a Job with this but the file it generates contains to much additional information that messes up the XML.

Any suggestions welcome as I'm a novice when it comes to SQL.

Upvotes: 3

Views: 9835

Answers (2)

NiteOwls
NiteOwls

Reputation: 309

After long digging I found a solution to my problem:

I created a stored procedure like this:

USE [DATABASE NAME]
GO
/****** Object:  StoredProcedure [dbo].[asp_Write_String_To_File]    Script Date:   11/21/2013 09:33:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [PROCEDUER NAME] 
  (
    @String Varchar(max), --8000 in SQL Server 2000
    @Path VARCHAR(255),
    @Filename VARCHAR(100)
  )

AS
DECLARE  @objFileSystem int,
         @objTextStream int,
         @objErrorObject int,
         @strErrorMessage Varchar(1000),
         @Command varchar(1000),
         @hr int,
         @fileAndPath varchar(80)

set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT

Select @FileAndPath=@path+'\'+@filename
    if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
    if @HR=0 execute @hr = sp_OAMethod   @objFileSystem   , 'CreateTextFile' , @objTextStream OUT, @FileAndPath,2,True

    if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='writing to the file "'+@FileAndPath+'"'
    if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Write', Null, @String

    if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
    if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'

    if @hr<>0
    begin
    Declare 
        @Source varchar(255),
        @Description Varchar(255),
        @Helpfile Varchar(255),
        @HelpID int

        EXECUTE sp_OAGetErrorInfo  @objErrorObject, @source output,@Description output,@Helpfile output,@HelpID output
        Select @strErrorMessage='Error whilst ' +coalesce(@strErrorMessage,'doing something') +', '+coalesce(@Description,'')
        raiserror (@strErrorMessage,16,1)
    end
EXECUTE  sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream

then in the query I call the stored procedure like this:

USE [DATABASE NAME]
declare @xml nvarchar(max)
declare @FilePath nvarchar(255)
declare @FileName nvarchar(255)
set @FilePath = '[Path to store file]'       -- Location to store the file
set @FileName = '[File Name to store].xml'   -- This is the XML filename

BEGIN
  set @xml =
      (
        SELECT ( -- Add This
               SELECT FinishCode, Reason, COUNT(*) AS Numbers
                 FROM [DATABASE] ch WITH (NOLOCK)
                WHERE [Condition 1]
                  AND [Condition 2]
        GROUP BY Reason, FinishCode
                ORDER BY Numbers
              FOR XML PATH('FinishCode'),
                 ROOT('UK_Products_Pipeline'),
                 TYPE
                ) -- Add This
         FOR XML PATH('Dial_Stats') -- Add This
   )

exec asp_Write_String_To_File @xml, @FilePath, @FileName

END

This then calls the stored procedure and the file is written to your

Upvotes: 3

daveBM
daveBM

Reputation: 357

You could try write a quick c# console app to do this for you then just plug it into task scheduler?

The console app will run the query and get the result and then write it to a configured file location.

Upvotes: 0

Related Questions