Multiple Job steps using same variable or one temp table

I need a constant to be available in multiple steps of a job (more
specifically, getdate() from step one). I tried storing it in a variable or
temp table but when I get to step two of the job and try to retrieve the
value, I get "must declare variable..." or "table does not exist" (depending
on which method I am testing).
I did a little test and the SPID in each one of the steps is the same so
shouldn't the temp table be accessible from all steps?
A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
table?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>I need a constant to be available in multiple steps of a job (more
> specifically, getdate() from step one). I tried storing it in a variable or
> temp table but when I get to step two of the job and try to retrieve the
> value, I get "must declare variable..." or "table does not exist" (depending
> on which method I am testing).
> I did a little test and the SPID in each one of the steps is the same so
> shouldn't the temp table be accessible from all steps?
|||Initially, my main reason for not using a permanent table is that I will need
to drop it at the end and if step 2 fails and the table gets dropped in step
3 then it won't get dropped.
I could modify step 2 to continue on to step 3 even if it fails but then
the job will show as completed succesfully even if step two failed.
Maybe I will just use a permanent table and keep it so that I don't need to
drop it at the end...
"Tibor Karaszi" wrote:

> A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
> number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
> table?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>
>
|||> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
That is what I would do.

> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed successfully even if step two failed.
Have two finishing steps. If step 2 fails, goto step 4 which drops the table and returns fails
status whether step 4 is OK or not...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:FBFC6287-C3C4-47C3-A58F-9AF3B373E3D7@.microsoft.com...[vbcol=seagreen]
> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed succesfully even if step two failed.
> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
> "Tibor Karaszi" wrote:

Multiple Job steps using same variable or one temp table

I need a constant to be available in multiple steps of a job (more
specifically, getdate() from step one). I tried storing it in a variable or
temp table but when I get to step two of the job and try to retrieve the
value, I get "must declare variable..." or "table does not exist" (depending
on which method I am testing).
I did a little test and the SPID in each one of the steps is the same so
shouldn't the temp table be accessible from all steps?A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
table?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>I need a constant to be available in multiple steps of a job (more
> specifically, getdate() from step one). I tried storing it in a variable or
> temp table but when I get to step two of the job and try to retrieve the
> value, I get "must declare variable..." or "table does not exist" (depending
> on which method I am testing).
> I did a little test and the SPID in each one of the steps is the same so
> shouldn't the temp table be accessible from all steps?|||Initially, my main reason for not using a permanent table is that I will need
to drop it at the end and if step 2 fails and the table gets dropped in step
3 then it won't get dropped.
I could modify step 2 to continue on to step 3 even if it fails but then
the job will show as completed succesfully even if step two failed.
Maybe I will just use a permanent table and keep it so that I don't need to
drop it at the end...
"Tibor Karaszi" wrote:
> A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
> number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
> table?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
> >I need a constant to be available in multiple steps of a job (more
> > specifically, getdate() from step one). I tried storing it in a variable or
> > temp table but when I get to step two of the job and try to retrieve the
> > value, I get "must declare variable..." or "table does not exist" (depending
> > on which method I am testing).
> >
> > I did a little test and the SPID in each one of the steps is the same so
> > shouldn't the temp table be accessible from all steps?
>
>|||> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
That is what I would do.
> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed successfully even if step two failed.
Have two finishing steps. If step 2 fails, goto step 4 which drops the table and returns fails
status whether step 4 is OK or not...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:FBFC6287-C3C4-47C3-A58F-9AF3B373E3D7@.microsoft.com...
> Initially, my main reason for not using a permanent table is that I will need
> to drop it at the end and if step 2 fails and the table gets dropped in step
> 3 then it won't get dropped.
> I could modify step 2 to continue on to step 3 even if it fails but then
> the job will show as completed succesfully even if step two failed.
> Maybe I will just use a permanent table and keep it so that I don't need to
> drop it at the end...
> "Tibor Karaszi" wrote:
>> A profiler trace reveals that Agent does a logout and login. What you are seeing is the same spid
>> number being reused. As Agent won't execute the same job simultaneously, why not use a permanent
>> table?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
>> news:0B9550DB-C93F-42FD-AB5D-670049929375@.microsoft.com...
>> >I need a constant to be available in multiple steps of a job (more
>> > specifically, getdate() from step one). I tried storing it in a variable or
>> > temp table but when I get to step two of the job and try to retrieve the
>> > value, I get "must declare variable..." or "table does not exist" (depending
>> > on which method I am testing).
>> >
>> > I did a little test and the SPID in each one of the steps is the same so
>> > shouldn't the temp table be accessible from all steps?
>>

multiple column values into a single value

hi all,
I am trying to take all the values from a single varchar column and to inset
them into a variable so that they are seperated with the '-' sign.
I am trying to avoid cursors.
by the way- all the rows have a column with the same value in the table
for example:
create table test (col1 int, col2 varchar(20))
insert test values(1,'aaa')
insert test values(1,'bbb')
insert test values(1,'ccc')
the result sould look like this 'aaa-bbb-ccc'
thaks,
alonA cursor/iterative solution is best suited for this.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:BEB95AAB-5C04-4466-B940-B00AAAAE0DE3@.microsoft.com...
> hi all,
> I am trying to take all the values from a single varchar column and to
> inset
> them into a variable so that they are seperated with the '-' sign.
> I am trying to avoid cursors.
> by the way- all the rows have a column with the same value in the table
> for example:
> create table test (col1 int, col2 varchar(20))
> insert test values(1,'aaa')
> insert test values(1,'bbb')
> insert test values(1,'ccc')
> the result sould look like this 'aaa-bbb-ccc'
> thaks,
> alon|||create table #test (col1 int, col2 varchar(20))
insert #test values(1,'aaa')
insert #test values(1,'bbb')
insert #test values(1,'ccc')
DECLARE @.concat VARCHAR(100)
SET @.concat=''
SELECT
CASE WHEN @.concat='' THEN '' ELSE '-' END + col2
FROM #test
DROP TABLE #Test
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"alon" <alon@.discussions.microsoft.com> wrote in message
news:BEB95AAB-5C04-4466-B940-B00AAAAE0DE3@.microsoft.com...
> hi all,
> I am trying to take all the values from a single varchar column and to
> inset
> them into a variable so that they are seperated with the '-' sign.
> I am trying to avoid cursors.
> by the way- all the rows have a column with the same value in the table
> for example:
> create table test (col1 int, col2 varchar(20))
> insert test values(1,'aaa')
> insert test values(1,'bbb')
> insert test values(1,'ccc')
> the result sould look like this 'aaa-bbb-ccc'
> thaks,
> alon|||Maybe this can help:
http://milambda.blogspot.com/2005/0...s-as-array.html
Of course you'll need to change it to meet your specific needs.
ML|||So you want to destroy First Normal Form (1NF) with a proprieetary
kludge? In a tiered archtiecture, display is done inthe front end and
not in the database. This is far more basic than just SQL programming.|||http://www.aspfaq.com/2529
"alon" <alon@.discussions.microsoft.com> wrote in message
news:BEB95AAB-5C04-4466-B940-B00AAAAE0DE3@.microsoft.com...
> hi all,
> I am trying to take all the values from a single varchar column and to
> inset
> them into a variable so that they are seperated with the '-' sign.
> I am trying to avoid cursors.
> by the way- all the rows have a column with the same value in the table
> for example:
> create table test (col1 int, col2 varchar(20))
> insert test values(1,'aaa')
> insert test values(1,'bbb')
> insert test values(1,'ccc')
> the result sould look like this 'aaa-bbb-ccc'
> thaks,
> alon