Trading in xp_cmdshell for SQLCLR (Part 2)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jonathan Kehayias » Trading in xp_cmdshell for SQLCLR (Part 2) -...

52 posts, Page 1 of 6

1 2 3 4 5 »»»

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

Rate Topic

Display Mode

Topic Options

Author

Message

Jonathan Kehayias

Posted Tuesday, March 03, 2009 12:17 AM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

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

Post #667066

darren.ramsay

Posted Tuesday, March 03, 2009 6:23 AM

Forum Newbie

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 1, Visits: 18

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?

Post #667238

gareth.bowen

Posted Tuesday, March 03, 2009 8:11 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:15 AM
Points: 15, Visits: 348

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

Post #667311

latina.washington

Posted Tuesday, March 03, 2009 8:32 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 06, 2009 8:38 AM
Points: 1, Visits: 12

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?

Post #667325

michael.langdon

Posted Tuesday, March 03, 2009 8:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 3:40 PM
Points: 117, Visits: 31

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.

Post #667346

UMG Developer

Posted Tuesday, March 03, 2009 9:35 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148

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

Post #667411

SQL_EXPAT

Posted Tuesday, March 03, 2009 9:36 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 27, 2012 9:09 PM
Points: 136, Visits: 383

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

cheers

thanks

SQL_EXPAT

Post #667412

joconnor

Posted Tuesday, March 03, 2009 11:22 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 10, 2009 10:15 AM
Points: 1, Visits: 9

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

Post #667535

tomas.skopec-1032766

Posted Wednesday, March 04, 2009 2:41 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, August 13, 2009 2:23 AM
Points: 1, Visits: 4

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

Post #667957

noeld

Posted Wednesday, March 04, 2009 8:24 AM

SSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260, Visits: 1,977

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

Post #668290

« Prev Topic | Next Topic »

52 posts, Page 1 of 6

1 2 3 4 5 »»»

Permissions