Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Trading in xp_cmdshell for SQLCLR (Part 2) - Export Data to Flat Files Expand / Collapse
Author
Message
Posted Wednesday, March 4, 2009 8:27 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
-- 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.




* Noel
Post #668300
Posted Thursday, March 5, 2009 8:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 3:45 AM
Points: 41, Visits: 459
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


Post #669303
Posted Thursday, March 5, 2009 3:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 1, 2009 8:28 AM
Points: 3, Visits: 31
What is
"EXTERNAL NAME [SQLCLRBulkCopy].[StoredProcedures].[WriteResultsToCsvFile]"

Can I create the procedure? And execute? Do I need to do any thing else?

Thanks
Post #669750
Posted Friday, March 6, 2009 7:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 15, 2009 3:26 AM
Points: 1, Visits: 10
When i executed the code createProcedure WriteResultsToCsvFile, I got the following error.

Msg 6528, Level 16, State 1, Procedure WriteResultsToCsvFile, Line 1
Assembly 'SQLCLRBulkCopy' was not found in the SQL catalog of database 'DBName'.

Post #670187
Posted Friday, March 6, 2009 8:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 1, 2009 8:28 AM
Points: 3, Visits: 31
I think you need to copy the code given at the bottom under resources, and execute it from your SSMS.
Then run
EXECUTE [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 commands
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

Then 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.
Post #670295
Posted Friday, March 6, 2009 9:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #670384
Posted Monday, March 9, 2009 5:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 11, 2014 4:08 PM
Points: 12, Visits: 129
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
Post #672063
Posted Friday, March 13, 2009 8:06 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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 | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #675749
Posted Friday, March 13, 2009 8:09 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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 | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #675751
Posted Friday, March 13, 2009 8:11 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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 | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #675752
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse