Trading in xp_cmdshell for SQLCLR (Part 2)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jonathan Kehayias » Trading in xp_cmdshell for SQLCLR (Part 2) -...

52 posts, Page 6 of 6

«««2 3 4 5 6

Trading in xp_cmdshell for SQLCLR (Part 2) - Export Data to Flat Files

Rate Topic

Display Mode

Topic Options

Author

Message

pesche_h

Posted Saturday, June 18, 2011 1:01 PM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 9, Visits: 120

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

marek.konecki

Posted Wednesday, July 11, 2012 1:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 03, 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 »

52 posts, Page 6 of 6

«««2 3 4 5 6

Permissions