Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
mendesm
mendesm
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 260
I just finished a project to eliminate all the xp_cmdshell used in our code in one of our more sensitive databases. Sensitive information was hard coded all over the place and the code was just as bad as it can get and then some (result of contractors that were hire to get 6 months of work in just 3 weeks, the funny part of this is that all of their sloppy code was replaced with just one afternoon's worth of C# coding!). We no longer allow the use of xp_cmdshell in our enterprise.

Anyway, I can contribute my code for a future article if anyone would be interested in it.

We ended up with the following CLR procs:

1. clrFileExists - checks if a file exists
2. clrFileMove - moves a file
3. clrFolderCreate - creates a folder
4. clrFolderListFiles - list files in a folder, because the development team needed a way to not check for 100+ files one by one
5. clrFtpDeleteFile - deletes a file from an FTP server
6. clrFtpGet - gets a file from an FTP server. Originally this would import the contents of the file directly from the FTP server and into a SQL table without an intermediate file, but the developers started getting all sorts of crazy ideas and I had to make them settle down and ended up coding it to download the file from the FTP server and saving it locally.
7. clrFtpListFiles - lists files in an FTP server
8. clrFtpPutFromQuery - sends the results of a query to a file in an FTP server

We also coded something extremely similar to the delimited file export ones discussed in the article.
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
mendesm (5/6/2011)
Anyway, I can contribute my code for a future article if anyone would be interested in it.


I would be interested in your article and the CLR code when you get a chance. Thanks!
Matt DeWitt-381357
Matt DeWitt-381357
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 24
While this is all kinds of fun, you could simply use SSIS and be done with it in about 2 minutes.
mchen 67060
mchen 67060
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 6
Thanks for the great example.
One thing I am also very interested in is being able to produce a vs.net report in a pdf file and sending an email from sql server. Can this be done using SQLCLR (or SQLCLR with sql server reporting service)?
volox4games
volox4games
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 26
UMG Developer (3/16/2009)
Jonathan Kehayias (3/13/2009)
The correct way to do this is to create a SQL Agent Job that runs the SSIS/DTS package, and then start that job using sp_start_job. SQLCLR is not needed for this kind of task. You can use the builtin MSDB roles to control security around this, and or impersonation to escalate priviledges as necessary.


I know about that option, but I don't like having to create a SQL Agent Job for every SSIS package just to be able to run it on the server, and the other problem is that we usually need to wait in the procedure until the SSIS package completes. I suppose we could setup a loop calling sp_help_job to find out when it finished and if it errored. Is there something I have missed, an option or something, with sp_start_job that will have it not continue the procedure until the job finishes?

Using xp_cmdshell works well for this without any extra code, but it does open up a big hole.


I agree. We have processes that need to call out to launch SSIS packages and wait for them to complete. Calling to xp_cmdshell is the easy way to syncronously launch and wait for a SSIS package. If there were a SQLCLR way to do it that avoided xp_cmdshell, then that would be great.

... And I'm not too interested in the solution that was mentioned that involves loading up every DLL under the sun so if someone has an alternative that is simpler... maybe...?
volox4games
volox4games
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 26
Because having an exectuble / code call a job that looks up a package in a table and calls a package...
a) Is like asking the guy on your left to scratch the back of the guy on on your right
b) Doesn't provide the feedback to the application regarding errors / failures of the SSIS execution
c) Requires SQL agent to be running
d) Requires the executable to poll for job completion rather than being a syncronous operation
pesche_h
pesche_h
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 134
noeld (3/4/2009)
-- Cranfield (3/3/2009)
How does the performance compare to native BCP OUT? should it be the same.

cheers

BCP should OUTPERFORM this by far.

The problem is that it is NOT as flexible and controlled as when you know what the code can or cannot do.


Sorry, a bit late, I know. I am just wondering: is it possible to start BCP using CLR instead of xp_cmdshell? That would combine the best of both worlds ...
pesche_h
pesche_h
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 134
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
marek.konecki
marek.konecki
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search