Export results of a Stored Procedure to a PC Folder

  • Oscar_Boots

    Old Hand

    Points: 313

    Hi Forum,

    I have a Stored Procedure that selects one record from a table.

    I want to select one row at a time (maybe using a cursor?) and paste into a Folder on my PC's 'C' Drive.

    I have below the code to create the Stored Proc & then the script to run it.

    This was given to me & I'm not really sure if this is the best way to go about doing what I need.

    All suggestions welcome.

    --SELECT *
    --FROM [AdventureWorks2008R2].[Production].[Document]

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE dbo.WriteBlob
    @Document varbinary(max),
    @filepath nvarchar(512),
    @append_boolean bit = 0
    AS
    BEGIN
    SET NOCOUNT ON;

    select * from Production.Document WHERE DocumentNode = 0x58 -- I have added a value just to get a result - I need to use a variable that changes.

    END
    GO

    Here's the code that exports a record.

    USE AdventureWorks2008R2
    GO

    DECLARE @ID int
    DECLARE @Document varbinary(max) = (select [Document] from Production.Document WHERE DocumentNode = 0x58) --@ID)
    DECLARE @Title nvarchar(512) = (select [Title] from Production.Document WHERE DocumentNode = 0x58) --ID = @ID)
    DECLARE @FileExtension nvarchar(128) = (select [FileExtension] from Production.Document WHERE DocumentNode = 0x58) --ID = @ID)
    DECLARE @filepath nvarchar(512) = N'C:\Users\Desktop\SQLOutput\' + @FileExtension + '\' + @Title
    DECLARE @append_boolean bit = 0

    EXECUTE [dbo].[WriteBlob]
    @Document
    ,@filepath
    ,@append_boolean

     

  • Thom A

    SSC Guru

    Points: 98627

    This seems like a task better suited to something like SSIS, rather than a Stored Procedure. C:\ is also the disc on your SQL Server host, not on your PC (I assume it's a remote SQL Instance). It's very unlikely that the service account running SQL Server on the other host has access to your disc so you would be better saving the files to a path is does on the network. You'll need to use the UNC path too: so something like \\MyFileServer\MyShare\Oscars Exports\...

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Oscar_Boots

    Old Hand

    Points: 313

    Thanks Thom A,

    The use of 'C' is just for this purpose, it's actually on a Server.

    Appreciate your advice, can you help with the Proc script though?

    Thanks

  • Oscar_Boots

    Old Hand

    Points: 313

    Ok, I've changed this to a script using a cursor.

    What can I do to get this Adventureworks table to save into nominated folder? (I'm using a folder on my PC just to get this to a forum so I can get the syntax right).

    Thanks

    DECLARE @name NVARCHAR(50) -- table name 
    DECLARE @path NVARCHAR(256) -- path for backup files
    DECLARE @fileName NVARCHAR(256) -- filename for backup
    DECLARE @id as NVARCHAR(10) -- to use to iterate through the records

    SET @path = 'C:\Users\Desktop\SQLOutput'
    SET @id = '0x58' -- Just using an actual record to get this to work??

    DECLARE db_cursor CURSOR FOR
    SELECT Document
    FROM Production.Document
    WHERE DocumentNode = @id

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @name

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @name = @path + @filename + '.eml'
    BACKUP DATABASE @name TO DISK = @fileName

    FETCH NEXT FROM db_cursor INTO @name
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor

    The error I am getting is below;

    Msg 6522, Level 16, State 2, Line 15
    A .NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid":
    Microsoft.SqlServer.Types.HierarchyIdException: 24001: SqlHierarchyId.Parse failed because the input string '0x58' is not a valid string representation of a SqlHierarchyId node.
    Microsoft.SqlServer.Types.HierarchyIdException:
    at Microsoft.SqlServer.Types.SqlHierarchyId.Parse(SqlString input)

     

     

  • Thom A

    SSC Guru

    Points: 98627

    '0x58' and 0x58 aren't the same; one is a string representation of a binary value, and one is a binary value. If you run SELECT CONVERT(varbinary,'0x58'); you don't get 0x58 you get  0x30783538. If your ID column is really a (var)binary then declare your parameter/variable as a (var)binary.;Ootherwise a clause like WHERE ID = @ID is going to implicitly convert the value of IDto a varchar (and 0x58 as a varchar is 'X') and then not match your input parameter.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Oscar_Boots

    Old Hand

    Points: 313

    Thanks but all I want to do is use a Cursor to copy a table row by row to a folder on a server drive.

    Can you help with that?

    Thanks

  • Ross McMicken

    SSCarpal Tunnel

    Points: 4373

    You probably can't do that directly from a stored procedure in a safe and secure way. You can do it from sqlcmd or powershell. See options here https://www.sqlservercentral.com/articles/8-ways-to-export-sql-results-to-a-text-file

    Here's a reference to using the OA stored procs, but I would avoid those as buggy https://www.red-gate.com/simple-talk/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

    You could also use integration services, assuming you want to schedule this and run it periodically. Use integration services to take the query results and write to a file, then execute the backup. You may be able to avoid a SP in that case bu using intergration services to iterate through your list of records.

  • Oscar_Boots

    Old Hand

    Points: 313

    Thanks, I've seen all those but I can';t find one that I can use & get it to work.

    Forget it, thanks

  • Ken McKelvey

    SSCoach

    Points: 18291

    It looks as though you want to export blobs. I would do this quickly with powershell, something like:

    https://andyspecht.github.io/2017-06-23-extracting-images/

     

  • ETL

    Newbie

    Points: 1

    Its good explaining and it is working for me in my local but when i am connected to server and i execute this procedure over there how can i get file location to be my local or any other server location ?

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply