Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Agent job output query results to file size too large Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 5:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 5:13 AM
Points: 61, Visits: 721
I have a complex query which I wish to run everyday (shown below)


USE GATEKEEPER 

IF OBJECT_ID('tempdb..#splitoutdata','U') IS NOT NULL DROP TABLE #SPLITOUTDATA;
CREATE TABLE #SPLITOUTDATA (
DC_INDEX_FK INT,
ROWNUM INT,
BITOFDATA VARCHAR(max));


IF OBJECT_ID(N'dbo.Split_XML', N'TF') IS NOT NULL DROP FUNCTION dbo.Split_XML
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.Split_XML
(
@Parameter VARCHAR(MAX)
,@Delimiter VARCHAR(1)
)
RETURNS @Result TABLE
(
ItemNumber INT
,ItemValue VARCHAR(MAX)
)
AS
BEGIN
DECLARE @XML XML ;
SET @Parameter = ( SELECT @Parameter
FOR XML PATH('')
) ;
SELECT @XML = '<r>' + REPLACE(@Parameter, @Delimiter, '</r><r>') + '</r>' ;

INSERT INTO @Result
(
ItemNumber
,ItemValue
)
SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL) ) AS ItemNumber
, Item.value('text()[1]', 'VARCHAR(MAX)') AS ItemValue
FROM @XML.nodes('//r') R ( Item ) ;
RETURN ;
END ;
GO
;WITH
REFORMATTEDDATA AS
(
SELECT
DC_INDEX_FK,
REPLACE(REPLACE(CAST(RAW_CLOB AS VARCHAR(MAX)),CHAR(13),''),CHAR(10),'')AS RAWCLOB2
FROM
DATAFLOW_CLOB
WHERE
CAST(RAW_CLOB AS VARCHAR(MAX)) LIKE 'AAA|S0141007|%201208__|%'
)


INSERT INTO #SPLITOUTDATA
SELECT
DC_INDEX_FK,
ROW_NUMBER()OVER(PARTITION BY DC_INDEX_FK ORDER BY split.ItemNumber) AS ROWNUM,
split.ItemValue AS BITOFDATA
FROM REFORMATTEDDATA
CROSS APPLY dbo.Split_XML(reformatteddata.RAWCLOB2,'|') SPLIT


CREATE CLUSTERED INDEX idx1 ON #SPLITOUTDATA (DC_INDEX_FK,ROWNUM)


SELECT

[Date],
RunType,
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],
[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48]

FROM
(
SELECT
DC_INDEX_FK,
(SELECT BITOFDATA FROM #SPLITOUTDATA so2 WHERE so2.DC_INDEX_FK = so1.DC_INDEX_FK and so2.ROWNUM = 12) AS [Date] ,
(SELECT BITOFDATA FROM #SPLITOUTDATA so3 WHERE so3.DC_INDEX_FK = so1.DC_INDEX_FK and so3.ROWNUM = 13) AS RunType ,
ROW_NUMBER()OVER(PARTITION BY DC_INDEX_FK ORDER BY ROWNUM) AS ROWNUM,




(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.DC_INDEX_FK = so1.DC_INDEX_FK AND so4.ROWNUM = so1.ROWNUM +14) AS BITOFDATA
FROM #SPLITOUTDATA so1
WHERE BITOFDATA = 'SPH'

AND
(SELECT BITOFDATA FROM #SPLITOUTDATA so5 WHERE so5.DC_INDEX_FK = so1.DC_INDEX_FK AND so5.ROWNUM = 10) NOT IN ('TR01')
) p
PIVOT
(MAX (BITOFDATA)
FOR ROWNUM IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48])
) AS PVT



IF OBJECT_ID('tempdb..#splitoutdata','U') IS NOT NULL DROP TABLE #SPLITOUTDATA;
IF OBJECT_ID(N'dbo.Split_XML', N'TF') IS NOT NULL DROP FUNCTION dbo.Split_XML

I wish to output the results to file automatically when it runs every day. I have tried using an SQL Agent job and using the Advanced tab in the job step properties to define an output file but using this method makes the file very large (37kb) compared to when I just right click the query results and select "Save Results as.." (3kb).

Because the file size is so large it normally takes a while to open and often I find that the query results do not even display correctly.

Can anyone offer any advice on how else I can output these results automatically? I don't have SSIS capabilities and I tried using BCP but I'm new to using BCP and it didn't seem to work for me.
Post #1373729
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse