Job & Temporal tables

Hi everyone. Thanks in advance for any answer you can provide.
I got a sp with lots of temporal tables (not global, just #) and it runs ok
with osql. However when I schedule it in a job it returns an error that tell
s
me that #temp does not exist.
Someone told me that I need to create global temporary tables. Just wanna
check with you if this solution corrects the issue.It's pretty tough to tell without seeing exactly what you are doing. It is
certainly possible to create a temp table and use it from a scheduled job.
Can you post the actual code?
Andrew J. Kelly SQL MVP
"popootts" <popootts@.discussions.microsoft.com> wrote in message
news:3066A7C0-76AA-4521-8E76-2DE678091F17@.microsoft.com...
> Hi everyone. Thanks in advance for any answer you can provide.
> I got a sp with lots of temporal tables (not global, just #) and it runs
> ok
> with osql. However when I schedule it in a job it returns an error that
> tells
> me that #temp does not exist.
> Someone told me that I need to create global temporary tables. Just wanna
> check with you if this solution corrects the issue.|||>> I got sp with lots of temporal tables (not global, just #) .. <<
I think you meant TEMPORARY tables :)
And in the non-standard, non-portable T-SQL syntax, they disappear
after the module in whcih they were created closes.
wanna check with you if this solution corrects the issue. <<
That will work, but it is crappy programming. What you really want to
do it write is standard, portable code that uses derived tables, VIEWs
or simple queries.
Temp tables are a sign that the approach you are using is based on
procedural steps and not a declarative approach. Basically, in the
1960's each temp table would have been a scratch tape in a COBOL
program.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications as to what you are actually trying to do.|||Hi again and thanks for the answer.
Here's a piece of code (too long to copy it all):
****************************************
***
CREATE procedure user_dbo.test_statistics @.p_date datetime
as
create table #tm0
(qid int, qmsg varchar(100), qdate datetime)
create table #tm1
(
qid int, qdate datetime, qcat varchar(9), qno int, qct int
)
insert into #tm1
select did, ddate, dcat, dno, dct from
all_values where ddate=dateadd(day,-4,@.p_date)
... more code...
****************************************
**************
then, when I execute this on osql it runs ok, however scheduled on a job it
returns the error thar #tmp1 does not exist.
Any help will be appreciated.
Regards
"popootts" wrote:

> Hi everyone. Thanks in advance for any answer you can provide.
> I got a sp with lots of temporal tables (not global, just #) and it runs o
k
> with osql. However when I schedule it in a job it returns an error that te
lls
> me that #temp does not exist.
> Someone told me that I need to create global temporary tables. Just wanna
> check with you if this solution corrects the issue.|||See if this helps:
http://tinyurl.com/67hsr
-oj
"popootts" <popootts@.discussions.microsoft.com> wrote in message
news:2BFDDE44-4932-45F0-9148-49F75C953239@.microsoft.com...
> Hi again and thanks for the answer.
> Here's a piece of code (too long to copy it all):
> ****************************************
***
> CREATE procedure user_dbo.test_statistics @.p_date datetime
> as
> create table #tm0
> (qid int, qmsg varchar(100), qdate datetime)
> create table #tm1
> (
> qid int, qdate datetime, qcat varchar(9), qno int, qct int
> )
> insert into #tm1
> select did, ddate, dcat, dno, dct from
> all_values where ddate=dateadd(day,-4,@.p_date)
> ... more code...
> ****************************************
**************
> then, when I execute this on osql it runs ok, however scheduled on a job
> it
> returns the error thar #tmp1 does not exist.
> Any help will be appreciated.
> Regards
>
> "popootts" wrote:
>|||Oj, thanks for the suggestion. Everything is normal on that side.
in my Sql Server EM I can schedule the job and run it without problems.
However in other EM (where the prod. version is in) when they check semantic
s
they get the error.
Since the temporary table is created while the sesion is running I don't
get why if fails on the syntax check. It makes no sense.
Thanks for any help .
"oj" wrote:

> See if this helps:
> http://tinyurl.com/67hsr
>
> --
> -oj
>
> "popootts" <popootts@.discussions.microsoft.com> wrote in message
> news:2BFDDE44-4932-45F0-9148-49F75C953239@.microsoft.com...
>
>