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

Jonathan Kehayias

One Orange Chip

Points: 26778
More actions
March 3, 2009 at 12:17 am

#200462

Comments posted to this topic are about the item Trading in xp_cmdshell for SQLCLR (Part 2) - Export Data to Flat Files
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[/url]

Viewing 15 posts - 1 through 15 (of 48 total)

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

darren.ramsay Valued Member Points: 59 More actions · Answer 1

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?

gareth.bowen SSC Veteran Points: 262 More actions · Answer 2

Hi,

Does the Assembly and stored procs have to be installed in each database, you wish to call the stored proc from? The example works fine, when:

SET @sqlcmd ='Select * from sys.objects'

SET @filename = 'c:\object_export.txt'

EXECUTE master.[dbo].[WriteResultsToCsvFile]

@sqlcmd

,@filename

However the information returned is from the Master database and not the database I do the call from.

Equally if I change @sqlcmd to

SET @sqlcmd ='Select * from thisdb.dbo.atable'

SET @filename = 'c:\object_export.txt'

EXECUTE master.[dbo].[WriteResultsToCsvFile]

@sqlcmd

,@filename

I do not get any output.

I must be missing the obvious, please enlighten me.

Thanks

latina.washington SSC Rookie Points: 41 More actions · Answer 3

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?

michael.langdon SSC Enthusiast Points: 163 More actions · Answer 4

A common request is to automate the moving of backup files from a server to another. Generally used Robocopy with the path etc being dynamically built.

Would be interested in seeing this as a SQLCLR solution as already known xp_cmdshell has access to lots of areas of the os that can cause damage.

UMG Developer SSChampion Points: 13482 More actions · Answer 5

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?)

SQL_EXPAT SSCrazy Points: 2708 More actions · Answer 6

How does the performance compare to native BCP OUT? should it be the same.

cheers

thanks

SQL_EXPAT

joconnor Valued Member Points: 63 More actions · Answer 7

Can this be used to start a service that is on one server and will be posting the command on a different server? I need to notify Microstrategy (on a different server) when ETL starts with the following command:

cmdmgr –n “I7 – Test (8.1.2)” -u i7admin -p 1ntegral7test -f c:\changeprojectstatus.scp

tomas.skopec-1032766 Valued Member Points: 51 More actions · Answer 8

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

noeld SSC Guru Points: 96590 More actions · Answer 9

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!!

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 10

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

Jimmy M Default port Points: 1483 More actions · Answer 11

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

umadhira Valued Member Points: 69 More actions · Answer 12

What is

"EXTERNAL NAME [SQLCLRBulkCopy].[StoredProcedures].[WriteResultsToCsvFile]"

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

Thanks

kpraseeda Grasshopper Points: 21 More actions · Answer 13

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

umadhira Valued Member Points: 69 More actions · Answer 14

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.