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 «««23456

Trading in xp_cmdshell for SQLCLR (Part 2) - Export Data to Flat Files Expand / Collapse
Author
Message
Posted Saturday, June 18, 2011 1:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 2:13 AM
Points: 9, Visits: 132
Yet another late reply:

We mostly use xp_cmdshell + BCP in combination with format files. I'd be very interested in seeing a CLR assembly that would be able to read a format file and export the data accordingly.

(of course, if you can actually execute BCP from CLR, this problem is automatically solved).

Thank you for considering this!

Kind regards
- Pesche
Post #1127787
Posted Wednesday, July 11, 2012 1:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 3, 2012 11:09 AM
Points: 1, Visits: 8
Dear Jonathan,
Can I run this SP (a bit changed to create XML file and return 0 if file is exorted w/o errors or 1 if not) in my transaction to check if the file was successfully exported and update Export flag if yes? The code would look like this:

BEGIN TRAN

some sql selects & inserts preparing the data to export and placing in @XML variable

DECLARE @ID INT
INSERT TrackingTable (XML, Export) VALUES (@XML,0)
SELECT @ID = SCOPE_IDENTITY()

DECLARE @CMD VARCHAR(50), @Result BIT
SET @CMD = 'SELECT XML FROM TrackingTable WHERE ID = ' + CAST(@ID AS VARCHAR)
EXECUTE @Result = [dbo].[WriteResultsToCsvFile] @CMD , 'SomeFileName.xml'

IF(@Result = 1)
BEGIN
ROLLBACK TRAN
RETURN 1 -- error code
END

-- file is exported so update the status
UPDTE TrackingTable SET EXPORT = 1 WHERE ID = ' + CAST(@ID AS VARCHAR)

COMMIT TRAN

Are there any disadvantages of this approach?

Thanks in advance,
Marek
Post #1328548
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse