July 30, 2009 at 12:21 pm
How would you create the SP for using DelimitedFileWriter and choosing pipes | ?
I see where in the Example files where you create the different procedures but if I choose to create one on Delimitedfilewriter @value=N'DelimitedFileWriter.vb' how would I specify the delimiter?
April 13, 2010 at 5:06 pm
I could believe that performance might be about the same for the BULK INSERT part of the routine. Of course, you use BULK INSERT without using xp_cmdShell anyway.
The original question was about the performance of the SQLCLR assembly compared to the BCP OUT and I remain interested in hearing about anyone's experience there.
April 19, 2010 at 10:25 am
Great article and examples.
How would I get the provideoutput code (see below) to write to a database table rather than printing to screen? I'm using the VB.NET version, but would happily convert C# code if anyone has any ideas or pointers of how to do this?
'----------------------------------------------------------------------
Catch ex As Exception
' Catch block to handle Exceptions Raise
[error] = ex
'SqlContext.Pipe.Send(ex.Message);
Finally
' End code to provide output including error messages if needed.
If provideoutput Then
Dim sdr As New SqlDataRecord(New SqlMetaData("RowsWritten", SqlDbType.Int), New SqlMetaData("FileName", SqlDbType.NVarChar, 480), New SqlMetaData("Errors", SqlDbType.NVarChar, 4000))
sdr.SetValue(0, rowcount)
sdr.SetValue(1, filename)
sdr.SetValue(2, [error].Message)
SqlContext.Pipe.Send(sdr)
End If
End Try
'----------------------------------------------------------------------
April 19, 2010 at 10:19 pm
bob.hodgman (4/13/2010)
I could believe that performance might be about the same for the BULK INSERT part of the routine. Of course, you use BULK INSERT without using xp_cmdShell anyway.The original question was about the performance of the SQLCLR assembly compared to the BCP OUT and I remain interested in hearing about anyone's experience there.
A co-worker did a test and found that the BCP out portion of the SQL CLR routine was around 3-4 times slower than the BCP out with the xp_cmdshell.
July 15, 2010 at 3:53 pm
I'm getting an error when trying to run this
Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken='xxxxxxxxxxxxxx'' failed.
I replaced the public key with x's as a heads up.
Can anyone give me any insight as to whythis may be happening?
May 3, 2011 at 6:40 am
CLR for SSIS execution would be great.
May 6, 2011 at 6:58 am
bill.windle (7/30/2009)
How would you create the SP for using DelimitedFileWriter and choosing pipes | ?I see where in the Example files where you create the different procedures but if I choose to create one on Delimitedfilewriter @value=N'DelimitedFileWriter.vb' how would I specify the delimiter?
Looks to me like WriteResultsToCsvFile() and WriteResultsToTabFile() are identical, save for the delimiter they use in the call to DelimitedFileWriter.WriteHeadersToStream() and Delimited.WriteRowToStream().
If you were to take a copy of one of the Write* methods, add another parameter for the delimiter, and then use the value of that parameter in the call to DelimitedfileWriter.WriteHeadersToStream() and .WriteRowToStream(), I think you'd be covered for any delimiter you want.
So declare the method thus:
public static void WriteResultsToFile(string sqlcmd, string filename, bool includeheaders, string delimiter, bool quoteall, bool provideoutput)
and then in the calls to write the files use
DelimitedFileWriter.WriteHeadersToStream(stream, dr, delimiter, quoteall);
and
DelimitedFileWriter.WriteRowToStream(stream, dr, delimiter, quoteall);
I think...seems like that would work, and you'd have a "generic" method that would cover any delimiter you might want.
May 6, 2011 at 12:52 pm
This is very interesting to me. Could this process also be used to write to .xls file? I have a 64 bit system and using SSIS to write to excel is proving troublesome because of the lack of 64 bit excel drivers. I'm wondering if using a CLR would get around that somehow.
It seems like you could "trick" excel by naming the file with .xls even though you are producing a tab delimited or comma delimited file.
Or am I dreaming??
Thanks!
Margaret
May 6, 2011 at 1:05 pm
If you make the file a comma-delimited file and give it a ".CSV" extension then Excel should be able to open it up just fine. Just make sure you quote-delimit any text entries if you have any text that contains embedded commas or you'll get unexpected "extra" columns.
If you want a native XLS then there are third-party .NET libraries that can help there. You could also automate Excel on the server to write an Excel file but I strongly recommend against doing that...Office doesn't really play well in that scenario (been burned by that before :sick:).
May 6, 2011 at 1:30 pm
Thanks -- I know excel can open it just fine, but it isn't the same as it actually BEING an excel file (at least to my users who are looking specifically for excel files). Also I believe if I have the file formatted a certain way then export the data into it, the formatting is retained in excel, but not in a csv file. (I could be wrong here as I so often am).
May 6, 2011 at 1:48 pm
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.
May 6, 2011 at 1:53 pm
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!
May 9, 2011 at 11:34 am
While this is all kinds of fun, you could simply use SSIS and be done with it in about 2 minutes.
May 11, 2011 at 7:41 pm
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)?
May 12, 2011 at 7:18 pm
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...?
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply