SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


File Handling A Different Way


File Handling A Different Way

Author
Message
rsconnolly
rsconnolly
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 81
I cannot read the sample code on the form. All I see is a horizontal scroll bar.
Anipaul
Anipaul
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11837 Visits: 1407
Hi Shashank

I felt something missing in the article. Overall the article seems incomplete. The process may not the best but you tried something different. Smile



JMI
JMI
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4589 Visits: 775
Does it work for everybodey?
When I run it form QA it keeps creating an empty file.

exec master..xp_cmdshell 'echo "XXX-XXX" >> c:\code\QueryResults.txt'

Thanks,
Rob



Phil Factor
Phil Factor
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6835 Visits: 3050
There are a lot of uses for this technique. Here is an example from Nigel Rivett for doing FTP in a stored procedure
http://www.nigelrivett.net/FTP/s_ftp_GetFile.html
he also has a clever way of writing format files for BCP in the same way. Naturally, one wouldn't give the normal application user any access to this!.


Best wishes,

Phil Factor
Simple Talk
Your Name Here
Your Name Here
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 843
I have to agree with Phil. "New" is kind of misleading since any DBAs worth their paycheck knew about using xp_cmdshell about 15 minutes after it was introduced back around SQL 4.21 .

Given the very narrow constraint of reserving this file creation to the handful of people blessed with membership in the sysadmins role, used for specialty troubleshooting sessions and subsequent mandatory file cleanup I'd say the article's suggestion has merit. In my environments that contain HIPAA data, this would be enabled only after that Ghost Whisperer lady put me at peace by shoving me into "the light" because saying "over my dead body" wouldn't be enough.

Also, it's pretty shallow-minded to overlook that fact that while one execution of this command may seem harmless and convenient, I look at it from the fact that I have thousands of concurrent connections 7x24. Simply "appending to a file" can run my server out of space in a heartbeat if poor choices are made. In my humble opinion, [absolutely nothing] should consume space on my server's hard drives that I don't have control over.

Sound harsh? I'm the "nice guy." My security counterparts would have a field day. Shashank would get hit harder than a bag of jellybeans at a daycare.
timothyawiseman
timothyawiseman
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3388 Visits: 920
There is nothing inherently wrong with doing file handling from within a T-SQL script when needed , and it is possible to set the security on the windows system and on the SQL Server in such a way that it would be just as secure as using .NET code. I have in fact used precisely the technique this article describes to create schema.ini files on an as needed basis in conjunction with using opendatasource to read text files on a non-production system.

While there are certain situations where this is a good technique though, it will not often be the best technique. Depending on the amount and type of information being processed there will very often be a better answer. If it is information the user will need immediately, it is probably better to return it immediately through whatever interface is used. If it is say a report meant to be picked up and read the script could be sent to e-mail it instead of leaving it in a file. If it is a large amount of information then something along the lines of DTS, SSIS, or BCP would be more appropriate.

The technique does have its place, but it is rarely the best one.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Dave Vroman
Dave Vroman
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 727
xp_cmdshell is such a dangerous extention that we've removed it from the SQL servers.
This sounds nice, but it is very dangerous for any web application.

Dave
Sr. Software Engineer - www.geeks.com
Tom Garth
Tom Garth
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2601 Visits: 1499
Samuel Clough (4/22/2008)
I'm surprised by this article. For one thing, as already stated, this stored proc should never be enabled. Secondly, why use a database to do file i/o? I wouldn't want someone writing files on my database server. It sorta smells like a hack.

To me ...


Pinch I hate infinitives.

Security is a separate issue that always must be dealt with, but there is nothing at all unsecure about using xp_cmdshell in your code.

The article is not in depth enough to make a judgement regarding whether or not using xp_cmdshell was the best method. However when weighing the pros and cons, one must always consider the maintainability of the code. It doesn't get much more maintainable than a stored procedure.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Brandon Forest
Brandon Forest
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 446
There are times when it is necessary to use xp_cmdshell, but this isn't one of them. As stated in a post above the real issue is securing credentials in the web config files. Using hashing functions is one approach that works there. Other approaches use the built in security of ASP.NET. Several good articles are:

Enhanced and Secure Connection Strings in Web.Config by Vasudevan Deepak Kumar
http://www.codeproject.com/KB/web-security/secure_connectionstrings.aspx

Security Features in ASP.NET - Authentication by Cynthia Carolina
http://www.asp101.com/articles/cynthia/authentication/default.asp

How to use the ASP.NET utility to encrypt credentials and session state connection strings.
http://support.microsoft.com/kb/329290

Brandon Forest
Mike DiRenzo
Mike DiRenzo
SSC Eights!
SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)SSC Eights! (843 reputation)

Group: General Forum Members
Points: 843 Visits: 210
Hmm, Xp_cmdShell huh? Hmmm. If it works, it works. Although....ah forget it. This one has been beat to pulp.

Thanks for the article.

-M
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search