INSERT Statement/Temp Tables/Stored Procs with Parameters

INSERT Statement/Temp Tables/Stored Procs with Parameters

Post by Matt Thalma » Sat, 09 Sep 2000 03:36:00



Ok...


the temporary table to insert data into.  I've created a temp table before
executing the stored proc using Query Analyzer and have verified its
existence with sp_tables.  Now when I execute the stored proc it contains
the statements



...
...


What's going on?

-Matt Thalman

 
 
 

INSERT Statement/Temp Tables/Stored Procs with Parameters

Post by Carlos Eduardo Roja » Sat, 09 Sep 2000 04:00:40


You have to use a dynamic query inside your stored procedure.
i.e.

Beware that the user that executes the stores procedure, in addition to have
execute permissions on the stored procedure, would have to have permissions
on the objects referenced by the dynamic query. In your case, you wouldn't
need to set permissions because temporary objects (objects in tempdb) don't
have any kind of security.
--
Hope this helps,,
---------------------------------------------------
Carlos Eduardo Rojas
MCSE+I, MCDBA, MCSS


> Ok...


> the temporary table to insert data into.  I've created a temp table before
> executing the stored proc using Query Analyzer and have verified its
> existence with sp_tables.  Now when I execute the stored proc it contains
> the statements



> ...
> ...


> What's going on?

> -Matt Thalman


 
 
 

INSERT Statement/Temp Tables/Stored Procs with Parameters

Post by Matt Thalma » Sat, 09 Sep 2000 04:16:46


Ok, that helps.  I'm getting some weird errors now.  This is what I'm
executing...



> You have to use a dynamic query inside your stored procedure.
> i.e.

> Beware that the user that executes the stores procedure, in addition to
have
> execute permissions on the stored procedure, would have to have
permissions
> on the objects referenced by the dynamic query. In your case, you wouldn't
> need to set permissions because temporary objects (objects in tempdb)
don't
> have any kind of security.
> --
> Hope this helps,,
> ---------------------------------------------------
> Carlos Eduardo Rojas
> MCSE+I, MCDBA, MCSS



> > Ok...


of
> > the temporary table to insert data into.  I've created a temp table
before
> > executing the stored proc using Query Analyzer and have verified its
> > existence with sp_tables.  Now when I execute the stored proc it
contains
> > the statements



> > ...
> > ...

> > But I receive this error when executing:  Invalid object name

> > What's going on?

> > -Matt Thalman

 
 
 

INSERT Statement/Temp Tables/Stored Procs with Parameters

Post by Matt Thalma » Sat, 09 Sep 2000 04:20:25


Whoops.  Accidentally sent that last one too soon.  This is what I'm
executing..

EXEC('

     SELECT *
     FROM EQUIP_LIST WHERE (RACK_POS_MAJOR + RACK_POS_MINOR) NOT IN
          (SELECT (RACK_POS_MAJOR + RACK_POS_MINOR) FROM EQUIP_CHECK_OUT

END_DATE)
          )
')

I receive this error...

Server: Msg 207, Level 16, State 3, Line 2
Invalid column name '?'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name '?'.


variable which is a datetime type.  How do I insert that variable correctly
into this statement?

-Matt Thalman



> You have to use a dynamic query inside your stored procedure.
> i.e.

> Beware that the user that executes the stores procedure, in addition to
have
> execute permissions on the stored procedure, would have to have
permissions
> on the objects referenced by the dynamic query. In your case, you wouldn't
> need to set permissions because temporary objects (objects in tempdb)
don't
> have any kind of security.
> --
> Hope this helps,,
> ---------------------------------------------------
> Carlos Eduardo Rojas
> MCSE+I, MCDBA, MCSS



> > Ok...


of
> > the temporary table to insert data into.  I've created a temp table
before
> > executing the stored proc using Query Analyzer and have verified its
> > existence with sp_tables.  Now when I execute the stored proc it
contains
> > the statements



> > ...
> > ...

> > But I receive this error when executing:  Invalid object name

> > What's going on?

> > -Matt Thalman

 
 
 

INSERT Statement/Temp Tables/Stored Procs with Parameters

Post by Carlos Eduardo Roja » Sat, 09 Sep 2000 09:12:25



--
Hope this helps,,
---------------------------------------------------
Carlos Eduardo Rojas
MCSE+I, MCDBA, MCSS


> Whoops.  Accidentally sent that last one too soon.  This is what I'm
> executing..

> EXEC('

>      SELECT *
>      FROM EQUIP_LIST WHERE (RACK_POS_MAJOR + RACK_POS_MINOR) NOT IN
>           (SELECT (RACK_POS_MAJOR + RACK_POS_MINOR) FROM EQUIP_CHECK_OUT

<=
> END_DATE)
>           )
> ')

> I receive this error...

> Server: Msg 207, Level 16, State 3, Line 2
> Invalid column name '?'.
> Server: Msg 207, Level 16, State 1, Line 2
> Invalid column name '?'.


> variable which is a datetime type.  How do I insert that variable
correctly
> into this statement?

> -Matt Thalman



> > You have to use a dynamic query inside your stored procedure.
> > i.e.

> > Beware that the user that executes the stores procedure, in addition to
> have
> > execute permissions on the stored procedure, would have to have
> permissions
> > on the objects referenced by the dynamic query. In your case, you
wouldn't
> > need to set permissions because temporary objects (objects in tempdb)
> don't
> > have any kind of security.
> > --
> > Hope this helps,,
> > ---------------------------------------------------
> > Carlos Eduardo Rojas
> > MCSE+I, MCDBA, MCSS



> > > Ok...


name
> of
> > > the temporary table to insert data into.  I've created a temp table
> before
> > > executing the stored proc using Query Analyzer and have verified its
> > > existence with sp_tables.  Now when I execute the stored proc it
> contains
> > > the statements



> > > ...
> > > ...

> > > But I receive this error when executing:  Invalid object name

> > > What's going on?

> > > -Matt Thalman

 
 
 

1. stored procs & temp tables

when trying this, I get a 'source does dot exist' error (msg 51001). This is
happening on the line prior to return(0). when i replace the 'exec sp_Xtab'
line with 'select * from tempdb..test', I get a good resultset. what am I
doing wrong?? thanks in advance.

Create Procedure sp_TestTemp

as
 drop table tempdb..test
select dateadd(dd, 0, datename(mm,todaysdate) + '1 ' + convert(char(4),
datepart(yy,todaysdate))) as intMoYr, division, sum(rentals) as lngRentals
 into tempdb..test
 from tbltraffic

 group by dateadd(dd, 0, datename(mm,todaysdate) + '1 ' + convert(char(4),
datepart(yy,todaysdate))), division
exec sp_Xtab intmoyr, division, lngrentals, tempdb..test, 1

return (0)

2. Any way to send a 64 K email with CDOSYS?

3. stored procs and temp tables

4. Forms only

5. Stored Procs, unique temp Tables, and Good Design Practice

6. How can I insert an image into a SQL-server Table?

7. Temp tables, stored procs and ASP

8. Backup and Restore Question

9. rdo-stored procs w/temp tables

10. Stored Procs and Temp Tables

11. Global temp tables and stored procs

12. stored procs & temp tables