I don't understand it

Dear all,
I'm executing a DTS which own a sql server task that launch a stored
procedure.
This stored procedure run a WHILE sentence and then launch a bcp command
dinamically.
Well, everything works debugging that stored procedure or simply launch it
throught Query Analyzer with its parameters.
Problem come up when I launch the DTS. That sql server task works but only
launch one time the BCP command.
I know that is very difficult reach to see the exit but...
There goes an excerpt:
set @.cont = 1
Set @.iCont = (select count(*) from tblDownloadKF_Bridge_Info)
WHILE @.cont < @.iCont + 1
begin
set @.table = (select name from tblDownloadKF_Bridge_Info where ID = @.cont)
SET @.bcpCommand= 'bcp ' + @.db + '' + @.table + ' out ' + @.strPath +
Ltrim(RTrim(@.table)) + '.dat -c -S ' + @.host + ' -U ' + @.strUser +' -P '
+
@.strPassword + ''
EXEC @.result = master.dbo.xp_cmdshell @.bcpCommand
if (@.result = 0)
set @.status = 'done'
else
set @.status = 'error'
update tblDownloadKF_Bridge_Info
set Remarks = Ltrim(Rtrim(@.status)),
dateoperation = getdate()
where ID = @.cont
set @.table = ''
set @.cont = @.cont + 1
end
GO
Is there any restriction on that command via DTS'
Thanks so much,Hi Enric,
Your problem is not the BCP command but xp_cmdshell stored procedure.
"INF: How to Run a DTS Package as a Scheduled Job"
http://support.microsoft.com/defaul...kb;en-us;269074
Cristian Lefter, SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:0C0CB603-97B8-40B0-9255-D6E3E3F245B3@.microsoft.com...
> Dear all,
> I'm executing a DTS which own a sql server task that launch a stored
> procedure.
> This stored procedure run a WHILE sentence and then launch a bcp command
> dinamically.
> Well, everything works debugging that stored procedure or simply launch it
> throught Query Analyzer with its parameters.
> Problem come up when I launch the DTS. That sql server task works but only
> launch one time the BCP command.
> I know that is very difficult reach to see the exit but...
> There goes an excerpt:
> set @.cont = 1
> Set @.iCont = (select count(*) from tblDownloadKF_Bridge_Info)
> WHILE @.cont < @.iCont + 1
> begin
> set @.table = (select name from tblDownloadKF_Bridge_Info where ID = @.cont)
>
> SET @.bcpCommand= 'bcp ' + @.db + '' + @.table + ' out ' + @.strPath +
> Ltrim(RTrim(@.table)) + '.dat -c -S ' + @.host + ' -U ' + @.strUser +' -P
> ' +
> @.strPassword + ''
> EXEC @.result = master.dbo.xp_cmdshell @.bcpCommand
> if (@.result = 0)
> set @.status = 'done'
> else
> set @.status = 'error'
> update tblDownloadKF_Bridge_Info
> set Remarks = Ltrim(Rtrim(@.status)),
> dateoperation = getdate()
> where ID = @.cont
> set @.table = ''
> set @.cont = @.cont + 1
> end
> GO
>
> Is there any restriction on that command via DTS'
> Thanks so much,

I dont know how to use storedProcedure that returns one value as a result to a VB calling

My stored procedure works perfectly when I run Query Analyser, but when I run my VB program I get the eror: I get the message : An SqlParameter with ParameterName'@.total' is not contained by this SqlParameterCollection.

Here is my stored Proc and my VB program is right below

I- Stored Proc:

CREATE PROCEDURE dbo.totalsub
@.account bigint,
@.total bigint output
AS
select total=sum(SubPhnNmbr) from tblsub whereSubAccNmbr=@.account
return
GO

II- And my pogram in VB is:

Dim totsub As Int64
Dim cm As New SqlCommand
Dim cn As New MyConnection
cn.open
'my connection is defined by me don't worry about it
cm.CommandType = CommandType.StoredProcedure
cm.CommandText = "totalsub"
cm.Connection = cn
Dim pm As SqlParameter
pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Account", System.Data.SqlDbType.BigInt))
pm.Value = 100000165
pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("total", System.Data.SqlDbType.BigInt, 4))
pm.Direction = ParameterDirection.Output
totsub = cm.Parameters("total").Value
cm.ExecuteScalar()
totsub = cm.Parameters("total").Value

I also tried using @.total instead of total and I tried ParameterDirection.ReturmValue instead of ParameterDirection.Output

No Luck, someone pls can help

check the second part ofthis article.|||Thanks.