BCP question

Question

BCP question

Larry Gilbert

Grasshopper

Points: 14
More actions
September 7, 2007 at 3:47 pm

#178334

I have 6 sql tables that I need to generate into text files. The product I'm working on will be a commerical product. I don't want to install server studio etc. on client PCs. I have a dynamic sql that shells out a bcp command - no sweat - except that when I try to use non-sysadm user I get into the std permission problem (which I'm still resolving). I also am getting concerned that I may get push-back from client's dbas for giving user accts xp_cmd_shell authority. Is there any other way to generate text files from tables that will be relatively fast - without putting an sql console on the client's pc. I keep thinking that theres got to be an easier way than writing a vb program to read/write tables!

thanks

Viewing 3 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 1

BCP from Command Prompt itself (batch file) or Proxy user to run a job that has privs. Guess you could also use DTS if you really had to...

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Larry Gilbert Grasshopper Points: 14 More actions · Answer 2

thanks. I explored a couple of different issues. I had trouble getting bcp to work on a client pc. The client pc had sql native client on it. I couldn't find any docs on how to get bcp to work on a client PC short of doing a server managemnt installation. There might be - it just isn't clearly documented. I also tried sqldmod using the odbcbulkcopy. I got that to work but I had to make permission changes but more importantly it was like watching paint dry - so slow - I also had a rough time finding docs on it for the property and methods.

In the end I made a simple write_text routine that first finds the number of fields in the schema of the table, writes a header record and then writes to the records out. It ended up being the most flexible and at least on my sample size, as fast as bcp. My goal has been to make the product simple to install and setup and the dumbest way seemed to be the best way. I can't believe that there's a sql bulkinsert but not a sql bulkexport.

Thanks for your interest.