"ED P" <anonymous@discussions.microsoft.com> wrote in message
news:23de601c45ecf$b7bbb5b0$a401280a@phx.gbl...
> I'm relatively new to DTS and am having what seems like a
> very basic problem. I have simplified what I am trying
> to do down to the following:
>
> I create a global variable called gvRosterID as an int
> with a default value of 10. Then I create a connection
> and then and an associated ExecuteSQL Task with two lines
> of SQL:
> insert into egprosters values(1)
> SELECT 99 as RosterID
>
> I then set the Output Parameter RosterID to the global
> variable gvRosterID and save the package.
>
> Now I execute the package. A single row with a single
> field value of 1 gets inserted into egprosters. But the
> Global Variable is still 10.
>
> Here's the odd thing. When I comment out the first line
> (the insert), the global variable DOES get set to 99.
> Or, when I move the insert statement to after the select,
> the insert still occurs and the global variable does get
> set to 99. Or, if I put this all inside a stored proc
> and call the proc from the Execute SQL task, the behavior
> is exactly the same - if the insert is before the select
> inside the stored proc, the global variable does not get
> set, otherwise it does.
>
> I have tried checking and unchecking transactions in the
> package and the stored proc, tried changing variable and
> parameter names, etc. Doing an update or delete instead
> of the insert causes the same problem. What I am
> actually trying to do is slightly different than the
> above, but this is the simplest example of the error.
> What I am trying to do is do an insert (subject to a few
> SQL checks), return the identity value of the inserted
> row, and pass that value to the next task, which is a
> Transform Data task.
>
> Any thoughts would be welcome!
>
> - Ed Parrot

>-----Original Message-----
>What happens if you issue
>
>SET NOCOUNT ON
>
>as the first line in the batch?
>
>Why not split the statements in the ExecuteSQL task into
> 1 Task?
>
>--
>
>Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>www.SQLDTS.com - The site for all your DTS needs.
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>"ED P" <anonymous@discussions.microsoft.com> wrote in
message
>news:23de601c45ecf$b7bbb5b0$a401280a@phx.gbl...
>> I'm relatively new to DTS and am having what seems
like a
>> very basic problem. I have simplified what I am trying
>> to do down to the following:
>>
>> I create a global variable called gvRosterID as an int
>> with a default value of 10. Then I create a connection
>> and then and an associated ExecuteSQL Task with two
lines
>> of SQL:
>> insert into egprosters values(1)
>> SELECT 99 as RosterID
>>
>> I then set the Output Parameter RosterID to the global
>> variable gvRosterID and save the package.
>>
>> Now I execute the package. A single row with a single
>> field value of 1 gets inserted into egprosters. But
the
>> Global Variable is still 10.
>>
>> Here's the odd thing. When I comment out the first
line
>> (the insert), the global variable DOES get set to 99.
>> Or, when I move the insert statement to after the
select,
>> the insert still occurs and the global variable does
get
>> set to 99. Or, if I put this all inside a stored proc
>> and call the proc from the Execute SQL task, the
behavior
>> is exactly the same - if the insert is before the
select
>> inside the stored proc, the global variable does not
get
>> set, otherwise it does.
>>
>> I have tried checking and unchecking transactions in
the
>> package and the stored proc, tried changing variable
and
>> parameter names, etc. Doing an update or delete
instead
>> of the insert causes the same problem. What I am
>> actually trying to do is slightly different than the
>> above, but this is the simplest example of the error.
>> What I am trying to do is do an insert (subject to a
few
>> SQL checks), return the identity value of the inserted
>> row, and pass that value to the next task, which is a
>> Transform Data task.
>>
>> Any thoughts would be welcome!
>>
>> - Ed Parrot
>
>
>.