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 database performance

I'm planning on creating multiple databases on a client because I'm storing a large amount of binary data (jpg's and pdf's) in tables and there are size limitations for a single database. So, in order to get around the size limitations, I'm just creating one database for my main tables and multiple other databases to hold the binary data.

Any performance considerations I should consider in regards to multiple databases? Any other thoughts?

Hi,

what about not storing the data in the database ? Do a quick one on this document, it has some detailed information for your design:

http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||First, what size limit in SQL 2005 are you trying to avoid?

Second, as someone else pointed out, saving files in the database is not a good design. The main problem is, what do you do with them once they are there? How do you get them out? The OS is much better at saving files efficiently than SQL. Creating an "index" database into the files is a much better method.

To answer your question, it depends on your design and situation. What are you going to do with the data, how do you query the databases, etc. There is very little impact in selecting data from tables in different database on the same sever.|||I think I may have posted this in the wrong group (I am actually going to use SQL Server CE), but the 2 responses were extremely helpful. Thanks!