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 Tuesday, March 03, 2009 12:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:15 AM
Points: 1,708, Visits: 1,790
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
Posted Tuesday, March 03, 2009 6:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 24, 2013 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
Posted Tuesday, March 03, 2009 8:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:14 AM
Points: 15, Visits: 388
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
Posted Tuesday, March 03, 2009 8:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, March 03, 2009 8:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 16, 2013 8:08 AM
Points: 119, Visits: 37
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
Posted Tuesday, March 03, 2009 9:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:50 AM
Points: 2,163, Visits: 2,184
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
Posted Tuesday, March 03, 2009 9:36 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Tuesday, March 03, 2009 11:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, March 04, 2009 2:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, March 04, 2009 8:24 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:03 PM
Points: 6,266, Visits: 2,027
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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse