Monday, March 12, 2012

Problem of running sp through VB

Hi,
I have a sp running well manually in SQL analyzer. This sp is using BCP to
archive data from SQL tables to datafiles.
The problem is if the sp is run through VB and a table has lot of data, the
process will stop after exec bcp(datafile is created) and doesn't go through
other part of the sp. VB part doesn't get any error. If all the tables dealt
with don't have too many data, it works well.
Any suggesting will be appreciatedYou might want to increase your CommandTimeout to greater than 30 seconds.
-oj
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:7E219AF2-2036-4D10-8FCD-029D22A9B367@.microsoft.com...
> Hi,
> I have a sp running well manually in SQL analyzer. This sp is using BCP to
> archive data from SQL tables to datafiles.
> The problem is if the sp is run through VB and a table has lot of data,
> the
> process will stop after exec bcp(datafile is created) and doesn't go
> through
> other part of the sp. VB part doesn't get any error. If all the tables
> dealt
> with don't have too many data, it works well.
> Any suggesting will be appreciated|||I set CommandTimeout = 0. If it's timeout error, VB will get error message,
right?
"oj" wrote:

> You might want to increase your CommandTimeout to greater than 30 seconds.
> --
> -oj
>
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:7E219AF2-2036-4D10-8FCD-029D22A9B367@.microsoft.com...
>
>|||Yes. "0" means indefinitely. Also, be aware that SQL will wait indefinitely
for any external calls via xp_cmdshell until it returns. If there is
something wrong at the system level and xp_cmdshell hangs, SQL would not
know about it.
-oj
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:E7298BD1-4236-42CC-9245-34BBA05FA5B0@.microsoft.com...
>I set CommandTimeout = 0. If it's timeout error, VB will get error message,
> right?
> "oj" wrote:
>|||xp_cmdshell works fine because datafile was created. However it stop after
that without any error.
"oj" wrote:

> Yes. "0" means indefinitely. Also, be aware that SQL will wait indefinitel
y
> for any external calls via xp_cmdshell until it returns. If there is
> something wrong at the system level and xp_cmdshell hangs, SQL would not
> know about it.
> --
> -oj
>
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:E7298BD1-4236-42CC-9245-34BBA05FA5B0@.microsoft.com...
>
>|||Can you use BULK INSERT instead BCP?
AMB
"Tom" wrote:
> xp_cmdshell works fine because datafile was created. However it stop after
> that without any error.
> "oj" wrote:
>|||Tom,
Try downloading ntpmon/ntfilmon from sysinternals to see what's going. Also,
use "-e" flag of bcp to capture any eror.
-oj
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:4BCB7A0C-25F7-4FEE-9495-09D58D796E5F@.microsoft.com...
> xp_cmdshell works fine because datafile was created. However it stop after
> that without any error.
>|||Can I use BULK INSERT to copy data from SQL table to datafile?
"Alejandro Mesa" wrote:
> Can you use BULK INSERT instead BCP?
>
> AMB
> "Tom" wrote:
>|||Sorry, thought you were importing.
AMB
"Tom" wrote:
> Can I use BULK INSERT to copy data from SQL table to datafile?
> "Alejandro Mesa" wrote:
>|||There is no any error in error file. I wander if there is any difference
between running a sp in SQL query analyzer and through VB.
"oj" wrote:

> Tom,
> Try downloading ntpmon/ntfilmon from sysinternals to see what's going. Als
o,
> use "-e" flag of bcp to capture any eror.
> --
> -oj
>
> "Tom" <Tom@.discussions.microsoft.com> wrote in message
> news:4BCB7A0C-25F7-4FEE-9495-09D58D796E5F@.microsoft.com...
>
>

No comments:

Post a Comment