Thank you all for your help.

If I use an input text variable, I got this error:

create procedure my_text (@input_1 varchar,

@input_2 text )

AS

BEGIN

declare ...

set @input_2...

I created a table with a column of text type.

Insert some data. BCP works fine!

Thank you very much.

Hi, putting my data into a temp table is a good idea. But I go this problem:

Because my data is pieced together from around 30 tables. After concating those data,...

I tried at client machine using cmd prompt, it works. Thank you very much.

Hi, I used wrong servername and that's why it failed. Now I get the command executed, but has a new question:

I execute the xp_cmdshell 'bcp ...' command in

sql server client...

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

Thanks, it works for me.

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

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

Thank you very much.

Do you have a way to return the result sets

to another procedure or function after

your execute the code:

exec (@SQLStr) ?

I have similar situation in my code. I...

Thanks.

I need to export data not import data. Can

Bulk Insert be used?

Hi, Thanks for the prompt reply. I just tried

the sp_dboption way.

I executed it in query analyzer, and I got this

message:

The command completed successfully.

Does this mean the option is set...

I read an article talking about the opposite part. Here is some excerpts from the article:

"The dynamic SQL approach will certainly work, but it has two downsides. The first is...

Hi, I just tried in my sql analyzer.

Should the last line of the code be:

'inner join category c on

c.parentcatid = p.categoryid' ?

I tried c.parentcatid = c.categoryid, it returns...

Or Can you do this if the column you return is fixed columns:

when you declare the sp, set the input parameters' values as null, then

passing in your parameters...