Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Agent job output query results to file size too large


SQL Server Agent job output query results to file size too large

Author
Message
sqlrd22
sqlrd22
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search