InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 15:48:38 GMT20http://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxDear 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 TRANsome sql selects & inserts preparing the data to export and placing in @XML variableDECLARE @ID INTINSERT TrackingTable (XML, Export) VALUES (@XML,0)SELECT @ID = SCOPE_IDENTITY()DECLARE @CMD VARCHAR(50), @Result BITSET @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 codeEND-- file is exported so update the statusUPDTE TrackingTable SET EXPORT = 1 WHERE ID = ' + CAST(@ID AS VARCHAR)COMMIT TRANAre there any disadvantages of this approach?Thanks in advance,MarekWed, 11 Jul 2012 13:49:46 GMTmarek.koneckihttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxYet 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- PescheSat, 18 Jun 2011 13:01:10 GMTpesche_hhttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]noeld (3/4/2009)[/b][hr][quote][b]-- Cranfield (3/3/2009)[/b][hr]How does the performance compare to native BCP OUT? should it be the same.cheers[/quote]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.[/quote]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 ...Sat, 18 Jun 2011 12:46:21 GMTpesche_hhttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxa. It's more secure.b. If an application needs feedback, the results and status may be logged to a table.c. In every instance I have administered, the SQL Agent is always running.d. Yes, but that should not be a problem, especially if the results and status are logged to a table.Tue, 17 May 2011 09:47:08 GMTAndrew L. Smith-213145http://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxBecause 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 rightb) Doesn't provide the feedback to the application regarding errors / failures of the SSIS executionc) Requires SQL agent to be runningd) Requires the executable to poll for job completion rather than being a syncronous operationMon, 16 May 2011 20:32:23 GMTvolox4gameshttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxWhy not create a table that has the name of the SSIS package you want to run. Retrieve the package name and start it with the SQL Agent.Fri, 13 May 2011 07:11:01 GMTAndrew L. Smith-213145http://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]UMG Developer (3/16/2009)[/b][hr][quote][b]Jonathan Kehayias (3/13/2009)[/b]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.[/quote]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.[/quote]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...?Thu, 12 May 2011 19:18:06 GMTvolox4gameshttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxThanks 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)?Wed, 11 May 2011 19:41:18 GMTmchen 67060http://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxWhile this is all kinds of fun, you could simply use SSIS and be done with it in about 2 minutes.Mon, 09 May 2011 11:34:28 GMTMatt DeWitt-381357http://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxJonathan:Frequently we encounter implementations by third party vendors that utilize xp_cmdshell for the importing of data as well as the exporting of data. Unfortunately, their developers take the "easy" way out and want sysadmin rights for their application credentials. We need to educate developers in good coding practices when accessing SQL Server. Time and time again we run into problems and vulnerabilities with these applications.Regards,Andrew SmithMon, 09 May 2011 10:25:31 GMTAndrew L. Smith-213145http://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]mendesm (5/6/2011)[/b][hr]Anyway, I can contribute my code for a future article if anyone would be interested in it.[/quote]I would be interested in your article and the CLR code when you get a chance. Thanks!Fri, 06 May 2011 13:53:30 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxI 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 exists2. clrFileMove - moves a file3. clrFolderCreate - creates a folder4. clrFolderListFiles - list files in a folder, because the development team needed a way to not check for 100+ files one by one5. clrFtpDeleteFile - deletes a file from an FTP server6. 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 server8. clrFtpPutFromQuery - sends the results of a query to a file in an FTP serverWe also coded something extremely similar to the delimited file export ones discussed in the article.Fri, 06 May 2011 13:48:50 GMTmendesmhttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxThanks -- 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).Fri, 06 May 2011 13:30:39 GMTeyespi20http://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxIf 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:).Fri, 06 May 2011 13:05:25 GMTdmbakerhttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxThis 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!MargaretFri, 06 May 2011 12:52:47 GMTeyespi20http://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]bill.windle (7/30/2009)[/b][hr]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?[/quote]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:[code] public static void WriteResultsToFile(string sqlcmd, string filename, bool includeheaders, string delimiter, bool quoteall, bool provideoutput)[/code]and then in the calls to write the files use[code]DelimitedFileWriter.WriteHeadersToStream(stream, dr, delimiter, quoteall);[/code]and[code]DelimitedFileWriter.WriteRowToStream(stream, dr, delimiter, quoteall);[/code]I think...seems like that would work, and you'd have a "generic" method that would cover any delimiter you might want.Fri, 06 May 2011 06:58:45 GMTdmbakerhttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxCLR for SSIS execution would be great.Tue, 03 May 2011 06:40:28 GMTmike 1098http://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxI'm getting an error when trying to run thisRequest 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?Thu, 15 Jul 2010 15:53:23 GMTjoshua.m.piercehttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]bob.hodgman (4/13/2010)[/b][hr]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.[/quote]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.Mon, 19 Apr 2010 22:19:26 GMTbob.hodgmanhttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxGreat 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'----------------------------------------------------------------------Mon, 19 Apr 2010 10:25:01 GMTsquidder11http://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxI 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.Tue, 13 Apr 2010 17:06:45 GMTbob.hodgmanhttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxHow 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?Thu, 30 Jul 2009 12:21:50 GMTbill.windlehttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]mark.drobnis (5/28/2009)[/b][hr]JonathanI am having a problem with trying to move the assembly to a DB other than Master.The option of creating the assembly in master is not something that we want to do.Are there any other methods that can be used to give the assembly the permissions needed without defining the LOGIN and GRANT EXTERNAL ACCESS ASSEMBLY TO CLR_Login CREATE LOGIN CLR_Login FROM ASYMMETRIC KEY CLR_Key-- Grant the External Access Priviledge to the LoginGRANT EXTERNAL ACCESS ASSEMBLY TO CLR_Login I tried defining the login in master but it can't find the assembly when it is not also in masterIf I add the assembly and the key to master I can than add the assembly to another DB. However I then need to drop the assembly from master and the CLR_key remains in the master DB this seems like a lot of work inorder to get the assemblies into other DB.[/quote]Mark, You would have to create the assembly in master, generate the key, then drop the assembly from master in order to keep from setting TRUSTWORTHY ON. A few extra steps but worth it for the security of having TRUSTWORTHY OFF. The alternative is to open the solution, and compile to a DLL. Then copy the DLL to the server file system and generate the key from the DLL. Then you can delete the DLL. Those are unfortunately the only two ways I know how to get the Key generated.Thu, 28 May 2009 08:45:55 GMTjmkehayiashttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxJonathanI am having a problem with trying to move the assembly to a DB other than Master.The option of creating the assembly in master is not something that we want to do.Are there any other methods that can be used to give the assembly the permissions needed without defining the LOGIN and GRANT EXTERNAL ACCESS ASSEMBLY TO CLR_Login CREATE LOGIN CLR_Login FROM ASYMMETRIC KEY CLR_Key-- Grant the External Access Priviledge to the LoginGRANT EXTERNAL ACCESS ASSEMBLY TO CLR_Login I tried defining the login in master but it can't find the assembly when it is not also in masterIf I add the assembly and the key to master I can than add the assembly to another DB. However I then need to drop the assembly from master and the CLR_key remains in the master DB this seems like a lot of work inorder to get the assemblies into other DB.Thu, 28 May 2009 08:19:30 GMTmark.drobnishttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]Peter Shea (4/2/2009)[/b][hr]Is it possible to run DTS packages and SSIS using SQLCLR?I currently use xp_cmdshell to do this.Thanks in advance.Peter[/quote]Yes, it is.Again, BOL/MSDN have a pretty good coverage of the subject. Of course there is a lot of typical, and sometimes contradictory, "this is how" and no why, but it worked for me: http://msdn.microsoft.com/en-us/library/aa337077(SQL.90).aspxCodePlex even has an excellent example: http://sqlsrvintegrationsrv.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=17647For SQLCLR see my previous note.Reagards,Rafael HMon, 06 Apr 2009 20:59:39 GMTRafael Hhttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxIs it possible to run DTS packages and SSIS using SQLCLR?I currently use xp_cmdshell to do this.Thanks in advance.PeterThu, 02 Apr 2009 05:23:13 GMTPeter Sheahttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]HR (3/9/2009)[/b][hr]For me the last frontier would be a similarly practical, to the point article on SSIS library integration: say create a data transfer sproc that uses SSIS Data Flow task.[/quote]It turns out that BOL has a decent coverage on SSIS programming. Good enough to just refer to it. Do make sure to also look in http://blogs.msdn.com/mattm/archive/2008/12/30/samples-for-creating-ssis-packages-programmatically.aspx.But, what i did not find explicitly addressed was using SSIS programming from within "SQLCLR". After blowing a whole bunch of time it turns out that SSIS assemblies reference a whole bunch of other assemblies, each of them of course to be loaded into SQL Server. I am working on 2005 Dev edition 32 bit (help me God on 64-s...). It is not a pretty picture, and I am not a .NET expert, so the solution was to take all of those .dll-s and place them into C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 folder. Note that the DLLs come from various sources. Here is the list, sorry, look their locations up yourselves because some depend on SQL Server installation, others - who knows what:microsoft.sqlserver.manageddtsmicrosoft.sqlserver.dtsruntimewrapsystem.windows.formssystem.drawingaccessibilitysystem.runtime.serialization.formatters.soapmicrosoft.sqlserver.msxml6_interopmicrosoft.sqlserver.sqltdiagmmicrosoft.sqlserver.pipelinehostmicrosoft.sqlserver.dtspipelinewrapmicrosoft.sqlserver.dtsmsgAnother rub is that access has to be UNSAFE, including the login that does the installing. And, finally, the microsoft.sqlserver.manageddts must be installed by a sysadmin and the DB set to TRUSTWORTHY for the ManagedDTS to get installed, this must be created separately before your assembly(ies). This might of course invalidate the whole thing unless TRUSTWORTHY and UNSAFE get miraculously ok-ed by DBAs.Good luck,RafaelSun, 22 Mar 2009 19:57:41 GMTRafael Hhttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]Jonathan Kehayias (3/13/2009)[/b]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.[/quote]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.Mon, 16 Mar 2009 10:09:36 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]latina.washington (3/3/2009)[/b][hr]I have a similar question as already asked...the code that is included, does it have to be executed within the master database? Can the assembly/key generation be done within the database that will be executing the procedure?Also, what would the solution look like to import the data back into sql server, using the text files created during the export?[/quote]The Key/Login has to be created in Master intially, from there, you can add the assembly to another database and create the user from the login there and use it locally in that database. Cross database queries are possible, but they require additional steps which can document later and post back here or as an article. I would probably use stored procedures and impersonation from within master if I needed to do multi-database exporting, or create a user common to all databases that can access the needed schemas and allow cross database permission chaining??? I'd have to play with it to be sure how it would actually work. I chose master for the example/sample because everyone has it, but the assembly/procedure is portable to other databases for local use.Fri, 13 Mar 2009 20:34:29 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]Steve Jones - Editor (3/6/2009)[/b][hr]For those of you that have asked questions of Jonathan (the author), be aware that he went to Marine Corps Boot Camp last week as part of his Reserve commitments and can't answer for a couple weeks. If you haven't gotten an answer, please feel free to send me a PM and I'll send these on to Jonathan when he returns.[/quote]Contrary to what Steve said, I am in the Army Reserves, and attending Drill Sergeant School, not boot camp, though they are somewhat similar. I did Basic Combat Training 10 years ago, and am now in school learning to be a Drill Sergeant to teach Basic Combat Training during my active duty periods each year.Fri, 13 Mar 2009 20:29:12 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]UMG Developer (3/3/2009)[/b][hr]How about a SQLCLR method to execute a DTS or SSIS package on the server instead of using xp_cmdshell and calling DTEXEC.EXE? (Or is there another way to do that in an SP that I have missed?)[/quote]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.Fri, 13 Mar 2009 20:20:08 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]jmanly (3/5/2009)[/b][hr]I currently use xp_cmdshell to help with dynamic restores, where a common backup directory (on a file server on the network) is passed to a restore stored procedure and the procedure issues a "DIR \\server\share\dbname\*.BAK" to retrieve all available backup files in order to find the latest one (assuming a live to dev restore that wouldn't access msdb backup history).Actually, replacing xp_cmdshell is not only a security gain, in fact it would give me much finer control. In order to find the latest backup file i just run a max on the filename, in the assumption that the backup file was created by a process that includes the date and time in the backup file name, e.g. maintenance plans. Using a .net CLR i could presumably retrieve the backup file's date by accessing the file attributes.It would also help my restore/backup verification processes if i could verify that the files have been archived, which again a CLR proc could do by looking at the file's Archived flag in its attributes.I have had a try at this in the past but gave up when i hit .net security issues accessing the file on the file server! (The backups are not written locally). If you want to have a stab at this one i would be very interested to see your results. Almost all of my xp_cmdshell usage is for finding out about backup files on a remote file server where the backups are written.(Admittedly .Net CLRs would allow me to interrogate full msdb backup history across the estate without the need for linked servers, and without passing hard-coded backup directories, but there would be times when i would want to specify the backup location as opposed to get it from msdb, and as i say it is really helpful to get other file attributes).Very useful examples so far.Thanks.James[/quote]James,If you look at the first article, you can use that assembly and TVF to get a list of directory contents with the dates. As long as the SQL Server Service Account has ACL rights to the network share, it will allow you to retrieve its contents. This would then allow you to build the operation that you are after. I use that TVF to read network share paths from SQL Server without problems, but you have to have the Service Account setup with access to the share.Fri, 13 Mar 2009 20:18:09 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]darren.ramsay (3/3/2009)[/b][hr]Thanks for the great example.One thing I am 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?[/quote]Is it possible? Yes and No. It really depends on how you tried to do it, but usually you run into problems with unsupported and blocked assembly loading in SQLCLR for safety reasons. You should use Reporting Services and Subscription Services in RS to do this. It creates a SQL Agent Job which you can then fire off using sp_start_job from TSQL if needed. There are a few articles on the web that can be found for how to do this.Fri, 13 Mar 2009 20:11:07 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]noeld (3/4/2009)[/b][hr][quote][b]-- Cranfield (3/3/2009)[/b][hr]How does the performance compare to native BCP OUT? should it be the same.cheers[/quote]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.[/quote]Do you have performance benchmarks or metrics that support this? If you look at the background operations in SQL Trace for this, it is using BULK INSERT. Any performance difference is minimal at best. The SqlBulkCopy object is very efficient for bulk loading data.Fri, 13 Mar 2009 20:09:13 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspx[quote][b]noeld (3/4/2009)[/b][hr][quote][b]tomas.skopec (3/4/2009)[/b][hr]Thanks for great article.I would like to ask why are you putting source files into SQL assembly? I tought that compliled dll would be enought.Thanks in advance,Tomas[/quote]For Debugging!![/quote]It isn't for debugging at all. There is a DMV in SQL called sys.assembly_files which holds the source text so you can view the source easily from TSQL if you need to without the original source solution in Visual Studio. One of the biggest complaints I have seen from DBA's in speaking on SQLCLR and the forums is that they don't know what the compiled assembly does. If you add the files, they can always view the source immediately from TSQL by querying the DMV.Fri, 13 Mar 2009 20:06:20 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxThank you for the superb series!Almost nowhere can one find a comprehensive coverage of CLR deployment intricacies, most places and the only 2 dedicated books on CLR integration (shamelessly) suggest the TRUSTWORTHY thing which would never fly with DBAs. I just created substitutes for DOS file commands in CLR because xp_cmdshell was yanked out, and found these articles more useful than the Rational Guide - they got me started and helped in making it all happen and deployed into production.For me the last frontier would be a similarly practical, to the point article on SSIS library integration: say create a data transfer sproc that uses SSIS Data Flow task.Mon, 09 Mar 2009 17:37:40 GMTRafael Hhttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxFor those of you that have asked questions of Jonathan (the author), be aware that he went to Marine Corps Boot Camp last week as part of his Reserve commitments and can't answer for a couple weeks. If you haven't gotten an answer, please feel free to send me a PM and I'll send these on to Jonathan when he returns.Fri, 06 Mar 2009 09:37:37 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxI think you need to copy the code given at the bottom under resources, and execute it from your SSMS.Then runEXECUTE [dbo].[WriteResultsToCsvFile] @sqlcmd = 'select * from sys.objects' ,@filename = 'c:\object_export.txt'You might get error that 'clr enabled' is disable and enable it. If you get that error then run these commandsEXEC sp_configure 'show advanced options', 1GORECONFIGUREGOEXEC sp_configure 'clr enabled', 1GORECONFIGUREGOThen run the above execute command to export the data into a file or you can change that select statement to what ever you want to export into a file. I am not sure what this is doing in his script"EXEC sys.sp_addextendedproperty" with his(Jonathan) user name but it is working for me. GREAT WORK.Fri, 06 Mar 2009 08:38:01 GMTumadhirahttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxWhen i executed the code createProcedure WriteResultsToCsvFile, I got the following error.Msg 6528, Level 16, State 1, Procedure WriteResultsToCsvFile, Line 1Assembly 'SQLCLRBulkCopy' was not found in the SQL catalog of database 'DBName'.Fri, 06 Mar 2009 07:14:34 GMTkpraseedahttp://www.sqlservercentral.com/Forums/Topic667066-1365-1.aspxWhat is "EXTERNAL NAME [SQLCLRBulkCopy].[StoredProcedures].[WriteResultsToCsvFile]"Can I create the procedure? And execute? Do I need to do any thing else?ThanksThu, 05 Mar 2009 15:17:46 GMTumadhira