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

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    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]

  • darren.ramsay

    Valued Member

    Points: 59

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

    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

    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

    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

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

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

    cheers

    thanks

    SQL_EXPAT

  • joconnor

    Valued Member

    Points: 63

    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

    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

    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

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

    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

    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

    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

    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.

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

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