April 4, 2002 at 7:17 pm
Hi, I had never used bcp before. So I got a lot of questions to ask about bcp.
First, how to run bcp inside my user-defined
sp?
Second, can I run it at application level, say,
in my perl code level?
Third, how fast will it be? Can I finish a bcp
job within 10 seconds for exporting around 5000 records as ASCII text file from a nonindexed temp table?
Forth, Can I format the output file, using ',' to separate each column when running bcp?
Thanks in advance.
April 4, 2002 at 8:16 pm
1) You can use master..xp_cmdshell 'bcp strings here', but look at BULK INSERT instead as it does the same thing but lives in SQL, no shelling needed.
2) If you can shell then yes, I know perl is able to but I don't do much perl and bcp is an app, but if not then execute a BULK INSERT thru sql connection.
3) Depends, should be fairly fast based on what you stated.
4) Yes, see bcp utility in SQL BOL on -t field_term argument
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 4, 2002 at 8:38 pm
Thanks.
I need to export data not import data. Can
Bulk Insert be used?
April 4, 2002 at 8:40 pm
Sorry, did not understand this and that answer is no.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 4, 2002 at 8:49 pm
xp_cmdshell 'bcp xxxx'
see BOL for the xxx options.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 4, 2002 at 8:54 pm
Can I use xp_cmdsehll inside my stored procedure?
The book(Microsfot SQL Server 2000 DBA Survival Guide) p 409 says:
'Do not use xp_cmdshell to call BCP from within a user-defined transaction in a stored procedure. Doing so can lead to endless blocking!'
What is the best approach for me to do the data exporting?
Thanks in advance.
April 4, 2002 at 9:00 pm
So let me ask this. Are you needing to do this per transaction or timed. If timed then you might want to look at DTS with a schedule anyway, but you can use bcp if you wish. If per transaction then you get into issues with any called method in the realm of possible blocking. Sorry I cannot think what may best help you there.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 5, 2002 at 11:30 am
Yes, it is like a transaction.
Our software is web-based. Once the user clicks
on the exporting data button, it triggers an application function to call backend database
function to prepare the data ( get data from
various columns among 30 tables ), and format
the data (csv format) and send the data back to the web.
I did this piece for oracle database. Normally it takes around 15 secs for exporting around 5000 records ( pieced together from 30 tables)
from the user clicking on the button in UI to
the data returned to the user.
Now, we need to port our code from oracle to sql server, and I couldn't figure out a good way to achieve the same task.
Any recommondations and helps are welcome.
April 5, 2002 at 12:13 pm
I'd use a queueing method, not a trigger to do this. Setup your trigger to insert a value in a table that you need. Then setup a DTS package or stored proc to do the export. Run this every xx minutes and read the table to see if you need to export something. Then export it.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 5, 2002 at 12:21 pm
Or even then you could create a job for the DTS package that is not enabled and issue a sp_start_job to kick it off in a trigger (but you will have to handle errors from trying to start an already active job). This way it happens closer to when a transaction takes place. And to ensure catches as much as possible enable the job with a run like Steve says but not too often and use sp_start_job to augment the frequency.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 8, 2002 at 12:23 pm
In my previous reply, I used the word 'triggers', all I mean is once the frontend user
click on getexport button, the action 'fires'
a function call to the db part to get the data back to the server. In oracle, I didn't use any
trigger. All the transactions need to be 'real time', something like a cron job to check exporting request will still cause some time delay. I plan to do this: a frontend button action will fire a function call to a stored procedure in db. The stored procedure will prepare the data and send the data back to the application server.
The central issue is:
which method I should use to send the data back: bcp, dts, or anything else.
I read from book that when running bcp, the bcp will set endless locks on db. That's not what I want. Because when once client is running exporting, some other clients might also be doing something else.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply