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


BCP Utility to output contents into CSV


BCP Utility to output contents into CSV

Author
Message
bikram.g.it
bikram.g.it
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 120
Hi Guys,

Thanks for all your suggestions.

I have spotted the problem - it is nothing to do with the permission, bcp, cmdshell, sybase (or anything as thought about ......)

What it turns out to be is @sql contains LF \ CR and xp_cmdshell doesn't want to troop along with this.....w00tAngryCrazy

However surprisingly when passed the same query directly into xp_cmdshell it worked fine.

So for the purpose of the topic is not resolved. But I will carry on in this voyage of discovery as to why it is so fussy about the specials chars in @sql unless you guys already know it!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)

Group: Administrators
Points: 247619 Visits: 19791
Thanks for the update. I'm not sure I would have thought of that, but a great catch.

Did you use some type of hex editor to find this?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
bikram.g.it
bikram.g.it
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 120
Cheers! I just wanted to format the ugly looking SQL in @sql while someone was watching over my shoulder and then I had a light bulb moment - that it could be a possibility that BCP / cmdshell is having trouble having those LFs...... and there was the result.
sqldriver
sqldriver
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3892 Visits: 2547
Wouldn't you have to do something like:


EXECUTE @RESULT = 'MASTER..xp_cmdshell '+ @sql +'_1, NO_OUTPUT'



or


SET @RESULT = EXECUTE MASTER..xp_cmdshell [sql]_1, NO_OUTPUT
set @result = replace(@result, '[sql]', '@sql')
exec @result


Sigerson
Sigerson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1567 Visits: 1239
This is what has worked for me. This code is embedded in a S/P and runs on a schedule and gives me a CSV like clockwork every night. Needs no tweaking of permissions or access, other than turning xp_cmdshell ON and OFF. In addition, bcp is itself used to fire a S/P, not just a static query. Note that the stored procedure name can also be parameterized and works fine. I just leave in plain text for maintainability.


--turn xp_cmdshell ON as I leave them off by default:
EXECUTE master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

SET @SQL = 'bcp "exec [WorkflowV3].[dbo].aStatusExtractDataCSV" queryout "' + @destFolder + @fileName + '" -t \, -T -c'

EXECUTE master.dbo.xp_cmdshell @SQL

--and then turn xp_cmdshell back OFF for security's sake
EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE
EXECUTE master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE



Good luck!

Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)

Group: General Forum Members
Points: 377356 Visits: 42915
Sigerson (5/23/2013)
Needs no tweaking of permissions or access, other than turning xp_cmdshell ON and OFF.


I'm just curious... why do you turn xp_CmdShell OFF? Since anyone (including an attacker) with "SA" privs can turn it on, there's no non-trivial benefit to turning it off. Since only "SA"s can use it to begin with (unless you've made the horrible mistake of granting someone a proxy), there's also no non-trivial benefit to turning it off.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sigerson
Sigerson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1567 Visits: 1239
I turn it off just because I've read that since it's OFF by default at installation, it's best to leave it that way. Also (I read) having it on is a security risk. So keeping it off just seems like basic housekeeping (?) No one ever logs in as 'sa'.

I'm a sole developer not a DBA. But I'd like to understand this idea of proxies more b/c of another problem I'm having with bcp. You posted to my thread a few days ago, right after Lowell did. Link: http://www.sqlservercentral.com/Forums/FindPost1454773.aspx

We have a client-server system, front-end written in javascript. All the corporate users have user records in SQL Server under their own names, which I believe the app inserts automatically. But the app itself connects to the d/b and does all its work under it's own login, let's call it 'GroverSQL'. So when the CSV file is created by my s/p, the Windows owner shows up as "[myDomain]\GroverSQL." Is this what you mean by proxy?

My problem in the other thread is that while I can get my bcp s/p to fire from SQL Agent, when I try and run the same s/p from withing a javascript ASP call from the front end, it fails with a permissions error. All members of Domain Users have inherited Write permissions to the CSV's destination folder. So I'm getting a permissions problem having a user unable to run a s/p from the front end, but GroverSQL has no trouble executing the s/p at all.

I'm hoping the concept of a proxy might be useful here. Or am I way off track?

Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Sigerson
Sigerson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1567 Visits: 1239
Forgot my other question regarding the 'horrible mistake' of granting a proxy. I don't know that I did that and wouldn't want to, but I just don't know. I log into the SQL Server instance with a user with Domain Admin privileges. Is that bad?

Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)

Group: General Forum Members
Points: 377356 Visits: 42915
@Sigerson,

My apologies for the long overdue response. I lost track of this thread.

The type of "proxy" that I was talking about isn't like the fact that multiple people login to an application and the application talks to the database with its own login (although I do have an appreciation for Developers that actually pass user information through the app especially when the app writes to the database). The type of proxy I'm talking about is where an individual user is granted privs to run xp_CmdShell directly even though that user does not have "SA" privs.

The reason why such a proxy is a terrible mistake is because the user isn't using xp_CmdShell within the confines of their own login privs. Rather, xp_CmdShell will operate as the login for the SQL Service itself, which frequently has extraordinary privs across the domain (another mistake... the SQL Server Service login should also be limited). That means that the unprivileged user can use xp_CmdShell to (for example) see files and other databases that they're not supposed to. It also means that they can modify or even delete data/files and the only trace that may (provided there is any auditing) be left behind is that the SQL Server Service login did it.

My take on the use of xp_CmdShell is that only those people with "SA" privs should be allowed to use it directly and only DBAs should have "SA" privs and then only the most trusted ones. NO application should ever have "SA" or even "DBO" privs. Everyone else (including apps) should only be allowed to do what a stored procedure will allow them to do (which can include xp_CmdShell functionality if done properly). I also believe that well protected processes that do things like calling BCP using CmdExec tasks in jobs or using xp_CmdShell in properly protected stored procedures is just fine.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sigerson
Sigerson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1567 Visits: 1239
@Jeff:

Thanks for the information, it answered a lot of my questions. I'm an accidental DBA at best. I never grant individual users any special privs so I don't think I'm in any jeopardy here. No users can execute xp_cmdshell except through an application s/p, and I always disable it in the s/p code as soon as I can.

Anyway, thanks again for the confirmation.

Sigerson

"No pressure, no diamonds." - Thomas Carlyle
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