How to return all of the result of a sql query or save it to a file instead

Question

I have the following sql stored procedure:

USE [FishTrackerProfessional]
GO
/****** Object:  StoredProcedure [dbo].[CreateErsSalesAddSubmission]    Script Date: 08/06/2015 15:57:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateErsSalesAddSubmission]

-- Add the parameters for the stored procedure here
@uname VARCHAR(10) ,
        @pword VARCHAR(10) ,
        @sntype VARCHAR(1) ,
        @action VARCHAR(10) ,
        @salesContractRef VARCHAR(10),

        @auctionId NCHAR(10) 

AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- Insert statements for procedure here
 SELECT
    RTRIM(@uname) AS '@uname',
    RTRIM(@pword) AS '@pword',

    (SELECT

        @snType AS '@snType',
        RTRIM(@action) AS '@action',
        COALESCE(@salesContractRef, '') AS '@salesContractRef',
        CONVERT(VARCHAR(10), DateOfPurchase, 112) AS '@saleDate',
        RTRIM(COALESCE(@auctionID, '')) AS '@auctionID',
        ISNULL(Logbook1 + ',', '') + ISNULL(Logbook2 + ',', '') + ISNULL(Logbook3 + ',', '') AS '@logBookNums',
        ISNULL(LandingDecNumber1 + ',', '') + ISNULL(LandingDecNumber2 + ',', '') + ISNULL(LandingDecNumber3 + ',', '') AS '@landingDecNums',
        COALESCE(VesselName, '') AS '@vesselName',
        RTRIM(VesselPLN) AS '@vesselPln',
        RTRIM(VesselOwner) AS '@vesselMasterOwner',
        COALESCE(CONVERT(VARCHAR(10), LandingDate1, 112), '') AS '@landingDate1',
        COALESCE(CONVERT(VARCHAR(10), LandingDate2, 112), '') AS '@landingDate2',
        COALESCE(CONVERT(VARCHAR(10), LandingDate3, 112), '') AS '@landingDate3',
        RTRIM(CountryOfLanding) AS '@countryOfLanding',
        RTRIM(PortOfLanding) AS '@portOfLanding',
        (SELECT

            COALESCE(RTRIM(SpeciesCode),'') AS '@speciesCode',
            RTRIM(FishingArea) AS '@faoAreaCode',
            COALESCE(RTRIM(IcesZone),'') AS '@ZoneCode',
            COALESCE(RTRIM(ld.DisposalCode),'') AS '@disposalCode',
            COALESCE(ld.FreshnessGrade,'') AS '@freshnessCode',
            COALESCE(ld.ProductSize,'') AS '@sizeCode',
            COALESCE(ld.PresentationCode,'') AS '@presentationCode',
            COALESCE(ld.PresentationState,'') AS '@stateCode',
            RTRIM(ld.NumberOfFish) AS '@numberOfFish',
            FORMAT(ld.Quantity, 'N2') AS '@weightKgs',
            FORMAT(Quantity * ld.UnitPrice, 'N2') AS '@value',
            COALESCE(ld.Currency,'') AS '@currencyCode',
            RTRIM(ld.WithdrawnDestinationCode) AS '@withdrawnDestinationCode',
            RTRIM(ld.BuyersRegistrationCode) AS '@buyerReg',
            RTRIM(ld.SalesContractRef) AS '@salesContractRef'

        FROM LandingDetails ld
        JOIN LandingHeaders lh
            ON ld.LandingId = lh.LandingId
        WHERE ld.LandingId = lh1.LandingId
        FOR XML PATH ('salesline'), TYPE)



    FROM LandingHeaders lh1

    WHERE lh1.AllocatedErsId IS NULL AND lh1.LandingDate1 BETWEEN '2015-05-01' AND '2015-05-30'
    ORDER BY VesselName,lh1.LandingId

    FOR XML PATH ('salesnote'), TYPE)
FOR XML PATH ('ers')
END

This is capable of returning a great deal of information when run from within ssms. However when this procedure is mapped to a function in my entitydata model returning a scalar of type string it returns but a small portion of it.

I beginning to think that ssms has been specially atuned to display the results of queries using for xml and therefore this is going to be a potential problem for me unless I can do something else.

I know that I can save the results of this sproc from the ssms result and it will all be saved to a single xml file. So would it be possible from within the sproc to direct that the results be saved to a file of a specific name (say somewhere public like the Public Documents folder) and that it should always be overwritten when the procedure is run.

Thanks

https://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/ — yazanpro, Jun 08 '15 at 15:14

score 0 · Answer 1 · edited May 23 '17 at 10:26

It is possible, but I'm not sure it'd really be a good idea. Your SQL Server user account would need additional permissions to write to the disk, you'd need to manage and maintain a method to make sure you don't inadvertently overwrite files (which SQL programming is not really designed to do as well as, say, .NET), and any consumer of your procedure would need some special knowledge (this procedure returns its resultsets somewhere else, and here are the error codes it returns when this failure happens or that failure happents, etc.).

You'd really be best off just making sure you consume your procedure properly. For EF, this question may be helpful: Entity framework calling a FOR XML stored procedure truncates at 2033 characters

But keep in mind that some other (non-EntityFramework) client might want to use that SP someday - rewriting it in a way that gets around this EF-specific limitation might leave that consumer in the dust, requiring further gymnastics that really should've just been performed by the EF client in the first place. Alternatively, just call the procedure using ADO.NET instead of the EF - it seems to me that it might be easier to do that in this case than try to work around a strange EF limitation.

How to return all of the result of a sql query or save it to a file instead

1 Answers1