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

Question

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

Viewing 15 posts - 16 through 30 (of 48 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

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.

Rafael H SSC Veteran Points: 273 More actions · Answer 2

Thank 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.

Kindest Regards,

R

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

noeld (3/4/2009)
tomas.skopec (3/4/2009)
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
For Debugging!!

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.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

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.

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.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

darren.ramsay (3/3/2009)
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?

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.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

jmanly (3/5/2009)
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

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.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

UMG Developer (3/3/2009)
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?)

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.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

Steve Jones - Editor (3/6/2009)
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.

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.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

latina.washington (3/3/2009)
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?

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.

UMG Developer SSChampion Points: 13482 More actions · Answer 10

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.

Rafael H SSC Veteran Points: 273 More actions · Answer 11

HR (3/9/2009)
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.

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.manageddts

microsoft.sqlserver.dtsruntimewrap

system.windows.forms

system.drawing

accessibility

system.runtime.serialization.formatters.soap

microsoft.sqlserver.msxml6_interop

microsoft.sqlserver.sqltdiagm

microsoft.sqlserver.pipelinehost

microsoft.sqlserver.dtspipelinewrap

microsoft.sqlserver.dtsmsg

Another 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,

Rafael

Kindest Regards,

R

Peter Shea SSC Journeyman Points: 75 More actions · Answer 12

Is it possible to run DTS packages and SSIS using SQLCLR?

I currently use xp_cmdshell to do this.

Thanks in advance.

Peter

Rafael H SSC Veteran Points: 273 More actions · Answer 13

Peter Shea (4/2/2009)
Is it possible to run DTS packages and SSIS using SQLCLR?
I currently use xp_cmdshell to do this.
Thanks in advance.
Peter

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).aspx

CodePlex even has an excellent example: http://sqlsrvintegrationsrv.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=17647

For SQLCLR see my previous note.

Reagards,

Rafael H

Kindest Regards,

R

mark.drobnis Valued Member Points: 59 More actions · Answer 14

Jonathan

I 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 Login

GRANT 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 master

If 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.

jmkehayias SSC Rookie Points: 33 More actions · Answer 15

mark.drobnis (5/28/2009)
Jonathan
I 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 Login
GRANT 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 master
If 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.

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.