Export results of a Stored Procedure to a PC Folder

Question

Post reply

Export results of a Stored Procedure to a PC Folder

Oscar_Boots

Old Hand

Points: 362

April 27, 2019 at 4:14 am

#3543431

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

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

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

Thom A SSC Guru Points: 99130 More actions · Answer 1

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.
Larnu.uk

Oscar_Boots Old Hand Points: 362 More actions · Answer 2

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: 362 More actions · Answer 3

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: 99130 More actions · Answer 4

'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.
Larnu.uk

Oscar_Boots Old Hand Points: 362 More actions · Answer 5

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: 4435 More actions · Answer 6

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: 362 More actions · Answer 7

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: 18842 More actions · Answer 8

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: 6 More actions · Answer 9

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 ?